Adv. SQL Generation Options - Refresh the Dataset

[michael]

I have a SQLDataAdapter that contols data between a SQL
table whose Primary Key is an "Identity" column and a
DataSet in my application.

I also have a DataGrid that displays the DataSet's copy
of the SQL table. I noticed that if I click on a "new
row", the Identity column is incremented automatically.
Now, lets say that I don't actually enter anything in the
new row and click on an "old" row, and then click on the
new row again. Well, the Identity column auto-increments
again, even though the Identity column number is one
higher than it should be (I never entered data the first
time). If I now execute an .Update, the row is added to
the SQL database, but the value of the Identity column in
the SQL database is correctly incremented while the value
of the Identity column in the dataset is NOT the same
value as the Identity column of the row just entered in
the SQL database (and this causes all kinds of problems!).

What then is the purpose of the Refresh the DataSet
option in the DataAdapter? It doesn't seem to update the
Identity column of the DataSet at all?

Michael

 

[Joe Fallon]

The identity value in the dataset is just a "placeholder" so you need to set
it to some "fake" value.
Everyone suggests -1 with a Step of -1 but I use the highest possible
positive number because my identitu fields satrt at the lowest possible
negative number, not Zero.

In a parent child relationship the real trick is returning and cascading the
true identity value to the child.
See the comments below for how to do it:
================================================
The solution is to set up cascade update in your dataset so that when the
new Identity value is returned from the database you update the master table
and cascade the change to the child.
You have to trap the RowUpdated event and then post the new identity value.

For SQL Server, there is a way to send 2 statements separated by a
semi-colon.
The first is your Parent update statement the second is Select
@@Scope_Identity.
(I am not 100% sure about this though.) (See below for the method I really
use.)

For Access and Oracle (and SQL Server if you don't use multiple statements):
You have to trap the RowUpdated event and then post the new identity value.

================================================================
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back from
SQL Server
'w/o the real Identity value, the child records won't be added to
SQL Server.
AddHandler da_Eimhdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================================
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================================
Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================





For Oracle - I use this code:
================================================================

Private Sub da_Handle_OracleRowUpdated(ByVal sender As Object, ByVal e As
OracleRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetOracleSequence(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

Private Function GetOracleSequence(ByRef cnn As OracleConnection) As
Integer
Dim oCmd As New OracleCommand("SELECT SEQ_EIMHDR_EIMKEY.CURRVAL FROM
DUAL", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================

 

[Kevin Sun [MS]]

How did you new a record in the datagrid? Did you simply click the "new"
record indicator in the data grid or click a "new" button on the form?

Did you check the Refresh the DataSet option in the data adapter (advanced
SQL Generation Option), so that you can refresh the DataSet after inserting
and updating the table and retrived identity column values, default values,
and other values calculated by the database.

[More Information]:

320897.KB.EN-US HOW TO: Retrieve an Identity Value from a Newly Inserted
Record from SQL
http://support.microsoft.com/default.aspx?scid=KB;EN-US;320897

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Content-Class: urn:content-classes:message
| From: "michael" <[email protected]>
| Sender: "michael" <[email protected]>
| Subject: Adv. SQL Generation Options - Refresh the Dataset
| Date: Sun, 5 Oct 2003 17:24:43 -0700
| Lines: 25
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOLoD1ybKymvLLsThm3RmX6aSZeAg==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62881
| NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I have a SQLDataAdapter that contols data between a SQL
| table whose Primary Key is an "Identity" column and a
| DataSet in my application.
|
| I also have a DataGrid that displays the DataSet's copy
| of the SQL table. I noticed that if I click on a "new
| row", the Identity column is incremented automatically.
| Now, lets say that I don't actually enter anything in the
| new row and click on an "old" row, and then click on the
| new row again. Well, the Identity column auto-increments
| again, even though the Identity column number is one
| higher than it should be (I never entered data the first
| time). If I now execute an .Update, the row is added to
| the SQL database, but the value of the Identity column in
| the SQL database is correctly incremented while the value
| of the Identity column in the dataset is NOT the same
| value as the Identity column of the row just entered in
| the SQL database (and this causes all kinds of problems!).
|
| What then is the purpose of the Refresh the DataSet
| option in the DataAdapter? It doesn't seem to update the
| Identity column of the DataSet at all?
|
| Michael
|
|

 

[michael]

I always suspected that the Identity in the DataSet was a
placeholder. I really appreciate your insight into the
fix.

However, I always assumed that after I executed the
DataAdapter.Update, the "real" values from the SQL server
table would replace the "fake" placeholder values in the
DataSet. In my case, after .Update, there is no updating
like I would have expected, the "fake" placeholders are
unchanged. Isn't that what "Refresh the DataSet" is
supposed to do? Put the "real" values in place?

Michael

-----Original Message-----
The identity value in the dataset is just

a "placeholder" so you need to set

 

[michael]

I "Newed" the new record by simply clicking on the empty
record at the bottom of the grid.

I am certain that the "Refresh the DataSet" option is
clicked.

However, after I execute the DataAdapter.Update, the
Dataset still has the "fake" placeholder values in the
Identity columns and not the "real" ones that I see in
the SQL database. I thought that the purpose of
the "Refresh the DataSet" was to retrieve the actual
Identity (and defaults...) from the SQL table and place
these values in the DataSet, removing any original "place
holders".

This feature does not seem to Refresh my DataSet at all.
BTW, the Insert, Delete seem to work just fine.

Take a look at Joe Fallon's post on this same subject.
It's the post just above yours.

Michael

-----Original Message-----
How did you new a record in the datagrid? Did you simply click the "new"
record indicator in the data grid or click a "new" button on the form?

Did you check the Refresh the DataSet option in the data adapter (advanced
SQL Generation Option), so that you can refresh the DataSet after inserting
and updating the table and retrived identity column values, default values,
and other values calculated by the database.

[More Information]:

320897.KB.EN-US HOW TO: Retrieve an Identity Value from a Newly Inserted
Record from SQL
http://support.microsoft.com/default.aspx?scid=KB;EN- US;320897

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Content-Class: urn:content-classes:message
| From: "michael" <[email protected]>
| Sender: "michael" <[email protected]>
| Subject: Adv. SQL Generation Options - Refresh the Dataset
| Date: Sun, 5 Oct 2003 17:24:43 -0700
| Lines: 25
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOLoD1ybKymvLLsThm3RmX6aSZeAg==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62881
| NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I have a SQLDataAdapter that contols data between a SQL
| table whose Primary Key is an "Identity" column and a
| DataSet in my application.
|
| I also have a DataGrid that displays the DataSet's copy
| of the SQL table. I noticed that if I click on a "new
| row", the Identity column is incremented automatically.
| Now, lets say that I don't actually enter anything in the
| new row and click on an "old" row, and then click on the
| new row again. Well, the Identity column auto- increments
| again, even though the Identity column number is one
| higher than it should be (I never entered data the first
| time). If I now execute an .Update, the row is added to
| the SQL database, but the value of the Identity column in
| the SQL database is correctly incremented while the value
| of the Identity column in the dataset is NOT the same
| value as the Identity column of the row just entered in
| the SQL database (and this causes all kinds of problems!).
|
| What then is the purpose of the Refresh the DataSet
| option in the DataAdapter? It doesn't seem to update the
| Identity column of the DataSet at all?
|
| Michael
|
|

.