1

I'm using the OleDBConnectivity system to connect and use a Microsoft Access database.

I'm adding a record to the table in the database called "PayInfo", and the primary key is automatically set to the next available integer. How do I check what the value of the primary key it was assigned to is? I know this is probably an awful idea, but the only thing I could think of was to re-read the database using the entered values. The issue with this, though it's very unlikely, is that its possible to have 2 identical records stored in the database, their only difference being the primary key, and I need to be able to read the specific one.

My current subroutine for adding the record to the database is as follows:

OleDbCommand command = connection.CreateCommand();
connection.Open();

// The SQL statement:
command.CommandText = "INSERT INTO PayInfo ([UserID], [FullName], [CardType], [CardNo], [CVV], [ExpDate], [Address])" +
                      "VALUES ('" + Global.UserID.ToString() + "','" + PayInfo[6] + "','" + PayInfo[0] + "','" + PayInfo[1] + "','" + PayInfo[2] + "','" + exp + "','" + adress + "')";

command.Connection = connection;

command.ExecuteNonQuery(); //executes the SQL command.

connection.Close();
return true;  //Successful transaction
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Milosz Wedrowski
  • 15
  • 4
  • 2
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) – marc_s Oct 31 '22 at 18:40

1 Answers1

2

After executing your insert query, you need to execute another query to get the generated primary key:

command.Parameters.Clear();
command.CommandText = "SELECT @@IDENTITY";
int primaryKey = Convert.ToInt32(Command.ExecuteScalar());

See Microsoft: Retrieving Identity or Autonumber Values for more details:

The Jet database engine does not support the execution of multiple statements in a batch or the use of output parameters, so it is not possible to use either of these techniques to return the new Autonumber value assigned to an inserted row. However, you can add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

Fruchtzwerg
  • 10,999
  • 12
  • 40
  • 49
  • `@@IDENTITY` might not be the right choice, e.g. if `PayInfo` has any triggers-- see [this question](https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide) – John Wu Oct 31 '22 at 19:21