Link to home
Start Free TrialLog in
Avatar of SteveB2175
SteveB2175

asked on

How to update multiple tables in the same Dataset ??

I have a form with 2 DataGridViews.  The first DataGridView shows user names, the 2nd dgv shows security permissions for the name that was selected in the first dgv.  All the data is from a single SQL Server table.

I bound each dgv to a dataset via code (no drag & drop). I created a single dataset, with 2 tables (maybe this was not wise ?).

My problem is that I can't find a way to update the SQL server table with a single button.  I have created 2 "update" buttons with the following code:

 Private Sub btnUpdateUserInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateUserInfo.Click
        If DS.HasChanges Then
            Dim UpdateCount As Integer = da.Update(DS.Tables("UserInfo"))
            MessageBox.Show(UpdateCount & " Records Updated")
        Else
            MessageBox.Show("Nothing to Update")
        End If
    End Sub

    Private Sub btnUpdateUsers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateUsers.Click
        If DS.HasChanges Then
            Dim UpdateCount As Integer = da.Update(DS.Tables("UserName"))
            MessageBox.Show(UpdateCount & " Records Updated")
        Else
            MessageBox.Show("Nothing to Update")
        End If
    End Sub

There are several problems here:

1) I would prefer that the user only have to click a single button to make any updates necessary.
2) If the user clicks the wrong button (trying to update the wrong DS table) an error is returned
3) I am only aware of the test "if ds.haschanges..." which tells me that there are changes, but not which table the changes exist in.

So, are there answers to my questions, should I create a separate dataset for each dgv, or am I approaching this whole thing the wrong way ??

Thanks !!
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveB2175
SteveB2175

ASKER

Wow, both of you have provided very helpful info.

I'll split the points, and thanks again !!
Whoa now, looks like I accepted this one too fast !!

I get the same problem with this as when I had two separate buttons.  If no changes was made to the "username" table I get the following error
 "Value cannot be null.  Parameter name: dataTable"

What now ?

 If DS.HasChanges Then
            Dim UpdateNameCount As Integer = da.Update(DS.Tables("UserName")) < PROBLEM HERE
            Dim UpdateInfoCount As Integer = da.Update(DS.Tables("UserInfo"))
            MessageBox.Show(UpdateNameCount & " UserName Records Updated" & Environment.NewLine & _
                                       UpdateInfoCount & " UserInfo Records Updated")
        Else
            MessageBox.Show("Nothing to Update")
        End If
What that error message is telling you is that there is no datatable at the reference DS.Tables("UserName").  So when and how do you create/fill the datatable that you expect to find with that reference?

Roger
OK, I got it.

I have a sub (show below) that loads all the user names into a dgv:

        Me.Cursor = Cursors.WaitCursor
         Dim SqlLine As String
        SqlLine = "SELECT UserName FROM Security ORDER BY UserName "

        Dim ConnectionString As String = blah blah blah
        Dim Connection As New SqlClient.SqlConnection(ConnectionString)

         Dim cmd As New SqlClient.SqlCommand(SqlLine, TediaConnection)
        da.SelectCommand = cmd
        Ds = New DataSet  <<<<<< PROBLEM WAS HERE
        da.Fill(DS, "UserName")
        Dim cb As New SqlClient.SqlCommandBuilder(da)
        cmd.Connection.Close()
        dgvUsers.DataSource = DS.Tables("UserName")

         Me.Cursor = Cursors.Default

I also have a sub that is almost exactly the same that loads the permissions for a selected user into a second dgv.

The problem as that each sub re-set the dataset (DS = New Dataset) I moved the DS = new dataset to the load event for the form and no more problems !!

So, how can I give you some points for your help ??  I am VERY grateful for all the help I receive on this board, and I want to thank those that help me.
No need for further points so far as I'm concerned.  Regard it as part of the "after sales service" ;-)

Roger