Trying to create foreign keys and insert their values in a .mdb database using C# and OleDb

Question

I have a database with 3 tables. One of them holds a relation of the other two. E.g.:

Table1 = idTable1 (PK_Table1), attribute1, attribute2, attribute3

Table2 = idTable2 (PK_Table2), attribute1

Table3 = idTable3 (PK_Table3), attribute1 (FK relating to idTable1), attribute2 (FK relating to idTable2)

All primary keys are auto-incrementing fields assigned automatically by Access (2002 version, that's why my db is a .mdb).

In my code I insert data in Table1 and Table2 using some code like this:

public void insert()
{
    string query = "INSERT INTO Table1 (attribute1,attribute2,attribute3) VALUES (?,?,?)";
    OleDbConnection dbConnection = new OleDbConnection();
    dbConnection.ConnectionString = connStr;
    try
    {
        dbConnection.Open();
        OleDbCommand commandStatement = new OleDbCommand();
        OleDbCommand primarykey = new OleDbCommand("ALTER TABLE Table1 ADD CONSTRAINT pk_Table1 primary key(idTable1)", dbConnection);
        primarykey.Connection = dbConnection;

        commandStatement.Connection = dbConnection;
        commandStatement.CommandText = query;
        commandStatement.Parameters.Add("attribute1", OleDbType.Integer).Value = attribute1;                
                commandStatement.Parameters.Add("attribute2", OleDbType.Integer).Value = attribute2;
        commandStatement.Parameters.Add("attribute3", OleDbType.Integer).Value = attribute3;

        commandStatement.ExecuteNonQuery();
        primarykey.ExecuteNonQuery(); 
        dbConnection.Close();
        dbConnection.Dispose();
        commandStatement.Dispose();
                primarykey.Dispose();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

(and then something like that for Table2 as well).

For each row in Table1, I insert about 40 rows in Table2 (they're tables holding info of a contest and their contestants).

Now I need to create a relation between those two, using Table3, which must reference the id of both tables as foreign keys.

And that's where I'm lost. I don't know how to say "take the id of the row you just inserted in Table1 and then the id of a row you just inserted in Table2 and insert them as a new record in Table3".

Is there a way to get the autoincrementing IDs that are being assigned by the database, as soon as I insert a record?

Sami Kuhmonen Sami Kuhmonen · Accepted Answer · 2015-05-13T14:27:30

After inserting a row you can use SELECT @@Identity to retrieve the autoincremented value. Store it, insert the other row, do the same select and then insert these values to the link table.

More information (for VB.NET, but easily understandable) from Microsoft at HOW TO: Retrieve the Identity Value While Inserting Records into Access Database

Trying to create foreign keys and insert their values in a .mdb database using C# and OleDb

1 Answers