Date query about BD Access

Question

Date query about BD Access

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Previous information: I have a database with a Date / Time type field in Access (dd / mm / yy to hh: mm: ss). The idea is to put a date (entered in three TextBox TBDia TBMes TBAnio ) and that I take the records of that date (to see the hours) in a DataGridView called DGVFechas . This is my code:

    'cogemos la fecha de los TextBox'
    Dim fecha As Date = DateSerial(TBAnio.Text, TBMes.Text, TBDia.Text)

    'cogemos todos los datos de la fecha'
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=F:\CrossChex.mdb")
    con.Open()
    Dim query2 As String = "SELECT CheckTime, CheckType FROM Checkinout WHERE CheckTime = " & fecha
    Dim DA2 As New OleDbDataAdapter(query2, con)
    Dim DT2 As New DataTable
    DA2.Fill(DT2)

    DGVFechas.DataSource = DT2

    'cambiamos el nombre de las columnas del DataGridView'
    DGVFechas.Columns(0).HeaderText = "Horas"

I've tried declaring fecha as string and I have not been able to make it work either. I guess the fault is in the query, which will not be "=" but "LIKE" or similar, but I have already tried all the combinations that have occurred to me and I have not found the form.

There are fragments of code that I have deleted since they are not important for this failure, maybe I have removed something more but I think that is not the case.

Thank you very much in advance!

fecha vb.net ms-access

asked by CharlieMR 25.04.2018 в 12:04

source

2 answers

2

The problem you have is that you are not having a good date for the consultation.

In order for the DB engine to correctly catch the date you are passing it to compare it with a column you can add it as a string in the following way:

Dim query2 As String = "SELECT CheckTime, CheckType FROM Checkinout WHERE CheckTime = '" & fecha.ToString("dd/MM/yyyy") & "'"

When passing any data as string in a SQL query you should always add the single quotes ' to identify it as string .

You can also pass it by parameters indicating the type of data you pass:

Dim query2 As String = "SELECT CheckTime, CheckType FROM Checkinout WHERE CheckTime = @fecha"
Dim COMANDO As New OleDbCommand(query2, con);
COMANDO.Parameters.Add("@fecha", OleDbType.Date).Value = fecha;
Dim DA2 As New OleDbDataAdapter(COMANDO)
Dim DT2 As New DataTable
DA2.Fill(DT2)

answered by 25.04.2018 в 12:20

Show data with filter in SQL query current_timestamp does not exist in PostgreSQL?

score 1 · Accepted Answer

These types of problems usually have a common origin: You should never concatenate strings to generate queries . On the one hand, you expose the application to Sql Injection. On the other, sometimes problems like this appear.

Although you do not say it explicitly, I understand that CheckTime is of type DateTime , but you pass a string to try to compare it. The best solution is to always use parameterized queries. In this way, the conversion of types and generation of the query is done by the database engine.

In your case, you can try as follows:

Dim query2 As String = "SELECT CheckTime, CheckType FROM Checkinout WHERE CheckTime = @fecha"
Dim da2 As New OleDbDataAdapter(query2, con)
da2.SelectCommand.Parameters.AddWithValue("@fecha",fecha)
Dim DT2 As New DataTable
da2.Fill(DT2)

Edit:

In one of your comments you say the following:

In the database that field is with data as 04/27/2017 08:30 while I compare it with 04/27/2017

In that case, you can not use the = comparator in your query. I recommend you use BETWEEN and use two dates: you want it and the next day, something like this:

Dim fecha As Date = DateSerial(TBAnio.Text, TBMes.Text, TBDia.Text)
Dim fecha2 As Date = fecha.AddDays(1)
Dim query2 As String = "SELECT CheckTime, CheckType FROM Checkinout WHERE CheckTime BETWEEN @fecha and @fecha2"
da2.SelectCommand.Parameters.AddWithValue("@fecha",fecha)
da2.SelectCommand.Parameters.AddWithValue("@fecha2",fecha2)
...