Blue_Steel
asked on
SQL statement to create dataset where table date field is > than a datepicker.value
Hi
I'm new to VS2008 (an old VB6 programmer), and I'm really struggling with this. I've googled until I'm blue in the face with no joy.
I'm sure it's a very simple solution. I've spent hours with no luck.
Basically, I have a table that contains a date field (Access 2007 database, and I'm using VB.Net). I have a form with DateTimepicker on it for user selection.
I'm then trying to create a dataset with an SQL statement that looks to see if the table date field (FDOB1) is a later date than the DateTimepicker value.
Any help would be appreciated, as it's a bit of a showstopper at the moment (aren't they all!)
Al
I'm new to VS2008 (an old VB6 programmer), and I'm really struggling with this. I've googled until I'm blue in the face with no joy.
I'm sure it's a very simple solution. I've spent hours with no luck.
Basically, I have a table that contains a date field (Access 2007 database, and I'm using VB.Net). I have a form with DateTimepicker on it for user selection.
I'm then trying to create a dataset with an SQL statement that looks to see if the table date field (FDOB1) is a later date than the DateTimepicker value.
Any help would be appreciated, as it's a bit of a showstopper at the moment (aren't they all!)
Al
sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > " + "'" + DateTimePicker1.Value + "'"
ASKER
Hi and thanks for your reply.
I did state that I'm using Access 2007, not SQL.
I changed the query slightly to ensure DateTimepicker1 was returning a short date. The Access field FDOB1 is a short date too. The new query is
sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FNAME1 <> '' AND FDOB1 > " + "'" + DateTimePicker1.Value.ToSh
When I run the full query the error is {"Data type mismatch in criteria expression."}
The Msgbox (which I use often to check any sql problems) shows as SELECT NAMe,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' AND FDOB1 > '25/01/2002'
If I run the query SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' it works fine.
As both FDOB1 and DateTimepicker1 are dates and not strings, I'm lost.
I did state that I'm using Access 2007, not SQL.
I changed the query slightly to ensure DateTimepicker1 was returning a short date. The Access field FDOB1 is a short date too. The new query is
sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FNAME1 <> '' AND FDOB1 > " + "'" + DateTimePicker1.Value.ToSh
When I run the full query the error is {"Data type mismatch in criteria expression."}
The Msgbox (which I use often to check any sql problems) shows as SELECT NAMe,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' AND FDOB1 > '25/01/2002'
If I run the query SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' it works fine.
As both FDOB1 and DateTimepicker1 are dates and not strings, I'm lost.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for this!
1. Create sqlconnectiono object with connectionstring to connect to DB
2. Create sqldataadapter object. you can skip ponit 3 & 4 by using one of the constructor which allows direct sql and connection object.
3. Create sqlcommand object and set connection property
4. SelectCommand property of adapter object
5. Use fill method of dataadapter and pass a dataset/datatable object to it
hope this helps :)