Vb.net Question

Check if the Row Checked in DataGridview exist in Database

Hello Everyone Good Morning,

I have a

DatagridView
with a
Checkboxcolumn
My Goal here is to check if the Checked Row is already exist in the Database but my code does not give me the output I want. Whats wrong?

Here it is

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As MySqlConnection = New MySqlConnection("datasource=localhost;database=operations;userid=root;password=admin1950;Convert Zero Datetime=True")
conn.Open()
Dim comm As MySqlCommand = New MySqlCommand()
comm.Connection = conn
Dim name As String
For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1 Step 1
If Me.DataGridView1.Rows(i).Cells(0).Value = True Then
name = Me.DataGridView1.Rows(i).Cells(1).Value
comm.CommandText = "select ElecAssigned,ScheduleDate from assignments where ElecAssigned = '" & name & "' and ScheduleDate = @ScheduleDate"
comm.Parameters.AddWithValue("@ScheduleDate", DateTimePicker1.Value)
comm.ExecuteNonQuery()
Dim reader As MySqlDataReader
reader = comm.ExecuteReader
If reader.HasRows Then
MsgBox("The persons that you Selected is also Scheduled Today.")
End If
End If
Next
End Sub


Here is the Scenario.

I save a Data in the Table
assignments
and it looks like this.

enter image description here

and here is the Scenario in the Program

enter image description here

I checked a row in the
Datagridview
and select a Date in
DateTimePicker
which is the same in the my table there must be Message that will show saying
The selected Person is also Scheduled Today


here is the code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connection As New MySqlConnection("datasource=localhost;database=operations;userid=root;password=admin1950;Convert Zero Datetime=True")
Dim command As New MySqlCommand("SELECT COUNT(*) FROM Assignments WHERE ElecAssigned = @ElecAssigned AND ScheduleDate = @ScheduleDate", connection)
Dim parameter = command.Parameters.Add("@ElecAssigned", MySqlDbType.VarChar, 50)

command.Parameters.AddWithValue("@ScheduleDate", DateTimePicker1.Value)

connection.Open()

For Each row As DataGridViewRow In Me.DataGridView1.Rows
If CBool(row.Cells(0).Value) Then
parameter.Value = CStr(row.Cells(1).Value)

If CInt(command.ExecuteScalar()) > 0 Then
'Match found.

Else
MsgBox("The Personnel(s) you Selected is also Scheduled Today")
'No match found.
'Save It
End If
End If
Next
End Sub


Regardless of how many I select the program will check if the persons selected is also checked.

TYSM for future help.

Answer

E.g.

Dim connection As New MySqlConnection("connection string here")
Dim command As New MySqlCommand("SELECT COUNT(*) FROM Assignments WHERE ElecAssigned = @ElecAssigned AND ScheduledDate = @ScheduledDate", connection)
Dim parameter = command.Parameters.Add("@ElecAssigned", MySqlDbType.VarChar, 50)

command.Parameters.AddWithValue("@ScheduledDate", DateTimePicker1.Value.Date) 'Add a word Date to check only the Date

connection.Open()

For Each row As DataGridViewRow In Me.DataGridView1.Rows
    If CBool(row.Cell(0).Value) Then
        parameter.Value = CStr(row.Cells(1).Value)

        If CInt(command.ExecuteScalar()) > 0 Then
            'Match found.
        Else
            'No match found.
        End If
    End If
Next

You might require a few small adjustments but that's basically the way to go about it if you want to know individually for each checked row.

Comments