How to delete records from datagrid

Question

Albert Pinto 0 Newbie Poster

14 Years Ago

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

vb.net

4 Contributors
17 Replies
2K Views
3 Years Discussion Span
Latest Post 11 Years Ago Latest Post by KimberGariando

finito 46 Nearly a Posting Virtuoso

14 Years Ago

Welcome to the forums, Please introduce yourself at http://www.daniweb.com/forums/forum165.html.

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

finito 46 Nearly a Posting Virtuoso

14 Years Ago

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?

finito 46 Nearly a Posting Virtuoso

14 Years Ago

Why are your doing

Return MyCommand.ExecuteNonQuery()

Remove return and try

db.executeQuery(sql)

take out rows.

finito 46 Nearly a Posting Virtuoso

14 Years Ago

Which Line is the error coming up at?

finito 46 Nearly a Posting Virtuoso

14 Years Ago

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

Reply to this topic

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.

Albert Pinto 0 Newbie Poster · Answer 1 · 2010-06-05T15:29:54+00:00

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

Albert Pinto 0 Newbie Poster · Answer 2 · 2010-06-05T17:57:38+00:00

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.

Albert Pinto 0 Newbie Poster · Answer 3 · 2010-06-07T10:35:08+00:00

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

Thanx..........

Albert Pinto 0 Newbie Poster · Answer 4 · 2010-06-07T13:56:25+00:00

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.....????

Albert Pinto 0 Newbie Poster · Answer 5 · 2010-06-07T13:56:44+00:00

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.....????

Albert Pinto 0 Newbie Poster · Answer 6 · 2010-06-07T13:59:39+00:00

Albert Pinto 0 Newbie Poster

14 Years Ago

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

Albert Pinto 0 Newbie Poster · Answer 7 · 2010-06-07T18:21:29+00:00

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).

finito 46 Nearly a Posting Virtuoso · Answer 8 · 2010-06-07T20:56:15+00:00

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.

Albert Pinto 0 Newbie Poster · Answer 9 · 2010-06-08T21:07:15+00:00

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

finito 46 Nearly a Posting Virtuoso · Answer 10 · 2010-06-08T22:58:10+00:00

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

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

sathishkumarcg 0 Newbie Poster · Answer 11 · 2013-09-26T19:38:27+00:00

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

KimberGariando 0 Newbie Poster · Answer 12 · 2013-11-05T16:52:25+00:00

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