Update Table datasource from asp:GridView

Question

User1741069310 posted

Why unable to update editable data?

System.Data.SqlClient.SqlException: The variable name '@AGM_Cli' has already been declared. Variable names must be unique within a query batch or stored procedure

                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" />
                    <Columns>
                        <asp:CommandField ShowEditButton="True" />
                        <asp:BoundField DataField="AGM_Cli " HeaderText="AGM_Cli " SortExpression="AGM_Cli " />
                        <asp:BoundField DataField="AGM_Due" HeaderText="AGM_Due" SortExpression="AGM_Due" />
                        <asp:BoundField DataField="AGM_Held" HeaderText="AGM_Held" SortExpression="AGM_Held" />
                        <asp:BoundField DataField="AGM_Last_Acc" HeaderText="AGM_Last_Acc" SortExpression="AGM_Last_Acc" />
                        <asp:BoundField DataField="AGM_Rtd" HeaderText="AGM_Rtd" SortExpression="AGM_Rtd" />
                        <asp:BoundField DataField="AGM_Sgd " HeaderText="AGM_Sgd " SortExpression="AGM_Sgd " />
                        <asp:BoundField DataField="Ann_Cli  " HeaderText="Ann_Cli  " SortExpression="Ann_Cli  " />
                        <asp:BoundField DataField="Ann_Filed" HeaderText="Ann_Filed" SortExpression="Ann_Filed" />
                        <asp:BoundField DataField="Ann_Sgd " HeaderText="Ann_Sgd " SortExpression="Ann_Sgd " />
                        <asp:BoundField DataField="AR_Date" HeaderText="AR_Date" SortExpression="AR_Date" />
                        <asp:BoundField DataField="AR_Due " HeaderText="AR_Due " SortExpression="AR_Due " />
                        <asp:BoundField DataField="ClientId" HeaderText="ClientId" SortExpression="ClientId" />
                        <asp:BoundField DataField="Year " HeaderText="Year " SortExpression="Year " />
                    </Columns>
                    <EditRowStyle BackColor="#2461BF" />
                    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EFF3FB" />
                    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#F5F7FB" />
                    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                    <SortedDescendingCellStyle BackColor="#E9EBEF" />
                    <SortedDescendingHeaderStyle BackColor="#4870BE" />
                </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ProConnectionString %>" 
                    SelectCommand="SELECT  *  FROM MS.dbo.FS where ClientId = 1 AND [Year] BETWEEN 2017 AND 2019" 
                    UpdateCommand="update MS.dbo.FS set AGM_Cli=@AGM_Cli,AGM_Due=@AGM_Due,AGM_Held=@AGM_Held,AGM_Last_Acc=@AGM_Last_Acc,AGM_Rtd=@AGM_Rtd,AGM_sgd=@AGM_sgd,Ann_Cli=@Ann_Cli,Ann_Filed=@Ann_Filed,Ann_Sgd=@Ann_Sgd,Ar_Date=@Ar_Date,Ar_Due=@Ar_Due where ClientId = @ClientId and [Year] = @Year "
                    OnUpdated="OnDSUpdatedHandler"
                    >

                <UpdateParameters>
                    <asp:Parameter Name="AGM_Cli"   Type="datetime" />
                    <asp:Parameter Name="AGM_Due"  Type="datetime" />
                    <asp:Parameter Name="AGM_Held"    Type="datetime" />
                    <asp:Parameter Name="AGM_Last_Acc"       Type="string" />
                    <asp:Parameter Name="AGM_Rtd"     Type="string" />
                    <asp:Parameter Name="AGM_sgd" Type="datetime" />
                    <asp:Parameter Name="Ann_Cli" Type="datetime" />
                    <asp:Parameter Name="Ann_Filed" Type="datetime" />
                    <asp:Parameter Name="Ann_Sgd" Type="datetime" />
                    <asp:Parameter Name="Ar_Date" Type="datetime" />
                    <asp:Parameter Name="Ar_Due" Type="datetime" />
                    <asp:Parameter Name="ClientId" Type="Int32" DefaultValue="0" />
                    <asp:Parameter Name="Year" Type="Int32" DefaultValue="0" />
                  </UpdateParameters>


                </asp:SqlDataSource>

Answer 1

User503812343 posted

The Select Columns and the Update Parameters should match in order to update using SqlDataSource, that means if you select(query or procedure) is

returning 3 fields in gridview, then all of them should be the parameter for the update, you can miss out the actual updations in database if not required,

but the <UpdateParameters> should have all the fields

Answer 2

User-893317190 posted

Hi fsze88,

This error usually happens when you add two sqlParameter with the same name to a sqlCommand or sqlAdapter.

For example, the two SqlParamter below has the same name @date.

  using (SqlCommand com = new SqlCommand("INSERT INTO [Period] (Date1, Date2) VALUES(@data, @data)", con))
                {
                    SqlParameter[] ParamArr = new SqlParameter[2];
                    ParamArr[0] = new SqlParameter("@data", Convert.ToDateTime("2015-09-12"));
                    ParamArr[1] = new SqlParameter("@data", Convert.ToDateTime("2016-02-13"));

                    com.Parameters.AddRange(ParamArr);
                   con.Open();
                    com.ExecuteNonQuery();
                   
                }

I haven't seen  duplicated sqlparameter names in your UpdateCommand,

What do you write in you code behind?

You could refer to the link below, it has a similar problem.

https://social.msdn.microsoft.com/Forums/vstudio/en-US/61a1cccd-9f05-47b2-8aa5-fb7d9c0239ea/about-this-error-quotthe-variable-name-ornumber-has-already-been-declaredquotvariable-names?forum=csharpgeneral

Best regards,

Ackerly Xu