can't retrieve updated autoincremented value

G

Guest

I copied the code from both Programming Microsoft Visual
Basic.NET for Microsoft Access databases, and ADO.NET
Core Reference in order to retrieve the autoincremented
key index for the latest new row. I of course altered to
code to reflect my database, and it didn't work. Below
is the code. I'd be much obliged if somebody could tell
me why. The code runs, all right, but the message box at
the end gives the value of 0.

thank you very much.

dennist

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "hij"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True
'rowDateType.ID = 11
'rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO datetype
(ID,DateType,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO datetype(DateType,CreateDate,ChangeDate,Active)
values (?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

'cn.Open()
'Dim dsB As New ds1
'da.Fill(dsB, "DateType")



End Sub
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub



Friend Sub HandleRowUpdated(ByVal sender As Object, _
ByVal e As
OleDbRowUpdatedEventArgs)
Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
cn.Open()

Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
e.Row("ID") = CType
(cmdGetIdentity.ExecuteScalar, Integer)
e.Row.AcceptChanges()
End If

End Sub

End Class
 
P

Paul Clement

¤ I copied the code from both Programming Microsoft Visual
¤ Basic.NET for Microsoft Access databases, and ADO.NET
¤ Core Reference in order to retrieve the autoincremented
¤ key index for the latest new row. I of course altered to
¤ code to reflect my database, and it didn't work. Below
¤ is the code. I'd be much obliged if somebody could tell
¤ me why. The code runs, all right, but the message box at
¤ the end gives the value of 0.

Is this a Jet 4.0 database (Access 2000 or higher)?


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

David Sceppa

Dennist,

The code in your RowUpdated handler uses a different
connection. Use the same connection and the query should
retrieve the new value successfully.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
G

Guest

worked perfectly. I dimensioned strconn and cn as module
level variables and commented out the appropriate lines
elsewhere.

Thank you very much. However, neither of the books I
consulted did it this way and it still worked. I'm not
going to pull my hair out about it, however.

dennist
 
R

Ron Allen

dennist,
You can also get the current connection and command from the
OleDbRowUpdatedEventArgs passed in. It would be e.Command.Connection to get
the active connection in your sub.

Ron Allen
worked perfectly. I dimensioned strconn and cn as module
level variables and commented out the appropriate lines
elsewhere.

Thank you very much. However, neither of the books I
consulted did it this way and it still worked. I'm not
going to pull my hair out about it, however.

dennist
 

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

Error in INSERT INTO statement 6
parameter problems 15
can't transfer value from class to form 2
can't create new rows by code 1
can't add row to ms access table 19
can't add row in code to access mdb 1
OleDbCommand should be disposed? Holding conn reference... 1
function connect works; sub doesn't; weird 5

Top