海量更新数据库.Net和c#
本文关键字：Net 数据库 更新 | 更新日期: 2023-09-27 18:04:17
我目前正在制作一个网页，该网页将为我的实习在SQL Server 2008数据库中批量更新一个表。我卡住了如何在CommandText中正确声明正确的WHERE子句。没有WHERE子句导致每一行更新都是相同的。将WHERE子句声明为WHERE CLLI = @CLLI的子句返回一个声明Must declare Scalar Variable "@CLLI"的异常。如果我为@CLLI添加DECLARE子句，则不会发生异常，但也不会发生更新。我已经更新了我的代码与3 SQL语句，我已经尝试了我的CommandText在帖子的底部。如有任何帮助，我将不胜感激，谢谢。
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" %>
<标题>批量更新
<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;
没有抛出异常，但没有更新发生。
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异常:必须声明标量变量"@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";
更新数据库中的每一行以匹配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
下面是表中的列。CLLI是主键，我在WHERE子句中将其设置为WHERE CLLI = @CLLI。据我所知，这是你推荐的，我认为是正确的，但正如我所说，它没有更新。再次感谢。
首先，您可以用以下内容替换您的代码墙:
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);
}
(请注意，我们必须为单元格做i-1，因为c#中的大多数数组/列表都是0索引，但您的文本框和Sql参数名称是1索引。通常避免这种情况是一个好主意，因为off-by-1错误很容易犯，而且很难发现)
任何时候你把同一行写了3次或更多次，只做了一些微不足道的改变，你都是在浪费你的(宝贵的)时间。请使用循环。
至于为什么你的代码不工作…更新一行的正常语法类似于…
UPDATE {table} SET {field1Name} = {value1}, ..., {fieldnName} = {valuen} WHERE {condition}
如果省略WHERE {condition}，它将更新表中的所有行。在您的例子中，条件应该是表中的Id字段与您想要更新的记录的Id匹配。
一次只能更新一行，因此需要循环更新代码，依次遍历每一行。假设每行有20个字段…
// 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
根据"bulk"指的是多少行，这可能太慢，也可能太慢。如果您想要更新几十万行以上的数据，您可能需要改变您的方法，并查看bcp和替代品
编辑(在注释之后):
更具体地说，您的查询需要生成如下SQL:
UPDATE {tablename} SET Field1='{Row1Textbox1Value}', Field2='{Row1Textbox2Value}' WHERE ID = {Row1Id};
UPDATE {tablename} SET Field1='{Row2Textbox1Value}', Field2='{Row2Textbox2Value}' WHERE ID = {Row2Id};
现在，如果你还没有ID，你真的需要后退一步去获得一个。使用惟一字段以外的任何字段将来都会引起问题。使用唯一但不是聚集索引的字段将产生性能成本。
如果执行这样的查询会给你一个异常，请让我们确切地知道异常是什么，我们可以跟踪问题。
顺便说一下，获取SqlCommand生成的SQL是非常重要的。使用SQL Server Activity Monitor并设置一个过滤器来只列出来自开发机器(或测试数据库或类似数据库)的查询要简单得多。
你将能够看到你的代码在做什么。将其与上面的示例进行比较，我们将能够开始将问题归零。