Mass Updating Database ASP.Net and C# -
i'm working on web page mass update table in database on sql server 2008 internship. stuck on how declare correct clause in commandtext. no where clause causes every row update same. clause where clauses stated where clli = @clli returns exception stating must declare scalar variable "@clli". if add declare clause @clli no exceptions occur no updates occur either. have updated code 3 sql statements have tried commandtext @ bottom of post . 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 [datatable] [item0] = @item0" insertcommand="insert [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 * [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 [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" clli = @clli; no exception thrown 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" clli = @clli; sql exception: must declare 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 database match first row in 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 columns in table. clli primary key, have set in clause clli = @clli. can tell recommended, , think correct, said, no updates being made. again.
first of all, can replace wall of code following:
for(int = 1; <= 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 have i-1 cell arrays/lists in c# 0-indexed textbox , sql param names 1-indexed. it's idea avoid off-by-1 errors easy make , hard spot)
any time write same line 3 or more times trivial changes, you're wasting (valuable) time. use loop instead.
as why code isn't working... normal syntax update 1 row like...
update {table} set {field1name} = {value1}, ..., {fieldnname} = {valuen} {condition} if omit where {condition}, update rows in table. in case, condition should id field in table matches id record want update.
you can update 1 row @ time you'll need update code in loop, iterating on each row in turn. assuming 20 fields each row...
// open connection for(int row = 0; row < rowcount; row++) { // build query for(int = 1; <= 20; i++) { // set query parameters } //execute query } // close connection depending on how many rows mean "bulk", may or may not slow. if you're trying update more few hundred thousand rows, might want change approach , bcp , alternatives
edit (following on comments):
to more specific, queries need generating sql this:
update {tablename} set field1='{row1textbox1value}', field2='{row1textbox2value}' id = {row1id};
update {tablename} set field1='{row2textbox1value}', field2='{row2textbox2value}' id = {row2id};
now, if haven't got id, need go step , one. using other unique field going cause problems in future. using field unique not clustered index going have performance cost.
if executing query giving exception, please let know exception , can track down problem.
incidentally, getting sql generated sqlcommand non-trivial. it's far simpler use sql server activity monitor , set filter list queries development machine (or test database or similar).
you'll able see code doing. compare above examples, , we'll able start zeroing in on problem.
Comments
Post a Comment