vb.net update access record with 2 Where clauses

Question

I am getting a syntax error while trying to use 2 Where values from my database to make an update to the "IddeEmpleado" Employee ID in which "Fecha" which means Date. The employee already clocked in, and I want to update when it clock outs in the same line I got the clock in hour.

 Private Sub btnClockout_Click(sender As Object, e As EventArgs) Handles btnClockout.Click
    Dim ds As New DataSet()

    Dim connString As String


    Dim myConnection As OleDbConnection = New OleDbConnection
    connString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = ..\Bases de Datos\Horario.accdb"
    myConnection.ConnectionString = connString

    myConnection.Open()

    Dim cmd As OleDbCommand = New OleDbCommand("select * from Empleado where IdDeEmpleado like '%" + txtEmpId.Text + "%'", myConnection)
    Dim Adpt As New OleDbDataAdapter(cmd)

    If (Adpt.Fill(ds, "Empleado")) Then

        Dim con2 As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = ..\Bases de Datos\Horario.accdb")



        Try
            Dim Finalizoturno As Object
            Finalizoturno = DateTimePicker1.Text

            con2.Open()

            Dim updateQuery As String = "Update EmpleadoHorario Set FinalizoTurno= '" & DateTimePicker1.Text & "' Where Fecha= '" & datePicker.Text & " ' AND IddeEmpleado = '" & txtEmpId.Text & "'"

            Dim cmd2 As New OleDbCommand(updateQuery, con2)

            cmd2.ExecuteNonQuery()





            MessageBox.Show("Salida de Turno Aceptada")

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            con2.Dispose()

            End Try
            con2.Close()

        Else

            MessageBox.Show("ID de Empleado no encontrado")




        End If
        myConnection.Close()

End Sub

Answer 1

First and forever, turn on Option Strict in Project Properties, Compile tab.

2. You are creating objects you don't need. A DataSet and a DataAdapter are not necessay to just get the count.

3. You can initialize your connection in one line by passing the connection string to the constructor.

4. Don't open you connection until right before you need it.

5. A Primary Key is never duplicated so it makes no sense to use a Like clause in your query. You are just checking if the ID exists. Don't retrieve data when all you need is a count.

6. Use parameters. Acces cares not what the name of the parameter is, just the order; so make sure you add the parameters in the same order they appear in the query.

7. It is rare to need to declare a variable as Object. Finalizoturno is a Date so declate it as such. The Value property of a DateTimePicker returns a date, the Text property returns a string.

`

Private Sub btnClockout_Click(sender As Object, e As EventArgs) Handles btnClockout.Click
    Dim myConnection As New OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = ..\Bases de Datos\Horario.accdb")
    Dim updateQuery As String = "Update EmpleadoHorario Set FinalizoTurno= ? Where Fecha= ? AND IddeEmpleado = ?;"
    Dim cmd As New OleDbCommand(updateQuery, myConnection)
    Try
        cmd.Parameters.Add("Fecha1", OleDbType.Date).Value = DateTimePicker1.Value
        cmd.Parameters.Add("Fecha2", OleDbType.Date).Value = datePicker.Value
        cmd.Parameters.Add("ID", OleDbType.Integer).Value = CInt(txtEmpId.Text)
        myConnection.Open()
        Dim retVal As Integer = cmd.ExecuteNonQuery()
        myConnection.Close()
        If retVal = 1 Then
            MessageBox.Show("Salida de Turno Aceptada")
        Else               
            MessageBox.Show("ID de Empleado no encontrado")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        cmd.Dispose()
        myConnection.Dispose()
    End Try
End Sub

`