Error when selecting between two dates from vb.net to sql

Question

I have the code below

technician_sqlsource.SelectCommand = "Select analyst as Analyst, sample_description, RFA_number, convert(varchar(10), updated_date, 103)as updated_date, customer, po_number, total_charged from New_Analysis_Data where analyst =  '" & FullName & "' and updated_date > '" & CDate(startdate.Text) & "'" & " and updated_date < '" & CDate(enddate.Text) & "'"

This basically passed through the sql command to the sql server and retrieves data between 2 dates. These dates come from 2 text boxes (start and end date). When I run this I get the error - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

If I remove & "'" & " and updated_date < '" & CDate(enddate.Text) & "'" then it will work, seems to be an issue with the enddate.

The query is vulnerable to sql injection attacks. You're practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented May 24, 2013 at 13:28
Apart from the sql-injection issue which is very serious and can be avoided easily with sql-parameters, what date-strings are you trying to convert to a datetime and what culture are you using(e.g. "en-us")? CDate uses the current culture to convert a string to Date. — Tim Schmelter
– Tim Schmelter, Commented May 24, 2013 at 13:32
culture is set to en-gb, I dont understand the difference ie why does it work if I remove enddate and just leave > startdate. It displays everything greater than the date I enter. As so as I add < enddate it throws the error — Silentbob
– Silentbob, Commented May 24, 2013 at 14:18
It's not the code change that fixes things, it's the data. The end date your are testing with does not convert to a datetime object correctly. It is possible your calendar extender is creating a US date that is invalid for the en-gb culture? ex: 5/24/2013 is correct in US, but invalid for en-gb. — Joel Coehoorn
– Joel Coehoorn, Commented May 24, 2013 at 14:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

Use query parameters!

technician_sqlsource.SelectCommand = _
      "SELECT analyst as Analyst, sample_description, RFA_number, " & _
            " convert(varchar(10), updated_date, 103)as updated_date, " & _ 
            " customer, po_number, total_charged " & _ 
      " FROM New_Analysis_Data " & _
      " WHERE analyst = @FullName " & _
            " AND updated_date >= @StartDate AND updated_date < @EndDate"

 With technician_sqlsource.SelectCommand.Parameters
      .Add("@FullName", SqlDbType.VarChar,50).Value = FullName
      .Add("@StartDate", SqlDbType.DateTime).Value = startdate.Text
      .Add("@EndDate", SqlDbType.DateTime).Value = enddate.Text
 End With

This also may or may not fix your problem with the end date. Even if it doesn't fix the problem, you should get a clearer error message... but most likely, you're not entering a valid date format in that textbox. Have you considered using a DatePicker control?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 24, 2013 at 13:35

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

3 Comments

Dhwani Over a year ago

Great answer. I am beginner so I choose the way of beginner. You show path not only to user2050577 but also me. +1 for your giving us right direction.

Silentbob Over a year ago

I now get an error on technician_sqlsource.SelectCommand.Parameters says parameters is not a memeber of string

Silentbob Over a year ago

just for info I am using 2 textboxes combined with calender extenders

Ted · Accepted Answer · 2013-05-24 13:49:48Z

1

1st off you should use SqlParameters!!!

It is much safer and you will also get rid of the date conversion issue.

            SqlCommand cmd=new SqlCommand();
            cmd.CommandText = @"
            Select  analyst as Analyst, 
                    sample_description,
                    RFA_number,
                    convert(varchar(10), updated_date, 103) as updated_date,
                    customer,
                    po_number,
                    total_charged
            from    New_Analysis_Data
            where   analyst =  @analyst
            and     updated_date between @startdate and @enddate";

            technician_sqlsource.SelectCommand = cmd;

            technician_sqlsource.SelectCommand.Parameters.Add(new SqlParameter("@analyst", FullName));
            technician_sqlsource.SelectCommand.Parameters.Add(new SqlParameter("@startdate",CDate(startdate.Text)));
            technician_sqlsource.SelectCommand.Parameters.Add(new SqlParameter("@enddate", CDate(enddate.Text)));

edited May 24, 2013 at 13:49

answered May 24, 2013 at 13:40

Ted

4,0671 gold badge23 silver badges35 bronze badges

2 Comments

Silentbob Over a year ago

looks good but I now get error on the technician_sqlsource.SelectCommand = cmd as it says value of technician_sqlsource.SelectCommand cannot be converted to a string

Ted Over a year ago

yes, you're right, then add the parameters to technician_sqlsource rather than the cmd

matzone · Accepted Answer · 2013-05-24 14:28:41Z

1

If you want still in your old (unsafe) style .. you can try this ..

technician_sqlsource.SelectCommand = "Select analyst as Analyst, sample_description, RFA_number, convert(varchar(10), updated_date, 103)as updated_date, customer, po_number, total_charged from New_Analysis_Data where analyst =  '" & FullName & "' and updated_date > #" & startdate.Text & "# and updated_date < #" & enddate.Text & "#"

But later you must use query parameters! .. to prevent SQL Injection !

edited May 24, 2013 at 14:28

answered May 24, 2013 at 14:05

matzone

5,7293 gold badges19 silver badges20 bronze badges

5 Comments

Silentbob Over a year ago

quick question - whats the # for

Silentbob Over a year ago

doesnt work for me Im afraid, get multiple errors to do with the date format.

Silentbob Over a year ago

nope, still get the original error..arrrggghhh, thanks for trying to help though

Silentbob Over a year ago

I use 2 textboxes with 2 ajax calender extenders

matzone Over a year ago

if you used VB datetimepicker .. I think my first try will work, but ajax calendar extender .. I don't know .. sorry

Dhwani · Accepted Answer · 2013-05-24 13:30:27Z

0

try this:

technician_sqlsource.SelectCommand = "Select analyst as Analyst, sample_description, RFA_number, convert(varchar(10), updated_date, 103)as updated_date, customer, po_number, total_charged from New_Analysis_Data where analyst =  '" & FullName & "' and updated_date > '" & CDate(startdate.Text) & "' and updated_date < '" & CDate(enddate.Text) & "'"

answered May 24, 2013 at 13:30

Dhwani

7,66618 gold badges82 silver badges143 bronze badges

1 Comment

Tim Schmelter Over a year ago

@user2050577: You are getting the same error because the string is converted to date first, then the sql query is getting executed. But because the string is not a valid datetime the answer of ITBeginner doesn't solve your first problem. So first use sql-parameters, then solve your string-to-date conversion issue(you haven't shown examples), then use the corect sql which is also easier when you don't concatenate strings to a sql query but use parameters.

Collectives™ on Stack Overflow

Error when selecting between two dates from vb.net to sql

4 Answers 4

Use query parameters!

3 Comments

2 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Use query parameters!

3 Comments

2 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related