Error Incorrect syntax near '<'. What I miss?

Question

0.00/5 (No votes)

See more:

VB

Dim DataSearch1 As DateTime = Nothing
       Dim DataSearch2 As DateTime = Nothing

VB

Dim SampleOrder As String = Nothing
       Dim Requester As String = Nothing
       Dim ProjectNumber As String = Nothing
       Dim Coordonator As String = Nothing
       Dim Quantity As String = Nothing
       Dim DataSearch1 As DateTime = Nothing
       Dim DataSearch2 As DateTime = Nothing

       Dim SelectQuery As String = "SELECT OrderID, DVMOrderNumber, Requester, ProjectNumber, CostCenter, CostResponsable, CostCategory, TotalSamples, OrderDescription, FinalCustomerAddress, TargetDate, AssemblyPlannedDate, LogisticsResponsable, ShippingMode, ShippingAddress, Urgent, HasBOM, HazardousMaterial, ExpressShipment, HasSerialComponents, PrototypeComponents, OrderType FROM Orders_Header"
       Dim WhereQuery As String = " WHERE 1=1"

       Requester = BOX_Requester1.Text
       Requester = Find.Find2(Requester)
       ProjectNumber = BOX_ProjectNumber1.Text
       ProjectNumber = Find.Find2(ProjectNumber)
       SampleOrder = BOX_SampleOrder1.Text
       SampleOrder = Find.Find2(SampleOrder)
       Coordonator = DDL_Coordonator1.Text
       Coordonator = Find.Find2(Coordonator)
       Quantity = DDL_Coordonator1.Text
       Quantity = Find.Find2(Quantity)

       If BOX_DesiredDelivery1.Text <> Nothing And BOX_DesiredDelivery2.Text <> Nothing Then
           DataSearch1 = Convert.ToDateTime(BOX_DesiredDelivery1.Text)
           DataSearch2 = Convert.ToDateTime(BOX_DesiredDelivery2.Text)
           WhereQuery = WhereQuery & " AND '" & DataSearch1 & "' >= TargetDate <='" & DataSearch2 & "'"
       Else
           If DataSearch1 <> Nothing Then
               WhereQuery = WhereQuery & " AND TargetDate >= '" & DataSearch1 & "'"
           End If
           If DataSearch2 <> Nothing Then
               WhereQuery = WhereQuery & " AND TargetDate <= '" & DataSearch2 & "'"
           End If
       End If

       If BOX_DesiredDelivery1.Text <> Nothing Then
           DataSearch1 = Convert.ToDateTime(BOX_DesiredDelivery1.Text)
       End If
       If BOX_DesiredDelivery2.Text <> Nothing Then
           DataSearch2 = Convert.ToDateTime(BOX_DesiredDelivery2.Text)
       End If

       If Requester <> Nothing Then
           WhereQuery = WhereQuery & " AND Requester LIKE '" & Requester & "'"
       End If
       If ProjectNumber <> Nothing Then
           WhereQuery = WhereQuery & " AND ProjectNumber LIKE '" & ProjectNumber & "'"
       End If
       If SampleOrder <> Nothing Then
           WhereQuery = WhereQuery & " AND DVMOrderNumber LIKE '" & SampleOrder & "'"
       End If

       If Coordonator <> Nothing Then
           WhereQuery = WhereQuery & " AND LogisticsResponsable LIKE '" & Coordonator & "'"
       End If
       If Quantity <> Nothing Then
           WhereQuery = WhereQuery & " AND TotalSamples LIKE '" & Quantity & "'"
       End If

       Dim myQuery As String = SelectQuery & WhereQuery
       SqlDataSource2.SelectCommand = myQuery
       SqlDataSource2.DataBind()
       GridView1.DataSourceID = "SqlDataSource2"
       GridView1.DataBind()

Work all but when i put that part :

SQL

If BOX_DesiredDelivery1.Text <> Nothing And BOX_DesiredDelivery2.Text <> Nothing Then
           DataSearch1 = Convert.ToDateTime(BOX_DesiredDelivery1.Text)
           DataSearch2 = Convert.ToDateTime(BOX_DesiredDelivery2.Text)
           WhereQuery = WhereQuery & " AND '" & DataSearch1 & "' >= TargetDate <='" & DataSearch2 & "'"
       Else
           If DataSearch1 <> Nothing Then
               WhereQuery = WhereQuery & " AND TargetDate >= '" & DataSearch1 & "'"
           End If
           If DataSearch2 <> Nothing Then
               WhereQuery = WhereQuery & " AND TargetDate <= '" & DataSearch2 & "'"
           End If
       End If

My cod doesn't work, i can i miss something here:

VB

If BOX_DesiredDelivery1.Text <> Nothing And BOX_DesiredDelivery2.Text <> Nothing Then
            DataSearch1 = Convert.ToDateTime(BOX_DesiredDelivery1.Text)
            DataSearch2 = Convert.ToDateTime(BOX_DesiredDelivery2.Text)
            WhereQuery = WhereQuery & " AND '" & DataSearch1 & "' >= TargetDate <='" & DataSearch2 & "'"
        Else

Posted 3-Sep-15 1:06am

MoraruVladut

Add a Solution

2 solutions

Solution 1

Why are you converting textbox input to DateTime values:

VB

DataSearch2 = Convert.ToDateTime(BOX_DesiredDelivery2.Text)

(Although you should use DateTime.TryParse instead, of Convert, as the later throws an exception and kills your app if the user makes a mistake, and the former lets you tell him gracefully instead)
And then you convert it back to a string again, and probably confuse SQL:

VB

WhereQuery = WhereQuery & " AND TargetDate <= '" & DataSearch2 & "'"

And that's probably your problem - the default "convert DateTime to String" implementation generates a string that is dependant on your system settings, which may not be anything SQL understands.

Use a Parameterized query instead, and pass the DateTime value directly instead of converting it to a string at all.
Chances are your problem will go away at the same time...

Posted 3-Sep-15 1:28am

OriginalGriff

Comments

MoraruVladut 3-Sep-15 7:36am

Thanks for help

MoraruVladut 3-Sep-15 7:36am

I found the problem is a little lower

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MoraruVladut · Accepted Answer · 2015-09-03T01:35:00

Solution 2

I find the error
i Change WhereQuery = WhereQuery & " AND '" & DataSearch1 & "' >= TargetDate <='" & DataSearch2 & "'"

With

WhereQuery = WhereQuery & " AND TargetDate >= '" & DataSearch1 & "' AND TargetDate <= '" & DataSearch2 & "'"

And now work, Thanks!

Posted 3-Sep-15 1:35am

MoraruVladut