CODEWITHSUNDEEP
c#sqlsqlcommandsqlclient
skylerl
skylerl

Reputation: 4170

C# Update Table using SqlCommand.Parameters

I'm trying to update an MSSQL table using SqlCommand, I think it's a syntax error with my T-SQL, but here is what I have so far:

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

The parameters are working as they should, however, the table never gets updated when I run the code. 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's signature:

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

Upvotes: 5

Views: 37149

Answers (5)

zozox256
zozox256

Reputation: 1

I don't know if it changed since you first asked. but this works for me , e.g :

var SchoolName = cmd.Parameters.AddWithValue("@SchoolName", school.SchoolName);
SchoolName.SqlDbType = SqlDbType.NVarChar;

In your code it's :

sqlCmd.Parameters.Add("@emailParam", SqlDbType.NVarChar);
sqlCmd.Parameters["@emailParam"].Value = ticketToBeSubmitted.getEmail();

Upvotes: 0

Ant
Ant

Reputation: 1

Sounds like your hosting provider limits your debug options, forcing you to do it the old fashioned way. What if immediately after the update, you put something like:

;SELECT @@ROWCOUNT

then instead of ExecuteNonQuery, do ExecuteScalar, and see if SQL even thinks its updated anything.

Upvotes: 0

skylerl
skylerl

Reputation: 4170

Had to use if(!Page.IsPostBack)

Upvotes: 2

GrayWizardx
GrayWizardx

Reputation: 21121

Couple of questions:

  1. Is this inside of a transaction thats getting rolledback?
  2. Have you verified that you @ticketIDParam matches a set of rows on the table? Especially if its not just a integer key
  3. Are you updating rows that have no side effects (i.e. your updating to the same values)?
  4. Can you provide the paramaters.Add statements for this query
  5. Is there a trigger or other setting on the table (I assume not, as you did not mention anything).
  6. You said you know the params are working correctly, can you say how you verified this? (profiler, visual inspection, etc).

Upvotes: 1

womp
womp

Reputation: 116977

UPDATE FROM is invalid syntax (edit: OP corrected this). The problem might also be the "text" column. text is a keyword in SQL Server, since it's a datatype. Try putting brackets around it.

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

Upvotes: 5

Related Questions