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

Hi,

I have the following code: -

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

        Connect()
        Dim da As New SqlClient.SqlDataAdapter(sql, oConn)
        da.Fill(ds, "Tests") ' populate data set "da" with the data returned from sql command "ds" and set dataset name to "Rigs"
        DataGridView1.DataSource = ds.Tables("Tests")

There is correct data in the database but when I run the above and select the dates it does not show any data in the datagridview.

What am I doing wrong please.

Best Regards
Lee
ljhodgettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mdouganCommented:
Two things, I would put single quotes around the dates, also, I would format the dates to be  yyyy-mm-dd
0
Jorge PaulinoIT Pro/DeveloperCommented:
Try changing to this:

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdouganCommented:
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") & "'"
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

mdouganCommented:
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.
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> 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-
0
mdouganCommented:
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"

0
Jorge PaulinoIT Pro/DeveloperCommented:
mdougan I use that in my code but I don't know the asker requirements. Maybe he doesn't use the time.
0
mdouganCommented:
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'
0
ljhodgettAuthor Commented:
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
0
ljhodgettAuthor Commented:
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
0
mdouganCommented:
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).
0
mdouganCommented:
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
0
ljhodgettAuthor Commented:
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
0
mdouganCommented:
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
0
ljhodgettAuthor Commented:
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
0
Jorge PaulinoIT Pro/DeveloperCommented:
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)"
0
mdouganCommented:
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
0
ljhodgettAuthor Commented:
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


0
Jorge PaulinoIT Pro/DeveloperCommented:
And the problem now is ... ?
0
ljhodgettAuthor Commented:
Hi,

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

Best Regards
Lee
0
Jorge PaulinoIT Pro/DeveloperCommented:
But now you are inserting values! It gives any error message ?
0
mdouganCommented:
Well, as I mentioned above, the smalldatetime data type does not record the seconds of the timestamp, only down to the minutes.  So, you will have to take that into account when constructing your between statement.

Though I can't see why your insert wouldn't work, I've found that I've eliminated date problems by always inserting dates or datetimes into SQL Server in the format   '2008-02-11 13:24:32.111'  if it is a datetime data type, or for smalldatetime '2008-02-11 13:24'  or '2008-02-11'  

If I only care about the date portion of the field, I store it as smalldatetime with just the date portions as above... that will store 00:00:00 for the time portion.  That way, you can do between statements such as

where CONVERT(VARCHAR(10), MYDATEFIELD, 121) between '2008-02-01' and '2008-02-11'

and you will get both dates inclusive of each other.

In your SQL Statement, you are not formatting NOW in any way... if I were you, I'd format it to one of the smalldatetime formats I listed above, otherwise, you'll be having the time truncated or possibly rounded, shouldn't cause a problem, though it might...

I want you to try the Where clause that I've posted above, substituting your date field and see if you get rows between the dates that you are interested in.  Then, if you want to expand it to include the minutes and seconds, then change the varchar definition to  VARCHAR(16) and add the hours:minutes to the dates in the between clause.
0
ljhodgettAuthor Commented:
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

0
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help Lee
0
mdouganCommented:
Great!

(LOL, look at the very first post)
0
ljhodgettAuthor Commented:
lol,

I never thought it would be the insert. I formatted everything else in the end.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.