Deleting datagridview row without deleting duplicates

user3562155

I have a datagridview with data, one column (EmployeeID) has the same value because it can occur more than once, I have a delete button which when clicked it deletes all the rows which has the EmployeeID 1 lets say. Is it possible to make it work so that it only deletes the row that I have selected other than it deleting all rows with a particular value.

I have the code here:

Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
        'check for the selected item in the list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intemployeeno As Integer = Me.DataGridView1.SelectedRows(0).Cells("EmployeeID").Value
                'open connection if not already opened
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If
                'delete data
                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = cnn
                'cmd.CommandText = "DELETE * FROM attendance " & intemployeeno
                cmd.CommandText = "DELETE FROM attendance WHERE employeeID=" & intemployeeno
                cmd.ExecuteNonQuery()
                'refresh data
                Me.refreshdata()
                'Display pop up alert before deleting row of data
            Else
                MessageBox.Show("Select 1 row before you hit Delete")
                'close connection
                cnn.Close()

            End If
        End If
    End Sub

Thanks

Next part of code

Public Class Form4

Dim cnn As New OleDb.OleDbConnection

'open connection to database

Private Sub refreshdata()
    If Not cnn.State = ConnectionState.Open Then
        'open connection
        cnn.Open()
    End If
    'create new data and insert the data values
    Dim da As New OleDb.OleDbDataAdapter("SELECT TestNumber as [TestNumber], EmployeeID as [EmployeeID], " & _
                                    "firstname as [FirstName], lastname as [LastName], date as [Date], holiday as [Holiday], halfday as [HalfDay], other as [Other], sick as [Sick], unpaidholiday as [UnpaidHoliday], daystaken as [DaysTaken], datefrom as [DateFrom], dateto as [DateTo] " & _
                                    "FROM Attendance ORDER BY FirstName", cnn)
    Dim dt As New DataTable
    'fill data into datatable
    da.Fill(dt)
    'offer data to be placed in datagridview
    Me.DataGridView1.DataSource = dt
    'close connection
    cnn.Close()
End Sub
Douglas Barbin

Your datagridview needs a column with the attendance table's primary key. It can be a hidden (non-visible) column if you don't want the user to see it.

Without a primary key (or a unique constraint, but let's use the PK for simplicity's sake), there is no way for the database to ensure that you are referring to a single, specific row.

Then, you need to change the following lines in your VB code:

Dim myPrimaryKey As Integer = Me.DataGridView1.SelectedRows(0).Cells("PrimaryKeyColumnName").Value

Change your delete statement as well:

cmd.CommandText = "DELETE FROM attendance WHERE AttendancePK=" & myPrimaryKey

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related