472,328 Members | 1,013 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

Retrieving Identity After Dataset update

I have created a dataset with two tables and an insert command, I need to be
able to retreive the Key Identity after inserting into table "A" for use in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.
Nov 18 '05 #1
3 9624
Hi Justin,

I think you want to use ADO.NET's InsertCommand for this.

You might want to check this:

http://msdn.microsoft.com/library/de...anidcrisis.asp

Retrieving Identity or Autonumber Values

http://msdn.microsoft.com/library/de...mberValues.asp
"The following code example shows how to return the auto-incremented value
as the output parameter and specify it as the source value for the
CategoryID column in the DataSet.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID,
CategoryName FROM Categories", nwindConn)

catDA.InsertCommand = New SqlCommand("InsertCategory", nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

catDA.InsertCommand.Parameters.Add("@CategoryName" , SqlDbType.NChar, 15,
"CategoryName")

Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@Identity",
SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

nwindConn.Close()
"Justin" <Ju****@discussions.microsoft.com> wrote in message
news:C4**********************************@microsof t.com...
I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.


Nov 18 '05 #2
I have tried that code, what I can't figure out is exactly what variable
holds the identity. If its "myParam" then how do I cast it to an int variable?

Thanks, Justin.

"Ken Cox [Microsoft MVP]" wrote:
Hi Justin,

I think you want to use ADO.NET's InsertCommand for this.

You might want to check this:

http://msdn.microsoft.com/library/de...anidcrisis.asp

Retrieving Identity or Autonumber Values

http://msdn.microsoft.com/library/de...mberValues.asp
"The following code example shows how to return the auto-incremented value
as the output parameter and specify it as the source value for the
CategoryID column in the DataSet.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID,
CategoryName FROM Categories", nwindConn)

catDA.InsertCommand = New SqlCommand("InsertCategory", nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

catDA.InsertCommand.Parameters.Add("@CategoryName" , SqlDbType.NChar, 15,
"CategoryName")

Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@Identity",
SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

nwindConn.Close()
"Justin" <Ju****@discussions.microsoft.com> wrote in message
news:C4**********************************@microsof t.com...
I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.


Nov 18 '05 #3
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.

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_Hdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Hdr.Update(NewHdrRecords)

'child table
da_Ln.Update(NewLnRecords)
================================================== ==============
'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("key") = 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("key") = 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
================================================== ==============

--
Joe Fallon


"Justin" <Ju****@discussions.microsoft.com> wrote in message
news:C4**********************************@microsof t.com...
I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.

Nov 18 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Taras | last post by:
Hello! I have a problem. I'm using a dataset in VB.NET with multiple tables with relations between them. I would like dataset to update to all...
2
by: Niyazi | last post by:
Hi, I have not understand the problem. Before all the coding with few application everything worked perfectly. Now I am developing Cheque Writing...
2
by: Mojtaba Faridzad | last post by:
Hi, Please check these lines: DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "mytable"); DataRow row; row = dataSet.Tables.Rows;...
1
by: Taras | last post by:
Hello! I have a problem. I'm using a dataset in VB.NET with multiple tables with relations between them. I would like dataset to update to all...
9
by: Kevin Hodgson | last post by:
I'm experiencing a strange Dataset Update problem with my application. I have a dataset which has a table holding a set of customer information...
8
by: Dave | last post by:
I have a form with a label that should show an invoice number. The invoice number should be generated by sql Server using an autoincremented...
1
by: Krish2007 | last post by:
Hi, Consider the following scenario I have a Database source i had filled in my dataset with the help of Dataadapter after working on the...
4
by: Mark Olbert | last post by:
I am struggling with trying to retrieve the value of an autoincrement identity field after a DetailsView Insert operation. The DetailsView is bound...
15
by: gunnar.sigurjonsson | last post by:
Im having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.