AddRow New ID

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I've read through all the posts regarding autonumber and am still
struggling. I have a SQL200 database, all inserts and updates are
done via stored procedures. My inserts, determine the next id, create
the new record with the new ID and pass the id back in an output
parameter. I am sucessfully getting the new value back but have an
issues with the Merge. Here is a subset of my code:

dsChanges = dsMeds.GetChanges
If Not dsChanges Is Nothing Then
Try
cnnMeds.Open()
daMeds.Update(dsChanges, dsChanges.Tables(0).TableName)
dsMeds.Merge(dsChanges)
dsMeds.AcceptChanges()
Return True
Catch updateException As System.Exception
MsgBox("Error Updating Medications:" & vbCrLf &
updateException.ToString, , "SaveChanges")
Finally
cnnMeds.Close()
End Try
End If

The problem is, after an Insert, I end up with 2 rows in my grid for
the one record inserted. I know this is due to the merge combining
based on ID, and the original dataset has a ID value of a negative
number and the changes dataset has the new ID, so it results in two
rows.

Is there a way to fix this other than just refilling the original
dataset from the database?

Thanks!
 
Hi Heather,


Heather said:
I've read through all the posts regarding autonumber and am still
struggling. I have a SQL200 database, all inserts and updates are
done via stored procedures. My inserts, determine the next id, create
the new record with the new ID and pass the id back in an output
parameter. I am sucessfully getting the new value back but have an
issues with the Merge. Here is a subset of my code:
The problem is, after an Insert, I end up with 2 rows in my grid for
the one record inserted. I know this is due to the merge combining
based on ID, and the original dataset has a ID value of a negative
number and the changes dataset has the new ID, so it results in two
rows.

Is there a way to fix this other than just refilling the original
dataset from the database?
Click to expand...

This is what I am doing as workaround.
Before update, i create an array of added row pairs.
I basically create a pair of added row in original dataset and a row with
*the same id* in changes dataset.
After successful update, i loop through my pairs and I update id in original
dataset from the paired row in changes dataset. (before that I make the
column readonly = false).
It works just fine for me.
 
Why do you need to extract changes from the dataset and update to the
database. you can directly call update method with dsmeds.

Rajesh Patel
 
Hi Rajesh,

Rajesh Patel said:
Why do you need to extract changes from the dataset and update to the
database. you can directly call update method with dsmeds.
Click to expand...

It is necessary in case you need to rollback changes?
 
Hi Rajesh,

You might but it will discard all changes made to it since creation or last
AcceptChanges method.
 
Thank you both for your input. I honestly just took snippets of
update code from a test form I created with the Data Form Wizard.
Wonder why the code is created that way through the wizard? It now
works just fine if I use the entire dataset.
 
Heather,
Add a new element to your table in the xsd, set autoincrement
properties, add a key element on this new field and set as the dataset
primary key. Recompile and run!

I am assuming you are using a strongly typed dataset. If not do the
equivelent using code.

Cecil Howell MCT, MCSD

EMAIL: add an 'H' after cecil in return address
 
Back
Top