Dataset Merge and @@IDENTITY

I have an application that contains a dataset of product data that is stored in SQL Server 2000. The original table in SQL Server has an nteger primary key that is an Identity field.

The application allows users to add new records to the dataset and then update the datasource. The update datasource call writes the new rows to SQL Server with no problems. However, quite oftern when the update datasource happens the identity field creatred in the dataset has already been allocated (someone else added a record at same time).

Now, things are still OK as SQL Server will happily add the record, but now the identity field allocated by sql Server is different to the one in my original dataset.

How do I get my original dataset to show the revised identity field generated by SQL Server?

I know you can get the value using @.@.IDENTITY but how do I get that back into the dataset?Try using the SCOPE_IDENTITY function instead of @.@.IDENTITY. It has a narrower scope, so you should get back the value you want.

Don|||Getting the revised value isnt the problem.

The isssue is how do I reflect that back into the orignal dataset.

The only way I can see to do this is process additions to the datasource separately from updates and after each insert, fetch the revised IDENTITY field and then write a load of code to examine the new datasource generated value, compare it with the value originally created by the dataset and if it differs, then change the dataset value.

That make sense?

dataset and identity of new record inserted

Hi,

I have 2 tables in my databasePrescriptionHeader and PrescriptionDetails.

My PrescriptionHeader table has the following fields:

PrescriptionID -identity field

PatientID

PatientfName

Patientlname

PrescriptionDetails table has the following fields:

PrescriptionDetailID -identity

PrescriptionID -from PrescriptionHeader table

MedicineDosage

The functionInsertPrescription inserts values into the tablePrescriptionHeader. I want the same function to then insert the value ofMedicineDosage intoPrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?

Thanks

Function InsertPrescription(ByVal PatientIDAsString, _

ByVal PatientFnameAsString, _

ByVal PatientlnameAsString, ByValMedicineDosage as String)

Dim DBAdapterAs SqlDataAdapter

Dim DBDataSetAs DataSet

Dim SQLStringAsString

Dim DBCommandBuilderAs SqlCommandBuilder

SQLString ="SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"

DBAdapter =New SqlDataAdapter(SQLString, DBConnection)

DBDataSet =New DataSet

DBAdapter.Fill(DBDataSet)

Dim AddedRowAs DataRow = DBDataSet.Tables(0).NewRow()

AddedRow("PatientID") = PatientID

AddedRow("PatientfName") = PatientFname

AddedRow("Patientlname") = Patientlname

DBDataSet.Tables(0).Rows.Add(AddedRow)

DBCommandBuilder =New SqlCommandBuilder(DBAdapter)

DBAdapter.Update(DBDataSet)

EndFunction

The following article is very helpful in your case

Inserting relational data using DataSet and DataAdapter

HTH
Regards