VB.NET and SQL Server GET PK ID from maint table and send to FK in dependent tables

I would like to ask for some help, im a beginner when it comes to VB and SQL, im currently doing some school activity and im having trouble on how to get the auto incremented PK of my main table which is tbl_personalinfo with PK "PID" i want to get the PK ID of my main table after inserting a data in my Personalinfo table and send its value to corresponding dependent tables with the FK PID? i need to get the value of the PK ID from main table and send it to related tables with FK PID,
i dont know if i made my problem clear or understandable but if anyone can help. i would appreciate it so much.

Dim insertquery As String = "insert into tbl_PersonalInfo (FirstName, LastName, MiddleName, DateOfBirth, PlaceOfBirth, TinNo, GsisNo, PagIbigNo, PhilhealthNo, SSSNo, AgencyEmployeeNo, Citizenship, Height, Weight, Sex, CStatus) values (@FirstName,@LastName, @MiddleName, @DateOfBirth, @PlaceOfBirth, @TinNo, @GsisNo, @PagibigNo, @PhilhealthNo, @SSSNo, @AgencyEmployeeNo, @Citizenship, @Height, @Weight, @Sex, @CStatus)"
execquery(insertquery)
Public Sub execquery(ByVal query As String)
Dim cmd As New SqlCommand(query, connection)
cmd.Parameters.AddWithValue("@FirstName", txtboxFN.Text)
cmd.Parameters.AddWithValue("@LastName", txtboxSN.Text)
cmd.Parameters.AddWithValue("@MiddleName", txtboxMN.Text)
cmd.Parameters.AddWithValue("@DateOfBirth", DateTimePicker1.Value)
cmd.Parameters.AddWithValue("@PlaceOfBirth", txtboxPOB.Text)
cmd.Parameters.AddWithValue("@TinNo", txtboxTIN.Text)
cmd.Parameters.AddWithValue("@GsisNo", txtboxGSIS.Text)
cmd.Parameters.AddWithValue("@PagIbigNo", txtboxPagibig.Text)
cmd.Parameters.AddWithValue("@PhilhealthNo", txtboxPhilhealth.Text)
cmd.Parameters.AddWithValue("@SSSNo", txtboxSSS.Text)
cmd.Parameters.AddWithValue("@AgencyEmployeeNo", txtboxAgency.Text)
cmd.Parameters.AddWithValue("@Citizenship", CboxCitizenship.Text)
cmd.Parameters.AddWithValue("@Height", txtboxHeight.Text)
cmd.Parameters.AddWithValue("@Weight", txtBoxWeight.Text)
cmd.Parameters.AddWithValue("@Sex", CboxSex.Text)
cmd.Parameters.AddWithValue("@CStatus", CboxStatus.Text)
connection.Open()
cmd.ExecuteNonQuery()
connection.Close()


this is my insert code, where should i add the query to get the last inserted ID? btw does it refer to PID? which is my PK? what i really wanna do, is after inserting data into the main table, i wanna get the last inserted ID in order for me to insert data into related tables with FK PID. i dont know how to exactly explain what i want im sorry >.< i just hope that somebody can understand of what i want a bit.

1 Answer
1

SQL Server exposes the last auto-generated ID via the @@IDENTITY field, which is global, and the SCOPE_IDENTITY function, which is limited to the current scope. When you insert a record, you can immediately query one of those to get the auto-generated ID for the new record. That can be retrieved into your VB app and used however is appropriate.

@@IDENTITY

SCOPE_IDENTITY

Usually, you'll have a DataSet with multiple DataTables and a DataRelation between them. You can configure the DataRelation to propagate updates from the parent table to the child. What happens is that, when you add the rows to your DataTables, temporary IDs are generated. You can use the temporary ID from your parent row as foreign key values in your child rows. When you save the parent data to the database, the final IDs generated by the database are retrieved back into the parent DataTable to replace the temporary values and the DataRelation propagates those changes to the child DataTable. You then save the child data with the correct foreign key values for the database.

DataSet

DataTables

DataRelation

DataRelation

DataTables

DataTable

DataRelation

DataTable

If you're not using a DataTable then you can use an output parameter on the command object used to save the parent record to retrieve the auto-generated ID and use that in your child records.

DataTable

Dim data As New DataSet

Dim parentTable = data.Tables.Add("Parent")
Dim childTable = data.Tables.Add("Child")

Dim parentIdColumn = parentTable.Columns.Add("ParentId", GetType(Integer))
parentTable.Columns.Add("ParentName", GetType(String))

'Configure auto-generated temporary primary key values to easily distinguish them from final values.
With parentIdColumn
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
End With

parentTable.PrimaryKey = parentIdColumn

Dim childIdColumn = childTable.Columns.Add("ChildId", GetType(Integer))
childTable.Columns.Add("ChildName", GetType(String))
Dim foreignKeyColumn = childTable.Columns.Add("ParentId", GetType(Integer))

'Configure auto-generated temporary primary key values to easily distinguish them from final values.
With childIdColumn
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
End With

childTable.PrimaryKey = childIdColumn

Dim parentChildRelation = data.Relations.Add("ParentChild", parentIdColumn, foreignKeyColumn)

'Propagate changes to parent IDs from the parent table to the child.
parentChildRelation.ChildKeyConstraint.UpdateRule = Rule.Cascade

Dim connection As New SqlConnection("connection string here")

Dim parentAdapter As New SqlDataAdapter("SELECT * FROM Parent", connection)
Dim childAdapter As New SqlDataAdapter("SELECT * FROM Child", connection)

'Include a query on insert to retrieve the auto-generated ID.
Dim parentInsertCommand As New SqlCommand("INSERT INTO Parent (ParentName) VALUES (@ParentName); SELECT ParentId = SCOPE_IDENTITY();", connection)

parentInsertCommand.Parameters.Add("@ParentName", SqlDbType.VarChar, 50, "ParentName")
parentAdapter.InsertCommand = parentInsertCommand

'Include a query on insert to retrieve the auto-generated ID.
Dim childInsertCommand As New SqlCommand("INSERT INTO Child (ChildName, ParentId) VALUES (@ChildName, @ParentId); SELECT ChildId = SCOPE_IDENTITY();", connection)

childInsertCommand.Parameters.Add("@ChildName", SqlDbType.VarChar, 50, "ChildName")
childInsertCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0, "ParentId")
childAdapter.InsertCommand = childInsertCommand

'Retrieve existing data.
connection.Open()
parentAdapter.Fill(parentTable)
childAdapter.Fill(childTable)
connection.Close()

'Add new data.

Dim parentRow = parentTable.NewRow()

parentRow("ParentName") = "New Parent"
parentTable.Rows.Add(parentRow)

Dim parentId = parentRow(parentIdColumn)

Dim childRow = childTable.NewRow()

childRow("ChildName") = "New Child 1"
childRow(foreignKeyColumn) = parentId
childTable.Rows.Add(childRow)

childRow = childTable.NewRow()

childRow("ChildName") = "New Child 2"
childRow(foreignKeyColumn) = parentId
childTable.Rows.Add(childRow)

'View the IDs.
Console.WriteLine("Before save:")
Console.WriteLine("ParentId: 0", parentId)

For Each childRow In childTable.Rows
Console.WriteLine("ChildId: 0; ParentId: 1", childRow(childIdColumn), childRow(foreignKeyColumn))
Next

'Save the parent changes.
parentAdapter.Update(parentTable)

'View the IDs.
Console.WriteLine("During save:")
Console.WriteLine("ParentId: 0", parentId)

For Each childRow In childTable.Rows
Console.WriteLine("ChildId: 0; ParentId: 1", childRow(childIdColumn), childRow(foreignKeyColumn))
Next

'Save the child changes.
parentAdapter.Update(parentTable)

'View the IDs.
Console.WriteLine("After save:")
Console.WriteLine("ParentId: 0", parentId)

For Each childRow In childTable.Rows
Console.WriteLine("ChildId: 0; ParentId: 1", childRow(childIdColumn), childRow(foreignKeyColumn))
Next


I haven't actually tested that code explicitly and I can't right at this moment, but I've done this stuff before and am fairly confident that that is correct. I'll test it properly soon and make any changes that might be required.

The most important points to note are the SQL code in the InsertCommand properties and the configuration of the DataRelation.

InsertCommand

DataRelation

Here's an example using ExecuteNonQuery:

ExecuteNonQuery

Dim connection As New SqlConnection("connection string here")

'Include a query on insert to retrieve the auto-generated ID.
Dim parentInsertCommand As New SqlCommand("INSERT INTO Parent (ParentName) VALUES (@ParentName); SELECT @ParentId = SCOPE_IDENTITY();", connection)

parentInsertCommand.Parameters.Add("@ParentName", SqlDbType.VarChar, 50).Value = "New Parent"

Dim parentIdParameter = parentInsertCommand.Parameters.Add("@ParentId", SqlDbType.Int)

parentIdParameter.Direction = ParameterDirection.InputOutput

connection.Open()
parentInsertCommand.ExecuteNonQuery()
connection.Close()

Dim parentId = CInt(parentIdParameter.Value)


In theory, that parameter's Direction should be Output but, in my experience, that doesn't work and you need to use InputOutput. Note also that you should be able to without the parameter and call ExecuteScalar to get the value via a result set. I've never specifically tried that though.

Direction

Output

InputOutput

ExecuteScalar

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.