C # update table using SqlCommand.Parameters

Question

I am trying to update an MSSQL table using SqlCommand, I think this is a syntax error with my T-SQL, but here is what I still have:

SqlCommand sqlCmd = new SqlCommand("UPDATE yak_tickets SET email = @emailParam, subject = @subjectParam, text = @textParam, statusid = @statusIDParam, ticketClass = @ticketClassParam WHERE id = @ticketIDParam", sqlConn); 

The parameters work as they should, but the table is never updated when the code runs. Any help would be appreciated =)

Here is the rest of the code:

  #region Parameters /* Parameters */ sqlCmd.Parameters.Add("@ticketIDParam", SqlDbType.BigInt); sqlCmd.Parameters["@ticketIDParam"].Value = ticketID; sqlCmd.Parameters.Add("@emailParam", SqlDbType.NVarChar); sqlCmd.Parameters["@emailParam"].Value = ticketToBeSubmitted.getEmail(); sqlCmd.Parameters.Add("@subjectParam", SqlDbType.NVarChar); sqlCmd.Parameters["@subjectParam"].Value = ticketToBeSubmitted.getSubject(); sqlCmd.Parameters.Add("@textParam", SqlDbType.Text); sqlCmd.Parameters["@textParam"].Value = ticketToBeSubmitted.getTicketContent(); sqlCmd.Parameters.Add("@statusIDParam", SqlDbType.NVarChar); sqlCmd.Parameters["@statusIDParam"].Value = ticketToBeSubmitted.getStatus(); sqlCmd.Parameters.Add("@ticketClassParam", SqlDbType.NVarChar); sqlCmd.Parameters["@ticketClassParam"].Value = ticketToBeSubmitted.getTicketClass(); #endregion #region Try/Catch/Finally /* Try/Catch/Finally */ try { sqlConn.Open(); sqlCmd.ExecuteNonQuery(); } catch (SqlException sqlEx) { sqlErrorLabel.Text = sqlEx.ToString(); sqlErrorLabel.ForeColor = System.Drawing.Color.Red; } finally { sqlConn.Close(); } 

And the method signature:

  public static void updateTicketInDatabase(Ticket ticketToBeSubmitted, Label sqlErrorLabel, int ticketID) 

Answer 1

UPDATE FROM is invalid syntax (edit: OP fixed this). The " text " column may also be a problem. text is a keyword in SQL Server, as it is a data type. Try putting brackets around it.

 UPDATE yak_tickets SET email = @emailParam, subject = @subjectParam, [text] = @textParam, statusid = @statusIDParam, ticketClass = @ticketClassParam WHERE id = @ticketIDParam 

Answer 2

Had to use if (! Page.IsPostBack)

Answer 3

A couple of questions:

• Is this inside a transaction receiving rollbackback?
• Have you confirmed that @ticketIDParam matches a set of rows in a table? Especially if it is not only an integer key
• Are you updating rows that have no side effects (i.e. updating to the same values)?
• Can you provide paramators.Add parameters for this request.
• Is there a trigger or other settings in the table (I assume not, since you didn't mention anything).
• You said that you know that the parameters work correctly, can you tell how you confirmed this? (profiler, visual inspection, etc.).

Answer 4

It looks like your hosting provider is limiting your debugging options, forcing you to do this the old fashioned way. What if right after the update you put something like:

 ;SELECT @@ROWCOUNT 

and then instead of ExecuteNonQuery, execute ExecuteScalar and look, even SQL is even thinking about updating it.