Adding new rows to related tables on a dataset 
Author Message
 Adding new rows to related tables on a dataset

Hi group:

On an VB.Net-SQL 7.0 winform application to be used on a multiuser
enviroment I have a dataset with two tables : tblHeader and tblDetails with
the following structure:

tblHeader:
-HeaderID
-Name
-Description

tblDetails:
-DetailID
-fHeaderID
-Item
-ItemDescrpiption

There is a parent-child relation between this 2 tables using the primary key
of tblHeader (HeaderID) and a foreign key on tblDetails (fHeaderID).

Both tables are not empty, mean they have records and I'll like to keep
them.

I will like to add at runtime several new rows to both tables, persist them
to the SQL Server 7.0 database and be sure that I keep the relation by using
the same value for HeaderID/fHeaderID on each table.

How can I do this?
Thanks in advance,
Erich



Sun, 27 Feb 2005 10:00:47 GMT  
 Adding new rows to related tables on a dataset
I'm not sure if I understand correctly your problem, but to do what you
want...
myDataSet.EnforceConstraints = False
myDataSet.AddWhateverDataYouWantInIt()
myDataSet.EnforceConstraints = True
myAdapter.Update(myDataSet)


Quote:
> Hi group:

> On an VB.Net-SQL 7.0 winform application to be used on a multiuser
> enviroment I have a dataset with two tables : tblHeader and tblDetails
with
> the following structure:

> tblHeader:
> -HeaderID
> -Name
> -Description

> tblDetails:
> -DetailID
> -fHeaderID
> -Item
> -ItemDescrpiption

> There is a parent-child relation between this 2 tables using the primary
key
> of tblHeader (HeaderID) and a foreign key on tblDetails (fHeaderID).

> Both tables are not empty, mean they have records and I'll like to keep
> them.

> I will like to add at runtime several new rows to both tables, persist
them
> to the SQL Server 7.0 database and be sure that I keep the relation by
using
> the same value for HeaderID/fHeaderID on each table.

> How can I do this?
> Thanks in advance,
> Erich



Sun, 27 Feb 2005 11:00:11 GMT  
 Adding new rows to related tables on a dataset
Thanks for your answer Etienne.

This is the part I'm having problems with:
myDataSet.AddWhateverDataYouWantInIt(),
How should I go to guarantee that the HeaderID / fHeaderID values that
constitute the relation link between the 2 tables on the dataset will be
valid once I update the tables to the database (multiuser enviroment).

What I do now is:

1. Add the header row to the dataset.
2. Update the database.
3. Retrieve the header ID from database/updated dataset.
4. Add the details rows using the HeaderID I retrieved on step 3.
5. Update the database.

Is there any way I can get rid of steps 2 & 3 ?

Thanks again,
Erich


Quote:
> I'm not sure if I understand correctly your problem, but to do what you
> want...
> myDataSet.EnforceConstraints = False
> myDataSet.AddWhateverDataYouWantInIt()
> myDataSet.EnforceConstraints = True
> myAdapter.Update(myDataSet)



> > Hi group:

> > On an VB.Net-SQL 7.0 winform application to be used on a multiuser
> > enviroment I have a dataset with two tables : tblHeader and tblDetails
> with
> > the following structure:

> > tblHeader:
> > -HeaderID
> > -Name
> > -Description

> > tblDetails:
> > -DetailID
> > -fHeaderID
> > -Item
> > -ItemDescrpiption

> > There is a parent-child relation between this 2 tables using the primary
> key
> > of tblHeader (HeaderID) and a foreign key on tblDetails (fHeaderID).

> > Both tables are not empty, mean they have records and I'll like to keep
> > them.

> > I will like to add at runtime several new rows to both tables, persist
> them
> > to the SQL Server 7.0 database and be sure that I keep the relation by
> using
> > the same value for HeaderID/fHeaderID on each table.

> > How can I do this?
> > Thanks in advance,
> > Erich



Mon, 28 Feb 2005 23:55:40 GMT  
 Adding new rows to related tables on a dataset
Oh, you're talking about database-auto-generated identity columns?

myDataAdapter.InsertCommand = New SqlCommand("myStoredProcedure")
myDataAdapter.InsertCommand.CommandType = StoredProcedure
myDataAdapter.InsertCommand.AddParametersAssociatedWithTheStoredProcedure(Wi
thAnOutputIdColumn)

That is, when you'll insert the row, you'll call a stored procedure which
will put the new ID in an output parameter. That value will automatically be
applied to your datarow if you do it correctly. On your relation, you can
tell to update automatically the child rows when the parent's ID change
(Cascade update).


Quote:
> Thanks for your answer Etienne.

> This is the part I'm having problems with:
> myDataSet.AddWhateverDataYouWantInIt(),
> How should I go to guarantee that the HeaderID / fHeaderID values that
> constitute the relation link between the 2 tables on the dataset will be
> valid once I update the tables to the database (multiuser enviroment).

> What I do now is:

> 1. Add the header row to the dataset.
> 2. Update the database.
> 3. Retrieve the header ID from database/updated dataset.
> 4. Add the details rows using the HeaderID I retrieved on step 3.
> 5. Update the database.

> Is there any way I can get rid of steps 2 & 3 ?

> Thanks again,
> Erich



> > I'm not sure if I understand correctly your problem, but to do what you
> > want...
> > myDataSet.EnforceConstraints = False
> > myDataSet.AddWhateverDataYouWantInIt()
> > myDataSet.EnforceConstraints = True
> > myAdapter.Update(myDataSet)



> > > Hi group:

> > > On an VB.Net-SQL 7.0 winform application to be used on a multiuser
> > > enviroment I have a dataset with two tables : tblHeader and tblDetails
> > with
> > > the following structure:

> > > tblHeader:
> > > -HeaderID
> > > -Name
> > > -Description

> > > tblDetails:
> > > -DetailID
> > > -fHeaderID
> > > -Item
> > > -ItemDescrpiption

> > > There is a parent-child relation between this 2 tables using the
primary
> > key
> > > of tblHeader (HeaderID) and a foreign key on tblDetails (fHeaderID).

> > > Both tables are not empty, mean they have records and I'll like to
keep
> > > them.

> > > I will like to add at runtime several new rows to both tables, persist
> > them
> > > to the SQL Server 7.0 database and be sure that I keep the relation by
> > using
> > > the same value for HeaderID/fHeaderID on each table.

> > > How can I do this?
> > > Thanks in advance,
> > > Erich



Tue, 01 Mar 2005 07:12:12 GMT  
 Adding new rows to related tables on a dataset
Can you pitch me an example of how to build this stored procedure that will

Then I use another sp to insert the 'Details' rows and this time using the
output parameter as input parameter, right?

I'm kind of new to SQL Sever, working with access for years, I hope I can
take the change without suffering too much...
I'm not using sp but SQL strings from VB.NET, but I guess I can implement sp
if I have to.

Thank u very much for your help,
Erich


Quote:
> Oh, you're talking about database-auto-generated identity columns?

> myDataAdapter.InsertCommand = New SqlCommand("myStoredProcedure")
> myDataAdapter.InsertCommand.CommandType = StoredProcedure

myDataAdapter.InsertCommand.AddParametersAssociatedWithTheStoredProcedure(Wi
Quote:
> thAnOutputIdColumn)

> That is, when you'll insert the row, you'll call a stored procedure which
> will put the new ID in an output parameter. That value will automatically
be
> applied to your datarow if you do it correctly. On your relation, you can
> tell to update automatically the child rows when the parent's ID change
> (Cascade update).



> > Thanks for your answer Etienne.

> > This is the part I'm having problems with:
> > myDataSet.AddWhateverDataYouWantInIt(),
> > How should I go to guarantee that the HeaderID / fHeaderID values that
> > constitute the relation link between the 2 tables on the dataset will be
> > valid once I update the tables to the database (multiuser enviroment).

> > What I do now is:

> > 1. Add the header row to the dataset.
> > 2. Update the database.
> > 3. Retrieve the header ID from database/updated dataset.
> > 4. Add the details rows using the HeaderID I retrieved on step 3.
> > 5. Update the database.

> > Is there any way I can get rid of steps 2 & 3 ?

> > Thanks again,
> > Erich



> > > I'm not sure if I understand correctly your problem, but to do what
you
> > > want...
> > > myDataSet.EnforceConstraints = False
> > > myDataSet.AddWhateverDataYouWantInIt()
> > > myDataSet.EnforceConstraints = True
> > > myAdapter.Update(myDataSet)



> > > > Hi group:

> > > > On an VB.Net-SQL 7.0 winform application to be used on a multiuser
> > > > enviroment I have a dataset with two tables : tblHeader and
tblDetails
> > > with
> > > > the following structure:

> > > > tblHeader:
> > > > -HeaderID
> > > > -Name
> > > > -Description

> > > > tblDetails:
> > > > -DetailID
> > > > -fHeaderID
> > > > -Item
> > > > -ItemDescrpiption

> > > > There is a parent-child relation between this 2 tables using the
> primary
> > > key
> > > > of tblHeader (HeaderID) and a foreign key on tblDetails (fHeaderID).

> > > > Both tables are not empty, mean they have records and I'll like to
> keep
> > > > them.

> > > > I will like to add at runtime several new rows to both tables,
persist
> > > them
> > > > to the SQL Server 7.0 database and be sure that I keep the relation
by
> > > using
> > > > the same value for HeaderID/fHeaderID on each table.

> > > > How can I do this?
> > > > Thanks in advance,
> > > > Erich



Tue, 01 Mar 2005 23:12:44 GMT  
 Adding new rows to related tables on a dataset
I use stored procedures only for one thing: inserting data to tables with
auto-generated ID.

ALTER PROCEDURE dbo.InsertCustommer

)
AS



Quote:
> Can you pitch me an example of how to build this stored procedure that
will

> Then I use another sp to insert the 'Details' rows and this time using the
> output parameter as input parameter, right?

> I'm kind of new to SQL Sever, working with access for years, I hope I can
> take the change without suffering too much...
> I'm not using sp but SQL strings from VB.NET, but I guess I can implement
sp
> if I have to.

> Thank u very much for your help,
> Erich



> > Oh, you're talking about database-auto-generated identity columns?

> > myDataAdapter.InsertCommand = New SqlCommand("myStoredProcedure")
> > myDataAdapter.InsertCommand.CommandType = StoredProcedure

myDataAdapter.InsertCommand.AddParametersAssociatedWithTheStoredProcedure(Wi

- Show quoted text -

Quote:
> > thAnOutputIdColumn)

> > That is, when you'll insert the row, you'll call a stored procedure
which
> > will put the new ID in an output parameter. That value will
automatically
> be
> > applied to your datarow if you do it correctly. On your relation, you
can
> > tell to update automatically the child rows when the parent's ID change
> > (Cascade update).



> > > Thanks for your answer Etienne.

> > > This is the part I'm having problems with:
> > > myDataSet.AddWhateverDataYouWantInIt(),
> > > How should I go to guarantee that the HeaderID / fHeaderID values that
> > > constitute the relation link between the 2 tables on the dataset will
be
> > > valid once I update the tables to the database (multiuser enviroment).

> > > What I do now is:

> > > 1. Add the header row to the dataset.
> > > 2. Update the database.
> > > 3. Retrieve the header ID from database/updated dataset.
> > > 4. Add the details rows using the HeaderID I retrieved on step 3.
> > > 5. Update the database.

> > > Is there any way I can get rid of steps 2 & 3 ?

> > > Thanks again,
> > > Erich



> > > > I'm not sure if I understand correctly your problem, but to do what
> you
> > > > want...
> > > > myDataSet.EnforceConstraints = False
> > > > myDataSet.AddWhateverDataYouWantInIt()
> > > > myDataSet.EnforceConstraints = True
> > > > myAdapter.Update(myDataSet)



> > > > > Hi group:

> > > > > On an VB.Net-SQL 7.0 winform application to be used on a multiuser
> > > > > enviroment I have a dataset with two tables : tblHeader and
> tblDetails
> > > > with
> > > > > the following structure:

> > > > > tblHeader:
> > > > > -HeaderID
> > > > > -Name
> > > > > -Description

> > > > > tblDetails:
> > > > > -DetailID
> > > > > -fHeaderID
> > > > > -Item
> > > > > -ItemDescrpiption

> > > > > There is a parent-child relation between this 2 tables using the
> > primary
> > > > key
> > > > > of tblHeader (HeaderID) and a foreign key on tblDetails
(fHeaderID).

> > > > > Both tables are not empty, mean they have records and I'll like to
> > keep
> > > > > them.

> > > > > I will like to add at runtime several new rows to both tables,
> persist
> > > > them
> > > > > to the SQL Server 7.0 database and be sure that I keep the
relation
> by
> > > > using
> > > > > the same value for HeaderID/fHeaderID on each table.

> > > > > How can I do this?
> > > > > Thanks in advance,
> > > > > Erich



Wed, 02 Mar 2005 00:18:20 GMT  
 Adding new rows to related tables on a dataset
Warm thanks from Havana, Cuba
Erich


Quote:
> I use stored procedures only for one thing: inserting data to tables with
> auto-generated ID.

> ALTER PROCEDURE dbo.InsertCustommer
> (


> )
> AS




> > Can you pitch me an example of how to build this stored procedure that
> will

> > Then I use another sp to insert the 'Details' rows and this time using
the
> > output parameter as input parameter, right?

> > I'm kind of new to SQL Sever, working with access for years, I hope I
can
> > take the change without suffering too much...
> > I'm not using sp but SQL strings from VB.NET, but I guess I can
implement
> sp
> > if I have to.

> > Thank u very much for your help,
> > Erich



> > > Oh, you're talking about database-auto-generated identity columns?

> > > myDataAdapter.InsertCommand = New SqlCommand("myStoredProcedure")
> > > myDataAdapter.InsertCommand.CommandType = StoredProcedure

myDataAdapter.InsertCommand.AddParametersAssociatedWithTheStoredProcedure(Wi

- Show quoted text -

Quote:
> > > thAnOutputIdColumn)

> > > That is, when you'll insert the row, you'll call a stored procedure
> which
> > > will put the new ID in an output parameter. That value will
> automatically
> > be
> > > applied to your datarow if you do it correctly. On your relation, you
> can
> > > tell to update automatically the child rows when the parent's ID
change
> > > (Cascade update).



> > > > Thanks for your answer Etienne.

> > > > This is the part I'm having problems with:
> > > > myDataSet.AddWhateverDataYouWantInIt(),
> > > > How should I go to guarantee that the HeaderID / fHeaderID values
that
> > > > constitute the relation link between the 2 tables on the dataset
will
> be
> > > > valid once I update the tables to the database (multiuser
enviroment).

> > > > What I do now is:

> > > > 1. Add the header row to the dataset.
> > > > 2. Update the database.
> > > > 3. Retrieve the header ID from database/updated dataset.
> > > > 4. Add the details rows using the HeaderID I retrieved on step 3.
> > > > 5. Update the database.

> > > > Is there any way I can get rid of steps 2 & 3 ?

> > > > Thanks again,
> > > > Erich



> > > > > I'm not sure if I understand correctly your problem, but to do
what
> > you
> > > > > want...
> > > > > myDataSet.EnforceConstraints = False
> > > > > myDataSet.AddWhateverDataYouWantInIt()
> > > > > myDataSet.EnforceConstraints = True
> > > > > myAdapter.Update(myDataSet)



> > > > > > Hi group:

> > > > > > On an VB.Net-SQL 7.0 winform application to be used on a
multiuser
> > > > > > enviroment I have a dataset with two tables : tblHeader and
> > tblDetails
> > > > > with
> > > > > > the following structure:

> > > > > > tblHeader:
> > > > > > -HeaderID
> > > > > > -Name
> > > > > > -Description

> > > > > > tblDetails:
> > > > > > -DetailID
> > > > > > -fHeaderID
> > > > > > -Item
> > > > > > -ItemDescrpiption

> > > > > > There is a parent-child relation between this 2 tables using the
> > > primary
> > > > > key
> > > > > > of tblHeader (HeaderID) and a foreign key on tblDetails
> (fHeaderID).

> > > > > > Both tables are not empty, mean they have records and I'll like
to
> > > keep
> > > > > > them.

> > > > > > I will like to add at runtime several new rows to both tables,
> > persist
> > > > > them
> > > > > > to the SQL Server 7.0 database and be sure that I keep the
> relation
> > by
> > > > > using
> > > > > > the same value for HeaderID/fHeaderID on each table.

> > > > > > How can I do this?
> > > > > > Thanks in advance,
> > > > > > Erich



Wed, 02 Mar 2005 21:51:57 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Adding a new row to a Dataset

2. HELP with adding new rows to a dataset

3. Add new row to a table dynamically

4. Adding a new order overwrite the last row in the table

5. trace new added rows of a table

6. adding a table row, without setting same defaults as previous row

7. row heights of table rows added by vba

8. Adding rows to related DataTables !!

9. adding rows dataset slow

10. how to add a new dataset from excel to access by means of vba

11. Need Help in Adding records in the related tables

12. Adding new records to a dataset

 

 
Powered by phpBB® Forum Software