How to get the value of autoincrement field from Database in dataset on client

[Ashish]

Hello,



I have a dataset which I populate from a table containing a Identity
(autoincrement) field.

Now this dataset is received on client offline. While offline I add a record
in the table and when user click save button,

I send the updated dataset (GetChanges()) to the server where I use
dataadapter.update(ds) after setting the selectcommand.



This insert the new record in database and the autogenerated column gets a
new value, but this value is not on the client side dataset.

Now if I update the same record that I added in above description and send
this updated dataset to server (when user click save button) since the id in
dataset is not on server DB, so it throws a concurrency violation.



How can I resolve this issue?

After every update I do not want to get all the data back from server and
populate the client screen again, there should be some way by which I can
get the exact Id of autoincrement field on client side.



Also note that user can insert multiple records before sending the dataset
to the server.



------------------------------------------------------

Code to save on the server

------------------------------------------------------



Try
objTrans = Me.ActiveConnection.BeginTransaction()

da = New SqlDataAdapter
Dim cb As SqlCommandBuilder

da.SelectCommand = New SqlCommand("SELECT EstimateId,
DiscountOnProspectivePrice, DiscountOnBillingPrice FROM X",
Me.ActiveConnection, objTrans)
cb = New SqlCommandBuilder(da)
da.Update(dsEstimate, "x")
da.SelectCommand = New SqlCommand("SELECT EstimateId, VendorId
FROM Y", Me.ActiveConnection, objTrans)
cb = New SqlCommandBuilder(da)
da.Update(dsEstimate, "y")

'************************* CommodityId is Identity Field in
Table Z *******************************************

'*********When Inserted I need the autoincremented value of
CommodityId and use it on the client ************************
da.SelectCommand = New SqlCommand("SELECT CommodityId,
EstimateId FROM Z", Me.ActiveConnection, objTrans)
cb = New SqlCommandBuilder(da)

'***************** Updating the second time gives error on below
line ******************************************
da.Update(dsEstimate, "z")


objTrans.Commit()




' -----------------------------------------------------------------
Catch exSql As SqlException
objTrans.Rollback()
ProcessException.HandleSQLException(exSql)
Catch exGet As Exception
objTrans.Rollback()
ProcessException.HandleGeneralException(exGet)
End Try




Thanks,

Ashish

 

[Val Mazur]

Hi Ashish,

Check next KB about it

http://support.microsoft.com/default.aspx?scid=kb;en-us;320141&Product=adonet