Member Avatar for sackymatt

Hi all

I have a situation, i have a form that has a datagridview, on form load, it selects the data from sql db, now what i want is when i right click on one row, i should be able to delete that row and it should also get deleted from the database.

Any help please.

Thanks,
Sacky

  • 4 Contributors
  • 8 Replies
  • 2K Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by Pgmer
Member Avatar for sackymatt

Hi all

I have a situation, i have a form that has a datagridview, on form load, it selects the data from sql db, now what i want is when i right click on one row, i should be able to delete that row and it should also get deleted from the database.

Any help please.

Thanks,
Sacky

Iam using vb.net 08

Member Avatar for Phasma

The the following will explain the Right-Click DataGridView part be sure to read both replies;

http://bytes.com/topic/visual-basic-net/answers/425607-datagridview-force-row-selection-right-click

Then in your Right-Click event you can:

For Each row As DataGridViewRow In DataGridView1.SelectedRows
            DataGridView1.Rows.Remove(row)
     Next

If your DataGridView is bound to a BindingSource which in turn has it's DataSource set to a DataTable of a DataSet filled by a SQLDataAdapter with CommandBuilder, calling the DataAdapter's Update Method will reflect the changes ino your DataGridView back to your SQL DataBase Table.

Edited by Phasma because: n/a
Member Avatar for sackymatt

Hi Phasma

Thanks for the reply. My datagridview fills with a select statement, there is no datasource abind to it. And i donot just want to right only, i want once i right click, i should be able to delete that row and simutenously delete from the db aswell.

thanks

Member Avatar for Pgmer

For this u need to write procedure to delete row from db or SQlL statement by passing the condition to delete. And load the new set of data and bind back to grid.

Member Avatar for Phasma

I thought you wanted to simultaneously delete the row and the record in the database with a minimal of coding. That’s why I suggested the BindingSource, Dataset, DataAdapter scenario. Yes this is OLEDB not SQL but they work pretty much the same. The following is the guts of it I have attached the entire code if you wish to view it. Tested on XP Pro SP3 and VB.net 2008 Framework 3.5

'Global Variables

Dim wrkDir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
  Dim da As New OleDbDataAdapter
  Dim ds As New DataSet
  Dim bs As New BindingSource
  Dim edit As Boolean
  'Coupon_Tracker.mdb
  Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & wrkDir & "\Coupon_Tracker.mdb")
Private Sub Form1_Load(ByVal sender As Object, _
   ByVal e As System.EventArgs) Handles Me.Load
    'Set the dataGridView's DataSource
    dgv1.DataSource = bs
  End Sub

Private Sub btnLoadAll_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnLoadAll.Click
    ds.Tables.Clear()

    Dim sql As String = "SELECT * FROM(Extensions);"

    Dim cmd As New OleDbCommand(sql, conn)

    da.SelectCommand = cmd

   'Have to have this to be able to do Updates
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.Fill(ds, "EXTENSIONS")
    bs.DataSource = ds.Tables(0)

  End Sub

Private Sub dgv1_CellMouseClick(ByVal sender As Object, _
                                           ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
                                           Handles dgv1.CellMouseClick
    Dim res As DialogResult
    If e.Button = Windows.Forms.MouseButtons.Right AndAlso e.RowIndex >= 0 Then
      dgv1.Rows(e.RowIndex).Selected = True
      res = MessageBox.Show("Are you sure you want to delete Row: " & e.RowIndex & "?", _
                            "REMOVE ROW?", MessageBoxButtons.YesNo)

    End If


    If res = Windows.Forms.DialogResult.Yes Then
      For Each row As DataGridViewRow In dgv1.SelectedRows
        dgv1.Rows.Remove(row)
        edit = True
        Call EditDataBase()
      Next
    End If

  End Sub

  Private Sub EditDataBase()

    If edit Then
      da.Update(ds, "EXTENSIONS")
      edit = False
    End If

  End Sub
Imports System.Data
Imports System.Data.OleDb
'I have 2 tables one with the original data and one I use for the demonstration
'You will need to change the connection string to your database and import sql. 
'I know you are using SQL DB but the methods are pretty much identical
'Clicking the LoadAll button will put all records into the datagridview
'right clicking on a row will bring up the dialog asking if you want to delete it
'When the user clicks yes the row is removed and then'
'DataAdapters update method is called and the record is deleted from the database
'try the code and see if it isn't a little easier 
'to let the DataAdapter do all your work for you
Public Class Form1
  Dim wrkDir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
  Dim da As New OleDbDataAdapter
  Dim ds As New DataSet
  Dim bs As New BindingSource
  Dim edit As Boolean
  'Coupon_Tracker.mdb
  Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & wrkDir & "\Coupon_Tracker.mdb")


  ''' <summary>
  ''' Update The DataBase
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Call EditDataBase()

  End Sub

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'reset the msaccess datatable to original record status
    Call InitializeDataTable()


    'Set the dataGridView's DataSource
    dgv1.DataSource = bs
  End Sub

  ''' <summary>
  ''' ToolStripButton
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub tsBtnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsBtnLoad.Click
    ds.Tables.Clear()

    If tsText.Text <> "" Then 'ToolStripTextBox
      Dim sql As String = "SELECT EXTENSIONS.EXTENSION, EXTENSIONS.DESCRIPTION, EXTENSIONS.EXECUTABLE_PATH " & _
                          "FROM(Extensions)" & _
                          "WHERE (((EXTENSIONS.EXTENSION)='" & tsText.Text & "'));"


      Dim cmd As New OleDbCommand(sql, conn) 'Used as the Select Command

      da.SelectCommand = cmd

      'Have to have this to be able to do Updates
      Dim cmdBuilder As New OleDbCommandBuilder(da)

      'Fille the dataSet
      da.Fill(ds, "EXTENSIONS")

      'Set tyhe BindingSource's DataSource
      bs.DataSource = ds.Tables(0)

    Else
      'Tell user they need to enter a search criteria
      Exit Sub
    End If

  End Sub

  Private Sub dgv1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv1.CellEndEdit
    edit = True
  End Sub

  ''' <summary>
  ''' Loads all the records to the DataGridView
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnLoadAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadAll.Click
    ds.Tables.Clear()

    Dim sql As String = "SELECT * FROM(Extensions);"

    Dim cmd As New OleDbCommand(sql, conn)

    da.SelectCommand = cmd
    'Have to have this to be able to do Updates
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.Fill(ds, "EXTENSIONS")
    bs.DataSource = ds.Tables(0)

  End Sub

  ''' <summary>
  ''' Right Click DataGridView to Select Rows
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub dgv1_CellMouseClick(ByVal sender As Object, _
                                           ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
                                           Handles dgv1.CellMouseClick
    Dim res As DialogResult
    If e.Button = Windows.Forms.MouseButtons.Right AndAlso e.RowIndex >= 0 Then
      dgv1.Rows(e.RowIndex).Selected = True
      res = MessageBox.Show("Are you sure you want to delete Row: " & e.RowIndex & "?", _
                            "REMOVE ROW?", MessageBoxButtons.YesNo)

    End If


    If res = Windows.Forms.DialogResult.Yes Then
      For Each row As DataGridViewRow In dgv1.SelectedRows
        dgv1.Rows.Remove(row)
        edit = True
        Call EditDataBase()
      Next
    End If

  End Sub

  Private Sub EditDataBase()

    If edit Then
      da.Update(ds, "EXTENSIONS")
      edit = False
    End If

  End Sub

  ''' <summary>
  ''' Deletes all records and copies the original 
  ''' records to the EXTENSIONS table
  ''' </summary>
  ''' <remarks></remarks>
  Private Sub InitializeDataTable()

    Dim copySQL As String = "INSERT INTO EXTENSIONS SELECT * FROM EXTENSIONS1;"
    Dim delSQL As String = "DELETE * FROM EXTENSIONS"

    Dim cmd As New OleDbCommand()

    Try
      cmd.CommandType = CommandType.Text
      cmd.CommandText = delSQL
      cmd.Connection = conn

      conn.Open()
      cmd.ExecuteNonQuery()
    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      conn.Close()
    End Try

    Try
      cmd.CommandType = CommandType.Text
      cmd.CommandText = copySQL
      cmd.Connection = conn

      conn.Open()
      cmd.ExecuteNonQuery()
    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      conn.Close()
    End Try

  End Sub

End Class
Member Avatar for sackymatt

Thanks guys for your assistance, it really helped me to arrive to the answer and i got it right.

Thanks

Member Avatar for Pgmer

Cool :) Then plz dont forget to mark the thread as solved.

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.