SQL statement for date field > DateTimepicker

[Blue Steel]

Hi guys. I'm new to VS (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). I have a form with DateTimepicker1 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.

sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > " + "'" + _
        DateTimePicker1.Value + "'"

Any help would be appreciated, as it's a bit of a showstopper at the moment (aren't they all!)

Al

 

[Blue Steel]

Moved?

Jeez, I run a couple of my own sites with vBulletin, and I posted in a wrong forum?

I did say I was old lol

I've spent the time since my original post trying the sql statement with loads of different date format statements and still no luck. This isn't a bump, just letting you know that I haven't cracked it yet. If I do I'll post the resolution, but don't hold your breath!

 

[InertiaM]

Please dont take this the wrong way, but you havent actually posted what your problem is. Is it throwing an error? Is it just not returning any records? As has been said many times on this board, we arent mind readers

Try the following:-

1. Run the query in Access. What does it give? Look at the SQL view of the Access query. What does it say? Does it give the results you expected?

2. After your sql = " line, try:-

Messagebox.Show (sql)

What does it say? Is it the same? If not, problem found

Solution:- Use parameterised queries. See the link in my signature.

 

[Blue Steel]

Hi and thanks for your reply.
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.ToShortDateString() + "'"


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.

Thanks for your help. I appreciate it.

 

[picoflop]

Dont take care of the correct format yourself, let the db driver do it for you.
check out .Parameters of the DBCommand object.

Like (from brain, not IDE):
cmd.commandtext = "SELECT foo FROM bar WHERE baz = @baz"
cmd.Parameters.AddWithValue("@baz", now())

This puts all the burden of creating the "correct" parameter syntax to the underlying driver.

 

[InertiaM]

The Access field FDOB1 is a short date too

As both FDOB1 and DateTimepicker1 are dates and not strings

Then, as I said and picoflop reiterated, use parameters and let them do the work for you. You dont need to try and change the format to a string.

I seem to remember that Access doesnt support named parameters, so you may need to investigate the exact syntax for supplying parameters.

 

[picoflop]

I seem to remember that Access doesnt support named parameters, so you may need to investigate the exact syntax for supplying parameters.

True. Though the names still can be used (afair), just make sure, that the parameters are ADDed in the order in which they appear in the sql statement
It's safe to use "?" instead of the parameter name, though. Then add a parameter without a name of course.

 

[cjard]

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.

In sql:
SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> ?

In vbcode:
command.Parameters.AddWithValue("fdob1param", DateTimePicker1.Value)


WARNING:
Dates are stored as fractional numbers since some epoch. If the epocj is Midnight Jan 1, 1900 then a date of midnight Jan 2 1900 is stored as 1, and SIX O CLOCK IN THE EVENING of Jan 2 is stored as 1.75

If you pick Jan 2 in your Date Time Picker, and the time component (hidden) in the picker is 3am your date value in the picker becomes 1.125

Consider carefully the implications of this on your query! If your row contains 1.75 and your parameter contains 1.125, they are not equal

Either round them, or use the BETWEEN or < and > operators to get a range of 1 day:

SELECT * FROM TABLE where fdob1 BETWEEN ? AND ?
AddWithValue("d1", dtp.Date)
AddWithValue("d2", dtp.Date.AddDays(1))

 

[Blue Steel]

Thanks for the replies guys. I knew it was something simple, it was just a Format function.

In case t helps anybody else out there, here is the answer:

sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > '" & FORMAT(DateTimePicker1.Value,"MM/dd/yyyy") & "'"


It works a treat.

 

[cjard]

If your FDOB1 is stored as a string, your query will not work properly
If your FDOB1 is stored as a date, your query will only work on computers that have a regional daqte/time format set to be the same as yours

=your query is a nasty hack, relying on the database's ability to convert a string to a date implicitly. I would suggest you read my post above again, and make a proper parameterised query, using a parameter of a date type..

..after all, you write this in your VB:

Dim i as Integer = 1
Dim d as Date = DateTime.Now

You do not store everything as string in your VB:

Dim i as String = 1.ToString()
Dim d as String = DateTime.Now.ToString()

Only to convert it back every time you want to use it

While Convert.ToInt32(i) < Convert.ToInt32("1000000")
  i = (Convert.ToInt32(i) * Convert.ToInt32(i)).ToString()
  MsgBox("The number squared is " & i)
End While

So why adopt a "store everything as a string and hope the database can convert it correctly" policy for your db? It'll trip you up, for sure..


cmd.CommandText = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > ?"
cmd.Parameters.AddWithValue("birthdate", DateTimePicker1.Value)


Was that REALLY so hard?