5

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)
Liam
  • 27,717
  • 28
  • 128
  • 190
skylerl
  • 4,030
  • 12
  • 41
  • 60
  • Its probably a stupid question, but did you assign your sql command to your sql connection? sqlCommand.Connection = sqlConn; I usually do it in the construction sqlCommand = new SqlCommand(sqlConn); I would think it would throw an error if you didnt. – GrayWizardx Dec 19 '09 at 23:23
  • Yeah, the constructor I used has it at the end. – skylerl Dec 19 '09 at 23:29
  • Are you actually using NVArchar fields in your db? or just varchar? – GrayWizardx Dec 19 '09 at 23:42

5 Answers5

5

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
womp
  • 115,835
  • 26
  • 236
  • 269
  • Oh, but it's still not working otherwise? Can we see more of the code? – womp Dec 19 '09 at 22:52
  • Might need clarifying - you can have a FROM clause in an UPDATE statement e.g. UPDATE t SET t.Field='Blah' FROM MyTable t WHERE t.Id = 1 – AdaTheDev Dec 19 '09 at 22:53
  • Thanks Ada. I just noticed that right off the bat he had UPDATE FROM, which was invalid. – womp Dec 19 '09 at 22:55
  • @womp - yep, I was going the mention the same thing. I knew what you meant, just thought others might not :) – AdaTheDev Dec 19 '09 at 22:58
  • I just ran a quick test, text with no brackets worked in a simple context, so the statement seems valid. There must be a problem with your parameters or data types/length. Some things to check is that your columns are long enough to hold the parameters that you're passing in, and that the ID parameter isn't null. – womp Dec 19 '09 at 23:00
  • Can you run a SQL profiler and watch the command execute? That would tell you the error at least. – womp Dec 19 '09 at 23:31
2

Had to use if(!Page.IsPostBack)

skylerl
  • 4,030
  • 12
  • 41
  • 60
1

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).
GrayWizardx
  • 19,561
  • 2
  • 30
  • 43
  • 1.) No 2.) Yes, the @ticketIDParam is sound, I've used it in other methods 3.) I am updating rows that may have their values set to the same thing already, yes. 4.) I added them above. 5.) No triggers 6.) The parameters are usually copy/pasted from the methods I made that /did/ work, however some needed some changes so their may be an error or two. – skylerl Dec 19 '09 at 23:15
  • If you could get a profiler trace you can see exactly what is being sent. and try executing the same command on the server yourself (assuming you have access). – GrayWizardx Dec 19 '09 at 23:24
  • I wish I did, GoDaddy is very restrictive with such access =/ – skylerl Dec 19 '09 at 23:26
0

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.

Ant
  • 1
0

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();
zozox256
  • 1
  • 1