Retreving an auto number field from Access
-
Hi all.... I am working on a VB.net application at attaches to a database in disconnected mode. I am haveing trouble retreving an autonumber field. Here is the code as I have it now: Dim drw1 As DataRow = das3.Tables("tomdb").NewRow() drw1("Name") = TextBox3.Text drw1("function") = TextBox4.Text If RadioButton1.Checked Then drw1("accesslevel") = "1" ElseIf RadioButton2.Checked Then drw1("accesslevel") = "2" ElseIf RadioButton3.Checked Then drw1("accesslevel") = "3" Else drw1("accesslevel") = "3" End If drw1("UserID") = Integer.MaxValue das3.Tables("tomDB").Rows.Add(drw1) AddHandler dap3.RowUpdated, New OleDb.OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated) -------------------------------------------------------------------------------------- Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As OleDb.OleDbRowUpdatedEventArgs) Dim int1 As Integer = 0 Dim cmd1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT @@IDENTITY", cnn3) If args.StatementType = StatementType.Insert Then int1 = CInt(cmd1.ExecuteScalar()) args.Row("UserID") = int1 End If End Sub -------------------------------------------------------------------------------------- In my code it should retreve the next record number but all I get is a 0. Any ideas? Gonz
-
Hi all.... I am working on a VB.net application at attaches to a database in disconnected mode. I am haveing trouble retreving an autonumber field. Here is the code as I have it now: Dim drw1 As DataRow = das3.Tables("tomdb").NewRow() drw1("Name") = TextBox3.Text drw1("function") = TextBox4.Text If RadioButton1.Checked Then drw1("accesslevel") = "1" ElseIf RadioButton2.Checked Then drw1("accesslevel") = "2" ElseIf RadioButton3.Checked Then drw1("accesslevel") = "3" Else drw1("accesslevel") = "3" End If drw1("UserID") = Integer.MaxValue das3.Tables("tomDB").Rows.Add(drw1) AddHandler dap3.RowUpdated, New OleDb.OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated) -------------------------------------------------------------------------------------- Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As OleDb.OleDbRowUpdatedEventArgs) Dim int1 As Integer = 0 Dim cmd1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT @@IDENTITY", cnn3) If args.StatementType = StatementType.Insert Then int1 = CInt(cmd1.ExecuteScalar()) args.Row("UserID") = int1 End If End Sub -------------------------------------------------------------------------------------- In my code it should retreve the next record number but all I get is a 0. Any ideas? Gonz
This is a text app I whiped up. The Button1_Click works and Button2_Click does not. You have to get the value before the connection is closed.
Private m_connection As OleDb.OleDbConnection Private m_connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" Protected Overrides Sub OnLoad(ByVal e As System.EventArgs) MyBase.OnLoad( e ) m_connectionString &= HunterDev.Environment.GetUserFolderPath( SpecialFolders.Personal ) & "\db1.mdb" End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click If ( m_connection Is Nothing ) Then m_connection = New OleDb.OleDbConnection( m_connectionString ) End If Dim command As New OleDb.OleDbCommand( "INSERT INTO Sample ( Name ) VALUES ( '" & TextBox1.Text & "' )", m_connection ) Dim idCommand As New OleDb.OleDbCommand( "SELECT @@IDENTITY", m_connection ) If ( m_connection.State <> ConnectionState.Open ) Then m_connection.Open() End If command.ExecuteNonQuery() Dim id As Integer = CInt( idCommand.ExecuteScalar() ) If ( m_connection.State <> ConnectionState.Closed ) Then m_connection.Close() End If TextBox2.AppendText( id.ToString() & vbCrLf ) End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim idCommand As New OleDb.OleDbCommand( "SELECT @@IDENTITY", m_connection ) If ( m_connection.State <> ConnectionState.Open ) Then m_connection.Open() End If Dim id As Integer = CInt( idCommand.ExecuteScalar() ) If ( m_connection.State <> ConnectionState.Closed ) Then m_connection.Close() End If TextBox2.AppendText( id.ToString() & vbCrLf ) End SubBo Hunter