Link to home
Create AccountLog in
Avatar of colofornia
colofornia

asked on

VB.Net and MS SQL, Windows Forms, /!\ SqlException was unhandled ... Incorrect syntax near the keyword 'WHERE'... Part Two :)

New to EE, closed previous question and assigned points before resolution... more points though here ;^)

Modified previous code (sorry about the hanging comma misunderstanding).

Blows up now at the same point, but points to the [extra] WHERE.

1. How do I handle two WHEREs?
2. Do I JOIN?
3. How do I JOIN with this code to get the results (if I am to JOIN)?


'Populate variable with SQL SELECT statement, statement split for an easier read
'--Start-----------------------------------------------------------------------------------
 
strSQL = "SELECT Zzz.PI AS [Nnnn0], Zzz.PAprStDt AS [Nnnn1], "
strSQL += "Xxxx.AClDu AS [Nnnn2], Zzz.PClAprDu AS [Nnnn3], "
strSQL += "Xxxx.AFe AS [Nnnn4],Xxxx.AOrd AS [Nnnn5], "
strSQL += "Xxxx.ARc AS [Nnnn6], Xxxx.AOrdBy [Nnnn7], "
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strSQL += "DATEDIFF(dd,[D.DOnHld],[D.DOffHld]) AS [NoHoldDays] "
strSQL += "WHERE(D.DOnHld Is Not NULL) "
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
strSQL += "case "
strSQL += "when ISNULL(Xxxx.ARc,3) = 3 THEN '3' else "
strSQL += "case "
strSQL += " WHEN CAST(CAST(DATEDIFF(hh, CONVERT(CHAR(25),Xxxx.ARc,120), "
strSQL += " CONVERT(CHAR(25),Zzz.PClAprDu,120)) AS Decimal(18, 2)) / 24 AS Decimal(18, 0)) < 0 then '2' else '1' "
strSQL += "End "
strSQL += "end as [YN] "
strSQL += "FROM (P LEFT JOIN Xxxx ON Zzz.PI = Xxxx.AI) "
strSQL += "WHERE ((Xxxx.AClDu) Between '" & strCalStartDate & "' AND '" & strCalEndDate & "') "
strSQL += " ORDER BY Xxxx.AOrdBy "
 
'Open and populate with information from database
da = New SqlDataAdapter(strSQL, sqlConn3)
ds = New DataSet
'Temporary table is populated with values from SQL SELECT statement
da.SelectCommand.CommandTimeout = 300
da.Fill(ds, "Property") '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< The happy little error window points here.

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi

>FROM (P LEFT JOIN Xxxx ON Zzz.PI = Xxxx.AI)

Is P your table name and zzz its alias?

>D.DOnHld
what is table D? it is not specified in your from and how is it joined with the other tables?

Avatar of colofornia
colofornia

ASKER

ee_rlee,

Zzzz was a global replace for P.  for this forum [I need to keep this code as secure as possible] - it is P, and run as

FROM (P LEFT JOIN Xxxx ON P.PI = Xxxx.AI)

I need to figure out the JOIN to add D and its WHERE to this query
how is table D related to table P and Xxxx?
The order of the SQL statement is:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

You have a Where at line 11 before the FROM at line 21 and then another where at line 22.  So you would want to move line 11 down after line 22 and change the where to an AND to combine the conditions.

However you don't have a tables D or Zzz defined at all.  Those need to go in the FROM clause.

From clauses look like
FROM <Table1> JOIN <Table2> ON Table1.FieldA = Table2.FieldA
JOIN Table3 ON Table2.FieldB = Table3.FieldA

where The Fields tell the server how the tables are related.
--
JimFive
JimFive

I tried this and get and error again: SqlException was unhandled - Incorrect syntax near the keyword 'case'.

I removed the D. schema as it returned an error about Invalid Columns (the exist in the DB).
strSQL = "SELECT P.PI AS [Nnnn0], P.PAprStDt AS [Nnnn1], "
strSQL += "Xxxx.AClDu AS [Nnnn2], P.PClAprDu AS [Nnnn3], "
strSQL += "Xxxx.AFe AS [Nnnn4],Xxxx.AOrd AS [Nnnn5], "
strSQL += "Xxxx.ARc AS [Nnnn6], Xxxx.AOrdBy [Nnnn7], "
 
     strSQL += "DATEDIFF(dd,[DOnHld],[DOffHld]) AS [NoHoldDays] "
 
strSQL += "case "
strSQL += "when ISNULL(Xxxx.ARc,3) = 3 THEN '3' else "
strSQL += "case "
strSQL += " WHEN CAST(CAST(DATEDIFF(hh, CONVERT(CHAR(25),Xxxx.ARc,120), "
strSQL += " CONVERT(CHAR(25),P.PClAprDu,120)) AS Decimal(18, 2)) / 24 AS Decimal(18, 0)) < 0 then '2' else '1' "
strSQL += "End "
strSQL += "end as [YN] "
strSQL += "FROM (P LEFT JOIN Xxxx ON P.PI = Xxxx.AI) "
 
     strSQL += "JOIN D on P.PI = D.DIdx "
 
strSQL += "WHERE ((Xxxx.AClDu) Between '" & strCalStartDate & "' AND '" & strCalEndDate & "') "
 
     strSQL += "AND (DOnHld Is Not NULL) "
 
strSQL += " ORDER BY Xxxx.AOrdBy "

Open in new window

you are missing a comma here

 strSQL += "DATEDIFF(dd,[DOnHld],[DOffHld]) AS [NoHoldDays] "

strSQL = "SELECT P.PI AS [Nnnn0], P.PAprStDt AS [Nnnn1], "
strSQL += "Xxxx.AClDu AS [Nnnn2], P.PClAprDu AS [Nnnn3], "
strSQL += "Xxxx.AFe AS [Nnnn4],Xxxx.AOrd AS [Nnnn5], "
strSQL += "Xxxx.ARc AS [Nnnn6], Xxxx.AOrdBy [Nnnn7], "
 
strSQL += "DATEDIFF(dd,[DOnHld],[DOffHld]) AS [NoHoldDays], "
 
strSQL += "case "
strSQL += "when ISNULL(Xxxx.ARc,3) = 3 THEN '3' else "
strSQL += " WHEN CAST(CAST(DATEDIFF(hh, CONVERT(CHAR(25),Xxxx.ARc,120), "
strSQL += " CONVERT(CHAR(25),P.PClAprDu,120)) AS Decimal(18, 2)) / 24 AS Decimal(18, 0)) < 0 then '2' else '1' "
strSQL += "end as [YN] "
strSQL += "FROM (P LEFT JOIN Xxxx ON P.PI = Xxxx.AI) "
 
strSQL += "JOIN D on P.PI = D.DIdx "
 
strSQL += "WHERE ((Xxxx.AClDu) Between '" & strCalStartDate & "' AND '" & strCalEndDate & "') "
 
strSQL += "AND (DOnHld Is Not NULL) "
 
strSQL += " ORDER BY Xxxx.AOrdBy "

Open in new window

Added comma to DATEDIFF expression and got this:

"Conversion failed when converting the varchar value 'NOR8549' to data type int."

I think this must come from my JOIN where I tried to JOIN to different data types???

        strSQL += "JOIN D on P.PI = D.DIdx "

D.DIdx is int and the PK

P.Pi is varchar and the PK

How else can I JOIN these two tables if not on the PK?

Am I asking the right questions?

Thank you
you must know how table D is joined/related with the other tables.
ASKER CERTIFIED SOLUTION
Avatar of JimFive
JimFive
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you JimFive
Thank you everyone for the help on this.
<gratitude>colofornia</gratitude>

Open in new window

Force accepted.
Vee_Mod
Community Support Moderator