Dataset & Datasource update problem 1

[amigo02]

In my Access database table I have an autonumber field which obviously is automatically given a value when a new record inserted

I use dataset in my asp.net (vb) to update the Datasource but I could not figure out how to read back this autonumber fields value after successfully inserting a record to table.

In other words dataset does not reflect the new value created automatically by database for autonumber field for that specific row after updating the Datasource and thus I have no access to autonumber field which I need to use to update another table.

The code is as follows:
oCommandBuilder = New OleDbCommandBuilder(WarehouseDA)

rowW = ds.Tables("WAREHOUSE").NewRow
Response.Write(rowW("Created"))
rowW("Consignee") = Request.QueryString("Consignee")
rowW("Shipper") = Request.QueryString("Shipper")
rowW("Carrier") = Request.QueryString("Carrier")
rowW("EntryDate") = Request.QueryString("EntryDate")
rowW("Marks") = Request.QueryString("Marks")
rowW("Comments") = Request.QueryString("Comments")

ds.Tables("WAREHOUSE").Rows.Add(rowW)

WarehouseDA.Update(ds, "WAREHOUSE")


-----------------
The following line is supposed to return the RecNo (autonumber) value of the newly inserted record but it just returns an empty value.

Response.Write("RecNo=" & ds.Tables("WAREHOUSE").Rows(0).Item("RecNo"))


It appears that the field values not assigned by me is not visible to dataset. Can I refresh the dataset without losing my pointer to current record so it reflects the changes made in database.

In old ASP , I used to have a recordset and when I update it all the fields from database including the autonumber fields value was available in the recordsets current row.

How can I accomplish this with .net

 

[DaZZleD]

For Access 2000 or Later and version 4 of the JET OLE DB provider, you can use SELECT @@IDENTITY in the RowUpdate event to get the last autonumber generated.

If you search for "Retrieving Identity or Autonumber Values" in the help it will give you an example.

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[amigo02]

I assume the last autonumber means the one belongs to the record I just create and not belonging to another record created by another user .

Can I assume in a multiuser environment that auto number belongs to me?

 

[DaZZleD]

you could since the request it's coming from your connection and Access has some sort (very basic) of transaction implementation

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage