Update database in master detail form

[syntia.wijaya]

Hi, I am a one and half week old VB.NET newbie. I have a simple yet frustratring problem that costs me 3 days and still not yet solved

I have 2 tables, Workpack and WP_Activity

Workpack
--------
WP_ID (PK)
Description
Plan_Start
Plan_Finish

WP_Activity
-----------
Part_ID (PK)
Part
Act_Desc
Quantity
WP_ID (FK)

I am trying to make master details in one form with 2 DataGridView. The master records are taken from workpack table and the details are taken from WP_Activity table. When I select the master's row, the details DataGridView will automatically changed the records where WP_Activity.WP_ID = Workpack.WP_ID. That works well.

Now, I want to update table WP_activity in database using click button event. This is the code I put in Sub saveButton_click:

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
Dim tableName As String = "WP_Activity"
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(detailsDataAdapter)
cb.DataAdapter.Update(dataSet, tableName)
End Sub

When I click the save button, I got
InvalidOperationException:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

I have tried thousands of code examples (a lil hyperbolic here ), but each generate different error or exception.

Oya, I use VS 2005 and SQL Server 2005.

Thank you in advance.

Regards,
Syntia Wijaya

 

[Arg81]

Did you use the GUI to generate your datasets and tableadapters?

If so you don't need to worry about your code, simply use

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click

me.[B]tableadapter[/B].update(me.[B]dataset.datatable[/B])

End Sub

All you need to do is replace the words in bold with your correct values.

Maybe your code will work, but I think the issue is where you've put dataset, datatable - change to dataset.datatable

 

[cjard]

Hi, I am a one and half week old

Something of a child prodigy then?

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
Dim tableName As String = "WP_Activity"
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(detailsDataAdapter)
cb.DataAdapter.Update(dataSet, tableName)
End Sub

Noo.. we dont use horrible old stuff like that in .NET 2.0

You say datagridview, so youre using the modern method of datagridview/tableadapter/bindingsource, right?

So just say:

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
WP_ActivityTableAdapter.Update(dataSet.WP_Activity)
End Sub

If you get an error like "TA needs a valiud Update command when passed a collection with modified rows"
It means your table has no primary key, so the wizard wasnt able to write for you automatically the query that will update this table. Either write the query manually, or ensure the table ahs an active primary key and try agian

For more information, click the DW2 link in my sig, and read the section "Simple Application" then read the section "Saving Related Data"

 

[cjard]

as im sure youre aware, you will get problems when attempting to update the database such that the parent:child relationship fails (i.e. create an orphan child)

 

[cjard]

me.tableadapter.update(me.dataset.datatable)

5 minutes, and I'd have had ya!

 

[syntia.wijaya]

Hi Cjard and Arg81, thank you very much for your replies

I tried as you said:

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
detailsDataAdapter.Update(data)
End Sub

but error still occurs:
ArgumentException was unhandled
This constraint cannot be enabled as not all values have corresponding parent values.

I code the the DataSet and DataAdapter like this:

Private masterBindingSource As New BindingSource()
Private detailsBindingSource As New BindingSource()
 
Dim connectionString As String = 
    "Data Source=BAT04834;Initial Catalog=WPDummy;" & _
    "Integrated Security=True"
Dim connectionSQL As New SqlConnection(connectionString)
 
Dim data As New DataSet()
 
Dim masterDataAdapter As SqlDataAdapter
Dim detailsDataAdapter As SqlDataAdapter
 
Try
masterDataAdapter = _
    New SqlDataAdapter("select * from Workpack", connectionSQL)
    masterDataAdapter.Fill(data, "Workpack")
 
detailsDataAdapter = _
    New SqlDataAdapter("select * from WP_Activity", connectionSQL)
    detailsDataAdapter.Fill(data, "WP_Activity")
 
' Establish a relationship between the two tables.
Dim relation As New DataRelation("WorkpackActivity", _
    data.Tables("Workpack").Columns("WP_ID"), _
    data.Tables("WP_Activity").Columns("WP_ID"))
data.Relations.Add(relation)
 
masterBindingSource.DataSource = data
masterBindingSource.DataMember = "Workpack"
detailsBindingSource.DataSource = masterBindingSource
detailsBindingSource.DataMember = "WorkpackActivity"
connectionSQL.Close()
Catch
End Try

Actually I think I created DataSet using Add new data sources icon in Data sources tab. But when I use that dataset, a tooltiptext "Reference to a non-shared member requires an object reference" occurs So I created another DataSet in my code.

ermm, is that where it goes wrong??

 

[cjard]

This constraint cannot be enabled as not all values have corresponding parent values.

You do understand about master/detail relationships dont you? youre not allowed to orphan detail records.. Every detail record must have a corresponding master before it can be added..

I code the the DataSet and DataAdapter like this:

Ya, dont do that.. Instead, have a read of the DW2 link in my signature, section "Creating a Simple app"...

Actually I think I created DataSet using Add new data sources icon in Data sources tab. But when I use that dataset, a tooltiptext "Reference to a non-shared member requires an object reference" occurs

You need to create an instance of the dataset on your form.. Youre probably using the type name and thinking it's an instance. THis is where VB falls down and confuses its users; some things like forms have default instances, otehr things do not. default instances have exactly the same names as the type.. it sucks!

read the dw2 link

 

[syntia.wijaya]

[/color]
You do understand about master/detail relationships dont you? youre not allowed to orphan detail records.. Every detail record must have a corresponding master before it can be added..

AFAIK, in database, it's a one to many relationship. a record in master table can have a lot of records in details table and a record in details table can and must have only one record in master table.

I'll try to follow your link tommorow as i don't have VS at home..

Thank you very much cjard..

 

[Arg81]

AFAIK, in database, it's a one to many relationship. a record in master table can have a lot of records in details table and a record in details table can and must have only one record in master table.

That is correct, but the following error;

This constraint cannot be enabled as not all values have corresponding parent values.

Basically says that you are trying to enter a row in your child grid that has a FK ID that doesn't exist in your parent grid as a PK....

 

[syntia.wijaya]

I got this problem solved. The problems lay on the DBMS (SQL Server 2005). I restore the day before and the relationship between those 2 tables is gone.

The other problem is my code and the IDE's code is overlapping.

I created a whole new database and I use add new data source and datagridview form designer then add this code when the form is loaded:

[SIZE=2][COLOR=#008000]'masters table[/COLOR][/SIZE]
[SIZE=2]WorkpackTableAdapter.Fill(WP2DataSet.Workpack)[/SIZE]
[SIZE=2]DataGridView1.DataSource = bindingSource[/SIZE]
[SIZE=2]bindingSource.DataSource = WP2DataSet[/SIZE]
[SIZE=2]bindingSource.DataMember = [/SIZE][SIZE=2][COLOR=#800000]"Workpack"[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#008000]'details table[/COLOR][/SIZE]
[SIZE=2]WP_ActivityTableAdapter.Fill(WP2DataSet.WP_Activity)[/SIZE]
[SIZE=2]DataGridView2.DataSource = detailsBindingSource[/SIZE]
[SIZE=2]detailsBindingSource.DataSource = bindingSource[/SIZE]
[SIZE=2]detailsBindingSource.DataMember = [/SIZE][SIZE=2][COLOR=#800000]"FK_WP_Activity_Workpack"[/COLOR][/SIZE]

And the Update method for save button, like you said:

[SIZE=2]WorkpackTableAdapter.Update(WP2DataSet.Workpack)[/SIZE]
[SIZE=2]WP_ActivityTableAdapter.Update(WP2DataSet.WP_Activity)[/SIZE]

and it works just fine..

Now, I'm working on finding how to activate the mouse right click button and enabling menu on it.

Thank you very much for your help

 

[cjard]

Add a ContextMenuStrip to the form, populate it with entries and fill in the code for the menu items.. Then any contrl whose .ContextMenu property is set to this context menu strip, will show the menu when it is right clicked