Hi, just wanted to know how to delete a record from datagrid, i tried the following code but it gave me an error in deleting records, I am using Mysql Database.
Following is the code:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        'recorddelete()
        Dim sql As String
        Dim db As New DBHelper
        Dim rows As Integer
        sql = "delete  from budget_details where ID =  & DataGridView1.CurrentRow.Cells(0).Value.ToString()& "
        rows = db.executeQuery(sql)
        If (rows > 0) Then
            MsgBox("Sucessfully Record Deleted")
        Else
            MsgBox("Error in Deleting Record")
        End If

    End Sub

I think problem is in this line, may be syntax problem:

sql = "delete from budget_details where ID = & DataGridView1.CurrentRow.Cells(0).Value.ToString()& "

Please help...........

Hi Sir, I tried your code, but its showing me the message "Error in deleting Record".
Any Idea......???
Thanx....

look at your code

Dim rows As Integer
sql = "delete  from budget_details where ID =  & DataGridView1.CurrentRow.Cells(0).Value.ToString()& "
rows = db.executeQuery(sql)

Where is the command builder where is the connection?

Where is db Defined?

where did you open or close the connection?

Hi Sir, I'm using following code:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.odbc
Imports System.Data.DataTable

Public Class ShowBudgetDetails

    Private Sub ShowBudgetDetails_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "SERVER=localhost;" & _
                "DATABASE=krishna;" & _
                "UID=root;" & _
                "PASSWORD=atmark;" & _
                "OPTION=3;"
        Dim myConnection As OdbcConnection = New OdbcConnection
        myConnection.ConnectionString = connString

        myConnection.Open()

        Dim queryPass As String = "SELECT * from budget_details"
        Dim da As New OdbcDataAdapter(queryPass, myConnection)
        Dim CmdB As New OdbcCommandBuilder(da)
        Dim ds As New DataSet
        da.Fill(ds, "budget_details")
        Dim dv As New DataView

        dv.Table = ds.Tables("budget_details")
        DataGridView1.DataSource = dv


    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        For Each RW As DataGridViewRow In DataGridView1.SelectedRows
            'Send the first cell value into textbox'
            TextBox1.Text = RW.Cells(0).Value.ToString
            TextBox2.Text = RW.Cells(1).Value.ToString
            TextBox3.Text = RW.Cells(2).Value.ToString
            TextBox4.Text = RW.Cells(3).Value.ToString
            TextBox5.Text = RW.Cells(4).Value.ToString
            TextBox6.Text = RW.Cells(5).Value.ToString
            TextBox7.Text = RW.Cells(6).Value.ToString

        Next
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String
        Dim rows As Integer
        Dim db As New DBHelper
        sql = "UPDATE budget_details SET Client_Name = '" & TextBox2.Text & "',Project_Name = '" & TextBox3.Text & "',Company_Name = '" & TextBox4.Text & "',Client_Investment = " & TextBox5.Text & ",Personal_Investment = " & TextBox6.Text & ",Overall_Budget = " & TextBox7.Text & " WHERE(Client_Id = " & TextBox1.Text & ")"


        rows = db.executeQuery(sql)
        If (rows > 0) Then
            MsgBox("Sucessfully Record Updated")
        Else
            MsgBox("Error in Updating Record")
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        populate()
    End Sub

    Private Sub populate()
        Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                    "SERVER=localhost;" & _
                    "DATABASE=krishna;" & _
                    "UID=root;" & _
                    "PASSWORD=atmark;" & _
                    "OPTION=3;"
        Dim myConnection As OdbcConnection = New OdbcConnection
        myConnection.ConnectionString = connString

        myConnection.Open()

        Dim queryPass As String = "SELECT * from budget_details"
        Dim da As New OdbcDataAdapter(queryPass, myConnection)
        Dim CmdB As New OdbcCommandBuilder(da)
        Dim ds As New DataSet
        da.Fill(ds, "budget_details")
        Dim dv As New DataView

        dv.Table = ds.Tables("budget_details")
        DataGridView1.DataSource = dv
    End Sub


    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        'recorddelete()
        Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                        "SERVER=localhost;" & _
                        "DATABASE=krishna;" & _
                        "UID=root;" & _
                        "PASSWORD=atmark;" & _
                        "OPTION=3;"
        Dim myConnection As OdbcConnection = New OdbcConnection
        myConnection.ConnectionString = connString

        myConnection.Open()
        'Dim CmdB As New OdbcCommandBuilder(da)
        Dim sql As String
        Dim db As New DBHelper
        Dim rows As Integer
        sql = "delete  from budget_details where ID = " & DataGridView1.CurrentRow.Cells(0).Value.ToString()
        rows = db.executeQuery(sql)
        If (rows > 0) Then
            MsgBox("Sucessfully Record Deleted")
        Else
            MsgBox("Error in Deleting Record")
        End If

    End Sub
End Class

db is an instance of the class DBHelper:

DBHelper definition is given below:

Imports System.Data.Odbc
Public Class DBHelper
    

    Dim MyConnection As New OdbcConnection(MyConString)
    Dim MyCommand As New OdbcCommand
    Dim adap As OdbcDataAdapter

    Public ds As New DataSet

    Private connectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=krishna;UID=root;PASSWORD=atmark;OPTION=3"
    Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=krishna;UID=root;PASSWORD=atmark;OPTION=3"

    Public Function executeQuerySelect(ByVal sql As String) As DataSet
        Try
            

            ds = New DataSet
            MyConnection = New OdbcConnection(MyConString)
            adap = New OdbcDataAdapter(sql, MyConnection)
            adap.SelectCommand.CommandText = sql
            adap.Fill(ds)


        Catch ex As Exception
            MsgBox("Error in Module executeQuerySelect  " & ex.Message.ToString)
            Debug.Write(ex.Message.ToString)
        End Try
        Return ds
    End Function
    Public Function executeQuery(ByVal sql As String) As Integer
        Try
            
            MyConnection = New OdbcConnection(MyConString)
            MyCommand = New OdbcCommand(sql, MyConnection)

            MyCommand.CommandType = CommandType.Text
            MyCommand.Connection.Open()
            Return MyCommand.ExecuteNonQuery()

        Catch ex As Exception
            Debug.WriteLine("Error in Module executeQuery  " & ex.Message.ToString)
        End Try

    End Function



   

    Public Sub connect()


        Dim MyConnection As New OdbcConnection(MyConString)
        MyConnection.Open()

        Dim MyCommand As New OdbcCommand
        MyCommand.Connection = MyConnection
    End Sub
End Class

I'm still unable to delete records from datagrid.

Why are your doing

Return MyCommand.ExecuteNonQuery()

Remove return and try

db.executeQuery(sql)

take out rows.

Hi Sir, I tried your code, but the problem still remains the same, I'm still getting the message "Error in deleting records".

Thanx..........

Which Line is the error coming up at?

Hi Sir......
Its not a syntax error actually, The form is executing fine,
Following is the code:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                        "SERVER=localhost;" & _
                        "DATABASE=krishna;" & _
                        "UID=root;" & _
                        "PASSWORD=atmark;" & _
                        "OPTION=3;"
        Dim myConnection As OdbcConnection = New OdbcConnection
        myConnection.ConnectionString = connString

        myConnection.Open()

        Dim sql As String
        Dim db As New DBHelper
        Dim rows As Integer
        sql = "delete  from budget_details where ID = " & DataGridView1.CurrentRow.Cells(0).Value.ToString()
        db.executeQuery(sql)
        If (rows > 0) Then
            MsgBox("Sucessfully Record Deleted")
        Else
            [B]MsgBox("Error in Deleting Record")[/B]
        End If

    End Sub

After clicking the Delete Button (which I have provided in that form), the Message box gets popped up, showing the message "Error in Deleting Records."
And its not deleting the records from the database (I'm using Mysql Database).

Any Idea.....????

Hi Sir......
Its not a syntax error actually, The form is executing fine,
Following is the code:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                        "SERVER=localhost;" & _
                        "DATABASE=krishna;" & _
                        "UID=root;" & _
                        "PASSWORD=atmark;" & _
                        "OPTION=3;"
        Dim myConnection As OdbcConnection = New OdbcConnection
        myConnection.ConnectionString = connString

        myConnection.Open()

        Dim sql As String
        Dim db As New DBHelper
        Dim rows As Integer
        sql = "delete  from budget_details where ID = " & DataGridView1.CurrentRow.Cells(0).Value.ToString()
        db.executeQuery(sql)
        If (rows > 0) Then
            MsgBox("Sucessfully Record Deleted")
        Else
            [B]MsgBox("Error in Deleting Record")[/B]
        End If

    End Sub

After clicking the Delete Button (which I have provided in that form), the Message box gets popped up, showing the message "Error in Deleting Records."
And its not deleting the records from the database (I'm using Mysql Database).

Any Idea.....????

Sorry,
I think it got posted twice....

Well did you check in the database if the record is deleted?
Rows is not defined so it will be zero by default so it will always give Error

Hi Sir,
Ofcourse I checked my database, Its showing all my records(I mean not a single row gets deleted when I click the delete button).

Try

sql = "delete  from budget_details where ID = " & DataGridView1.CurrentRow.Cells(0).Value.ToString()
MessageBox.Show(sql)
db.executeQuery(sql)

Just to see what DataGridView1.CurrentRow.Cells(0).Value.ToString() is

And see if ID holds such value at all.

Thanx Sir, My Problem is Solved.
Thanx for your time and patience.

Good to know, could you tell me what the problem was?

As others may have similar problems and may find this post helps them

this code is running :)
**sql = "delete from budget_details where ID = '" & DataGridView1.CurrentRow.Cells(0).Value.ToString()& "' "
**

Dim table As DataTable = dataSet1.Table(0)

If dataGridView1.SelectedRows.Count > 0 Then
Dim item As DataGridViewRow
For Each item In dataGridView1.SelectedRows
table.Rows.Find(CType(item.Cells(0).Value, Integer)).Delete()
Next
End If

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.