update dataset with multiple table from 1 single query to database

I need to update data from the changes that I made to the datagridview, so I don't know how to achieve this from the store procedure. Could you provide some more details?

thanks,

when you populated the gridview from you dataset, you must have mapped those fields. On change event in gridview could be used to reverse that map from the gridview cell back to the dataset.
Have a separate COMMIT button to wriite those changes back to the database, since you don't want to change anything on the database until the user has stopped fiddling and is definite that the changes he/she made are to be permanent.

You want a bit more robust treatment of data changes than just a series of separate updates to tables where there is a real or implied link across those tables.

A single stored procedure that is called by a COMMIT function can update any number of tables in a series of update statements and ensure data integrity by wrapping thjem all within a single transaction scope. If something fails, then everything is wound back and no tables are touched. Without a transaction scope you could end up with an update to one table succeeeding while another update faiils, potentially destroying data integrity and corrupting your data.

Thanks Both. Your comments are great. Both address the problem that I have. I wish to have some more details, but I will try to work from your advises now.

Hi Guys

I tried to use relations as Tommy suggested. (still learning so I want to do a simple way first)

First, I fill the dataset with data from the query(2 tables joined together) and link it to the datagridview.
Then I also add data from both tables to the dataset. After that, I created several relations among these tables in the dataset.

When I change data in the grid and click the update button. DATA IS STILL NOT UPDATED IN THE DATABASE.

Please let me know what's missing or wrong here. I really appreciate your help.

sub ....

try

dim strSql = "select qc.polinesID as polinesID, polines.item, qc.customerrequest, qc.note"
strSql &= " from qc inner join polines on qc.polinesId=polines.polinesID"

     data_adapter = New SqlClient.SqlDataAdapter(strSql,
            connect_string)
            data_adapter.Fill(ds, "table1")
            Me.binding_source.DataSource = ds.Tables("table1")

        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        Finally

        End Try

        Try
            data_adapter2 = New SqlClient.SqlDataAdapter("select * from qc", _
            connect_string)
            'Dim ds As New DataSet()
            data_adapter2.Fill(ds, "qc")
          Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        Finally

        End Try

        Try
            data_adapter3 = New SqlClient.SqlDataAdapter("select * from polines ", connect_string)
        
            data_adapter3.Fill(ds, "polines")
           

        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        Finally

        End Try
        Dim table1_qc As New DataRelation("table1_qc", ds.Tables("table1").Columns("polines"), _
                                           ds.Tables("qc").Columns("polines"))
        ds.Relations.Add(table1_qc)

        Dim table1_polines As New DataRelation("table1_polines", ds.Tables("table1").Columns("polinesID"), _
                                                ds.Tables("polines").Columns("polinesID"))
        ds.Relations.Add(table1_polines)

        Dim qc_polines As New DataRelation("qc_polines", ds.Tables("qc").Columns("polinesID"), _
                                                        ds.Tables("polines").Columns("polinesID"))
        ds.Relations.Add(qc_polines)


    End Sub

    Private Sub ButtonUpdate_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUpdate.Click

'update what changes in table1 (query) into qc table
        data_adapter2.Update(ds.Tables("qc"))
       
        MsgBox("updated")

     
    End Sub

Select allOpen in new window

I dont see where your declaring your dataset but I'm assuming your creating an Untyped dataset opposed to a Typed dataset. This can work but I would suggest a Typed Dataset so that your relationships are in place and you can add your action quieries directly to each data table.

Dont wanna jump ahead of ya yet but also there is a way to call one stored procedure and have it return multiple results sets. For example in you Stored Procedure you can call it once and have it fill all three of your tables, you just have to add table mappings to direct it to point which result set goes to which datatable.

Your calling an update method but I dont see where you created an update query associated with that particular data adapater

hi Tommy,

I declare the dataset outside the sub. I will swithch to store procedure later on as soon as I got this done.
The update method is built in function of the dataAdapter so it should work without the actual update query(I already tried it with gridview that only has data from 1 table). it's associated with the table in the select sql.

data_adapter2 = New SqlClient.SqlDataAdapter("select * from qc", _
            connect_string)

in this case data_adapter2 is associated with table qc so when call data_adataper2.update(ds.tables("qc")) it should update this table into database table qc.

However, I suspect that in my code there is still something missing or the way I set up the dataset is not correct.
do I use the query from the database for the datagridview or I should set up a  view based on the data tables in the dataset?

When I changed the data in the gridview, other table in the dataset that this data is associated with doeasn't change. that's problem.

If you follow me so far, please help.

thanks,

Calling an Select statement does not automatically add an update statement to your dataadapter. You either need to associate a commandbuilder object to that dataadapter or explicitly add the update statement to the dataadapters UpdateCommand. I suggest the latter, I've had problems in the past with the command builder when you make changes to your database, you then would need to update each of the typed datasets and redistribute the program.

Already added in the commandbuilder and it's still not updated into the database.
I'm sure there is a way to update the data table using the dataAdapter without having to loop through each row to make the update.

I have no problem when the datagridview only contain 1 table. This problem delay my projects a couple days already. If you can provide some details I really appreciate. If you can give me a simple example that would be nice. I search through the forum and although this is a common issue, I don't seem to find a concrete answer.

Thanks,

Eric

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim changes
        cmdBuilder = New SqlCommandBuilder(data_adapter2)
        changes = ds.GetChanges()

        If changes IsNot Nothing Then
            data_adapter2.Update(ds.Tables("qc"))

        End If

        'Me.binding_source2.DataSource = (ds.Tables("qc"))



        MsgBox("updated")


    End Sub

Select allOpen in new window

I found the solution. Loop through each row in the grid and update whatever the field I want to update to the appropriate table.

This is actually faster then adding tables to the dataset and create relationships between them. This make the code run slower

Thank you.

my solution is to loop through each row in the grid and update whatever cell to the appropriate table. I tried but not successful to use Tommy's comment about adding tables to the dataset and create relationships among them and then use each dataadapter to update the according table.

I may write the update statement as suggessted by Orcbighter in storeProcedure for data integrity.

thanks,