Get Auto id when inserting record into ms Access

5 Oct 2005 11:29 AM

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!

5 Oct 2005 12:12 PM

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?

Show quoteHide quote

"James Alba" <a@a.com> wrote in message
news:#azEHAayFHA.3892@TK2MSFTNGP12.phx.gbl...
> 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!
>
>

5 Oct 2005 4:35 PM

dd

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

-Duy

James Alba wrote:
Show quoteHide quote

> 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?
>
> "James Alba" <a@a.com> wrote in message
> news:#azEHAayFHA.3892@TK2MSFTNGP12.phx.gbl...
>
>>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!
>>
>>
>
>
>

5 Oct 2005 5:15 PM

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.

Show quoteHide quote

"James Alba" <a@a.com> wrote in message
news:uIyabYayFHA.2212@TK2MSFTNGP15.phx.gbl...
> 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?
>
> "James Alba" <a@a.com> wrote in message
> news:#azEHAayFHA.3892@TK2MSFTNGP12.phx.gbl...
> > 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!
> >
> >
>
>