Well, assuming you are using SQL Server, I would use a stored procedure with
parameters (if you are just getting started with ADO.Net, a good book is
invaluable, it's a vast topic). Use your DataAdapterConfigWizard to create
most of the stored procedures and the parameter code for you. You might use
Bill Vaughn's article to get you started:
http://msdn.microsoft.com/library/de...andbuilder.asp
Essentially what you will do is:
1. Do the Insert on table A
2. At the time of the Insert, retrieve the @@Identity value. This is the
"ReturnValue" in your parameters collection.
3. The relevant point here is that your table A Insert sproc will have
returned the Identity of the new Insert to table A, and so now your
datatable will contain the NEW Identity value. Use that value to do the
Insert on table B.
Here is a somewhat lengthy example that I have written recently. I've taken
out most of the error handling, checks for other events to occur, and just
used one field. I am creating a new Contact, then adding that the Referrals
table with the ContactID as a foreign key. The ContactSelectCommand pulls a
schema for the Contacts table. The ContactInsertCommand and
ReferralInsertCommand are created by the DACW, then tweaked. I'm showing
only the ContactInsertCommand and the ins_Contact sproc, but the Referrals
(table "B") code/sproc are identical in structure.
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
AddNewContact()
AddNewReferral()
End sub
Private Sub AddNewContact()
Dim strSQLServer As New SqlConnection(strConn)
Dim da As New SqlDataAdapter
da.SelectCommand = ContactSelectCommand()
da.FillSchema(ds, SchemaType.Source, "dtContactInfo")
da.Fill(ds)
Dim drNew As DataRow
drNew = ds.Tables("dtContactInfo").NewRow
If txtFirstName.Text <> "" Then
drNew("FirstName") = txtFirstName.Text
End If
'the rest of the contact info will follow
...........
ds.Tables("dtContactInfo").Rows.Add(drNew)
'this command calls ANOTHER stored proc to do the update
da.InsertCommand = ContactInsertCommand()
'using FirstReturnedRecord so we will have the newly returned ContactID
'to Insert into our Referral source
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
da.Update(ds, "dtContactInfo")
strSQLServer.Close()
End Sub
************************************************** **
'now we can Insert to "table B" as we have the returned ContactID
Private Sub AddNewReferral()
Dim strSQLServer As New SqlConnection(strConn)
Dim da As New SqlDataAdapter
da.SelectCommand = ReferralSelectCommand()
da.FillSchema(ds, SchemaType.Source, "dtRef")
da.Fill(ds)
Dim drNew As DataRow
drNew = ds.Tables("dtRef").NewRow
If CInt(ds.Tables("dtContactInfo").Rows(0)("ContactID ")) > 0 Then
drNew("ContactID") = CInt(ds.Tables("dtContactInfo").Rows(0)("ContactID "))
End If
'here we would go ahead and add all the other data to the "table B"
...........
ds.Tables("dtRef").Rows.Add(drNew)
da.InsertCommand = ReferralInsertCommand()
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
da.Update(ds, "dtRef")
End Sub
************************************************** **
Private Function ContactInsertCommand() As SqlCommand
Dim strSQLServer As New SqlConnection(strConn)
Dim cmd As New SqlCommand("ins_Contact", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As SqlParameterCollection = cmd.Parameters
'setup Parameters collection. All are input parameters
pc.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
pc.Add(New System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.VarChar, 25, "FirstName"))
'... other contact fields
Return cmd
End Function
************************************************** **
'now the Contact Insert sproc (the Referral Insert sproc is similar, just
diff fields and params)
ALTER PROCEDURE dbo.ins_Contact
(
....
@FirstName varchar(25),
'all the other contact params
.....
)
AS
SET NOCOUNT OFF;
INSERT INTO Contacts(FirstName, ... the rest of the columns)
VALUES (@FirstName, ... the rest of the params);
SELECT ContactID, FirstName, ... the rest of the columns
FROM Contacts WHERE (ContactID = @@IDENTITY)
"Geoff" <no********@email.com> wrote in message
news:d4********************@pipex.net...
ADO.Net
Geoff
"Earl" <br******@newsgroups.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... ADO or ADO.Net?
"Geoff" <no********@email.com> wrote in message
news:yb********************@pipex.net... Hi Earl
Yes, that's exactly it. How do I do this using ADO?
Geoff
"Earl" <br******@newsgroups.nospam> wrote in message
news:es**************@TK2MSFTNGP14.phx.gbl...
I'm not sure what you are asking, but it sounds like you want to make
sure you get the primary key from table A after you do an Insert then
use that for your foreign key in table B. The solution is to retrieve
@@Identity at the time you do the Insert into table A and use that for
your foreign key in B.
"Geoff" <no********@email.com> wrote in message
news:nf********************@pipex.net...
> Hi
>
> I was wondering if anybody could advice me on the following scenario:
>
> Suppose I have two data tables A and B respectively. Table B contains
> a foreign key to a primary key in A. If I add data to both tables and
> then perform an update, how can I be sure that the foreign key in B
> will have the correct value?
>
> To explain further, it is entirely possible that during filling the
> data in the dataset, and before the update, another user may have
> placed data in table A. For this reason, I forsee the possibility that
> the foreign key in B i.e. the link to table A, may not be correct.
>
> The only way I can see how to get this to work is to update A, reload
> A, get the new primary key, write this as the foreign key in B, and
> then finally update B!
>
> Am I making myself clear? Probably not :)
>
> If anybody understands my ramble and can help I'd be most greateful.
>
> Thanks in advance
>
> Geoff
>
