Get the next ID

[jodi1982]

I am trying to find the ID and then populate the database with a new record with the next available ID in an access Database.
Though the code I am using errors

        If sConn.State <> ConnectionState.Open Then
            sConn.Open()
        End If

        eDA0.SelectCommand = New OleDbCommand("Select * from audit", sConn)
        eCB0 = New OleDb.OleDbCommandBuilder(eDA0)

        eDA0.MissingSchemaAction = MissingSchemaAction.AddWithKey
        eDA0.Fill(eDS0, "audit")

        eDR0 = eDS0.Tables("audit_id").NewRow()
        eDR0("user_id") = sUserNumber
        eDR0("module_name") = "Inventio CRM"
        eDR0("item_summary") = "User Logon"
        eDR0("date_modified") = Date.Now

        eDS0.Tables("audit").Rows.Add(eDR0)

        eDR0 = Nothing
        eDS0.Dispose()
        eDA0.Dispose()
        sConn.Close()

Any ideas?

 

[cjard]

The code youre using downloads the entire contents of the audit table, then you add a new row to the local cache, then destroy the local cache, then close the database conenction.

This isnt microsoft access which uses a connected data model, this is VB.NET which uses a disconnected data model. For how to do data access in .NET 2, read the DW2 link in my signature.. Start with "Creating a Simple Data App", then read other sections as necessary. Your table should have an autonumber ID? If so its easy to get .NET to manage that for you, just set the .AutoIncrement to true, .Seed to -1 and .Step to -1 on the relevant typed datatable column. (That might not make any sense right now, but it will after you read the DW2 link). The lcoal ID of -1 will be sent to the db, the db will calculate the proper one and when the update is finished, your local table will have the proper value (it is copied back)

Read the DW2 link

 

[jodi1982]

Thank you, I read it last night and ithave managed to do what I needed.