locked
Update command failing RRS feed

  • Question

  • User-2057611790 posted

    Here's the code for the Access data source...

     

    <asp:SqlDataSource ID="srcCustomers" runat="server" ConnectionString="<%$ ConnectionStrings:TestSalesDBConn %>"
                DeleteCommand="DELETE FROM [tCustomers] WHERE [CustomerID] = ?" InsertCommand="INSERT INTO [tCustomers] ([LastName], [FirstName], [BillingAddress1], [BillingAddress2], [City], [State], [PostalCode], [PhoneNumber], [EmailAddress]) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                ProviderName="<%$ ConnectionStrings:TestSalesDBConn.ProviderName %>" SelectCommand="SELECT [CustomerID], [LastName], [FirstName], [BillingAddress1], [BillingAddress2], [City], [State], [PostalCode], [PhoneNumber], [EmailAddress] FROM [tCustomers]"
                UpdateCommand="UPDATE [tCustomers] SET [LastName] = @LastName, [FirstName] = @FirstName, [BillingAddress1] = @BillingAddress1, [BillingAddress2] = @BillingAddress2, [City] = @City, [State] = @State, [PostalCode] = @PostalCode, [PhoneNumber] = @PhoneNumber, [EmailAddress] = @EmailAddress WHERE [CustomerID] = @CustomerID">
                <DeleteParameters>
                    <asp:Parameter Name="CustomerID" Type="Int32" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:ControlParameter ControlID="txtLastName" Name="@LastName" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtFirstName" Name="@FirstName" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtAddress1" Name="@BillingAddress1" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtAddress2" Name="@BillingAddress2" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtCity" Name="@City" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="txtState" Name="@State" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="txtPostalCode" Name="@PostalCode" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtPhone" Name="@PhoneNumber" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtEmail" Name="@EmailAddress" PropertyName="Text"
                        Type="String" />
                    <asp:SessionParameter Name="@CustomerID" SessionField="userID" Type="Int32" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtAddress1" Name="BillingAddress1" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtAddress2" Name="BillingAddress2" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtCity" Name="City" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="txtState" Name="State" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="txtPostalCode" Name="PostalCode" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtPhone" Name="PhoneNumber" PropertyName="Text"
                        Type="String" />
                    <asp:ControlParameter ControlID="txtEmail" Name="EmailAddress" PropertyName="Text"
                        Type="String" />
                </InsertParameters>
            </asp:SqlDataSource>

     And here's the C# code-behind...

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                srcCustomers.Update();
                lblStatus.Text = "Update successful.";
            }
            catch (Exception ex)
            {
                lblStatus.Text = "Unsuccessful: " + ex.Message;
            }
        }

     The problem is that I get the "Update successful." message in my label, but the row in the database doesn't actually get updated.

    I know that the row exists, because I pre-populate the text boxes on my page with a query in the Page_Load event (assuming that the Session["userID"] variable isn't null).  When the page returns, though, it resets any changed textboxes to their previous values (since the pre-population query executes again), and a look at the database shows the row unchanged.

    I don't see what's not working, and I don't see anything in the trace or during debugging that gives me a clue.

    Friday, April 11, 2008 10:27 AM

Answers

  • User187056398 posted

    You could try using a textbox control as a parameter instead of a session parameter.  Set the textbox from the session parameter (then you'll be able to see what the value of the parameter is): 

         TextBoxCustomerID.Text = (int)Session(userID)

     

    By the way, is Parameter: CustomerID and SesssionField: userID correct?

        <asp:SessionParameter Name="@CustomerID" SessionField="userID" Type="Int32" />

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 11:34 AM
  • User187056398 posted

    I can think of three possibilities:

    • There is no record with a matching ID in the database.
    • The values being sent are indentical to the values already in the database.
    • You have more than one database, one is being updated but you are looking at another..
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 12:41 PM
  • User187056398 posted

    Wait a minute....

    Your update statement is using @parameters (SQL Server uses these)

         UpdateCommand="UPDATE [tCustomers] SET [LastName] = @LastName,

    Your insert (and delete statement) is using ? parameters (Access uses these)

        InsertCommand="INSERT INTO [tCustomers] ([LastName... VALUES ( ?,?, ?,  ?, ?, ?, ?, ?, ?)

     

    You can't mix these together.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 1:22 PM
  • User187056398 posted

     I see you are using an SQLDatasource:    <asp:SqlDataSource ...

    Why don't you delete your datasource, create a new one (of type AccessDataSource) and let the wizard create the ASP.Net code for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:24 PM
  • User-821857111 posted

    I can't see anything obviously wrong here, so I can only recommend that you either follow Steve's advice and redo the page - relying on the wizard to automatically generate all the commands and parameters, or stop using SqlDataSource controls for this type of thing, and code it yourself: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 4:42 PM
  • User-821857111 posted

    We'll get you using parameters in a minute, but in the meantime, change

    lblStatus.Text = "Update successful.";

    to

    lblStatus.Text = updatequery; 

    Then check to see if the db updated. Then paste the updatequery that appears in the label into Access to see if that works.

     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 16, 2008 3:06 PM

All replies

  • User187056398 posted

    You could try using a textbox control as a parameter instead of a session parameter.  Set the textbox from the session parameter (then you'll be able to see what the value of the parameter is): 

         TextBoxCustomerID.Text = (int)Session(userID)

     

    By the way, is Parameter: CustomerID and SesssionField: userID correct?

        <asp:SessionParameter Name="@CustomerID" SessionField="userID" Type="Int32" />

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 11:34 AM
  • User-2057611790 posted

    The parameter name was correct, but I changed it to @UserID just to make it look more sensible in the code.

    Changing the parameter to a control's text value has not affected the outcome of the query.  It doesn't throw an exception, but it still doesn't actually change the values in the database.

    Friday, April 11, 2008 12:02 PM
  • User187056398 posted

    I can think of three possibilities:

    • There is no record with a matching ID in the database.
    • The values being sent are indentical to the values already in the database.
    • You have more than one database, one is being updated but you are looking at another..
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 12:41 PM
  • User-2057611790 posted

    I am successfully querying the table to pre-populate the text fields, so there is definitely a matching record.

    I know that I'm changing the value, because I'm clearing the pre-populated value and replacing it.

    There is definitely only one database in the solution, and the server explorer also sees only one data connection.  Both the query to populate the fields and the Access datasource use the same connection string and the same table name.

    Friday, April 11, 2008 12:48 PM
  • User187056398 posted

    I know that I'm changing the value, because I'm clearing the pre-populated value and replacing it

    You're not changing the value of the ID...right?

    Friday, April 11, 2008 1:11 PM
  • User187056398 posted

    Wait a minute....

    Your update statement is using @parameters (SQL Server uses these)

         UpdateCommand="UPDATE [tCustomers] SET [LastName] = @LastName,

    Your insert (and delete statement) is using ? parameters (Access uses these)

        InsertCommand="INSERT INTO [tCustomers] ([LastName... VALUES ( ?,?, ?,  ?, ?, ?, ?, ?, ?)

     

    You can't mix these together.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 1:22 PM
  • User-2057611790 posted

    OK, I changed the update query to...

    UPDATE [tCustomers] SET [LastName] = ?, 
    [FirstName] = ?, 
    [BillingAddress1] = ?, 
    [BillingAddress2] = ?, 
    [City] = ?, 
    [State] = ?, 
    [PostalCode] = ?, 
    [PhoneNumber] = ?, 
    [EmailAddress] = ? 
    WHERE [CustomerID] = ?
    and made sure the parameters are in the right order
     
    <UpdateParameters>
      <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtAddress1" Name="BillingAddress1" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtAddress2" Name="BillingAddress2" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtCity" Name="City" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtState" Name="State" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtPostalCode" Name="PostalCode" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtPhone" Name="PhoneNumber" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="txtEmail" Name="EmailAddress" PropertyName="Text" Type="String" />
      <asp:ControlParameter ControlID="lblUserID" Name="UserID" PropertyName="Text" Type="Int32" />
    </UpdateParameters>
     
    Still no actual change to the database when the I call the Update method.
    Friday, April 11, 2008 3:04 PM
  • User187056398 posted

     I see you are using an SQLDatasource:    <asp:SqlDataSource ...

    Why don't you delete your datasource, create a new one (of type AccessDataSource) and let the wizard create the ASP.Net code for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:24 PM
  • User-821857111 posted

    I can't see anything obviously wrong here, so I can only recommend that you either follow Steve's advice and redo the page - relying on the wizard to automatically generate all the commands and parameters, or stop using SqlDataSource controls for this type of thing, and code it yourself: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 4:42 PM
  • User-2057611790 posted

    I am officially at a loss.

    I deleted the SQLDataSource and replaced it with an AccessDataSource, allowing it to set up the Insert and Update queries.

    No change in the result of the Update query; no exception thrown, yet the row in the DB doesn't actually get updated.

    So, I commented out the calls to the AccessDataSources Insert and Update methods, writing code to create my own connection and my own command (with command text), which I then ran.

     

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            string updatequery = "UPDATE [tCustomers] " +
                                 "SET [LastName] =  '" + txtLastName.Text + "', " +
                                     "[FirstName] = '" + txtFirstName.Text + "', " +
                                     "[BillingAddress1] = '" + txtAddress1.Text + "', " +
                                     "[BillingAddress2] = '" + txtAddress2.Text + "', " +
                                     "[City] = '" + txtCity.Text + "', " + 
                                     "[State] = '" + txtState.Text + "', " +
                                     "[PostalCode] = '" + txtPostalCode.Text + "', " +
                                     "[PhoneNumber] = '" + txtPhone.Text + "', " +
                                     "[EmailAddress] = '" + txtEmail.Text + "' " +
                                  "WHERE [CustomerID] = " + ((int)Session["userID"]).ToString();
            try
            {
                //srcCustomers.Update();
                OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["TestSalesDBConn"].ToString());
                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                conn.Open();
                    cmd.CommandText = updatequery;
                    cmd.ExecuteNonQuery();
                conn.Close();
                lblStatus.Text = "Update successful.";
            }
            catch (Exception ex)
            {
                lblStatus.Text = "Unsuccessful: " + ex.Message;
                lblStatus.Text += "&lt;br />" + updatequery;
            }

    Same thing.  Once I got the syntax write, it ran with out throwing an exception, but didn't update the database.

    Wednesday, April 16, 2008 2:34 PM
  • User-821857111 posted

    We'll get you using parameters in a minute, but in the meantime, change

    lblStatus.Text = "Update successful.";

    to

    lblStatus.Text = updatequery; 

    Then check to see if the db updated. Then paste the updatequery that appears in the label into Access to see if that works.

     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 16, 2008 3:06 PM
  • User-2057611790 posted

    Ah, that tipped me off.

    I wasn't verifying that the page was not a postback before pre-populating the form fields!

    I little If (!Page.IsPostBack) was needed.

    Wednesday, April 16, 2008 3:46 PM
  • User-1825514848 posted
    THANK YOU for reporting your success. I had the same problem with vb.net/sql or object datasource/sql db : details view, grid view, form view did not update although no error was reported, no exception existed, so I was going mad. Adding If Not Page.IsPostBack did the trick. I'd been searching the web and only your little note here pointed me to the solution.
    Sunday, February 8, 2009 1:52 PM