Primary key in children tables

[luisofo]

Hello there!
This is my problem: I do have in an SQL db a table which has a PK which must be filled in, following a consecutive numbering. This PK, as supposed is a FK in the children fields in another three tables. In VB.Net I don't know how to retirieve the last used ID number in the PK, for adding one in the parent table and fill in the FK in the children tables. Can anybody out there give me a hand on this subject? Thanks.
Luisofo

 

[TechGnome]

How are you inserting the data? Stored Procedures? or SQL right in the VB code?

You can use the @@IDENTITY (or SCOPE_IDENTITY()) to get the last ID inserted into the DB. Then you can pass that to the child records as needed.

-tg

 

[luisofo]

I am using VB.NET to fill the SQL db

 

[TechGnome]

I think that was fairly obvious.

What I was after was HOW are you doing it? A little bit of code would help. I'm not sure who keeps perpetuating the myth (one of these days I need to find out who it is) but we are not mind readers. We can't tell you wha't wrong with the code if you don't share with us what the code is.

-tg

 

[luisofo]

Sorry TG, I didn't get the right meaning of your question. Here below you will see the code I am using:

Dim strConnStuk As String = "Integrated Security=Yes;" & _
"Initial Catalog=CadSyS;" & _
"Data Source=localhost"

Dim sqlConnStuk As New SqlClient.SqlConnection(strConnStuk)

Dim adaptStuk As SqlClient.SqlDataAdapter
Dim adaptStukB As SqlClient.SqlDataAdapter
Dim strSQL As String = "SELECT TbStukSystemID,TbStukDatumID,TbStukIDRegCD,TbStukIDDeel1,TbStukIDDeel2 FROM TbStuk"
Dim strSQLB As String = "SELECT TbHypotheekHoofdsoomBedrag,TbHypotheekHoofdsomVermeerderdMet, TbHypotheekValuta FROM TbHypotheekGegevens"


adaptStuk = New SqlClient.SqlDataAdapter(strSQL, sqlConnStuk)
adaptStuk.Fill(CSData, "TbStuk")

adaptStukB = New SqlClient.SqlDataAdapter(strSQLB, sqlConnStuk)
adaptStukB.Fill(CSData, "TbHypotheekGegevens")


sqlConnStuk.Open()
Dim StukRow As DataRow = CSData.Tables("TbStuk").NewRow

StukRow("TbStukDatumID") = Me.ComboBox1.Tag
StukRow("TbStukIDRegCD") = Me.StukRegCD.Text
StukRow("TbStukIDDeel1") = Me.StukDeel1.Text
StukRow("TbStukIDDeel2") = Me.StukDeel2.Text
CSData.Tables("TbStuk").Rows.Add(StukRow)

Dim sqlCommBuild As New SqlClient.SqlCommandBuilder(adaptStuk)
adaptStuk.Update(CSData, "TbStuk")


adaptStukB = New SqlClient.SqlDataAdapter(strSQL, sqlConnStuk)
adaptStukB.Fill(CSData, "TbHypotheekGegevens")
Dim StukRowB As DataRow = CSData.Tables("TbHypotheekGegevens").NewRow
StukRowB("TbHypotheekSTUKID") = CSData.Relations("RelStukHypotheek")
StukRowB("TbHypotheekHoofdsoomBedrag") = Me.TextBox5.Text
StukRowB("TbHypotheekHoofdsomVermeerderdMet") = Me.TextBox6.Text
StukRowB("TbHypotheekValuta") = Me.HypotheekValuta.Tag

CSData.Tables("TbHypotheekGegevens").Rows.Add(StukRowB)

Dim sqlCommBuildB As New SqlClient.SqlCommandBuilder(adaptStukB)
adaptStukB.Update(CSData, "TbHypotheekGegevens")

Besides that I should tell you that the relationships are already done in the dataset (CSData). What can help me is a way to read the value of the last used ID in the table "TbStuk", that way I can feed the next record in the same table and also, as you should know, the children records in the table "TbHypotheekGegevens". If you know about a better way to do so, I am completly open to suggestions.

Thanks you very much for your interes, because this subject is getting me really mad.

Luis