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

Question

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 ?

@Tony Toews I believe ms-access-2007 to be an important tag, as do others it seems, because it is widely used. — Fionnuala
– Fionnuala, Commented Dec 7, 2009 at 10:52
The question isn't really about Access at all, of course, as only Jet/ACE is in use. But the original question in comments below has indicated that he's using A2007, so I think the 2007 tag is appropriate, if the tag should exist at all (and I don't believe version-specific tags should exist in the first place). — David-W-Fenton
– David-W-Fenton, Commented Dec 8, 2009 at 5:54
@David W. Fenton: "The question isn't really about Access at all, of course, as only Jet/ACE is in use" -- sure it is: the Access Datbase Engine. It would probably help if you enumerate 'ACE' -- what does the 'A' stand for, now? The whole world including Microsoft refers to the engine as 'Access'. Why should Stackoverflow be any different? — onedaywhen
– onedaywhen, Commented Dec 8, 2009 at 9:39

Fredrik Mörk · Accepted Answer · 2009-12-07 07:43:18Z

2

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 '

edited Dec 7, 2009 at 7:43

answered Dec 7, 2009 at 6:55

Fredrik Mörk

159k31 gold badges296 silver badges349 bronze badges

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

4 Comments

Eias.N Over a year ago

How can I use parameterized queries , I'm a freshman on it :P

Eias.N Over a year ago

I find and "Add" parameter but never find Update ?

Tony Toews Over a year ago

Paremeterized queries can be a huge pain. I've been using queries concatenated in VBA code for about 15 years now and they work just fine.

Fredrik Mörk Over a year ago

@Tony Toews: making really sure that the input is clean, or cleaning up after someone using the security hole can also be a huge mess.

Adriaan Stander · Accepted Answer · 2009-12-07 07:37:27Z

1

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") & "#";

edited Dec 7, 2009 at 7:37

answered Dec 7, 2009 at 6:57

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

4 Comments

Eias.N Over a year ago

When I use this code , compiler highlight the following code : DataAdapter1.Fill(DataSet1, "Tb") and tell me that types of data is not the same ? what is the problem ?

Adriaan Stander Over a year ago

Which database are you using?

Adriaan Stander Over a year ago

Check the edited answer, MS ACCESS dates work slightly different to Sql Server Dates

Eias.N Over a year ago

I used the parameters , it solve the problem thank you astander

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related