Error in sql statement when using sql server 2005 and vb.net

Two things, I would put single quotes around the dates, also, I would format the dates to be  yyyy-mm-dd

sql = "SELECT * " & _
              "FROM Tests_Taken " & _
              "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "' AND DateTaken between '" & Format(MonthCalendar1.SelectionStart, "yyyy-MM-dd") & "' and '" & Format(MonthCalendar2.SelectionStart, "yyyy-MM-dd") & "'"

You should also do something to verify that MonthCalendar2.SelectionStart is the correct property to be looking at... display that value to see if you are actually getting a date.  SelectionStart is normally where the cursor is in a text field.  I would expect you to be looking at MonthCalendar2.Value or something like that.

>> I would expect you to be looking at MonthCalendar2.Value or something like that.

mdougan,

MonthCalendar control doesn't have .value. You can choose SelectionStart and SelectionEnd to get the selection-

If you include the time as jpaulino suggests, and you want the date range to be inclusive of the second date, then you need to put a time which is at the end of the day

Dim Date1 As String =  MonthCalendar1.SelectionStart.ToString("yyyy-MM-dd") & " 00:00:00"
Dim Date2 As String =  MonthCalendar2.SelectionStart.ToString("yyyy-MM-dd") & " 23:59:59"

mdougan I use that in my code but I don't know the asker requirements. Maybe he doesn't use the time.

I agree, that if your database date field does not contain time values, then you can use the code with zeros for the times, and the between statement will pick up all dates within the range... however, if that is the case, then you don't need to specify the time because by providing a date without a time, the time portion will default to  00:00:00

so, for example if your database contains

2008-02-05 00:00:00
2008-02-06 00:00:00
2008-02-07 00:00:00

And you say where mydate between '2008-02-05 00:00:00' AND '2008-02-07 00:00:00'  then you will get all three dates.  Likewise, if you were to say where mydate between '2008-02-05' AND '2008-02-07'  then you will get all three dates.

I'm just pointing out that if your database contains:

2008-02-05 02:45:31.123
2008-02-06 23:01:00.444
2008-02-07 11:29:30.432

And you say where mydate between '2008-02-05 00:00:00' AND '2008-02-07 00:00:00'  then you will only get the first two dates.  

To get all 3 you would have to say where mydate between '2008-02-05 00:00:00.000' AND '2008-02-07 23:59:59.999'

Still no joy i'm afraid. Hopefully this will shed some light on it. I've managed to get in working in a query using the following sql code: -

SELECT     UserID, TestID, Score, DateTaken
FROM         Tests_Taken
WHERE     (UserID = 'ba91d2ee-1f5e-4f0e-8a2a-490d4914c165') AND (DateTaken BETWEEN '2008-02-02 00:00:00' AND '2008-06-02 23:59:59')

but when I put the code -


        Dim Date1 As String = MonthCalendar1.SelectionStart.ToString("yyyy-dd-MM") & " 00:00:00"
        Dim Date2 As String = MonthCalendar2.SelectionStart.ToString("yyyy-dd-MM") & " 23:59:59"
        sql = "SELECT * " & _
                 "FROM Tests_Taken " & _
                 "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "' AND DateTaken between '" & Date1 & "' and '" & Date2 & "'"


It comes up with the error message:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Best Regards
Lee

Hi,

Yes the time is included and the dates I have in the db are: -

02/06/2008 20:24:00
02/06/2008 20:24:00
02/06/2008 20:25:00
02/06/2008 20:26:00

Please also see my above comment

Best Regards
Lee

You need to display the contents of Date1 and Date2 sometime after populating them to ensure that you got a valid value from both MonthCalendar1.SelectionStart and MonthCalendar2.SelectionStart   If one of those was "empty", then the default is usually not a valid smalldatetime  (the default is something like 1/1/1900 and smalldatetime cannot go back that far).

Actually, a smalldatetime might not even be able to include the seconds... so, you might have to format just using the Hours and the Minutes

well the code creates the sql statement of:-

SELECT * FROM Tests_Taken WHERE UserID = 'ba91d2ee-1f5e-4f0e-8a2a-490d4914c165' AND DateTaken between '2008-01-02 00:00:00' and '2008-28-02 23:59:59'

Best Regards
Lee

What is the database definition of the DateTaken column?  Is it smalldatetime?  If so, then as I mentioned last, only include the hours and minutes in the format, not the seconds

Hi,

Even with the sql code as: -

SELECT * FROM Tests_Taken WHERE UserID = 'ba91d2ee-1f5e-4f0e-8a2a-490d4914c165' AND DateTaken between '2008-07-02 00:00' and '2008-29-02 23:59'

it comes up with the error message:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Even with the query screen it comes up with this message.

The date in the db of 02/06/2008 20:24:00 is month / day / year I presume even though the laptop is set to uk.

Best Regards
Lee

Sorry I had to leave. Have you tried with the convert formula that I have post it ?

Dim Date1 As String =  MonthCalendar1.SelectionStart.ToString("yyyy-MM-dd") & " 00:00:00"
Dim Date2 As String =  MonthCalendar2.SelectionStart.ToString("yyyy-MM-dd") & " 23:59:59"

sql = "SELECT * " & _
         "FROM Tests_Taken " & _
         "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "' AND DateTaken between CONVERT(DATETIME, '" & Date1.ToString  & "', 102) and CONVERT(DATETIME, '" & Date2.ToString & "', 102)"

Well, technically, dates are actually stored in the database as 4 byte ints, 2 bytes for the number of days since 1/1/1900 and the other for the hours and minutes (in the case of smalldatetime).  

SQL Server has a default for the way it displays dates, which gets set when SQL Server is installed... so, I think that defaults to month/day/year, but that doesn't affect the way dates are stored internally.

You still haven't said if the date in the field DateTaken is a smalldatetime or not, but presumably it is.

If so, maybe you should be converting it to varchar so that your SQL Statement looks like:

SELECT * FROM Tests_Taken WHERE UserID = 'ba91d2ee-1f5e-4f0e-8a2a-490d4914c165' AND convert(varchar(16), DateTaken, 121) between '2008-07-02 00:00' and '2008-29-02 23:59'

That will ensure that the DateTaken value gets converted to a string in the same  yyyy-MM-dd hh:nn format

Hi,

Sorry I had to change what I was doing but am back to this now. I'm afraid still no joy. I think it may be a good idea for me to recap exactually what I'm doing.

To populate the database I am using the code: -
        Dim command As New SqlCommand("INSERT Into Tests_Taken VALUES ('" & Replace(SQLUserID, "'", "''") & "', NEWID()," & No_Correct & ",'" & Now & "')", oConn)
        command.ExecuteNonQuery()

That creates the sql statement:
INSERT Into Tests_Taken VALUES ('ba91d2ee-1f5e-4f0e-8a2a-490d4914c165', NEWID(),8,'11/02/2008 13:15:08')

The column is set to smalldatetime and the above sql is for todays date of 11th febuary 2008

Best Regards
Lee

And the problem now is ... ?

Hi,

It's stil the same issue as before where the date between doesn't show the data.

Best Regards
Lee

But now you are inserting values! It gives any error message ?

We've got there now. I hear a big sigh of relief from everyone!!! lol. I needed the apostrophe on either side of the dates and I needed to format the date when I inserted the information.

Many Thanks for all your help.

Lee

Glad I could help Lee

Great!

(LOL, look at the very first post)

lol,

I never thought it would be the insert. I formatted everything else in the end.