Vb.net how to get Time data from access?

Question

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

So what's the actual problem? Is an exception being thrown? If so, try looking at the exception rather than ignoring it and displaying your own error message that may or may not have anything to do with the issue. — jmcilhinney
– jmcilhinney, Commented May 14, 2018 at 1:09
What is the data type of your HoradeComienzodeTrabajo and HoradeConclusiondeTrabajo columns? — jmcilhinney
– jmcilhinney, Commented May 14, 2018 at 1:13
Then you should not be assigning the values directly to String variables for one thing. You should be assigning them to DateTime variables in your VB code first and then, at the very least, you can look at the values in their native form and then go form there. If those DateTime values have their time portion set to zero then there's no time values in the database to begin with, so there's nothing you can do in your retrieval code to fix that. In that case, it would be your saving code that's the issue. — jmcilhinney
– jmcilhinney, Commented May 14, 2018 at 1:17

Joel Coehoorn · Accepted Answer · 2018-05-14 01:40:42Z

1

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.

edited May 14, 2018 at 1:40

answered May 14, 2018 at 1:27

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Najuto Over a year ago

I used this solution but I get this Comienzo Turno = 12/30/1899

Najuto Over a year ago

What I want to display is time and not date

Joel Coehoorn Over a year ago

Looking again, it means the returned record had NULL for that field.

Joel Coehoorn Over a year ago

But since you want time, I updated the code to show it, if it's available.

Najuto Over a year ago

I just need to change the format to display in 12 hour format.

|

Collectives™ on Stack Overflow

Vb.net how to get Time data from access?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related