1

I've just started teaching myself database access with vb.net and I've been using this as a reference. My MS Access database is a little more complex than the example my reference goes through since I've set up a relational database. I have two tables; Players and, Villages. Each Player can have one more more villages (1 to many relationship), this relationship is tied between the player ID which is the unique key in the Players table and used in the Villages table to define the owner of the village. Here's how I add a new player to the database;

 Public Sub NewPlayer(ByVal playerName As String) Dim playerID As Integer = getLastIdent("Players") + 1 Dim tmpnum As Integer = playerID Dim dsNewRow As DataRow Dim Sql As String Sql = "SELECT * FROM Players" da = New OleDb.OleDbDataAdapter(Sql, con) da.Fill(ds, "Players") Dim cb As New OleDb.OleDbCommandBuilder(da) dsNewRow = ds.Tables("Players").NewRow() dsNewRow.Item("ID") = playerID dsNewRow.Item("NameP") = playerName dsNewRow.Item("Coins") = 0 ds.Tables("Players").Rows.Add(dsNewRow) da.Update(ds, "Players") Call NewVillage(playerName, playerID) End Sub

The getLastIdent subroutine was my attempt at finding the last value used in the player ID field;

 Public Function getLastIdent(ByVal tblname As String) Dim sql As String = "SELECT @@IDENTITY FROM " & tblname Dim cmd As New OleDb.OleDbCommand(Sql, con) Return cmd.ExecuteScalar() End Function

But this doesn't work at all (returns 0 no matter what). The players can actually be created without a hitch (the incorrect ID number seems to fix itself at some point with the database correctly incrementing the AutoNumber. However to assign a village to the new player I run NewVillage;

Public Sub NewVillage(ByVal playerName As String, ByVal playerID As Integer) Dim numVils As Integer = getVilCount() Dim xpos As Integer Dim ypos As Integer xpos = CInt(Rnd()*50) ypos = CInt(Rnd()*50) Dim dsNewRow As DataRow Dim Sql As String Sql = "SELECT * FROM Villages" da = New OleDb.OleDbDataAdapter(Sql, con) da.Fill(ds, "Villages") Dim cb As New OleDb.OleDbCommandBuilder(da) dsNewRow = ds.Tables("Villages").NewRow() dsNewRow.Item("ID") = numVils + 1 dsNewRow.Item("NameV") = playerName & "'s Village" dsNewRow.Item("Xpos") = xpos dsNewRow.Item("Ypos") = xpos dsNewRow.Item("Owner") = playerID ds.Tables("Villages").Rows.Add(dsNewRow) da.Update(ds, "Villages") End Sub

This is where the whole thing collapses. The playerID that's passed to the subroutine isn't the correct value that's associated with the player I've just created (in face there isn't any player by that name since it always tries playerID = 1 and the AutoNumber starts higher than that due to deleted failed rows).

So how can I go about getting the true value of the ID from the players table? Is there some call I can make that forces the AutoNumber to update and then I can recheck it?

Improve this question
2
  • Possible duplicate of Select @@IDENTITY WHERE in Access Commented Nov 14, 2016 at 21:51
  • You insert the new Player without the ID. Then find what the new ID is. Then call NewVillage with this ID. Commented Nov 14, 2016 at 21:54

1 Answer 1

Reset to default
1

What I have done in the past is hook the RowUpdated event of the OleDbDataAdapter and in that hook, execute the identity retrieval command.

First, define the command that will be used to execute the identity clause at a module level and add a method to instantiate it:

Private cmdGetIdentity As OleDb.OleDbCommand Private Sub CreateIdentityCommand(con As OleDbConnection, tblName As String) Dim sql As String = "SELECT @@IDENTITY FROM " & tblName cmdGetIdentity = New OleDb.OleDbCommand(sql, con) End Sub

Next, create the event handler that will be used to retrieve the identity (since each table contains an ID column, this code can be used for both of your tables):

Private Sub RowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs) If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then ' Execute the command and move it into the row e.Row("ID") = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString()) ' Ensure the row's changes are accepted e.Row.AcceptChanges() End If End Sub

Finally, modify your existing methods to instantiate the identity command, hook the new event, and remove the existing code that attempts to set the ID. In NewVillage, for example, change:

 da = New OleDb.OleDbDataAdapter(Sql, con)

to

 da = New OleDb.OleDbDataAdapter(Sql, con) CreateIdentityCommand(con, "Villages") AddHandler da.RowUpdated, AddressOf RowUpdated

and then remove the lines:

 Dim numVils As Integer = getVilCount()

and:

 dsNewRow.Item("ID") = numVils + 1
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks for the help. This still causes the wrong index to be used in my other database (basically after I create a player I want to make a village and assign that village to a player by using the players unique ID in the village table). For some reason using your code along with then checking the value in the first position in the last row (to pass to the village creator) seems to pass the wrong value. It's like the update to the ID's happens after I make the villages - is there anyway to force this to happen every time I create a player?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.