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

Question

I'm working on a small offline C# application with an Access 2002 database (.mdb) and OleDb.

I have 2 tables where I need to insert data at the same time, one holding a foreign key of the other. So, to simplify let's say one table has 2 attributes: "idTable1" (auto-increment integer) and "number", and the other has 2 attributes: "idTable2" (auto-increment integer) and "fkTable1" (foreign key containing an integer value that matches an "idTable1" from table 1).

A foreach loop iterates over a collection and inserts each element in Table1. Then the idea is to use a SELECT @@Identity query on Table1 to get the auto-incrementing id field of the last record that was inserted, and insert that in Table2 as a foreign key.

I'm just trying the first part before I attempt to insert the foreign key: loop over a collection, insert each item in Table1 and get the idTable1 of the last inserted record. But whenever I try to execute SELECT @@Identity I get only 1 record in my database, even when the loop correctly iterates over all the collection items.

My code looks like this:

string queryInsertTable1 = "INSERT INTO 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 I comment out the last 3 lines:

commandStatement.CommandText = queryGetLastId;
LastInsertedId = (int)commandStatement.ExecuteScalar();

Then all records from Collection are correctly inserted in the BD. But as soon as I un-comment those, I get just 1 record inserted, while the value stored in "c" is the last element in the collection (so the loop worked fine).

I also tried calling commandStatement.Parameters.Clear() right after the commandStatement.ExecuteNonQuery() sentence, but that makes no difference (and it shouldn't, but I still tried).

I don't want to make things complicated by using transactions and such, if I can avoid them, since this is a very simple, single-computer, offline and small application. So if anyone knows what I could do to make that code work, I'd be very grateful :)

Andy Korneyev Andy Korneyev · Accepted Answer · 2015-05-14T14:26:30

Here: commandStatement.CommandText = queryGetLastId; you actually changing your command from inserting to selecting identity.

Thus, on the next iteration it will not insert anything, but again select for identity, that's why you're having only one record inserted into DB.

I think it's better to have two separate commands for inserting and for selecting identity.

Also note - you're trying to add new parameter into commandStatement on each iteration, so on iteration, say, N it will be N parameters. Either clear parameters before adding new one, or add parameter outside of loop and in the loop change its 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();
        }
    }
}

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

2 Answers