Get Auto id when inserting record into ms Access

[James Alba]

Hey all,

I am accessing an ms access database using .NET and C#. Like so,

/* Create the database connection. */
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + Name);
connection.Open();

And all is good.

My "ticket" table has the setup: auto number (PK), name and event_id.

I am successfully inserting records into the "ticket" table, BUT HOW can I
get the auto number of my PK that MS Access assigns???

I really need this PK!!!

for (int i = 0; i < j; ++i)
{
OleDbCommand c = new OleDbCommand("insert into ticket (name, event)
values('" + Customer + "', '" + ID + "')", connection);
c.ExecuteNonQuery();
}

Thankyou all!

 

[James Alba]

What I am after is something like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;816112

But simpler!

Do I really need a OleDbDataAdapter and event handler?

I just want something simple...

Ideas, comments?

 

[dd]

First thing crosses my mind is max(ID).
The last record inserted should get the max number in the auto number field.

-Duy

 

[Steve]

I just did this morning. I was't terribly concerned with elegance because I
know once proof of concept gets signed off, we will move it to SqlServer
where stored procedures make this easy.

Anyway, what I do is use Access Queries(Access' version of Sql sprocs)
I have one for the insert that take the parameters of the insert, say
_userName and _email
then another to fetch the ID where UserName = _username and Email = _email.

Since I have already set up the command object with the parameters, it's a
matter of calling ExecuteNonQuery for the update, then ExecuteReader for the
ID changing the Command object's CommandText to reflect the correct query.

It works. Not the prettiest thing, but works.