ADO.Net Database Question

[Guest]

I am new to Database applications and have a few questions. I have a
database with a orders table with a customerID, ProductsID... each pointing
to its respective tables(customer table and products table). Each order
will have a new customer (customer table is not used to show all the
customers it is used to record the customer info for that order only) and
the same is true for the products table. Each product is different in its
own regard and the products table will save the product and corresponding
options (fields in the table) for that order. This is not like the
Northwind db with the customers and products tables represent the
customers/products that can be used for orders. The customers and products
are dynamic and my front end (C# WinForm App) will update each table for an
order. Know here is my question: Since I have each key(ProductID and
CustomerID) being incremented. How can I save in the Orders table the keys
from the customer/products tables? Do I first update these tables to get
the keys and then update the orders table? How do I create a business
workflow object to handle this? Any help would be appreciated!

Thanks

 

[Guest]

Thanks for the post. I should have been more specific. Currently, we are
using Access 2002 and might make the move to MS SQL Server. I wish this was
not the case because
the "return SCOPE_IDENTITY()" would be very helpful in this situation.
Also, I am wondering is my workflow of entering customer data and product
data to get the autoincrement value for each table for the main orders table
the best solution?

Thanks

 

[Miha Markic]

Hi James,

I am new to Database applications and have a few questions. I have a
database with a orders table with a customerID, ProductsID... each pointing
to its respective tables(customer table and products table). Each order
will have a new customer (customer table is not used to show all the
customers it is used to record the customer info for that order only) and
the same is true for the products table. Each product is different in its
own regard and the products table will save the product and corresponding
options (fields in the table) for that order. This is not like the
Northwind db with the customers and products tables represent the
customers/products that can be used for orders. The customers and products
are dynamic and my front end (C# WinForm App) will update each table for an
order. Know here is my question: Since I have each key(ProductID and
CustomerID) being incremented. How can I save in the Orders table the keys
from the customer/products tables? Do I first update these tables to get
the keys and then update the orders table? How do I create a business
workflow object to handle this? Any help would be appreciated!

Check out my answer in identity column question few threads below.

 

[Miha Markic]

Thanks for the post. I should have been more specific. Currently, we are
using Access 2002 and might make the move to MS SQL Server. I wish this was
not the case because
the "return SCOPE_IDENTITY()" would be very helpful in this situation.

Well, then you should use @@identity.
http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

 

[David]

I am new to Database applications and have a few questions. I have a
database with a orders table with a customerID, ProductsID... each pointing
to its respective tables(customer table and products table). Each order
will have a new customer (customer table is not used to show all the
customers it is used to record the customer info for that order only) and
the same is true for the products table. Each product is different in its
own regard and the products table will save the product and corresponding
options (fields in the table) for that order. This is not like the
Northwind db with the customers and products tables represent the
customers/products that can be used for orders. The customers and products
are dynamic and my front end (C# WinForm App) will update each table for an
order.

I guess then the question is why do you have a ProductID and CustomerID?
If I understand the above, then the OrderID is unique on each table,
there's no need to use any other keys.

Know here is my question: Since I have each key(ProductID and
CustomerID) being incremented. How can I save in the Orders table the keys
from the customer/products tables? Do I first update these tables to get
the keys and then update the orders table? How do I create a business
workflow object to handle this? Any help would be appreciated!

In general, that's exactly what you'd do, although as I said in this
case you might be better off inserting the Order entry first, then using
that OrderID as the primary key for the other tables.

 

[William \(Bill\) Vaughn]

Again, see my article on handling Identity issues on my website
www.betav.com\articles.htm


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________