Updating with ADO.net - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Updating with ADO.net

Author

Topic

Chuck596
Starting Member

2 Posts

Posted - 2008-03-25 : 11:47:07

Hey

I have an ASP application that uses a stored procedure and ADO.net to update a sql server data file. The problem is I know the code is working, I don't have any errors with the ADO, no exceptions are caught. I can use the same basic code to insert a record using a different procedure. It is the update procedure that does not carry through.

So, I know I have a connection, the procedure works using the query builder directly so the procedure works, but when I run the code, I get no errors and no update to the datafile. I am not even sure how to trouble shoot this since I don't have an error to look up.

C# Code:-------------

private void UpdateIssue()
{
DateTime date = new DateTime();
date = Convert.ToDateTime(this.txtDate.Text);

//edit record in HelpDeskIssuesTbl here.

SqlConnection con = new SqlConnection("Data Source...");
SqlCommand comUpdateTicket = new SqlCommand("sp_UpdateHelpDeskIssues", con);
comUpdateTicket.CommandType = CommandType.StoredProcedure;
comUpdateTicket.Parameters.Add("@IssueID", this.GridView1.SelectedIndex.ToString());
comUpdateTicket.Parameters.Add("@EmpID", this.ddlEmployee.SelectedValue.ToString());
comUpdateTicket.Parameters.Add("@Date", date.ToShortDateString());
comUpdateTicket.Parameters.Add("@StatusID", this.ddlStatus.SelectedValue.ToString());

try
{
con.Open();
comUpdateTicket.ExecuteNonQuery();

}
catch (Exception ex)
{
this.lblMessage.Text = "Data save error: " + ex.Message.ToString();
this.pnlMessage.Visible = true;
}
finally
{
con.Close();
}
}

----------------------------------------------------
dbo.sp_UpdateHelpDeskIssues

(
@IssueID int,
@EmpID int,
@Date datetime,
@StatusID int
)

AS
UPDATE HelpDeskIssuesTbl
SET EmployeeID = @EmpID, IssueDate = @Date, IssueStatusID = @StatusID
WHERE (IssueID = @IssueID)
RETURN

Like I said the Stored Procedure does work when I run it directly in Visual Studio. I have double checked all the params and they all match up unless I am missing something.

Please send help! Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-25 : 13:49:46

Step through the code, make sure it is actually being executed, there may be a condition or a missing event handler that is causing it never to be executed. Also, remove your TRY/CATCH block while debugging.

- Jeff
http://weblogs.sqlteam.com/JeffS

Chuck596
Starting Member

2 Posts

Posted - 2008-03-25 : 14:25:03

Ok, I am wearing the mighty L on my forehead for this one. (L is for loser) I was using the GridView1.SelectedIndex instead of GridView1.SelectedDataKey.Value.ToString(). So the procedure was looking for the wrong primary key.

Thanks for your help jsmith. I never tried commenting out the try catch while debugging before.

I will keep that in mind in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources