UpdateParameter Not Working

Question

I am trying to set an update parameter manually. The command executes successfully and I can pull the data back to confirm that the parameter was set correctly. However my SQL update statement will not execute. If I manually define a default value or manually enter a value in the update statement it works fine, but if I try to set it through a command it does not work.

Here is my code:

SQL DATA SOURCE

<asp:SqlDataSource ID="reconcileDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
     OnUpdating="reconcileGrid_Updating"
    SelectCommand="GetReconcileItems" SelectCommandType="StoredProcedure"
    UpdateCommand="UPDATE item SET Stat = @Status WHERE ItemID = @ID">

    <UpdateParameters>
        <asp:Parameter Name="ID" />
        <asp:Parameter Name="TransType" Type="String" />
        <asp:Parameter Name="LocationID" Type="String" />
        <asp:Parameter Name="Description" Type="String" />
        <asp:Parameter Name="Status" Type="String" />
        <asp:Parameter Name="TransferLocation" Type="String" />
    </UpdateParameters>

</asp:SqlDataSource>

C# CODE

    protected void reconcileGrid_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {
      DropDownList ddlReconcileStatus = (DropDownList)reconcileGrid.Rows[reconcileGrid.EditIndex].Cells[0].FindControl("ddlReconcileStatus");
      DropDownList ddlTransferLocation = (DropDownList)reconcileGrid.Rows[reconcileGrid.EditIndex].Cells[0].FindControl("ddlTransferLocation");

      // Set the Stat Value
      reconcileDataSource.UpdateParameters["Status"].DefaultValue = ddlReconcileStatus.SelectedValue.ToString();
}

GRIDVIEW

<asp:UpdatePanel ID="reconcileUpdatePanel" runat="server">
        <ContentTemplate>
            <asp:GridView ID="reconcileGrid" runat="server" AutoGenerateColumns="False" 
                DataKeyNames="ID" DataSourceID="reconcileDataSource" >
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" ReadOnly="true" />
                    <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" ReadOnly="true"/>
                    <asp:BoundField DataField="last_known_location" HeaderText="Last Known Location" SortExpression="last_known_location" ReadOnly="true" />
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# evalStatus(Eval("Stat")) %>'></asp:Label></ItemTemplate><EditItemTemplate>
                            <asp:DropDownList ID="ddlReconcileStatus" runat="server" OnSelectedIndexChanged="ddlReconcileStatus_SelectedIndexChanged" AutoPostBack="true" >
                                <asp:ListItem Value="3" Text="Allocated"></asp:ListItem><asp:ListItem Value="4" Text="Transferred"></asp:ListItem></asp:DropDownList></EditItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="Transfer Location">
                        <EditItemTemplate>
                            <asp:DropDownList ID="ddlTransferLocation" runat="server" 
                                DataSourceID="ddlTransferLocationDataSource" DataTextField="Name" 
                                DataValueField="ID" Enabled="false" ></asp:DropDownList>
                        <asp:SqlDataSource 
                                ID="ddlTransferLocationDataSource" runat="server" 
                                ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                                SelectCommand="SELECT [ID], [Name] FROM [TransferLocation]">
                        </asp:SqlDataSource>
                        </EditItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </ContentTemplate>
    </asp:UpdatePanel>

score 1 · Accepted Answer · edited Feb 16 '16 at 20:15

1

First off, I am confused that your Update Command takes only two parameters:

UpdateCommand="UPDATE item SET Stat = @Status WHERE ItemID = @ID"

Whereas your parameter collection contains six parameters:

<UpdateParameters>
    <asp:Parameter Name="ID" />
    <asp:Parameter Name="TransType" Type="String" />
    <asp:Parameter Name="LocationID" Type="String" />
    <asp:Parameter Name="Description" Type="String" />
    <asp:Parameter Name="Status" Type="String" />
    <asp:Parameter Name="TransferLocation" Type="String" />
</UpdateParameters>

Actually, the problem is that you are passing parameters in the SQLDataSource.ItemUpdating event, but you have to pass these parameters in the GridView update event.

edited Feb 16 '16 at 20:15

Blorgbeard

101,031
48
228
272

answered May 27 '11 at 17:07

Muhammad Akhtar

51,913
37
138
191

I'm only using one parameter for testing, easier to work with. I forgot to mention the ID is a DataBound Field in the GridView, that's where it's value comes from. – Will May 27 '11 at 17:10
I doubt ID field is not passing. Secondly if you really want to test you could remove the others parameter from parameter collection. – Muhammad Akhtar May 27 '11 at 17:14
What I don't understand is why I can set this: "" and it will work, or I can do this: "UPDATE item SET Stat = '3' WHERE ItemID = @ID" and it will work. It must be an issue with this command: "reconcileDataSource.UpdateParameters["Status"].DefaultValue = ddlReconcileStatus.SelectedValue.ToString();" Which I have tested and confirmed it is in fact returning "3". – Will May 27 '11 at 17:18
are you using Details View or FormView ? – Muhammad Akhtar May 27 '11 at 17:20
No, I'm using an UpdatePanel thats all. I edited my original post with my GridView Code. – Will May 27 '11 at 17:22
@Will; one thing I can say the problem can be. You need to set that value in Gridview ItemUpdating event. Currently you are setting that value in SQLDataSource ItemUpating event. Can you test it now? – Muhammad Akhtar May 27 '11 at 17:37
@Muhammad Akhtar - That seems to have solved the problem. Thank you! Could you tell me why it wouldn't work in SQLDataSource ItemUpdating? – Will May 27 '11 at 17:49
Now test the first thing that creating problem. Now pass the parameters again in SQLDataSource ItemUpdating Event. but use Value property instead DefaultValeu. Try I am waiting your response. As I don't have Visual Stuido and I can't test :) – Muhammad Akhtar May 27 '11 at 17:52
@Muhammad Akhtar - I'm getting an error, there is no definition for the Value property only DefaultValue – Will May 27 '11 at 17:55
OK, then fine. You can go with previous solution. Remember to Mark this answer. – Muhammad Akhtar May 27 '11 at 17:57
Thank you, but I still don't understand why it doesn't work in SQLDataSource ItemUpdating, it seems like it should. – Will May 27 '11 at 17:59
If you are using any DataBound Control, like FormView/GridView/DetialsView, you have to pass value from the control instead from SQLDataSource/ObjectDataSource. Check this Article http://blogs.lessthandot.com/index.php/WebDev/WebDesignGraphicsStyling/setting-sqldatasource-parameter-from-the – Muhammad Akhtar May 27 '11 at 18:04

UpdateParameter Not Working

1 Answers1