MS Access - C# - Retrieve the latest inserted guid

Question

Is there a way to retrieve the latest inserted guid in access with C#?

I tried this: Created a table Cars with a field Id of type autonumber, replicationID and a field Name varchar(250).

var command = myConnection.CreateCommand();
command.Connection.Open();
command.CommandText = "INSERT INTO Cars(Name) VALUES ('Pagani')";
command.ExecuteNonQuery();
command = context.Database.Connection.CreateCommand();
command.CommandText = "SELECT @@Identity";
Console.WriteLine(command.ExecuteScalar());
command.Connection.Close();

The issue which I am getting is:

Console.WriteLine(command.ExecuteScalar());  

always shows 0

EDIT

To create the table you can use this statement over the C# OleDb connection (I think that from MS Access query does not work)

CREATE TABLE [Cars] (
 [Id] guid not null DEFAULT GenGUID(),
 [Name] text null
);
ALTER TABLE [Cars] ADD CONSTRAINT [PK_Cars_6515ede4] PRIMARY KEY ([Id])

Answer 1

I know this is not exactly what you are asking for, but let me suggest an alternative solution which might solve your underlying problem.

Create the GUID in C# and pass it to your insert:

var newGuid = Guid.NewGuid();

var command = myConnection.CreateCommand();
command.Connection.Open();
command.CommandText = "INSERT INTO Cars(Id, Name) VALUES (?, 'Pagani')";
command.Parameters.AddWithValue("@Id", newGuid);   // Note: OleDb ignores the parameter name.
command.ExecuteNonQuery();

Console.WriteLine(newGuid);

GUIDs are unique. It really doesn't matter whether it is generated by your application or by the Access database driver.

This option is in all respects superior to reading the GUID afterwards:

• You only need one database access.

• It's less code.

• It's easier.

And you can still omit the GUID in your INSERT in cases where you don't need to know the GUID - no need to change existing code.

Answer 2

If SELECT @@IDENTITY does not work for "ReplicationID" AutoNumber fields then the most likely way to retrieve such a value for a new record is to use an Access DAO Recordset insert, like this:

// required COM reference:
//     Microsoft Office 14.0 Access Database Engine Object Library
var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
Microsoft.Office.Interop.Access.Dao.Database db = dbe.OpenDatabase(
        @"C:\Users\Public\Database1.accdb");
Microsoft.Office.Interop.Access.Dao.Recordset rst = db.OpenRecordset(
        "SELECT [Id], [Name] FROM [Cars] WHERE FALSE", 
        Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset);

rst.AddNew();
// new records are immediately assigned an AutoNumber value ...
string newReplId = rst.Fields["Id"].Value;  // ... so retrieve it
// the returned string is of the form
//     {guid {1D741E80-6847-4CB2-9D96-35F460AEFB19}}
// so remove the leading and trailing decorators
newReplId = newReplId.Substring(7, newReplId.Length - 9);
// add other field values as needed
rst.Fields["Name"].Value = "Pagani";
// commit the new record
rst.Update();

db.Close();

Console.WriteLine("New record added with [Id] = {0}", newReplId);

which produces

New record added with [Id] = 1D741E80-6847-4CB2-9D96-35F460AEFB19

Answer 3

You can try like this using the OUTPUT :

INSERT INTO myTable(myGUID)
OUTPUT INSERTED.myGUID
VALUES(GenGUID())

You can try like this:

string str1 = "INSERT INTO Cars(Name) VALUES ('Pagani')";
string str2 = "Select @@Identity";
int ID;
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(str1, conn))
  {
    conn.Open();
    cmd.ExecuteNonQuery();
    cmd.CommandText = str2;
    ID = (int)cmd.ExecuteScalar();
  }
}