update dataset with multiple table from 1 single query to database

HI,

I'm new and this is what make me come to this forum here. I try to update a dataset, which contains data from multiple tables join together. I read that I will not be able to update this unless I create different data adapters for different table. I also need to add all related table to the dataset.

Question: When I make a change to my gridview, how do I update that change to the right table in the dataset and from there update it to the database?

I'm using visual studio 8 and work with sql server.

Any help is appreciated.

Thanks,

Eric

strSql = "select qc.polinesID, products.product_id, polines.item, description, polines.quantity, customerrequest, qcNote, qc.droptest, dropship, "
        strSql &= "cusresponse from qc inner join polines on qc.polinesId=polines.polinesID join po on "
        strSql &= "polines.poid=po.id left outer join products on polines.item=products.item where po.id= " & posql.poID

Open in new window

ericV2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OrcbighterCommented:
Simplest option would be to put your query into a stored procedure and have your update function call the stored procedure. In that way, you have a single resultset containing all the data you want (since you designed the stored proc) which can then be used to populate whatever control feature you wish.
An added benefit is that a stored proc is faster than dynamnic sql.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ericV2000Author Commented:
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,
OrcbighterCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Tommy11bCommented:
A single update statement will update a single table. What you can do is create a seperate table for each of the database tables within your dataset and create relation keys betweens these tables. A DataAdapter can then keep track of each of the tables seperately and update the needed records.
OrcbighterCommented:
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.
ericV2000Author Commented:
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.

ericV2000Author Commented:
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

Open in new window

Tommy11bCommented:
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
ericV2000Author Commented:
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,



Tommy11bCommented:
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.


ericV2000Author Commented:
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

Open in new window

ericV2000Author Commented:
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.
ericV2000Author Commented:
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,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.