sqldatasource update() not working

Question

I am using sqldatasource to perform an update.

I have a Transactions table which stores individual items that the user selects for purchase (item name, item price, orderID, etc)

I have an Orders table which stores values for the Order (account name, order total, etc)

Here is my SqlDataSource as defined in the .aspx page:

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NH_SWAGConnectionString %>" 

        InsertCommand="INSERT INTO [tblOrders] ([OrderDate], [OrderTotal], [OrderAccount], [OrderCostCentre]) VALUES (@OrderDate, @OrderTotal, @OrderAccount, @OrderCostCentre); SELECT @OrderNewID = SCOPE_IDENTITY()" 
        SelectCommand="SELECT * FROM [tblOrders]" 
        UpdateCommand="UPDATE [tblOrders] SET [OrderDate] = @OrderDate, [OrderTotal] = @OrderTotal, [OrderAccount] = @OrderAccount, [OrderCostCentre] = @OrderCostCentre WHERE [OrderID] = @original_OrderID AND [OrderDate] = @original_OrderDate AND [OrderTotal] = @original_OrderTotal AND [OrderAccount] = @original_OrderAccount AND [OrderCostCentre] = @original_OrderCostCentre" 
        OldValuesParameterFormatString="original_{0}" 

        oninserting="SqlDataSource2_Inserting"
        oninserted="SqlDataSource2_Inserted" onupdated="SqlDataSource2_Updated" 
        onupdating="SqlDataSource2_Updating">

        <InsertParameters>
            <asp:Parameter Direction="Output" Name="OrderNewId" Type="Int32" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="OrderDate" Type="DateTime" />                                
            <asp:Parameter Name="OrderAccount" Type="String" />
            <asp:Parameter Name="OrderCostCentre" Type="String" />
            <asp:Parameter Name="original_OrderID" Type="Int32" />
            <asp:Parameter Name="original_OrderDate" Type="DateTime" />
            <asp:Parameter Name="original_OrderTotal" Type="Decimal" />
            <asp:Parameter Name="original_OrderAccount" Type="String" />
            <asp:Parameter Name="original_OrderCostCentre" Type="String" />
            <%--<asp:ControlParameter ControlID="lblOrderTotal" Name="OrderTotal" 
                PropertyName="Text" />--%>
        </UpdateParameters>
    </asp:SqlDataSource>

You can see I blocked out the code that attaches the OrderID to a Textbox. I did this because that portion wasn't working. I then tried to modify the Parameter in Code-Behind:

        //Update Order Table with Total of Order
    //SqlDataSource2.UpdateParameters["OrderTotal"].DefaultValue = (string)OrderTotal.ToString();        
    SqlDataSource2.UpdateParameters.Add("OrderTotal", (string)OrderTotal.ToString());
    SqlDataSource2.Update();

The page doesn't error out, but the Update to OrderTotal in the Orders Db doesn't update. It stays at '0'. Can anyone shed some light on this for me? I've been trying for 2 days now. And yes, this is the last time I will be using SqlDataSource - It seems everyone recommends a different approach (DAC?) - Next project I will follow everyone's suggestion :)

Thanks all

Daniel Bonetti · Accepted Answer · 2012-10-25 13:46:24Z

2

Try this:

protected void SqlDataSource2_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    e.Command.Parameters[0].Value = OrderTotal;
    e.Command.Parameters[1].Value = OrderNewId;
}

It has been worked for me.

answered Oct 25, 2012 at 13:46

Daniel Bonetti

2,4462 gold badges27 silver badges39 bronze badges

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

Comments

Brian Mains · Accepted Answer · 2011-04-13 03:53:59Z

0

Add the OrderTotal to the list of parameters, and then do in code before the update:

SqlDataSource2.UpdateParameters["OrderTotal"].DefaultValue = OrderTotal.ToString();
SqlDataSource2.Update();

Using the DefaultValue property passes this value to the query.

Or, tap into the Updating event, and add the new value to the event argument collection of values. This gets passed to the backend.

HTH.

edited Apr 13, 2011 at 3:53

answered Apr 12, 2011 at 16:38

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

4 Comments

Kolten Over a year ago

I tried that before (see my commented-out code above) but I tried it again: //Update Order Table with Total of Order SqlDataSource2.UpdateParameters.Add("OrderTotal", (string)OrderTotal.ToString()); SqlDataSource2.UpdateParameters["OrderTotal"].DefaultValue = (string)OrderTotal.ToString(); SqlDataSource2.Update(); ...doesn't update the value :(

Kolten Over a year ago

Also tried adding the parameter to the aspx file and still doesn't update: <asp:Parameter Name="OrderTotal" Type="Decimal" />

Brian Mains Over a year ago

OK, updated my post above. You could also try the Updating event, passing the value through the event arg.

Kolten Over a year ago

I still haven't figured this out. What would the Table's column type have to be in SQL Server for this to work? Do you have a way that I can track the @originalOrderID and @OrderTotal parameter's value before the update occurs?

Kolten · Accepted Answer · 2011-05-01 19:57:40Z

0

OK, well not sure why it wouldn't automatically update, but I just set the updatecommand before updating manually, and that seemed to have worked:

SqlDataSource2.UpdateCommand = "UPDATE tblOrders SET [OrderTotal] = " + (string)OrderTotal.ToString() + " WHERE OrderID = " + OrderNewID;

answered May 1, 2011 at 19:57

Kolten

3,5036 gold badges45 silver badges60 bronze badges

Collectives™ on Stack Overflow

sqldatasource update() not working

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related