loading variable with AutoNumber field value after insert?

  • Thread starter Thread starter jmar
  • Start date Start date
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
 
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.
 
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...
 
' 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...
 
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...
 
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
 
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...
 
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
 
Back
Top