We help IT Professionals succeed at work.

Removing records from a database via gridview using vb.net

DJ P
DJ P used Ask the Experts™
on
I have a windows form I created in visual studio 2017, In the form I have a datagrid view called DataGridView1 the form connects to a sql database. The table that populates that database is called tbl_Supervisors and contains the following fields. Empl_ID (unique Primary Key) Last_Name, First_Name and so on. I created a button with the following code (see below) to remove a selected record or records from the grid. However, this does not removed the records from the database. I'm assuming I need something like SQL.ExecQuery("Delete From tbl_Supervisors where EMPL_ID=
but being a semi-newbie I don't know the proper syntax and where to place that line or lines of code in relation to my code below.

Private Sub cmdDeleteUser_Click(sender As Object, e As EventArgs) Handles cmdDeleteUser.Click
        If DataGridView1.SelectedRows.Count > 0 Then
            For i As Integer = DataGridView1.SelectedRows.Count - 1 To 0 Step -1
                DataGridView1.Rows.RemoveAt(DataGridView1.SelectedRows(i).Index)

            Next
        Else
            MessageBox.Show("No rows to select")
        End If
    End Sub
End Class
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Quick and dirty, at it's simplest:

Form1.vb -
Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Runtime.CompilerServices

Public Class Form1
    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim table As New DataTable
        Using connection = New SqlConnection(My.Settings.ConnectionString)
            Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                connection.Open()
                adapter.Fill(table)
                DataGridView1.DataSource = table
            End Using
        End Using
    End Sub

    Private Sub OnClick(sender As Object, e As EventArgs) Handles act_Insert.Click, act_Delete.Click
        Dim btn = CType(sender, Button)
        If Equals(btn, act_Delete) Then
            If DataGridView1.SelectedRows.Count > 0 Then
                Using connection = New SqlConnection(My.Settings.ConnectionString)
                    connection.Open()

                    Dim text = $"DELETE FROM SUPERVISORS WHERE ID IN ({String.Join(", ", DataGridView1.SelectedRows.Cast(Of DataGridViewRow).Select(Function(r) CType(r.DataBoundItem, DataRowView).Row("ID")))})"
                    Using command = New SqlCommand(text, connection)
                        command.ExecuteNonQuery()
                    End Using

                    Dim table As New DataTable
                    Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                        adapter.Fill(table)
                        DataGridView1.DataSource = table
                    End Using
                End Using
            End If
        ElseIf Equals(btn, act_Insert) Then
            Dim index = 0
            Using connection = New SqlConnection(My.Settings.ConnectionString)
                connection.Open()

                Using command = New SqlCommand("SELECT MAX(ID) FROM SUPERVISORS", connection)
                    Dim temp = command.ExecuteScalar()
                    If TypeOf temp Is DBNull Then
                        index = 0
                    Else
                        index = CInt(temp)
                    End If
                End Using

                Dim supervisors = (From i In Enumerable.Range(index, 10) Select New With {.Name = $"Supervisor{i}"}).ConvertToDataTable("dbo.Supervisors")
                Using bulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, Nothing) With {.DestinationTableName = supervisors.TableName}
                    bulkCopy.ColumnMappings.Add("Name", "Name")
                    bulkCopy.WriteToServer(supervisors)
                End Using

                Dim table As New DataTable
                Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                    adapter.Fill(table)
                    DataGridView1.DataSource = table
                End Using
            End Using
        End If
    End Sub
End Class

Module Extensions
    <Extension>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T), Optional ByVal name As String = Nothing) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = If(Not String.IsNullOrWhiteSpace(name), New DataTable(name), New DataTable())

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function
End Module

Open in new window

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.SplitContainer1 = New System.Windows.Forms.SplitContainer()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView()
        Me.act_Insert = New System.Windows.Forms.Button()
        Me.act_Delete = New System.Windows.Forms.Button()
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SplitContainer1.Panel1.SuspendLayout()
        Me.SplitContainer1.Panel2.SuspendLayout()
        Me.SplitContainer1.SuspendLayout()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SplitContainer1
        '
        Me.SplitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer1.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer1.Name = "SplitContainer1"
        Me.SplitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer1.Panel1
        '
        Me.SplitContainer1.Panel1.Controls.Add(Me.DataGridView1)
        '
        'SplitContainer1.Panel2
        '
        Me.SplitContainer1.Panel2.Controls.Add(Me.act_Insert)
        Me.SplitContainer1.Panel2.Controls.Add(Me.act_Delete)
        Me.SplitContainer1.Size = New System.Drawing.Size(415, 202)
        Me.SplitContainer1.SplitterDistance = 165
        Me.SplitContainer1.TabIndex = 0
        '
        'DataGridView1
        '
        Me.DataGridView1.AllowUserToAddRows = False
        Me.DataGridView1.AllowUserToDeleteRows = False
        Me.DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill
        Me.DataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.DataGridView1.Location = New System.Drawing.Point(0, 0)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.RowHeadersVisible = False
        Me.DataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
        Me.DataGridView1.Size = New System.Drawing.Size(415, 165)
        Me.DataGridView1.TabIndex = 0
        '
        'act_Insert
        '
        Me.act_Insert.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.act_Insert.Location = New System.Drawing.Point(247, 3)
        Me.act_Insert.Name = "act_Insert"
        Me.act_Insert.Size = New System.Drawing.Size(79, 25)
        Me.act_Insert.TabIndex = 1
        Me.act_Insert.Text = "Insert"
        Me.act_Insert.UseVisualStyleBackColor = True
        '
        'act_Delete
        '
        Me.act_Delete.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.act_Delete.Location = New System.Drawing.Point(328, 3)
        Me.act_Delete.Name = "act_Delete"
        Me.act_Delete.Size = New System.Drawing.Size(79, 25)
        Me.act_Delete.TabIndex = 0
        Me.act_Delete.Text = "Delete"
        Me.act_Delete.UseVisualStyleBackColor = True
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(415, 202)
        Me.Controls.Add(Me.SplitContainer1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.SplitContainer1.Panel1.ResumeLayout(False)
        Me.SplitContainer1.Panel2.ResumeLayout(False)
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.SplitContainer1.ResumeLayout(False)
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    Friend WithEvents SplitContainer1 As SplitContainer
    Friend WithEvents DataGridView1 As DataGridView
    Friend WithEvents act_Insert As Button
    Friend WithEvents act_Delete As Button
End Class

Open in new window

-saige-

Author

Commented:
Thanks!