dataset will not update

[discovery-power]

Hi All,

I have had some reall problems with this. I have a form with a datagridview, now I can add new records and the dataset will update fine, but it wont update when I want to delete a record.

Please see below my code.

Imports System.Data.SqlClient
Public Class Form2
    Dim da As SqlDataAdapter
    Dim conn As SqlConnection

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        conn = New SqlConnection("Data Source=JOHN-ELLIS-PC;Initial Catalog=mtrmanager;Integrated Security=True")
        conn.Open()
        Me.UsersTableAdapter.Fill(Me.MtrmanagerDataSet.Users)

    End Sub
       
    Private Sub UsersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
            da.SelectCommand = myBuilder.GetDeleteCommand
            da.UpdateCommand = myBuilder.GetUpdateCommand
            da.Update(Me.MtrmanagerDataSet)
            Me.Validate()
            Me.UsersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.MtrmanagerDataSet)

        Catch ex As Exception
            MsgBox("Updated Failed" & ex.Message)
        End Try

    End Sub

    Private Sub Form2_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        conn.Close()
    End Sub

End Class

I dont actually get any design or runtime errors, the dataset just will not update. I have been into the configuration screen for the dataset and made sure that all the correct check boxes are selected.

Not sure what else to do? has anyone got any idea's.

Thanks

John

[lolafuertes]

Please, verify the line 18 of your code.
Maybe you need to change to

da.DeleteCommand = myBuilder.GetDeleteCommand

Hope this helps

[discovery-power]

Please, verify the line 18 of your code.
Maybe you need to change to

da.DeleteCommand = myBuilder.GetDeleteCommand

Hope this helps

Hi,

Thanks for the help, it still doesnt update though, do you think it could be something to do with my update commands??

Thanks

John

[lolafuertes]

Please, can you be so kind to post the resulting commands?

[discovery-power]

Please, can you be so kind to post the resulting commands?

Hi,

Ok, I sorted this out finally. I decided to scrap the drag and drop idea for the dataset and do it all through code (which as strange as it seems, was much easier).

I also gave the table I am using a primary key.

I added a datagridview control and 2 button controls to my win form, I use 1 button to fill the datagridview control with data from the selected table, I also use 1 button to update the dataset.

See my working code below.

Imports System.Data.SqlClient
Public Class Form2

    Dim conn As SqlConnection
    Dim Users As SqlDataAdapter
    Dim dsUsers As DataSet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Users.Update(dsUsers, "Users")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        conn = New SqlConnection("Data Source=JOHN-ELLIS-PC;Initial Catalog=Usersdb;Integrated Security=True")
        dsUsers = New DataSet
        Users = New SqlDataAdapter("SELECT UserID,Name,Password,Phone FROM Users", conn)
        Dim cmdBldr As SqlCommandBuilder = New SqlCommandBuilder(Users)
        Users.Fill(dsUsers, "Users")
        DataGridView1.DataSource = dsUsers.Tables("Users")

    End Sub
End Class

Thanks for the help on this, im just glad its sorted now :)

Happy new year all.

John