sql - Trouble with the MS Access AutoNumber field and VB.net -

i've started teaching myself database access vb.net , i've been using this reference. ms access database little more complex example reference goes through since i've set relational database. have 2 tables; players and, villages. each player can have 1 more more villages (1 many relationship), relationship tied between player id unique key in players table , used in villages table define owner of village. here's how add new player database;

    public sub newplayer(byval playername string)     dim playerid integer = getlastident("players") + 1     dim tmpnum integer = playerid      dim dsnewrow datarow     dim sql string      sql = "select * players"      da = new oledb.oledbdataadapter(sql, con)     da.fill(ds, "players")      dim cb 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 attempt @ finding last value used in player id field;

    public function getlastident(byval tblname string)     dim sql string = "select @@identity " & tblname     dim cmd new oledb.oledbcommand(sql, con)     return cmd.executescalar() end function 

but doesn't work @ (returns 0 no matter what). players can created without hitch (the incorrect id number seems fix @ point database correctly incrementing autonumber. assign village new player run newvillage;

public sub newvillage(byval playername string, byval playerid integer)     dim numvils integer = getvilcount()            dim xpos integer     dim ypos integer     xpos = cint(rnd()*50)     ypos = cint(rnd()*50)       dim dsnewrow datarow     dim sql string      sql = "select * villages"      da = new oledb.oledbdataadapter(sql, con)     da.fill(ds, "villages")      dim cb 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 whole thing collapses. playerid that's passed subroutine isn't correct value that's associated player i've created (in face there isn't player name since tries playerid = 1 , autonumber starts higher due deleted failed rows).

so how can go getting true value of id players table? there call can make forces autonumber update , can recheck it?

what have done in past hook rowupdated event of oledbdataadapter , in hook, execute identity retrieval command.

first, define command used execute identity clause @ module level , add method instantiate it:

private cmdgetidentity oledb.oledbcommand  private sub createidentitycommand(con oledbconnection, tblname string)      dim sql string = "select @@identity " & tblname     cmdgetidentity = new oledb.oledbcommand(sql, con)  end sub 

next, create event handler used retrieve identity (since each table contains id column, code can used both of tables):

private sub rowupdated(byval sender object, byval e oledbrowupdatedeventargs)      if e.status = updatestatus.continue andalso e.statementtype = statementtype.insert         ' execute command , move row         e.row("id") = int32.parse(cmdgetidentity.executescalar().tostring())         ' ensure row's changes accepted         e.row.acceptchanges()     end if  end sub 

finally, modify existing methods instantiate identity command, hook new event, , remove existing code attempts set id. in newvillage, example, change:

    da = new oledb.oledbdataadapter(sql, con) 

to

    da = new oledb.oledbdataadapter(sql, con)     createidentitycommand(con, "villages")     addhandler da.rowupdated, addressof rowupdated 

and remove lines:

     dim numvils integer = getvilcount() 

and:

     dsnewrow.item("id") = numvils + 1