SQL syntax error

[son]

heya can someone help ..

 

i am using vb.net in my aspx.vb file i am writing code with SQL

 

i keep on getting an error message say the CAST expects AS

 

thanks in advance sonia

 

 

" SELECT Mobile.Label + ' ( ' + Mobile.Registration + ' / ' + CAST(CAN_Message.Locator_ID AS varchar(5)) + ' )' AS Vehicle," & _
                               " (cast (Cast(CAN_Message.Distance * "
       If Session("user_Miles") = True Then
           strSQL += "0.6214)as decimal(10,2)) as nvarchar) + 'm') as distance, "
       Else
           strSQL += "1.0000)as decimal(10,2)) as nvarchar) + 'km') as distance, "
       End If

       strSQL += _
                               " (cast (CAST(CAN_Message.Fuel_Consumption * "
       If Session("user_miles") = True Then
           strSQL += "4.546)as decimal(10,2)) as nvarchar) + 'gallons') as Fuel_Used, "
       Else
           strSQL += "22)as decimal(10,2))as nvarchar) + 'litres') as fuel_used ,"
       End If

       strSQL += _
                     " (cast (Cast(CAN_Message.Fuel_Consumption / CAN_Message.Distance / "
       If Session("user_Miles") = True Then
           strSQL += "1) as decimal(10,2)) as nvarchar) + ' mpg ')  as Consumption_Rate "
       Else
           strSQL += "100) as decimal(10,2)) as nvarchar) + ' lts/100km ') as  Consumption_Rate  "
       End If

       strSQL += _
                               " FROM CAN_Message INNER JOIN " & _
                               " Mobile ON CAN_Message.Locator_ID = Mobile.Mobile_ID " & _
                               " WHERE CAN_Message.Locator_Id IN (" & strMobileList & ") " & _
                               " AND CAN_Message.Creation_Date >=" & strSQLFromDateTime & " AND Creation_Date<=" & strSQLToDateTime & _
                               " GROUP BY Mobile.Label + ' ( ' + Mobile.Registration + ' / ' + CAST(CAN_Message.Locator_ID AS varchar(5)) + ' )' "

[Robby]

Have you done a step-through of strSQL and executed directly on the database, i.e. Query Analyzer

 

A couple of your CASTs don't look right.

[Joe Mamma]

Hi sonia. . .

I think you have an extra '(' before some of the casts:

 

" (cast (Cast(CAN_Message.Distance * "

 

also

 

your group by statement is underqualified