Reputation: 33
I want to get the work hour schedule for employees to show up in the labels of a form. The input will be = IDdeEmpleado "Employee ID" and Fecha "Date" and it should get Horadecomienzodetrabajo "Start"and horadeConclusiondeTrabajo "Finish".
This is the code I have so far 'the query:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Horario] WHERE [IddeEmpleado] = @Id AND [Fecha] = @Fecha ", myConnection)
cmd.Parameters.AddWithValue("@Id", txtEmpID.Text)
cmd.Parameters.AddWithValue("@Fecha", SqlDbType.Date).Value = lblDate.Text
Dim dr As OleDbDataReader = cmd.ExecuteReader
Dim HoradeEntrada As String = ""
Dim HoradeSalida As String = ""
'if found:
Try
HoradeEntrada = dr("HoradeComienzodeTrabajo")
HoradeSalida = dr("HoradeConclusiondeTrabajo")
lblComienzo.Text = HoradeEntrada
lblTermina.Text = HoradeSalida
Catch
MsgBox("Sorry,No Hay Horario para el ID Entrado", MsgBoxStyle.OkOnly, "Invalid ID")
End Try
Upvotes: 0
Views: 53
Reputation: 415600
There are several important changes in here:
Using myConnection As New OleDbConnection("connection string here"), _
cmd As New OleDbCommand("SELECT * FROM [Horario] WHERE [IddeEmpleado] = ? AND [Fecha] = ?", myConnection)
cmd.Parameters.AddWithValue("@Id", txtEmpID.Text)
cmd.Parameters.Add("@Fecha", OleDbType.Date).Value = CDate(lblDate.Text)
myConnection.Open()
Using dr As OleDbDataReader = cmd.ExecuteReader()
If dr.Read() Then
lblComienzo.Text = CDate(dr("HoradeComienzodeTrabajo")).ToString("HH:mm:ss.fff")
lblTermina.Text = CDate(dr("HoradeConclusiondeTrabajo")).ToString("HH:mm:ss.fff")
Else
MsgBox("Sorry,No Hay Horario para el ID Entrado", MsgBoxStyle.OkOnly, "Invalid ID")
End If
dr.Close()
End Using
End Using
Most of this stands alone, but I do need to explain one thing. Do NOT try to re-use the same connection object (like myConnection
) throughout your app. ADO.Net is built with a feature called "Connection Pooling" in mind, and all the major providers (including OleDb) support this. With Connection Pooling, re-using the same connection object works against you. It's likely to make your app slower, because database access will become a choke point and interfere with the built-in pooling.
Instead, you really should create a new connection object for most calls into the database.
Also note the change to SQL string. OleDb uses ?
as the parameter placeholder, rather than named @
parameters, and matches the ADO.Net parameter object to the placeholder in the SQL string based on position in the string and positions in the parameters collection on the OleDbCommand.
Upvotes: 1