469,138 Members | 1,393 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

loading variable with AutoNumber field value after insert?

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.OLED B.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

Jan 14 '07 #1
7 2810
"jmar" <jm******@yahoo.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.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.OLED B.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.
Jan 14 '07 #2
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" <jm******@yahoo.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.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.OLED B.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.
Jan 15 '07 #3
' 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
"jmar" <jm******@yahoo.comwrote in message
news:11*********************@s34g2000cwa.googlegro ups.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" <jm******@yahoo.comwrote in message
news:11**********************@11g2000cwr.googlegr oups.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.OLED B.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.

Jan 15 '07 #4
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:
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" <jm******@yahoo.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.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.OLED B.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.
Jan 15 '07 #5
On 14 Jan 2007 07:08:03 -0800, "jmar" <jm******@yahoo.comwrote:
>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.OLE DB.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
Jan 15 '07 #6
Stephany,

Thank you for your help. You provided exactly what I needed and saved
me a ton of time. It works perfectly. I appreciate it...

Jmar

Stephany Young wrote:
' 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
"jmar" <jm******@yahoo.comwrote in message
news:11*********************@s34g2000cwa.googlegro ups.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" <jm******@yahoo.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.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.OLED B.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.
Jan 21 '07 #7
"jmar" <jm******@yahoo.comschrieb
Stephany,

Thank you for your help. You provided exactly what I needed and
saved me a ton of time. It works perfectly. I appreciate it...

Jmar

Stephany Young wrote:
' 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

Be careful, in a multiuser environment, another user might have added a
record meanwhile, so you would get the custID of /his/ record, not of yours.

Consider using the "SELECT @@IDENTITY" syntax instead (IIRC not available
with < Jet 4.0 (<Access 2000)) because it's related to the same connection:
http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx

Unfortunatelly, the whole ADO.net reference in the online MSDN TOC seems to
be gone... (anybody knows who has stolen the "Programming with .Net
Framework topics?)
Armin

Jan 21 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ilan Sebba | last post: by
35 posts views Thread by Traci | last post: by
3 posts views Thread by Ivan Sammut | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.