Lets say I have two tables for simplicity (the real database has more tables like the pictures imply); both primary keys are auto-increment do not allow nulls, the foreign key allows nulls. I need to understand a fundamental concept:
One table is called customers, with the following columns:
The second table is called orders:
There is an Order form for a new customer, where you have to enter the customer info (name, phone) and the order info (product, quantity). I made text boxes for these on the form and a button.
How do I insert this info into the correct tables while ensuring the keys match after its done? I want the form to add customer info and tie orders to them.
I have tried table adapters, watched Youtube, written SQL queries and even typing in the data manually in the server explorer. I can retrieve data easily but inserting confuses me because of keys and auto increment. I think I am missing a fundamental concept. It seems that tutorials almost never show inserting data into multiple tables? Do I need a trigger or scope identity in my table adapter query?
Your dataset represents your database tables; you fill the dataset's tables with data, and then call update in the right order (you have to insert things into order and products before you can insert into orderitems)
If you're doing this in a modern version of VS your dataset will also generate a TableAdapterManager, a device that knows of all the tableadapters in the set and the order they should be used in to achieve correct functionality so doing the inserts is as simple as calling tableAdapterManager.UpdateAll(), tableAdapterManager being an instance of the TableAdapterManager class
There is a huge amount of info needed to pass to you to really answer your question, and I can't see the code so I can't tell how you've arranged things thus far, but in essence you either need your Orders and Products tables to already have data in them (downloaded from the db) or they need to have rows inserted as part of the order creation. Logically, I would assume the products are known. I also don't know if you're using VB or C# - i'll write in VB, as a C# programmer is probably less likely to complain about having to mentally translate the VB than vice versa:
Dim prodToAdd = ds.Products.Find(1234) 'gets a row from the product table with id 1234
'create an order
Dim order = ds.Orders.NewOrdersRow()
order.Address = "Whatever" 'set properties of the order
ds.Orders.Add(order) 'put the new row in the local datatable, not saved to db yet
'put a product on an order
ds.OrderItems.AddOrderItemsRow(prodToAdd, order, ...) 'directly add an orderitem row linking the Product and the Order
'save to db
tableAdapterManager.UpdateAll(ds)
If you don't have a manager, you must insert the rows to the db in the right order yourself:
ordersTableAdapter.Update(ds.Orders) 'yes, the method is called update, but it runs an INSERT sql for rows in an Added rowstate
orderItemsTableAdater.Update(ds.OrderItems)