Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If sConnection.State = ConnectionState.Closed Then
sConnection.ConnectionString = "SERVER = localhost; USERID=root; PASSWORD=123456; DATABASE=attc;"
sConnection.Open()
End If
Dim intNumRows As Integer
Dim sqlQuery As String = "SELECT COUNT(*) FROM tbl_time WHERE id = '" & txtid.Text & "' and date = '" & Format(Now, "dd-MM-yyyy") & "'"
Dim objCmd = New MySqlCommand(sqlQuery, sConnection)
intNumRows = objCmd.ExecuteScalar()
If intNumRows > 0 Then
MsgBox("รหัสนักศึกษาซ้ำค่ะ.")
Else
sqlQuery = "INSERT INTO tbl_time(id ,date, time) VALUES('" & txtid.Text & "','" & Format(Now, "dd-MM-yyyy") & "','" & Format(Now, "h:mm:ss") & " ')"
Dim sqlCommand As New MySqlCommand
With sqlCommand
.CommandText = sqlQuery
.Connection = sConnection
.ExecuteNonQuery()
End With
MsgBox("คุณได้บันทึกเวลาแล้วค่ะ")
txtid.Clear()
Refresh()
Form1.Show()
Hide()
End If
SELECT tbl_people.ID_Std , tbl_people.FullName FROM tbl_time
LEFT JOIN tbl_people ON tbl_people.ID_Std = tbl_time.ID_Std
WHERE tbl_time.CheckInTime = '5/12/2556'
SELECT tbl_people.ID_Std , tbl_people.FullName FROM tbl_people
LEFT JOIN tbl_time ON tbl_time.ID_Std = tbl_people.ID_Std AND tbl_time.CheckInTime = '5/12/2556'
WHERE tbl_time.ID_Time IS NULL
หรือ
Code (SQL)
SELECT tbl_people.ID_Std , tbl_people.FullName FROM tbl_people
WHERE tbl_people.ID_Std NOT IN (
SELECT tbl_people.ID_Std FROM tbl_time
LEFT JOIN tbl_people ON tbl_people.ID_Std = tbl_time.ID_Std
WHERE tbl_time.CheckInTime = '5/12/2556'
)
Load balance : Server 01
