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