"Geoff" <nodamnspam@email.com> wrote in message
news:nfCdnY7Wn8O5RE7eRVnyug@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
>

"Earl" <brikshoe@newsgroups.nospam> wrote in message
news:esQj064HGHA.2900@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" <nodamnspam@email.com> wrote in message
> news:nfCdnY7Wn8O5RE7eRVnyug@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
>>
>
>

"Geoff" <nodamnspam@email.com> wrote in message
news:ybGdneuS3akadU7eRVny0A@pipex.net...
> Hi Earl
>
> Yes, that's exactly it. How do I do this using ADO?
>
> Geoff
>
> "Earl" <brikshoe@newsgroups.nospam> wrote in message
> news:esQj064HGHA.2900@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" <nodamnspam@email.com> wrote in message
>> news:nfCdnY7Wn8O5RE7eRVnyug@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
>>>
>>
>>
>
>

"Earl" <brikshoe@newsgroups.nospam> wrote in message
news:%231iSBQ5HGHA.2064@TK2MSFTNGP09.phx.gbl...
> ADO or ADO.Net?
>
> "Geoff" <nodamnspam@email.com> wrote in message
> news:ybGdneuS3akadU7eRVny0A@pipex.net...
>> Hi Earl
>>
>> Yes, that's exactly it. How do I do this using ADO?
>>
>> Geoff
>>
>> "Earl" <brikshoe@newsgroups.nospam> wrote in message
>> news:esQj064HGHA.2900@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" <nodamnspam@email.com> wrote in message
>>> news:nfCdnY7Wn8O5RE7eRVnyug@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
>>>>
>>>
>>>
>>
>>
>
>

"Geoff" <nodamnspam@email.com> wrote in message
news:d4idnW3_fOAzmUneRVnytw@pipex.net...
> ADO.Net
>
> Geoff
>
> "Earl" <brikshoe@newsgroups.nospam> wrote in message
> news:%231iSBQ5HGHA.2064@TK2MSFTNGP09.phx.gbl...
>> ADO or ADO.Net?
>>
>> "Geoff" <nodamnspam@email.com> wrote in message
>> news:ybGdneuS3akadU7eRVny0A@pipex.net...
>>> Hi Earl
>>>
>>> Yes, that's exactly it. How do I do this using ADO?
>>>
>>> Geoff
>>>
>>> "Earl" <brikshoe@newsgroups.nospam> wrote in message
>>> news:esQj064HGHA.2900@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" <nodamnspam@email.com> wrote in message
>>>> news:nfCdnY7Wn8O5RE7eRVnyug@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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"Earl" <brikshoe@newsgroups.nospam> wrote in message
news:%239mxpN7HGHA.3408@TK2MSFTNGP12.phx.gbl...
> 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/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.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" <nodamnspam@email.com> wrote in message
> news:d4idnW3_fOAzmUneRVnytw@pipex.net...
>> ADO.Net
>>
>> Geoff
>>
>> "Earl" <brikshoe@newsgroups.nospam> wrote in message
>> news:%231iSBQ5HGHA.2064@TK2MSFTNGP09.phx.gbl...
>>> ADO or ADO.Net?
>>>
>>> "Geoff" <nodamnspam@email.com> wrote in message
>>> news:ybGdneuS3akadU7eRVny0A@pipex.net...
>>>> Hi Earl
>>>>
>>>> Yes, that's exactly it. How do I do this using ADO?
>>>>
>>>> Geoff
>>>>
>>>> "Earl" <brikshoe@newsgroups.nospam> wrote in message
>>>> news:esQj064HGHA.2900@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" <nodamnspam@email.com> wrote in message
>>>>> news:nfCdnY7Wn8O5RE7eRVnyug@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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

> 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
>
>

"R. MacDonald" <sci***@NO-SP-AMcips.ca> wrote in message
news:43d494fe$0$57735$dbd4b001@news.wanadoo.nl...
> Hello, Geoff,
>
> You don't say what database you are using.  Try searching your help or
> documentation for "transaction".  If your database software supports
> "transaction processing" (it probably does), I think you will find this is
> what you want.
>
> Typical use:
>
>     Begin a transaction.
>     Modify data in table
>     Modify related data (in related table)
>         "    "    "    "    "    "
>     End the transaction
>
> In the final step you can choose to either commit or roll back the
> changes.  When you commit, then other "referencers" of the database will
> "see" all your changes "atomically".  (I.e. all changes are made in a
> single operation.)
>
> Cheers,
> Randy
>
>
> Geoff wrote:
>> 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

"Geoff" <nodamnspam@email.com> wrote in message
news:66WdndroEbJmOUneRVnytA@pipex.net...
> Hi
>
> Thanks for your help. I'm using SQL Server. I'll try and find out some
> more on transactions. They sound interesting.
>
> Geoff
>
> "R. MacDonald" <sci***@NO-SP-AMcips.ca> wrote in message
> news:43d494fe$0$57735$dbd4b001@news.wanadoo.nl...
>> Hello, Geoff,
>>
>> You don't say what database you are using.  Try searching your help or
>> documentation for "transaction".  If your database software supports
>> "transaction processing" (it probably does), I think you will find this
>> is what you want.
>>
>> Typical use:
>>
>>     Begin a transaction.
>>     Modify data in table
>>     Modify related data (in related table)
>>         "    "    "    "    "    "
>>     End the transaction
>>
>> In the final step you can choose to either commit or roll back the
>> changes.  When you commit, then other "referencers" of the database will
>> "see" all your changes "atomically".  (I.e. all changes are made in a
>> single operation.)
>>
>> Cheers,
>> Randy
>>
>>
>> Geoff wrote:
>>> 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
>
>