Delete syntax in vb.net not executing in MySQL

[sims6785]

Hi,
I am using vb.net 2008 with MySQL for my project.
I have a datagridview on my form which displays records from Mysql table on form load.
i want to delete the selected row in datagrid from table on 'row header mouse click' event.

The 2 different codes that i tried --- deletes the record from the datagrid for a moment but does not delete from mysql table & re-appears when closed & debugged again.

Need help for the code which affects mysql table for deleting the selected record from datagrid.

my code is as below.
Have tried using 2 different codes seperately but both returns the same result as mention above.

*Plz note that i have also tried using the "code (2)" that is commented below "code(1)"

*Plz point out the mistakes (if any) in the current code & help out wit additional coding that is required.

mports MySql.Data.MySqlClient
Public Class Form1
    Public Con As New MySqlConnection
    Public mycmd As New MySqlCommand
    Public mycmdbldr As MySqlCommandBuilder
    Dim Connstr, indt As String
    Dim cmdtext As String
    Dim Msadptr As MySqlDataAdapter
    Dim myTable As DataTable
    Dim dr As MySqlDataReader

    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dgvUsr2.RowHeadersVisible = True
        dgvUsr2.Show()

        Connstr = "Server=localhost;user id = root;password=root;database=hdams;"

        cmdtext = "Select usr_id AS 'User Id',User_type AS 'User Type',fname AS 'First Name',mname AS 'Middle Name',lname AS 'Last Name',party_name AS 'Company',Join_dt AS 'Join Date',username,paswrd from user"

        Try
            Msadptr = New MySqlDataAdapter(cmdtext, Connstr)
            myTable = New DataTable
            Msadptr.Fill(myTable)
            dgvUsr2.DataSource = myTable

        Catch ex As Exception

        End Try
    End Sub

Private Sub dgvUsr2_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgvUsr2.MouseClick
      
            'code(1)     
            dgvUsr2.Rows.Remove(dgvUsr2.CurrentRow)


            'code (2)_______________________________________
            'Dim Dr As DataRow
            'Dr = myTable.Rows.Item(dgvUsr2.CurrentRow.Index)
            'Dr.Delete()
            'myTable.AcceptChanges()

            'Con.Open()
            'Connstr = "Server=localhost;user id = root;password=root;database=hdams;"

            'cmdtext = "Select usr_id AS 'User Id',User_type AS 'User Type',fname AS 'First Name',mname AS 'Middle Name',lname AS 'Last Name',party_name AS 'Company',Join_dt AS 'Join Date',username,paswrd from user"

            'Msadptr = New MySqlDataAdapter(cmdtext, Connstr)
            'myTable = New DataTable
            'Msadptr.Fill(myTable)
            'dgvUsr2.DataSource = myTable
            'Con.Close()

        MessageBox.Show("A RECORD IS DELETED")

       
        Catch

        End Try

    End Sub
End Class

Any HELP would be just great!!

Thanks in advance

[lolafuertes]

On the MySqlDataAdapter you only defined the SELECT command. You need to fill in the Insert, Delete and Update commands of the data adapter.
Then on your code 2, after myTable.AcceptChanges() youmust add

Msadptr.Update

Hope this helps

[sims6785]

Hello lolafuertes,
Thanks so much for your input!

i understood what i was missing out on.
Will try what you suggested now & get back here soon.

But i have 2 questions--

* you said on my code 2, after myTable.AcceptChanges() i must add
msadpter.update

now with this do you mean i need not use code(1) at ol & code(2) with update query is enough??

*Also u specified that i need to fill in the Insert, Delete and Update commands of the data adapter.what i am confused about is do i really need INSERT query in this & if yes where do i put it? wont UPDATE do the job?

Plz clarify.

Thanks again.
Looking forward to your reply.

[sims6785]

Its a bit urgent!!
Plz Reply soon!

Thank you.

[lolafuertes]

Msadaptr is the responsible for interact between the MyTable in memory and the phisical table 'user' in MySql.

Even you do not insert or update any thing in this procedure, is always a good programming practice to fill all the commands: SELECT, INSERT, UPDATE and DELETE. This way, the adapter will always work.

Your first code removes a row from the datagridview. I think is not necessary.

Your second code removes the row from the table in memory. To update the phisical table, you MUST call the Update method of the data adapter, and it will analyze wich records had been inserted, deleted or updated in the table to propagate those changes to the MySQL table.

IMO, lines 44 to 53 are not necessary. Because the data is already in memory is not necessary to re-fill the table and re-bind the data grid view. It is enough ro refresh the data grid view with dgvUsr2.Refresh().

Hope this helps.

[sims6785]

Hello,
Thank you for so quick & this wonderful info.
but my code gives an error on the line

Msadptr.Update()

it says ---Overload resolution failed because no accessible 'update' accepts this number of arguments.

As you advised, i removed code 1 &
immediately after code 2 i added
Msadptr.update()
But it gives the above error.

i tried putting

Msadptr.update(myTable)
Though the error vanished but it dint work.

I think i have to write the update query there. but i dont kno what do i put in 'SET' and 'WHERE' clause there.
or may be something else which you are expecting i might know but i dont.

I am just blank on what to do now.
Can you please put up an example of the codes which should follow code(2)

My code shows up as below.

Private Sub dgvUsr2_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgvUsr2.MouseClick
 

Dim Dr As DataRow
Dr = myTable.Rows.Item(dgvUsr2.CurrentRow.Index)
Dr.Delete()
myTable.AcceptChanges()

msadptr.update()

dgvusr2.Refresh()

MessageBox.Show("A RECORD IS DELETED")

End Sub

Thanks
Awaiting your reply.

[lolafuertes]

Please, on the thread http://www.daniweb.com/forums/thread276434.html see the answer from adatapost for an example on how to define the missing commands.

An yes, you need to specify wich datatable is to be updated so Msadptr.update(myTable) should be OK after you add the missing commands to you data adapter.

Hope this helps