loading variable with AutoNumber field value after insert?

J

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
 
H

Harry Strybos

jmar said:
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
Click to expand...
Try searching the web with "ADO.Net + get identity column" (without the
quotes). You will gets lots of help.
 
J

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 said:
jmar said:
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
Click to expand...
Try searching the web with "ADO.Net + get identity column" (without the
quotes). You will gets lots of help.
Click to expand...
 
S

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


jmar said:
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 said:
jmar said:
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
Click to expand...
Try searching the web with "ADO.Net + get identity column" (without the
quotes). You will gets lots of help.
Click to expand...
Click to expand...
 
R

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.

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 said:
jmar said:
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
Click to expand...
Try searching the web with "ADO.Net + get identity column" (without the
quotes). You will gets lots of help.
Click to expand...
Click to expand...
 
G

gene kelley

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
Click to expand...

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


Gene
 
J

jmar

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 said:
' 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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
A

Armin Zingler

jmar said:
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 said:
' 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
Click to expand...
Click to expand...


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Value of an AutoNumber Field? 4
Problem With Insert into MySQL DB using VB.Net 2005 3
Issue w/ AutoNumber Field during DAO Record Insert 1
ASP.NET Image Upload... Guru needed 1

Top