Line 1: Incorrect syntax near '='. 1

[Vika]

When executing the following SQL statment in VB6 I get Runtime Error Line1: Incorrect syntax near '='
I am using SQL 2000 as back end.

Can somebody tell me what am I doing wrong?

conn.Open "Provider=sqloledb; Data Source=acmedc1; Initial Catalog=OrderChecking; Integrated Security=SSPI;"

sql = "SELECT tblHeader.PO, tblHeader.BillName, tblHeader.BillAddress1, tblHeader.BillAddress2, " & _
"tblHeader.BillAddress3, tblHeader.BillCity, tblHeader.BillState, tblHeader.BillZip, " & _
"tblHeader.ShipName, tblHeader.ShipAddress1, tblHeader.ShipAddress2, tblHeader.ShipAddress3, " & _
"tblHeader.ShipCity, tblHeader.ShipState, tblHeader.ShipZip, tblDetails.ItemNo, " & _
"tblHeader.StoreNo, tblDetails.[Desc], tblDetails.Color, tblDetails.Style, 1 AS CartonCount, " & _
"tblDetails.[Size] AS [Size],IIf(tblDetails.[Size] = ""XS"",tblCartons.Qty,"""") AS XSQty, tblDetails.ItemNo as CustItem " & _
"FROM (tblHeader INNER JOIN tblDetails ON tblHeader.SO = tblDetails.SO) " & _
"INNER JOIN tblCartons ON (tblDetails.[LineNo] = tblCartons.[LineNo]) " & _
"AND (tblDetails.SO = tblCartons.SO) AND (tblDetails.ItemNo = tblCartons.ItemNo) " & _
WHERE (((tblHeader.SO) = '" & sOrder & "')) ORDER BY tblCartons.CartonNo"
Set rs = conn.Execute(sql)

 

[CaptainD]

Looks like you have double quotes. SQL Server does not like double quotes, you need to use " " with single quotes inside. try changing

IIf(tblDetails.[Size] = ""XS"",tblCartons.Qty,"""")

to

IIf(tblDetails.[Size] = "'XS'",tblCartons.Qty,"''")

just as you have it farther down

 WHERE (((tblHeader.SO) = '" & sOrder & "'))

 

[Vika]

CaptainD,

Yes I tried it. When you put ' (single quote) in front like this

"'XS'"

VB comments everything after the quote out.
But you are right it has something to do with SQL only liking single quotes and not double quotes. But the question is for is the right syntax to write it in VB?

Vika

 

[strongm]

Not

"'XS'" or "''"

just

'XS' and ''

 

[HarleyQuinn]

As strongm's post points out, if you're hardcoding the value in (and not inserting a variable into the sql string) you have no need to use the double quotes within at all.

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Vika]

I tried it with just single quotes and still getting the same runtime error,
"Line1: Incorrect syntax near '='". Any other suggestions?

conn.Open "Provider=sqloledb; Data Source=acmedc1; Initial Catalog=OrderChecking; Integrated Security=SSPI;"

sql = "SELECT tblHeader.PO, tblHeader.BillName, tblHeader.BillAddress1, tblHeader.BillAddress2, " & _
"tblHeader.BillAddress3, tblHeader.BillCity, tblHeader.BillState, tblHeader.BillZip, " & _
"tblHeader.ShipName, tblHeader.ShipAddress1, tblHeader.ShipAddress2, tblHeader.ShipAddress3, " & _
"tblHeader.ShipCity, tblHeader.ShipState, tblHeader.ShipZip, tblDetails.ItemNo, " & _
"tblHeader.StoreNo, tblDetails.[Desc], tblDetails.Color, tblDetails.Style, 1 AS CartonCount, " & _
"tblDetails.[Size] AS [Size],IIf(tblDetails.[Size] = 'XS',tblCartons.Qty,'') AS XSQty, tblDetails.ItemNo as CustItem " & _
"FROM (tblHeader INNER JOIN tblDetails ON tblHeader.SO = tblDetails.SO) " & _
"INNER JOIN tblCartons ON (tblDetails.[LineNo] = tblCartons.[LineNo]) " & _
"AND (tblDetails.SO = tblCartons.SO) AND (tblDetails.ItemNo = tblCartons.ItemNo) " & _
WHERE (((tblHeader.SO) = '" & sOrder & "')) ORDER BY tblCartons.CartonNo"
Set rs = conn.Execute(sql)

 

[gmmastros]

Provider = sqloledb

Are you using SQL Server?

The IIF syntax does not work for SQL Server.

Replace:
IIf(tblDetails.[Size] = 'XS',tblCartons.Qty,'') AS XSQty
With
Case When tblDetails.Size = 'XS' Then tblCartons.Qty Else '' End As XSQty

Also, if the tblCartons.QTY field is integer, then you should match the data types for example...

Case When tblDetails.Size = 'XS'
Then tblCartons.Qty
Else [!]0[/!] End As XSQty

Hope this help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vika]

I greatly appreciate your help, GMMASTROS!
It worked like a magic!

Thank you very much!

 

[gmmastros]

You're welcome. I'm glad to have helped.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom