How to save related data?

[lithium1976]

Hello, this is my first post here, and I am hoping it is a vb.net question, although I am using sql stored procedures also.

I am having some trouble inserting a record into its related table.

I am desinging my database front end in vs2008 vb.net, and the database itself is just 2 tables in SQL Server Management Studio Express -

tblCustomers
customerID (primary key)
customerName
customerAddress

tblBookings
BookingsID (primary key)
customerID
datebookingplaced

I am using two datagridviews to display the two the tables - dgvCustomers, and dgvBookings. I have set up the vb.net relationship between the two tables - one Customer has many Bookings (one customerID eg 001 can have many bookingsID's)

So, firstly I select/highlight a customer (eg customerID 001) from dgvCustomers. Next, I select an empty row from dgvBookings which creates a unique bookingsID for that row and adds customerID 001 to the customerID column in dgvBookings.

This is working ok up to this point. What I cannot next do is actually save/inesert/update the related customerID in dgvBookings into tblBookings. If I press the update button the row just goes blank and nothing gets saved. To make the save work I have to delete the related customerID from dgvBookings and type it in myself. Then I hit my update button. When I type in myself it puts the datagridview into EditMode (the pencil icon appears).

How do I save the related bookingsID and customerID in dgvBookings to my tblBookings without having to type into the datagridview? I want it to work like this - when I select an empty row in dgvBookings the bookingsID is created and the related customerID is added. I just want to click my update button to save the data from dgvbookings into tblBookings - without having to delete the customerID and type it back in myself.

Here is the code I am using -

Dim conn As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;" & "Initial Catalog=bookingsdbNEW;Data Source=TOM-PC\SQLEXPRESS")

Dim ds As New DataSet

Dim datatable As DataTable

'tblcustomers
Dim CustomersDataAdapter As New SqlDataAdapter("SELECT * from tblcustomers", conn)
Dim CustomersBindingSource As New BindingSource() 'for tblcustomers

'tblbookings
Dim BookingsDataAdapter As New SqlDataAdapter("SELECT * from tblbookings", conn)
Dim BookingsBindingSource As New BindingSource() 'for tblbookings

Private Sub test_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Preparing tblcustomers
CustomersDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
CustomersDataAdapter.Fill(ds, "tblcustomers")
datatable = ds.Tables("tblcustomers") 
CustomersBindingSource.DataSource = ds
CustomersBindingSource.DataMember = "tblCustomers"
dgvCustomers.DataSource = CustomersBindingSource

'Preparing tblCustomers-tblBookings Relationship
BookingsDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
BookingsDataAdapter.Fill(ds, "tblbookings")
datatable = ds.Tables("tblbookings")
Dim relation As New DataRelation("CustomersBookings", _
ds.Tables("tblCustomers").Columns("CustomerID"), _
ds.Tables("tblbookings").Columns("CustomerID"))
ds.Relations.Add(relation)
BookingsBindingSource.DataSource = CustomersBindingSource
BookingsBindingSource.DataMember = "CustomersBookings"
dgvBookings.DataSource = BookingsBindingSource

'Insert Customer to Bookings table
Dim AddCustomerToBookingsInsertCommand As New SqlCommand("testInsert", conn)
AddCustomerToBookingsInsertCommand.CommandType = CommandType.StoredProcedure
AddCustomerToBookingsInsertCommand.Parameters.Add("@bookingsID", SqlDbType.Int, 100, "bookingsID")
AddCustomerToBookingsInsertCommand.Parameters.Add("@customerID", SqlDbType.Int, 100, "customerID")
BookingsDataAdapter.InsertCommand = AddCustomerToBookingsInsertCommand

'Update Customer to Bookings 
Dim AddCustomerToBookingsUpdateCommand As New SqlCommand("testUpdate", conn)
AddCustomerToBookingsUpdateCommand.CommandType = CommandType.StoredProcedure
AddCustomerToBookingsUpdateCommand.Parameters.Add("@customerID", SqlDbType.Int, 100, "customerID")
BookingsDataAdapter.UpdateCommand = AddCustomerToBookingsUpdateCommand

My update button is -

BookingsDataAdapter.Update(ds, "tblBookings")

My insert and update stored procedures are -

ALTER PROCEDURE [dbo].[testInsert] 
(@bookingsID as int,
@customerID as int)
AS 
SET IDENTITY_INSERT tblbookings ON
INSERT INTO tblBookings (bookingsID, customerID)
VALUES(@bookingsID,@customerID)

ALTER PROCEDURE [dbo].[testUpdate] 
(@customerID as int)
AS
UPDATE tblBookings SET
customerID = @customerID
WHERE customerID = @customerID

 

[lithium1976]

Can anyone help?

 

[cjard]

The DW2 link in my signature hjas a help topic on this very issue

 

[lithium1976]

The DW2 link in my signature hjas a help topic on this very issue

Which help topic title is it? There are so many. Im using a dataadapter.

 

[lithium1976]

Which help topic is it? Are you referring to Walkthrough: Saving Data to a Database (Multiple Tables)? If so, that code is using a tableadpater.

 

[cjard]

Which help topic is it? Are you referring to Walkthrough: Saving Data to a Database (Multiple Tables)? If so, that code is using a tableadpater.

It's called something like "Saving Related Data"
And yes, it's using a tableadapter, which is a type-specific dataadapter. I dont use dataadapters because it means writing lots of boring, crappy code myself when I can get the IDE to write it better, faster and cleaner than I ever could in the time.

If you want to stick with dataadapters, its no problem, you'll just have to put in more manual donkeywork to get the tutorial to work than someone using tableadapters would..
..kinda like programming wiht notepad; it works but it takes a lot longer to lay out form code than someone using a visual designer

 

[lithium1976]

I just tried it using a table adapter from the MSDN Order Manager database example. Customers and Orders tables.

And still the same result, as soon as I select a new row in the orders table and click save in the bindingNavigator the selected row in the Orders Datagridview goes blank and doesnt save - unless I manually type in the customerID into the column - then it saves. The datagridview goes into editmode I think if I type into a cell. Thats why it only saves if I type in manually.

 

[cjard]

You might want to have a read of the sticky in this forum:

http://www.vbdotnetforums.com/data-access/13058-calling-endedit-causes-datarelations-fail.html

 

[lithium1976]

I've got it to work by adding bindingsource.movenext() to my datagridview cell doubleclick.