Retrieving value in AutoNumber field

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

I am trying to get the value from an Autonumber field in
an Access97 database when I insert a record into it. My
code is always returning 0 as the value which is wrong can
anyone see what the problem is?

Thanks in advance.


Private cmdGetIdentity As OleDbCommand

Private Sub btnNew_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnNew.Click

' Open Connection
Dim cnJetDB As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Da
ta Source=c:\webdata\test.mdb")
cnJetDB.Open()

' Create a DataAdaptor With Insert Command For
inserting records
Dim oleDa As New OleDbDataAdapter("Select ID, Name
from Test", cnJetDB)

' Command to Insert Records.
Dim cmdInsert As New OleDbCommand
cmdInsert.CommandText = "INSERT INTO Test (Name)
VALUES (?)"
cmdInsert.Connection = cnJetDB
cmdInsert.Parameters.Add(New OleDbParameter
("Name", OleDbType.Char, 30, "Name"))
oleDa.InsertCommand = cmdInsert

' Create a DataTable
Dim dtTest As New DataTable
oleDa.Fill(dtTest)

Dim drTest As DataRow

' Add Row to the Table
drTest = dtTest.NewRow
drTest("Name") = "New"
dtTest.Rows.Add(drTest)

' Create another Command to get IDENTITY Value
cmdGetIdentity = New OleDbCommand
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = cnJetDB

' Delegate for Handling RowUpdated event
AddHandler oleDa.RowUpdated, AddressOf
HandleRowUpdated

' Update the Data
oleDa.Update(dtTest)

' Release the Resources
cmdGetIdentity.Dispose()
cmdGetIdentity = Nothing
cmdInsert.Dispose()
cmdInsert = Nothing
cnJetDB.Close()
cnJetDB.Dispose()
cnJetDB = Nothing

End Sub

Private Sub HandleRowUpdated(ByVal sender As Object,
ByVal e As OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso
e.StatementType = StatementType.Insert Then
' Get the Identity column value
e.Row("ID") = Int32.Parse
(cmdGetIdentity.ExecuteScalar().ToString())
txtNewID.Text = e.Row("ID")
e.Row.AcceptChanges()
End If
End Sub
 
¤ I am trying to get the value from an Autonumber field in
¤ an Access97 database when I insert a record into it. My
¤ code is always returning 0 as the value which is wrong can
¤ anyone see what the problem is?

The SELECT @@IDENTITY feature is supported starting with Jet 4.0 (Access 2000 and higher) databases.
I'm not aware of a workaround.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Once the record is inserted you can do an ExecuteScaler on a SelectCommand
Object that has the SQL

"SELECT [ID] FROM table ORDER BY [ID] DESC;"

Then the ExecuteScalar will ALWAYS return the last ID in the database
(unless autonumber is random_autonumber)

Severin
 
¤ Once the record is inserted you can do an ExecuteScaler on a SelectCommand
¤ Object that has the SQL
¤
¤ "SELECT [ID] FROM table ORDER BY [ID] DESC;"
¤
¤ Then the ExecuteScalar will ALWAYS return the last ID in the database
¤ (unless autonumber is random_autonumber)
¤

You just have to keep in mind that the autonumber returned may not be one *you* just added when
working in a multi-user environment.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top