CODEWITHSUNDEEP
c#asp.netsqldatasource
Kolten
Kolten

Reputation: 3503

sqldatasource update() not working

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

Upvotes: 1

Views: 7719

Answers (3)

Daniel Bonetti
Daniel Bonetti

Reputation: 2416

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.

Upvotes: 2

Kolten
Kolten

Reputation: 3503

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;

Upvotes: 0

Brian Mains
Brian Mains

Reputation: 50728

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.

Upvotes: 0

Related Questions