Error in getting Dates in database (BETWEEN Clause| VB.NET|OLE)

Question

Hello ,

before a little time , I used a code to get the dates between 2 dates from the database (column with dates dd/mm/yy) , I think it works nice first time , the code is :

      Dim b As New Date
            Dim a As Integer
            a = Val(tx2.Text)
            b = System.DateTime.Today
            b = b.AddDays(-a)

             MsgBox(b)
 Conn.Open()
  SQLstr = " Select * from tb where lastvstart BETWEEN #01/01/1800# AND #" & b & "#"
 Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, Conn)
   DataSet1.Clear()

        DataAdapter1.Fill(DataSet1, "Tb")
        Conn.Close()

as you see , the code let the user to insert a number and minus it form the date of today , then calculates the date that I want , after that I use BETWEEN Clause to get all dates between them

But now , this code gets some data and overpasses others , I mean some of the dates is between the tow dates but the code never get it , why that happens ?

Answer 1

If you look at the generated SQL string, does it contain the date that you expect? I would assume that the database requires it to follow a specific format (either dd/MM/yyyy or MM/dd/yyyy given the hard coded date in the query). Could it be that your day and month switch places when the string version of the date is created and inserted into your SQL query?

As a side note, I would strongly recommend against concatenating SQL queries together like that. If possible, use parameterized queries instead. That could possibly also remove some type conversion issues.

Update
Here is an example of using a parameterized query over OLE DB to an Access database:

Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\path\file.mdb""")
    Using cmd As New OleDbCommand("select * from tb where lastvstart BETWEEN ? AND ?", connection)

     Dim param As OleDbParameter

     ' add first date '
     param = New OleDbParameter()
     param.DbType = DbType.Date
     param.Value = New DateTime(1800, 1, 1)
     cmd.Parameters.Add(param)

     'add second date '
     param = New OleDbParameter()
     param.DbType = DbType.Date
     param.Value = DateTime.Today.AddDays(-a)
     cmd.Parameters.Add(param)
     cmd.Parameters.Add(New OleDbParameter())

     connection.Open()
     Using adapter As New OleDbDataAdapter(cmd)
      Using ds As New DataSet()
       adapter.Fill(ds)
       Console.WriteLine(ds.Tables(0).Rows.Count)
      End Using ' DataSet ' 
     End Using ' OleDbDataAdapter '
    End Using ' OleDbCommand '
End Using ' OleDbConnection '

Answer 2

Can you not change the Sqlstr to

SQLstr = " Select * from tb where lastvstart <= '" & b.ToString("dd MMM yyyy") & "'";

EDIT, change based on DB

Use this string and check if it works

SQLstr = " Select * from tb where lastvstart <= #" & b.ToString("dd MMM yyyy") & "#";