Replicate MS Access task to Form

[DonToDawn]

In MS Access, I can use the following SQL statement in a query to show data from 2 tables. If I make a change in the results then these changes are applied to the 2 tables.

SELECT     tblMetrics.ID, tblMetrics.Username, tbProjectNames.Project, tblMetrics.Proj_Schedule, tblMetrics.MetDate, tblMetrics.DFT, tblMetrics.LDFT, 
                      tblMetrics.DWIFT, tblMetrics.DTC, tblMetrics.LDTC, tblMetrics.DWITC
FROM         (tblMetrics INNER JOIN
                      tbProjectNames ON tblMetrics.ProjID = tbProjectNames.ProjID)

I have created a form in VB.NET and connected a dataset using the wizard to the Access database and am able to view the data from the SQL statement in a Datagrid. The problem is how can I modify the data in the Datagrid so if I make a change the changes are saved to the 2 tables as what was happening if I was working directly in Access.

Thanks
Don

 

[4461766964]

This question is very similar to one that I asked just a bit ago. If you'd like you can see it here:

http://www.vbdotnetforums.com/data-access/28823-best-practices-no-afterupdate.html

The short answer is that VB.NET is intentionally designed to avoid this scenario. That is why it puts the "Save" button on the records navigation toolbar. (And I assume that it saves on exit if you use the wizard as well?)

There are many ways to get around this though if you must have this feature. No matter what, it will require quite a bit of coding. So far the easiest I have found is to set a global boolean variable like blnNeedUpdate and set it to true on the control/field's textchanged event. Then on the validate event you can use that boolean to confirm that the updates are acceptable and then call for the database update (like the code for the save button).

 

[DonToDawn]

Hi

I tried using the records navigation toolbar to save the changes but because the DataGrid contains data from 2 tables, i get an error when trying to save.

Because the SQL statment retrieves the data from the 2 tables, i thought there would be a generic command to save the data

 

[4461766964]

Not sure, this one might be beyond me. You should be able to save to two tables with no problems. What is the error specifically?

 

[DonToDawn]

Here is the error

Parameter ?_2 has no default value

The code behind the save button is

Me.Validate() 
        Me.TblMetricsBindingSource.EndEdit() 
        Me.TblMetricsTableAdapter.Update(Me.MetricsDataSet.tblMetrics)

 

[DonToDawn]

I had a look on MSDN and there is a section for saving data to multiple tables but only if the data from the tables are in different tableadapters and displayed in different DataGrids. I could not find any info for displaying data in a Datgrid from two tables and making changes to the data where the changes are saved to their respective tables.

I hope someone on the forum has come across this before as I have hit a brick wall

 

[cjard]

Does access let you update a join view? it must be a stupid db then, because we have the problem of key preservation here.

Suppose we have parent and child:

parentID, parentVal, parentChildID, childID, childVal
1, 100, 1, A, HELLO
1, 100, 1, B, GOODBYE


See one parent row joins to two child rows, because a join on (parentID = parentChildID) matches twice

Now tell me, you edit the data so it looks like:


parentID, parentVal, parentChildID, childID, childVal
1, 200, 1, A, HELLO
1, 400, 1, B, GOODBYE


Which one do you want to finish up with? There is only one parent row; it cannot accept values 200 and 400 simultaneously.

This is why "proper" db like SQL server and Oracle dont allow to update joined views where the primary key is not preserved

 

[DonToDawn]

Are you saying that it is not possible to save to two tables from the 1 Data Grid using a table adapter.

Excuse if I have misunderstood but this is new to me

 

[cjard]

Pretty much, yeah! There are ways round it but typically we dont use JOIN queries in writable datagridview, only read only ones..