c# - Loop to insert data in OleDb breaks when I try to execute more than 1 query -

i'm working on small offline c# application access 2002 database (.mdb) , oledb.

i have 2 tables need insert data @ same time, 1 holding foreign key of other. so, simplify let's 1 table has 2 attributes: "idtable1" (auto-increment integer) , "number", , other has 2 attributes: "idtable2" (auto-increment integer) , "fktable1" (foreign key containing integer value matches "idtable1" table 1).

a foreach loop iterates on collection , inserts each element in table1. idea use select @@identity query on table1 auto-incrementing id field of last record inserted, , insert in table2 foreign key.

i'm trying first part before attempt insert foreign key: loop on collection, insert each item in table1 , idtable1 of last inserted record. whenever try execute select @@identity 1 record in database, when loop correctly iterates on collection items.

my code looks this:

string queryinserttable1 = "insert table1 (numero) values (?)"; string querygetlastid = "select @@identity";  using (oledbconnection dbconnection = new oledbconnection(strdeconexion)) {     using (oledbcommand commandstatement = new oledbcommand(queryinserttable1, dbconnection))     {         dbconnection.open();         foreach (int c in collection)         {             commandstatement.parameters.addwithvalue("", c);             commandstatement.executenonquery();             commandstatement.commandtext = querygetlastid;             lastinsertedid = (int)commandstatement.executescalar();         }     } } 

if comment out last 3 lines:

commandstatement.commandtext = querygetlastid; lastinsertedid = (int)commandstatement.executescalar(); 

then records collection correctly inserted in bd. un-comment those, 1 record inserted, while value stored in "c" last element in collection (so loop worked fine).

i tried calling commandstatement.parameters.clear() right after commandstatement.executenonquery() sentence, makes no difference (and shouldn't, still tried).

i don't want make things complicated using transactions , such, if can avoid them, since simple, single-computer, offline , small application. if knows make code work, i'd grateful :)

here: commandstatement.commandtext = querygetlastid; changing command inserting selecting identity.

thus, on next iteration not insert anything, again select identity, that's why you're having 1 record inserted db.

i think it's better have 2 separate commands inserting , selecting identity.

also note - you're trying add new parameter commandstatement on each iteration, on iteration, say, n n parameters. either clear parameters before adding new one, or add parameter outside of loop , in loop change value only.

using (oledbconnection dbconnection = new oledbconnection(strdeconexion)) {     using (oledbcommand commandstatement = new oledbcommand(queryinserttable1, dbconnection))     using (oledbcommand commandidentity = new oledbcommand(querygetlastid , dbconnection))     {             commandstatement.parameters.add(new oledbparameter());          dbconnection.open();         foreach (int c in collection)         {             commandstatement.parameters[0].value = c;             commandstatement.executenonquery();              lastinsertedid = (int)commandidentity.executescalar();         }     } }