C# Update Table using SqlCommand.Parameters ASP.NET [duplicate]

Question

Possible Duplicate:
C# Update Table using SqlCommand.Parameters

I'm trying to update an SQL Server 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)

I've run this through a profiler, and what it does, is the following.

Page Loads ->
Audit Login: -- Sets a bunch of stuff (irrelevant)
SQL:BatchStarting -- SELECT * from yak_tickets
SQL:BatchCompleted -- SELECT * from yak_tickets
Audit Logout


Button Click Event ->
RPC:Completed: -- exec sp_reset_connection
Audit Login: -- Sets some more stuff
SQL:BatchStarting -- SELECT * from yak_tickets
SQL:BatchCompleted -- SELECT * from yak_tickets

Here is the code behind file for the form.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using YakStudios_Support.Includes;

namespace YakStudios_Support.ys_admin
{
    public partial class UpdateTicket : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            int ticketID = Convert.ToInt32(Request.QueryString["ticketID"]); // Grabs the ?ticketid number from the URL
            Ticket ticketBeingUpdated = TicketDatabase.selectTicketFromDatabase(sqlErrorLabel, 1); // Creates a new Ticket object to be used by the form to populate the text boxes

            /* Form Field Population */
            // Email
            emailTxt.Text = ticketBeingUpdated.getEmail();

            // Date Submitted
            dateSubText.Text = ticketBeingUpdated.getDateSubmitted().ToString();

            // Ticket Class
            classDropDown.SelectedValue = ticketBeingUpdated.getTicketClass();

            // Ticket Status
            statusDrop.SelectedValue = ticketBeingUpdated.getStatus();

            // Subject
            subjectTxt.Text = ticketBeingUpdated.getSubject();

            // Text
            textTxt.Text = ticketBeingUpdated.getTicketContent();
        }

        protected void editBtn_Click(object sender, EventArgs e)
        {
            emailTxt.Enabled = true;
            dateSubText.Enabled = true;
            classDropDown.Enabled = true;
            statusDrop.Enabled = true;
            subjectTxt.Enabled = true;
            textTxt.Enabled = true;
        }

        protected void submitBtn_Click(object sender, EventArgs e)
        {
            int ticketID = Convert.ToInt32(Request.QueryString["ticketID"]); // Grabs the ?ticketid number from the URL
            DateTime convertedDate = Convert.ToDateTime(dateSubText.Text);
            Ticket ticketUpdated = new Ticket(emailTxt.Text, convertedDate, subjectTxt.Text, textTxt.Text, statusDrop.SelectedValue, classDropDown.SelectedValue);
            //Ticket ticketUpdated = new Ticket(emailTxt.Text, subjectTxt.Text, textTxt.Text, classDropDown.SelectedValue);
            Response.Write(TicketDatabase.updateTicketInDatabase(ticketUpdated, sqlErrorLabel, 1));
            //Response.Redirect("ticketqueue.aspx");
        }
    }
}

What it seems to me like it's doing, is it's running my select method again, which is what it is supposed to do when the page loads. Is this being caused by the button refreshing the page?

ExecuteNonQuery returns an integer reporting the number of rows affecting. Have you checked what that value is? — Mark Byers
– Mark Byers, Commented Dec 27, 2009 at 16:15
Are you sure that your code is even being executed? Have you tried stepping through in the debugger? — Mark Byers
– Mark Byers, Commented Dec 27, 2009 at 16:19
I agree with Mark. Step through your code, making sure that all of the parameters are getting values. — DOK
– DOK, Commented Dec 27, 2009 at 16:28
Why post this again? You've only added a wee bit more info stackoverflow.com/questions/1934343/… — gbn
– gbn, Commented Dec 27, 2009 at 16:52
Okay Mark, I'm not the best debugger, but I did have my method return the rows affected, and the value is in fact, 1, meaning 1 row was updated I assume, however, no values changed. — skylerl
– skylerl, Commented Dec 27, 2009 at 17:00

Ta01 · Accepted Answer · 2009-12-27 17:09:15Z

1

Add a break point at ExecuteNonQuery, inspect your sqlCommand object , take a look at the parameters with the sqlcommand and make sure TicketId is being set to what you expect it to, since your update is driven by the ticketId, ensure it is being set correctly, or try running the TSQL equivalent directly on SQL server, my guess is that your ticketId variable isn't being set, code looks fine at first glance.

answered Dec 27, 2009 at 17:09

Ta01

31.7k13 gold badges74 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

skylerl Over a year ago

Aha! I inspected the ticketUpdated object, it doesn't contain any of the new values, it's fields are the same as the fields from the form, before the edit. Is this because the page is refreshing before it updates the data?

Ta01 Over a year ago

yes most likely. Take a look at your code that occurs on PageLoad, you're probably loading the data regardless if the Page is a Post Back or not. If you want to load the original data only once use if(!Page.IsPostBack) on initial load, I can't see your code, but most likely you have an edit button that is reloading your initial values or something.

skylerl Over a year ago

I do have an edit button, but I made everything enabled at the start, I'll try the if statement next.

Collectives™ on Stack Overflow

C# Update Table using SqlCommand.Parameters ASP.NET [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related