Najuto
Najuto

Reputation: 33

Vb.net how to get Time data from access?

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

Answers (1)

Joel Coehoorn
Joel Coehoorn

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

Related Questions