loading variable with AutoNumber field value after insert?

14 Jan 2007 3:08 PM

jmar

I'm hoping someone out there can give me a little guidance. I have
an Access Database "Customer.MDB" with a table "CustInfo" that
has the following design:

Field:    DataType
CustID     AutoNumber
Name    Text
Title    Text
Phone    Text

'=========================================================
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\customer.mdb;")
Dim MyCommand As New OleDbCommand
Dim ra As Integer

Conn.open
cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
(NameTxt, TitleText, PhoneNum)
MyCommand = New OleDbCommand(cmndSTR, conn)
ra = MyCommand.ExecuteNonQuery()
'============================================================

My question is this: Once this code has been executed and a record has
been added, what is an easy way for me to load a variable (call it
"intCustID") with the CustID value (the AutoNumber field) of the
record that was just added?

A code snippet would be GREATLY appreciated...

Jmar

14 Jan 2007 8:16 PM

Harry Strybos

Show quote Hide quote

"jmar" <jmarz***@yahoo.com> wrote in message
news:1168787283.160528.121140@11g2000cwr.googlegroups.com...
> I'm hoping someone out there can give me a little guidance. I have
> an Access Database "Customer.MDB" with a table "CustInfo" that
> has the following design:
>
> Field: DataType
> CustID AutoNumber
> Name Text
> Title Text
> Phone Text
>
> '=========================================================
> Dim conn As New
> OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=c:\customer.mdb;")
> Dim MyCommand As New OleDbCommand
> Dim ra As Integer
>
> Conn.open
> cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
> (NameTxt, TitleText, PhoneNum)
> MyCommand = New OleDbCommand(cmndSTR, conn)
> ra = MyCommand.ExecuteNonQuery()
> '============================================================
>
>
> My question is this: Once this code has been executed and a record has
> been added, what is an easy way for me to load a variable (call it
> "intCustID") with the CustID value (the AutoNumber field) of the
> record that was just added?
>
> A code snippet would be GREATLY appreciated...
>
> Jmar
>

Try searching the web with "ADO.Net + get identity column" (without the
quotes). You will gets lots of help.

15 Jan 2007 3:47 AM

jmar

I appreciate your response, but I'm wondering if there is a more
specific way to do it considering the method I am using to add the
record to the table

When I search the web, I can find several ways, but I'm looking to see
if there is some way that I can do it within the context of my code
(perhaps a Select command or something?). Any ideas out there?

Thanks,

Jmar

Harry Strybos wrote:
Show quoteHide quote

> "jmar" <jmarz***@yahoo.com> wrote in message
> news:1168787283.160528.121140@11g2000cwr.googlegroups.com...
> > I'm hoping someone out there can give me a little guidance. I have
> > an Access Database "Customer.MDB" with a table "CustInfo" that
> > has the following design:
> >
> > Field: DataType
> > CustID AutoNumber
> > Name Text
> > Title Text
> > Phone Text
> >
> > '=========================================================
> > Dim conn As New
> > OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
> > Source=c:\customer.mdb;")
> > Dim MyCommand As New OleDbCommand
> > Dim ra As Integer
> >
> > Conn.open
> > cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
> > (NameTxt, TitleText, PhoneNum)
> > MyCommand = New OleDbCommand(cmndSTR, conn)
> > ra = MyCommand.ExecuteNonQuery()
> > '============================================================
> >
> >
> > My question is this: Once this code has been executed and a record has
> > been added, what is an easy way for me to load a variable (call it
> > "intCustID") with the CustID value (the AutoNumber field) of the
> > record that was just added?
> >
> > A code snippet would be GREATLY appreciated...
> >
> > Jmar
> >
> Try searching the web with "ADO.Net + get identity column" (without the
> quotes). You will gets lots of help.

15 Jan 2007 4:31 AM

Stephany Young

' Begin a transaction here
cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES (NameTxt,
TitleText, PhoneNum)
MyCommand = New OleDbCommand(cmndSTR, conn)
ra = MyCommand.ExecuteNonQuery()
MyCommand.CommandText = "select max(CustID) from CustInfo"
intCustID = MyCommand.ExecuteScalar()
' Commit the transaction here

Show quoteHide quote

"jmar" <jmarz***@yahoo.com> wrote in message
news:1168832858.474638.19330@s34g2000cwa.googlegroups.com...
>I appreciate your response, but I'm wondering if there is a more
> specific way to do it considering the method I am using to add the
> record to the table
>
> When I search the web, I can find several ways, but I'm looking to see
> if there is some way that I can do it within the context of my code
> (perhaps a Select command or something?). Any ideas out there?
>
> Thanks,
>
> Jmar
>
>
> Harry Strybos wrote:
>> "jmar" <jmarz***@yahoo.com> wrote in message
>> news:1168787283.160528.121140@11g2000cwr.googlegroups.com...
>> > I'm hoping someone out there can give me a little guidance. I have
>> > an Access Database "Customer.MDB" with a table "CustInfo" that
>> > has the following design:
>> >
>> > Field: DataType
>> > CustID AutoNumber
>> > Name Text
>> > Title Text
>> > Phone Text
>> >
>> > '=========================================================
>> > Dim conn As New
>> > OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
>> > Source=c:\customer.mdb;")
>> > Dim MyCommand As New OleDbCommand
>> > Dim ra As Integer
>> >
>> > Conn.open
>> > cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
>> > (NameTxt, TitleText, PhoneNum)
>> > MyCommand = New OleDbCommand(cmndSTR, conn)
>> > ra = MyCommand.ExecuteNonQuery()
>> > '============================================================
>> >
>> >
>> > My question is this: Once this code has been executed and a record has
>> > been added, what is an easy way for me to load a variable (call it
>> > "intCustID") with the CustID value (the AutoNumber field) of the
>> > record that was just added?
>> >
>> > A code snippet would be GREATLY appreciated...
>> >
>> > Jmar
>> >
>> Try searching the web with "ADO.Net + get identity column" (without the
>> quotes). You will gets lots of help.
>

15 Jan 2007 2:52 PM

raibeart

SELECT Max(CustID) FROM CustInfo

If you were using SQL Server, you could use a stored procedure that did
teh insert and then returns the record inserted.

jmar wrote:
Show quoteHide quote

> I appreciate your response, but I'm wondering if there is a more
> specific way to do it considering the method I am using to add the
> record to the table
>
> When I search the web, I can find several ways, but I'm looking to see
> if there is some way that I can do it within the context of my code
> (perhaps a Select command or something?). Any ideas out there?
>
> Thanks,
>
> Jmar
>
>
> Harry Strybos wrote:
> > "jmar" <jmarz***@yahoo.com> wrote in message
> > news:1168787283.160528.121140@11g2000cwr.googlegroups.com...
> > > I'm hoping someone out there can give me a little guidance. I have
> > > an Access Database "Customer.MDB" with a table "CustInfo" that
> > > has the following design:
> > >
> > > Field: DataType
> > > CustID AutoNumber
> > > Name Text
> > > Title Text
> > > Phone Text
> > >
> > > '=========================================================
> > > Dim conn As New
> > > OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
> > > Source=c:\customer.mdb;")
> > > Dim MyCommand As New OleDbCommand
> > > Dim ra As Integer
> > >
> > > Conn.open
> > > cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
> > > (NameTxt, TitleText, PhoneNum)
> > > MyCommand = New OleDbCommand(cmndSTR, conn)
> > > ra = MyCommand.ExecuteNonQuery()
> > > '============================================================
> > >
> > >
> > > My question is this: Once this code has been executed and a record has
> > > been added, what is an easy way for me to load a variable (call it
> > > "intCustID") with the CustID value (the AutoNumber field) of the
> > > record that was just added?
> > >
> > > A code snippet would be GREATLY appreciated...
> > >
> > > Jmar
> > >
> > Try searching the web with "ADO.Net + get identity column" (without the
> > quotes). You will gets lots of help.

15 Jan 2007 8:34 PM

gene kelley

Show quote Hide quote

On 14 Jan 2007 07:08:03 -0800, "jmar" <jmarz***@yahoo.com> wrote:

>I'm hoping someone out there can give me a little guidance. I have
>an Access Database "Customer.MDB" with a table "CustInfo" that
>has the following design:
>
>Field:    DataType
>CustID     AutoNumber
>Name    Text
>Title    Text
>Phone    Text
>
>'=========================================================
>Dim conn As New
>OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
>Source=c:\customer.mdb;")
>Dim MyCommand As New OleDbCommand
>Dim ra As Integer
>
>Conn.open
>cmndSTR = "Insert into CustInfo ([Name], [Title],[Phone]) VALUES
>(NameTxt, TitleText, PhoneNum)
>MyCommand = New OleDbCommand(cmndSTR, conn)
>ra = MyCommand.ExecuteNonQuery()
>'============================================================
>
>
>My question is this: Once this code has been executed and a record has
>been added, what is an easy way for me to load a variable (call it
>"intCustID") with the CustID value (the AutoNumber field) of the
>record that was just added?
>
>A code snippet would be GREATLY appreciated...
>
>Jmar

If using VB2005, search the help index for 'auto-incremented values'. See the
section, "Retriving Microsoft Access Autonumber Values".

Gene