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
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