c# - Can't write in sql table with parameters -
i want write data in sql table following code en error.
when have in visual studio can see there data in @p1, etc...
sqlcommand send = new sqlcommand("insert test(code,type,datum,uur,controller,fout,teller,omschrijving,graad,var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,var11,var12,var13,var14,var15,var16,var17,var18,var19,var20,var21,var22,var23,var24,var25,var26,var27,var28,var29,var30,var31,var32,var33,var34,var35,var36,var37,var38,var39,var40)" + "values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49)", connection); while (foutinformatie[0, lusteller] != null) //zolang uitvoeren als er data { //rij per rij in tabel plaatsensd key = foutinformatie[0, lusteller] + "/" + foutinformatie[1, lusteller] + "/" + foutinformatie[2, lusteller] + "/" + foutinformatie[3, lusteller] + "/" + foutinformatie[4, lusteller]; send.parameters.clear(); sqlparameter myparam1 = new sqlparameter("@p1", sqldbtype.text); myparam1.value = key; sqlparameter myparam2 = new sqlparameter("@p2", sqldbtype.text); myparam2.value = foutinformatie[0, lusteller]; sqlparameter myparam3 = new sqlparameter("@p3", sqldbtype.text); myparam3.value = foutinformatie[1, lusteller]; sqlparameter myparam4 = new sqlparameter("@p4", sqldbtype.text); myparam4.value = foutinformatie[2, lusteller]; sqlparameter myparam5 = new sqlparameter("@p5", sqldbtype.text); myparam5.value = foutinformatie[3, lusteller]; sqlparameter myparam6 = new sqlparameter("@p6", sqldbtype.text); myparam6.value = foutinformatie[4, lusteller]; sqlparameter myparam7 = new sqlparameter("@p7", sqldbtype.text); myparam7.value = foutinformatie[5, lusteller]; sqlparameter myparam8 = new sqlparameter("@p8", sqldbtype.text); myparam8.value = foutinformatie[6, lusteller]; sqlparameter myparam9 = new sqlparameter("@p9", sqldbtype.text); myparam9.value = foutinformatie[7, lusteller]; sqlparameter myparam10 = new sqlparameter("@p10", sqldbtype.text); myparam10.value = foutinformatie[8, lusteller]; sqlparameter myparam11 = new sqlparameter("@p11", sqldbtype.text); myparam11.value = foutinformatie[9, lusteller]; sqlparameter myparam12 = new sqlparameter("@p12", sqldbtype.text); myparam12.value = foutinformatie[10, lusteller]; sqlparameter myparam13 = new sqlparameter("@p13", sqldbtype.text); myparam13.value = foutinformatie[11, lusteller]; sqlparameter myparam14 = new sqlparameter("@p14", sqldbtype.text); myparam14.value = foutinformatie[12, lusteller]; sqlparameter myparam15 = new sqlparameter("@p15", sqldbtype.text); myparam15.value = foutinformatie[13, lusteller]; sqlparameter myparam16 = new sqlparameter("@p16", sqldbtype.text); myparam16.value = foutinformatie[14, lusteller]; sqlparameter myparam17 = new sqlparameter("@p17", sqldbtype.text); myparam17.value = foutinformatie[15, lusteller]; sqlparameter myparam18 = new sqlparameter("@p18", sqldbtype.text); myparam18.value = foutinformatie[16, lusteller]; sqlparameter myparam19 = new sqlparameter("@p19", sqldbtype.text); myparam19.value = foutinformatie[17, lusteller]; sqlparameter myparam20 = new sqlparameter("@p20", sqldbtype.text); myparam20.value = foutinformatie[18, lusteller]; sqlparameter myparam21 = new sqlparameter("@p21", sqldbtype.text); myparam21.value = foutinformatie[19, lusteller]; sqlparameter myparam22 = new sqlparameter("@p22", sqldbtype.text); myparam22.value = foutinformatie[20, lusteller]; sqlparameter myparam23 = new sqlparameter("@p23", sqldbtype.text); myparam23.value = foutinformatie[21, lusteller]; sqlparameter myparam24 = new sqlparameter("@p24", sqldbtype.text); myparam24.value = foutinformatie[22, lusteller]; sqlparameter myparam25 = new sqlparameter("@p25", sqldbtype.text); myparam25.value = foutinformatie[23, lusteller]; sqlparameter myparam26 = new sqlparameter("@p26", sqldbtype.text); myparam26.value = foutinformatie[24, lusteller]; sqlparameter myparam27 = new sqlparameter("@p27", sqldbtype.text); myparam27.value = foutinformatie[25, lusteller]; sqlparameter myparam28 = new sqlparameter("@p28", sqldbtype.text); myparam28.value = foutinformatie[26, lusteller]; sqlparameter myparam29 = new sqlparameter("@p29", sqldbtype.text); myparam29.value = foutinformatie[27, lusteller]; sqlparameter myparam30 = new sqlparameter("@p30", sqldbtype.text); myparam30.value = foutinformatie[28, lusteller]; sqlparameter myparam31 = new sqlparameter("@p31", sqldbtype.text); myparam31.value = foutinformatie[29, lusteller]; sqlparameter myparam32 = new sqlparameter("@p32", sqldbtype.text); myparam32.value = foutinformatie[30, lusteller]; sqlparameter myparam33 = new sqlparameter("@p33", sqldbtype.text); myparam33.value = foutinformatie[31, lusteller]; sqlparameter myparam34 = new sqlparameter("@p34", sqldbtype.text); myparam34.value = foutinformatie[32, lusteller]; sqlparameter myparam35 = new sqlparameter("@p35", sqldbtype.text); myparam35.value = foutinformatie[33, lusteller]; sqlparameter myparam36 = new sqlparameter("@p36", sqldbtype.text); myparam36.value = foutinformatie[34, lusteller]; sqlparameter myparam37 = new sqlparameter("@p37", sqldbtype.text); myparam37.value = foutinformatie[35, lusteller]; sqlparameter myparam38 = new sqlparameter("@p38", sqldbtype.text); myparam38.value = foutinformatie[36, lusteller]; sqlparameter myparam39 = new sqlparameter("@p39", sqldbtype.text); myparam39.value = foutinformatie[37, lusteller]; sqlparameter myparam40 = new sqlparameter("@p40", sqldbtype.text); myparam40.value = foutinformatie[38, lusteller]; sqlparameter myparam41 = new sqlparameter("@p41", sqldbtype.text); myparam41.value = foutinformatie[39, lusteller]; sqlparameter myparam42 = new sqlparameter("@p42", sqldbtype.text); myparam42.value = foutinformatie[40, lusteller]; sqlparameter myparam43 = new sqlparameter("@p43", sqldbtype.text); myparam43.value = foutinformatie[41, lusteller]; sqlparameter myparam44 = new sqlparameter("@p44", sqldbtype.text); myparam44.value = foutinformatie[42, lusteller]; sqlparameter myparam45 = new sqlparameter("@p45", sqldbtype.text); myparam45.value = foutinformatie[43, lusteller]; sqlparameter myparam46 = new sqlparameter("@p46", sqldbtype.text); myparam46.value = foutinformatie[44, lusteller]; sqlparameter myparam47 = new sqlparameter("@p47", sqldbtype.text); myparam47.value = foutinformatie[45, lusteller]; sqlparameter myparam48 = new sqlparameter("@p48", sqldbtype.text); myparam48.value = foutinformatie[46, lusteller]; sqlparameter myparam49 = new sqlparameter("@p49", sqldbtype.text); myparam49.value = foutinformatie[47, lusteller]; send.parameters.add(myparam1); send.parameters.add(myparam2); send.parameters.add(myparam3); send.parameters.add(myparam4); send.parameters.add(myparam5); send.parameters.add(myparam6); send.parameters.add(myparam7); send.parameters.add(myparam8); send.parameters.add(myparam9); send.parameters.add(myparam10); send.parameters.add(myparam11); send.parameters.add(myparam12); send.parameters.add(myparam13); send.parameters.add(myparam14); send.parameters.add(myparam15); send.parameters.add(myparam16); send.parameters.add(myparam17); send.parameters.add(myparam18); send.parameters.add(myparam19); send.parameters.add(myparam20); send.parameters.add(myparam21); send.parameters.add(myparam22); send.parameters.add(myparam23); send.parameters.add(myparam24); send.parameters.add(myparam25); send.parameters.add(myparam26); send.parameters.add(myparam27); send.parameters.add(myparam28); send.parameters.add(myparam29); send.parameters.add(myparam30); send.parameters.add(myparam31); send.parameters.add(myparam32); send.parameters.add(myparam33); send.parameters.add(myparam34); send.parameters.add(myparam35); send.parameters.add(myparam36); send.parameters.add(myparam37); send.parameters.add(myparam38); send.parameters.add(myparam39); send.parameters.add(myparam40); send.parameters.add(myparam41); send.parameters.add(myparam42); send.parameters.add(myparam43); send.parameters.add(myparam44); send.parameters.add(myparam45); send.parameters.add(myparam46); send.parameters.add(myparam47); send.parameters.add(myparam48); send.parameters.add(myparam49); try { int pos = array.indexof(keys, key); if (pos == -1) { connection.open(); send.executenonquery(); connection.close(); } else { } } catch { connection.close(); // messagebox.show("can not open connection ! "); } lusteller = lusteller + 1; } } error:
the parameterized query '(@p1 text,@p2 text,@p3 text,@p4 text,@p5 text,@p6 text,@p7 text,' expects parameter '@p9', not supplied.
do have nullable columns in database? problem. if value null, have explicitly pass dbnull.value. if not, parameter may not recognized correctly , reported missing. assignment following:
myparamx.value = foutinformatie[0, lusteller] ?? dbnull.value; i recommend create parameters in more efficient way. first, create parameters command once before execute , call send.prepare(). in loop set values each row , execute command. way more efficient.
connection.open(); sqlcommand send = new sqlcommand( "insert test(code,type,datum,uur,controller,fout,teller,omschrijving,graad,var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,var11,var12,var13,var14,var15,var16,var17,var18,var19,var20,var21,var22,var23,var24,var25,var26,var27,var28,var29,var30,var31,var32,var33,var34,var35,var36,var37,var38,var39,var40)" + "values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49)", connection); (int = 1; <= 49; i++) { send.parameters.add("@p" + i, sqldbtype.text); } // prepare command (see documentation) send.prepare(); //zolang uitvoeren als er data while (foutinformatie[0, lusteller] != null) { //rij per rij in tabel plaatsensd key = foutinformatie[0, lusteller] + "/" + foutinformatie[1, lusteller] + "/" + foutinformatie[2, lusteller] + "/" + foutinformatie[3, lusteller] + "/" + foutinformatie[4, lusteller]; send.parameters[0].value = key; // assign values in loop regarding dbnull (int = 1; < 49; i++) { send.parameters[i].value = foutinformatie[i-1, lusteller] ?? dbnull.value; } send.executenonquery(); lusteller = lusteller + 1; } i did not run code, maybe there still index offset problem. should demonstrate how solve more elegant.
Comments
Post a Comment