Mass Updating Database ASP.Net and C#

Question

I'm currently working on a web page that will mass update a table in a database on SQL Server 2008 for my internship. I am stuck on how to properly declare the correct WHERE clause in the CommandText. No WHERE clause causes every row to update to be the same. A clause with the WHERE clauses stated as WHERE CLLI = @CLLI returns an exception stating Must declare Scalar Variable "@CLLI". If I add a DECLARE clause for @CLLI no exceptions occur but no updates occur either. I have updated my code with the 3 SQL statements I have tried for my CommandText at the bottom of the post . Any help would be appreciated, thank you.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.Web.UI.Adapters;
using TransferObjects;
using CCEBusinessL;
using System.IO;
using System.Configuration;


namespace WebApplication3
{
public partial class WebForm1 : System.Web.UI.Page
{
int rowIndex = 0;




    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {



            GridView1.DataBind();
        }
    }






    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection tConn = new SqlConnection("connectionstring ");

        SqlCommand tCommand = new SqlCommand();
        tCommand.Connection = tConn;
        tCommand.CommandText = "UPDATE TABLE SET ITEM1 = @ITEM1, ITEM2 = @ITEM2, ITEM3 = @ITEM3, ITEM4 = @ITEM4, ITEM5 = @ITEM5, ITEM6 = @ITEM6, ITEM7= @ITEM7, ITEM8 = @ITEM8, ITEM9 = @ITEM9, ITEM9.5 = @ITEM9.5, ITEM10 = @ITEM10, ITEM11 = @ITEM11, ITEM12 = @ITEM12, ITEM13 = @ITEM13, ITEM14 = @ITEM14, ITEM15 = @ITEM15, ITEM16 = @ITEM16, ITEM17= @ITEM17, ITEM18 = @ITEM18, ITEM19 = @ITEM19, ITEM20 = @ITEM20";


        TextBox TextBox1 = (TextBox)GridView1.Rows[rowIndex].Cells[0].FindControl("TextBox1");
        TextBox TextBox2 = (TextBox)GridView1.Rows[rowIndex].Cells[1].FindControl("TextBox2");
        TextBox TextBox3 = (TextBox)GridView1.Rows[rowIndex].Cells[2].FindControl("TextBox3");
        TextBox TextBox4 = (TextBox)GridView1.Rows[rowIndex].Cells[3].FindControl("TextBox4");
        TextBox TextBox5 = (TextBox)GridView1.Rows[rowIndex].Cells[4].FindControl("TextBox5");
        TextBox TextBox6 = (TextBox)GridView1.Rows[rowIndex].Cells[5].FindControl("TextBox6");
        TextBox TextBox7 = (TextBox)GridView1.Rows[rowIndex].Cells[6].FindControl("TextBox7");
        TextBox TextBox8 = (TextBox)GridView1.Rows[rowIndex].Cells[7].FindControl("TextBox8");
        TextBox TextBox9 = (TextBox)GridView1.Rows[rowIndex].Cells[8].FindControl("TextBox9");
        TextBox TextBox10 = (TextBox)GridView1.Rows[rowIndex].Cells[9].FindControl("TextBox10");
        TextBox TextBox11 = (TextBox)GridView1.Rows[rowIndex].Cells[10].FindControl("TextBox11");
        TextBox TextBox12 = (TextBox)GridView1.Rows[rowIndex].Cells[11].FindControl("TextBox12");
        TextBox TextBox13 = (TextBox)GridView1.Rows[rowIndex].Cells[12].FindControl("TextBox13");
        TextBox TextBox14 = (TextBox)GridView1.Rows[rowIndex].Cells[13].FindControl("TextBox14");
        TextBox TextBox15 = (TextBox)GridView1.Rows[rowIndex].Cells[14].FindControl("TextBox15");
        TextBox TextBox16 = (TextBox)GridView1.Rows[rowIndex].Cells[15].FindControl("TextBox16");
        TextBox TextBox17 = (TextBox)GridView1.Rows[rowIndex].Cells[16].FindControl("TextBox17");
        TextBox TextBox18 = (TextBox)GridView1.Rows[rowIndex].Cells[17].FindControl("TextBox18");
        TextBox TextBox19 = (TextBox)GridView1.Rows[rowIndex].Cells[18].FindControl("TextBox19");
        TextBox TextBox20 = (TextBox)GridView1.Rows[rowIndex].Cells[19].FindControl("TextBox20");
        TextBox TextBox21 = (TextBox)GridView1.Rows[rowIndex].Cells[20].FindControl("TextBox21");

       // SqlParameter p0 = new SqlParameter("@ITEM0", SqlDbType.VarChar);
       // tCommand.Parameters.Add(p0);
        SqlParameter p1 = new SqlParameter("@ITEM1", SqlDbType.Int);
        p1.Value = TextBox1.Text;
        tCommand.Parameters.Add(p1);
        SqlParameter p2 = new SqlParameter("@ITEM2", SqlDbType.VarChar);
        p2.Value = TextBox2.Text;
        tCommand.Parameters.Add(p2);
        SqlParameter p3 = new SqlParameter("@ITEM3", SqlDbType.VarChar);
        p3.Value = TextBox3.Text;
        tCommand.Parameters.Add(p3);
        SqlParameter p4 = new SqlParameter("@ITEM4", SqlDbType.VarChar);
        p4.Value = TextBox4.Text;
        tCommand.Parameters.Add(p4);
        SqlParameter p5 = new SqlParameter("@ITEM5", SqlDbType.VarChar);
        p5.Value = TextBox5.Text;
        tCommand.Parameters.Add(p5);
        SqlParameter p6 = new SqlParameter("@ITEM6", SqlDbType.VarChar);
        p6.Value = TextBox6.Text;
        tCommand.Parameters.Add(p6);
        SqlParameter p7 = new SqlParameter("@ITEM7", SqlDbType.Decimal);
        p7.Value = TextBox7.Text;
        tCommand.Parameters.Add(p7);
        SqlParameter p8 = new SqlParameter("@ITEM8", SqlDbType.Decimal);
        p8.Value = TextBox8.Text;
        tCommand.Parameters.Add(p8);
        SqlParameter p9 = new SqlParameter("@ITEM9", SqlDbType.Decimal);
        p9.Value = TextBox9.Text;
        tCommand.Parameters.Add(p9);
        SqlParameter p10 = new SqlParameter("@ITEM9.5", SqlDbType.Decimal);
        p10.Value = TextBox10.Text;
        tCommand.Parameters.Add(p10);
        SqlParameter p11 = new SqlParameter("@ITEM10", SqlDbType.Decimal);
        p11.Value = TextBox11.Text;
        tCommand.Parameters.Add(p11);
        SqlParameter p12 = new SqlParameter("@ITEM11", SqlDbType.Decimal);
        p12.Value = TextBox12.Text;
        tCommand.Parameters.Add(p12);
        SqlParameter p13 = new SqlParameter("@ITEM12", SqlDbType.Decimal);
        p13.Value = TextBox13.Text;
        tCommand.Parameters.Add(p13);
        SqlParameter p14 = new SqlParameter("@ITEM13", SqlDbType.Decimal);
        p14.Value = TextBox14.Text;
        tCommand.Parameters.Add(p14);
        SqlParameter p15 = new SqlParameter("@ITEM14", SqlDbType.Decimal);
        p15.Value = TextBox15.Text;
        tCommand.Parameters.Add(p15);
        SqlParameter p16 = new SqlParameter("@ITEM15", SqlDbType.VarChar);
        p16.Value = TextBox16.Text;
        tCommand.Parameters.Add(p16);
        SqlParameter p17 = new SqlParameter("@ITEM16", SqlDbType.Decimal);
        p17.Value = TextBox17.Text;
        tCommand.Parameters.Add(p17);
        SqlParameter p18 = new SqlParameter("@ITEM17", SqlDbType.VarChar);
        p18.Value = TextBox18.Text;
        tCommand.Parameters.Add(p18);
        SqlParameter p19 = new SqlParameter("@ITEM18", SqlDbType.Decimal);
        p19.Value = TextBox19.Text;
        tCommand.Parameters.Add(p19);
        SqlParameter p20 = new SqlParameter("@ITEM19", SqlDbType.VarChar);
        p20.Value = TextBox20.Text;
        tCommand.Parameters.Add(p20);
        SqlParameter p21 = new SqlParameter("@ITEM20", SqlDbType.VarChar);
        p21.Value = TextBox21.Text;
        tCommand.Parameters.Add(p21);


               // tCommand.Parameters.Add(new SqlParameter("@ITEM0", System.Data.SqlDbType.VarChar));
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM2", System.Data.SqlDbType.VarChar).Value = TextBox2);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM3", System.Data.SqlDbType.VarChar).Value = TextBox3);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM4", System.Data.SqlDbType.VarChar).Value = TextBox4);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM5_TYPE_ID", System.Data.SqlDbType.VarChar).Value = TextBox5);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM6", System.Data.SqlDbType.VarChar).Value = TextBox6);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM7", System.Data.SqlDbType.VarChar).Value = TextBox7);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM8", System.Data.SqlDbType.VarChar).Value = TextBox8);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM9", System.Data.SqlDbType.VarChar).Value = TextBox9);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM9.5", System.Data.SqlDbType.VarChar).Value = TextBox10);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM10", System.Data.SqlDbType.VarChar).Value = TextBox11);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM11", System.Data.SqlDbType.VarChar).Value = TextBox12);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM12", System.Data.SqlDbType.VarChar).Value = TextBox13);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM13", System.Data.SqlDbType.VarChar).Value = TextBox14);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM14", System.Data.SqlDbType.VarChar).Value = TextBox15);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM15", System.Data.SqlDbType.DateTime).Value = TextBox16);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM16", System.Data.SqlDbType.VarChar).Value = TextBox17);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM17", System.Data.SqlDbType.DateTime).Value = TextBox18);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM18", System.Data.SqlDbType.VarChar).Value = TextBox19);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM19", System.Data.SqlDbType.DateTime).Value = TextBox20);
               //  tCommand.Parameters.Add(new SqlParameter("@ITEM20", System.Data.SqlDbType.VarChar).Value = TextBox21);

        tConn.Open();
        tCommand.ExecuteNonQuery();

        tConn.Close();
    }












    }
}


<%@ Page Title="Mass Update" Language="C#" MasterPageFile="~/Site2.Master" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication3.WebForm1" %>

Bulk Update

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
    AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ITEM0" 
    DataSourceID="SqlDataSource1" >
     <Columns>
        <asp:BoundField DataField="ITEM0" HeaderText="ITEM0" ReadOnly="True" 
            SortExpression="ITEM0" />
        <asp:TemplateField HeaderText="ITEM1" SortExpression="ITEM1">

            <ItemTemplate>

                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ITEM1") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM2" SortExpression="ITEM2">

            <ItemTemplate>

                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ITEM2") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM3" SortExpression="ITEM3">

            <ItemTemplate>

                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("ITEM3") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM4" SortExpression="ITEM4">

            <ItemTemplate>

                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("ITEM4") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM5" SortExpression="ITEM5">

            <ItemTemplate>

                <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("ITEM5") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM6" SortExpression="ITEM6">

            <ItemTemplate>

                <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("ITEM6") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM7" 
            SortExpression="ITEM7">

            <ItemTemplate>

                 <asp:TextBox ID="TextBox7" runat="server" 
                    Text='<%# Bind("ITEM7") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM8" 
            SortExpression="ITEM8">

            <ItemTemplate>

                <asp:TextBox ID="TextBox8" runat="server" 
                    Text='<%# Bind("ITEM8") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM9" 
            SortExpression="ITEM9">

            <ItemTemplate>


                    <asp:TextBox ID="TextBox9" runat="server" 
                    Text='<%# Bind("ITEM9") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM10" 
            SortExpression="ITEM10">

            <ItemTemplate>


                    <asp:TextBox ID="TextBox10" runat="server" 
                    Text='<%# Bind("ITEM10") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM11" 
            SortExpression="ITEM11">

            <ItemTemplate>

                    T
                    <asp:TextBox ID="TextBox11" runat="server" 
                    Text='<%# Bind("ITEM11") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM12" 
            SortExpression="ITEM12">

            <ItemTemplate>


                    <asp:TextBox ID="TextBox12"   runat="server" 
                    Text='<%# Bind("ITEM12") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM13" 
            SortExpression="ITEM13">

            <ItemTemplate>


                    <asp:TextBox ID="TextBox13"  runat="server" 
                    Text='<%# Bind("ITEM13") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM14" 
            SortExpression="ITEM14">

            <ItemTemplate>


                    <asp:TextBox ID="TextBox14" runat="server" 
                    Text='<%# Bind("ITEM14") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM15" SortExpression="ITEM15">

            <ItemTemplate>

                <asp:TextBox ID="TextBox15" runat="server" Text='<%# Bind("ITEM15") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM16" 
            SortExpression="ITEM16">

            <ItemTemplate>

                <asp:TextBox ID="TextBox16" runat="server" 
                    Text='<%# Bind("ITEM16") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM17" SortExpression="ITEM17">

            <ItemTemplate>

                <asp:TextBox ID="TextBox17" runat="server" Text='<%# Bind("ITEM17") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM18" 
            SortExpression="ITEM18">

            <ItemTemplate>

                <asp:TextBox ID="TextBox18" runat="server" 
                    Text='<%# Bind("ITEM18") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM19" SortExpression="ITEM19">

            <ItemTemplate>

                <asp:TextBox ID="TextBox19" runat="server" Text='<%# Bind("ITEM19") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM20" 
            SortExpression="ITEM20">


            <ItemTemplate>

                <asp:TextBox ID="TextBox20" runat="server"
                    Text='<%# Bind("ITEM20") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ITEM21" SortExpression="ITEM21">

            <ItemTemplate>

                <asp:TextBox ID="TextBox21" runat="server" Text='<%# Bind("ITEM21") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

<asp:Button ID="Button1" runat="server" Text="Update" OnClick="Button1_Click" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings%>" 
    DeleteCommand="DELETE FROM [DATATABLE] WHERE [ITEM0] = @ITEM0" 
    InsertCommand="INSERT INTO [DATATABLE] ([ITEM0], [ITEM1], [ITEM2], [ITEM3], [ITEM4], [ITEM5], [ITEM6], [ITEM7], [ITEM8], [ITEM9], [ITEM10], [ITEM11], [ITEM12], [ITEM13], [ITEM14], [ITEM15], [ITEM16], [ITEM17], [ITEM18], [ITEM19], [ITEM20], [ITEM21]) VALUES (@ITEM0, @ITEM1, @ITEM2, @ITEM3, @ITEM4, @ITEM5, @ITEM6, @ITEM7, @ITEM8, @ITEM9, @ITEM10, @ITEM11, @ITEM12, @ITEM13, @ITEM14, @ITEM15, @ITEM16, @ITEM17, @ITEM18, @ITEM19, @ITEM20, @ITEM21)" 
    SelectCommand="SELECT * FROM [DATATABLE]" 

    UpdateCommand="UPDATE [DATATABLE] SET [ITEM1] = @ITEM1, [ITEM2] = @ITEM2, [ITEM3] = @ITEM3, [ITEM4] = @ITEM4, [ITEM5] = @ITEM5, [ITEM6] = @ITEM6, [ITEM7] = @ITEM7, [ITEM8] = @ITEM8, [ITEM9] = @ITEM9, [ITEM10] = @ITEM10, [ITEM11] = @ITEM11, [ITEM12] = @ITEM12, [ITEM13] = @ITEM13, [ITEM14] = @ITEM14, [ITEM15] = @ITEM15, [ITEM16] = @ITEM16, [ITEM17] = @ITEM17, [ITEM18] = @ITEM18, [ITEM19] = @ITEM19, [ITEM20] = @ITEM20, [ITEM21] = @ITEM21 WHERE [ITEM0] = @ITEM0">
    <DeleteParameters>
        <asp:Parameter Name="ITEM0" Type="String" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="ITEM0" Type="String" />
        <asp:Parameter Name="ITEM1" Type="Int32" />
        <asp:Parameter Name="ITEM2" Type="String" />
        <asp:Parameter Name="ITEM3" Type="String" />
        <asp:Parameter Name="ITEM4" Type="String" />
        <asp:Parameter Name="ITEM5" Type="String" />
        <asp:Parameter Name="ITEM6" Type="String" />
        <asp:Parameter Name="ITEM7" Type="Decimal" />
        <asp:Parameter Name="ITEM8" Type="Decimal" />
        <asp:Parameter Name="ITEM9" Type="Decimal" />
        <asp:Parameter Name="ITEM10" Type="Decimal" />
        <asp:Parameter Name="ITEM11" Type="Decimal" />
        <asp:Parameter Name="ITEM12" Type="Decimal" />
        <asp:Parameter Name="ITEM13" Type="Decimal" />
        <asp:Parameter Name="ITEM14" Type="Decimal" />
        <asp:Parameter Name="ITEM15" Type="Decimal" />
        <asp:Parameter Name="ITEM16" Type="DateTime" />
        <asp:Parameter Name="ITEM17" Type="Decimal" />
        <asp:Parameter Name="ITEM18" Type="DateTime" />
        <asp:Parameter Name="ITEM19" Type="Decimal" />
        <asp:Parameter Name="ITEM20" Type="DateTime" />
        <asp:Parameter Name="ITEM21" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="ITEM1" Type="Int32" />
        <asp:Parameter Name="ITEM2" Type="String" />
        <asp:Parameter Name="ITEM3" Type="String" />
        <asp:Parameter Name="ITEM4" Type="String" />
        <asp:Parameter Name="ITEM5" Type="String" />
        <asp:Parameter Name="ITEM6" Type="String" />
        <asp:Parameter Name="ITEM7" Type="Decimal" />
        <asp:Parameter Name="ITEM8" Type="Decimal" />
        <asp:Parameter Name="ITEM9" Type="Decimal" />
        <asp:Parameter Name="ITEM10" Type="Decimal" />
        <asp:Parameter Name="ITEM11" Type="Decimal" />
        <asp:Parameter Name="ITEM12" Type="Decimal" />
        <asp:Parameter Name="ITEM13" Type="Decimal" />
        <asp:Parameter Name="ITEM14" Type="Decimal" />
        <asp:Parameter Name="ITEM15" Type="Decimal" />
        <asp:Parameter Name="ITEM16" Type="DateTime" />
        <asp:Parameter Name="ITEM17" Type="Decimal" />
        <asp:Parameter Name="ITEM18" Type="DateTime" />
        <asp:Parameter Name="ITEM19" Type="Decimal" />
        <asp:Parameter Name="ITEM20" Type="DateTime" />
        <asp:Parameter Name="ITEM21" Type="String" />
        <asp:Parameter Name="ITEM0" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="WebForm2.aspx">View Mode</asp:HyperLink>

tCommand.CommandText = "UPDATE SITESTEST SET SITE_TYPE_ID = @SITE_TYPE_ID, ADDRESS = @ADDRESS, SITE_NAME = @SITE_NAME, CITY = @CITY, STATE = @STATE, STATUS = @STATUS, BAY_TOTAL_CAPACITY = @BAY_TOTAL_CAPACITY, BAY_TOTAL_WORKING = @BAY_TOTAL_WORKING, DSX1_CONNECTION_CAPACITY = @DSX1_CONNECTION_CAPACITY, DSX1_CONNECTION_ASSIGNED = @DSX1_CONNECTION_ASSIGNED, DSX3_CONNECTION_CAPACITY = @DSX3_CONNECTION_CAPACITY, DSX3_CONNECTION_ASSIGNED = @DSX3_CONNECTION_ASSIGNED, OCX_BULKHEADS_CAPACITY = @OCX_BULKHEADS_CAPACITY, OCX_BULKHEADS_ASSIGNED = @OCX_BULKHEADS_ASSIGNED, rack_26x15 = @rack_26x15, rack_26x15_date = @rack_26x15_date, rack_24x30 = @rack_24x30, rack_24x30_date = @rack_24x30_date, rack_24x42 = @rack_24x42, rack_24x42_date = @rack_24x42_date, notes = @notes" WHERE CLLI = @CLLI;

No Exception is thrown but no updates occur.

tCommand.CommandText = "DECLARE @CLLI VarChar UPDATE SITESTEST SET SITE_TYPE_ID = @SITE_TYPE_ID, ADDRESS = @ADDRESS, SITE_NAME = @SITE_NAME, CITY = @CITY, STATE = @STATE, STATUS = @STATUS, BAY_TOTAL_CAPACITY = @BAY_TOTAL_CAPACITY, BAY_TOTAL_WORKING = @BAY_TOTAL_WORKING, DSX1_CONNECTION_CAPACITY = @DSX1_CONNECTION_CAPACITY, DSX1_CONNECTION_ASSIGNED = @DSX1_CONNECTION_ASSIGNED, DSX3_CONNECTION_CAPACITY = @DSX3_CONNECTION_CAPACITY, DSX3_CONNECTION_ASSIGNED = @DSX3_CONNECTION_ASSIGNED, OCX_BULKHEADS_CAPACITY = @OCX_BULKHEADS_CAPACITY, OCX_BULKHEADS_ASSIGNED = @OCX_BULKHEADS_ASSIGNED, rack_26x15 = @rack_26x15, rack_26x15_date = @rack_26x15_date, rack_24x30 = @rack_24x30, rack_24x30_date = @rack_24x30_date, rack_24x42 = @rack_24x42, rack_24x42_date = @rack_24x42_date, notes = @notes" WHERE CLLI = @CLLI;

SQL Exception: Must declare the scalar variable "@CLLI".

tCommand.CommandText = "UPDATE SITESTEST SET SITE_TYPE_ID = @SITE_TYPE_ID, ADDRESS = @ADDRESS, SITE_NAME = @SITE_NAME, CITY = @CITY, STATE = @STATE, STATUS = @STATUS, BAY_TOTAL_CAPACITY = @BAY_TOTAL_CAPACITY, BAY_TOTAL_WORKING = @BAY_TOTAL_WORKING, DSX1_CONNECTION_CAPACITY = @DSX1_CONNECTION_CAPACITY, DSX1_CONNECTION_ASSIGNED = @DSX1_CONNECTION_ASSIGNED, DSX3_CONNECTION_CAPACITY = @DSX3_CONNECTION_CAPACITY, DSX3_CONNECTION_ASSIGNED = @DSX3_CONNECTION_ASSIGNED, OCX_BULKHEADS_CAPACITY = @OCX_BULKHEADS_CAPACITY, OCX_BULKHEADS_ASSIGNED = @OCX_BULKHEADS_ASSIGNED, rack_26x15 = @rack_26x15, rack_26x15_date = @rack_26x15_date, rack_24x30 = @rack_24x30, rack_24x30_date = @rack_24x30_date, rack_24x42 = @rack_24x42, rack_24x42_date = @rack_24x42_date, notes = @notes";

Updates every row in the database to match the first row in the gridview

CLLI            Varchar     Primary Key (ID)
SITE_TYPE_ID        Int
ADDRESS             Varchar
SITE_NAME           Varchar
CITY            Varchar
STATE           Varchar
STATUS          Varchar
BAY_TOTAL_CAPACITY      Numeric
BAY_TOTAL_WORKING       Numeric
DSX1_CONNECTION_CAPACITY    Numeric
DSX1_CONNECTION_ASSIGNED    Numeric
DSX3_CONNECTION_CAPACITY    Numeric
DSX3_CONNECTION_ASSIGNED    Numeric
OCX_BULKHEADS_CAPACITY  Numeric
OCX_BULKHEADS_ASSIGNED  Numeric
rack_26x15          Numeric
rack_26x15_date         datetime
rack_24x30          Numeric
rack_24x30_date         datetime
rack_24x42          Numeric
rack_24x42_date         datetime
notes           Varchar

Here are the columns in the table. CLLI is the primary key, which I have set up in the WHERE clause as WHERE CLLI = @CLLI. From what I can tell this is what you recommended, and what I think is correct, but as I said, with it no updates are being made. Thanks again.

Oh! I forgot to mention that the update function is a button. I know it is obvious from the code but just in case. — Daniel Sloan, Jul 24 '15 at 00:21
This isn't a "do my work for me" site. If there's something you don't understand, ask about a specific piece of code. Please don't just paste a wall of text. In this case, what have you tried? Do you understand why your code didn't work? Where do you think the problem is? What debugging have you done? etc. Perhaps most importantly, do you know what a `WHERE` clause is? — Basic, Jul 24 '15 at 00:24

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

First of all, you can replace your wall of code with something like the following:

for(int i = 1; i <= 20; i++) {
    TextBox t = (TextBox)GridView1.Rows[rowIndex].Cells[i-1].FindControl(String.Format("TextBox{0}", i));
    SqlParameter p = new SqlParameter(String.Format("@ITEM{0}", i), SqlDbType.Int);
    p.Value = t.Text;
    tCommand.Parameters.Add(p1);
}

(Note that we have to do i-1 for the cell as most arrays/lists in C# are 0-indexed but your textbox and Sql param names are 1-indexed. It's generally a good idea to avoid this as off-by-1 errors are easy to make and hard to spot)

Any time you write the same line 3 or more times with only trivial changes, you're wasting your (valuable) time. Use a loop instead.

As to why your code isn't working... The normal syntax to update one row is something like...

UPDATE {table} SET {field1Name} = {value1}, ..., {fieldnName} = {valuen} WHERE {condition}

If you omit the WHERE {condition}, it will update all rows in the table. In your case, the condition should be that the Id field in the table matches the Id for the record you want to update.

You can only update one row at a time so you'll need your update code in a loop, iterating over each row in turn. Assuming 20 fields for each row...

// Open connection
for(int row = 0; row < rowCount; row++) {
    // Build query
    for(int i = 1; i <= 20; i++) {
        // Set query parameters
    }
    //Execute query
}
// Close connection

Depending on how many rows you mean by "bulk", this may or may not be too slow. If you're trying to update more than a few hundred thousand rows, you might want to change your approach and look into bcp and alternatives

Edit (following on from comments):

To be more specific, your queries need to be generating SQL like this:

UPDATE {tablename} SET Field1='{Row1Textbox1Value}', Field2='{Row1Textbox2Value}' WHERE ID = {Row1Id};

UPDATE {tablename} SET Field1='{Row2Textbox1Value}', Field2='{Row2Textbox2Value}' WHERE ID = {Row2Id};

Now, if you haven't got an ID, you really need to go back a step and get one. Using anything other than a unique field is going to cause problems in future. Using a field that is unique but not the clustered index is going to have a performance cost.

If executing a query like that is giving you an exception, please let us know exactly what the exception is and we can track down the problem.

Incidentally, getting the SQL generated by SqlCommand is non-trivial. It's far simpler to use the SQL Server Activity Monitor and set up a filter to only list queries from your development machine (or to your test database or similar).

You'll then be able to see exactly what your code is doing. Compare that to the above examples, and we'll be able to start zeroing in on the problem.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 24 '15 at 00:37

Basic

26,321
24
115
201

We're talking 6000 rows. And I even told my boss "This is going to take a really long time," She told me to just do it that way regardless. The client instead of wanting to select what they want to see then generating the grid want to generate all 6000 rows then choose what they want. So I just stick these for loops under my button_click and put the WHERE back in my UPDATE statement? – Daniel Sloan Jul 24 '15 at 03:06
I know what the WHERE clause is. I had it as "WHERE ITEM20 = @ITEM20" but even though I had the parameters for it, it kept throwing an exception saying I didn't have the parameter set. So, I may not be using the right condition. So just to be clear, I just need to put these two loops under my Button_Click method? – Daniel Sloan Jul 24 '15 at 03:10
Also, to the initial comment, I'm not looking for anyone to do my work for me. I have been at this for 4 days. I was hired on as a Java developer, but was given this project in C# and ASP.net even though in the interview I said I have no experience with either one. So, only being 3 weeks in, and not receiving any training. This is why I am reaching out for help. And, I appreciate your time and Help. – Daniel Sloan Jul 24 '15 at 03:13
Yeah, the key thing you're missing is telling SQL which row to update - so you definitely need a WHERE clause on each update. To be fair, you don't _have_ to switch to using loops but your code will be very verbose otherwise! The error you were getting previously with the update is exactly the sort of thing to ask on this site. Add it back in, switch to using loops and then, if you're still having issues, update your question to include your new code and the error you get. We can go from there – Basic Jul 24 '15 at 07:11
I've made multiple attempts at fixing the SQL statement. I either receive an exception or there are no updates at all. Most examples that I find show the header column (usually a primary key) references in the WHERE clause, such as, "WHERE ID = @ID." This is what I am trying to do with CLLI (my header column). I need a WHERE clause which updates every row with the current data in the textbox. @Basic – Daniel Sloan Jul 24 '15 at 14:12
Well, a where clause matcehs 0 or more rows. Every row that is matched will have its specified values updated to be identical. Can you provide a rough structure for your table (most importantly, the ID/Primary Key field). `WHERE ID = @ID` is exactly what you need to do (assuming the field name is `ID`), but you need to get the ID for each row as you iterate through. If that's not one of your 20 values, you'll need to add it as a hidden field, or fetch it from the data set (assuming it's present). It's been quite a while since I've done Webforms but I'll help if possible. – Basic Jul 24 '15 at 17:43
So `ITEM1` contains the primary key on the table? – Basic Jul 24 '15 at 18:52
I updated my question with an outline of the table I am getting data from. I also labeled their type and the primary key. Thanks again. – Daniel Sloan Jul 24 '15 at 18:56
I had CLLI labled as ITEM0 (I got tired of changing all of the variables to generic ones, so I'm just using the actual now). The ITEM1 - ITEM20 values are what are in the new table in order. – Daniel Sloan Jul 24 '15 at 18:58
1

Thank you for all of the help. I managed to get to the point where instead of updating every row it only updates the first. I know at this point it's just a matter of using a loop to update each row (though I am struggling with that). I have actually moved on to a new part of the project and this weekend a colleague will be helping me with this code. Once I get this finished I will post my final code and check off the answer. Thanks again. – Daniel Sloan Jul 30 '15 at 14:38
@DanielSloan Did you ever get this working? – Basic Oct 02 '15 at 15:27
Yes I got it working. I moved on when I did not know where to place the for each loop. Later looking for information on how to do something similar with List Boxes there was a solution similar enough to apply to the GridView. A little over a month had passed before I found this solution on my own. – Daniel Sloan Oct 05 '15 at 13:32
Glad you got there in the end, apologies for not giving a clearer answer – Basic Oct 06 '15 at 08:36

Mass Updating Database ASP.Net and C#

Bulk Update

1 Answers1