ERROR: Collation conflict error for concatenation operation

[lwilly]

This is my sql statement:
stsql = "SELECT CO_NUMBER as CoNumber,CO_LN_NO as Line,COMP_WC as Parent,COMP_WC as Item, " & _
"ITEM_DESC as ItemDesc,QUANTITY as AuthorizedQuantity, COMP_PRICE as ListPrice, QUANTITY as OriginalQuantity FROM " & strKnzFSDataSQLPath & "FSExtract_HCPCFG WHERE CO_NUMBER = '" & _
txtWGSCONumber.Text & "'"

This works fine if I copy from the debug window and pasted into sql query analyzer. When run in VB app it gives me the error.

Any help would be appreciated.

 

[JBaileys]

I believe the last & is not necessary.

Secondly, I would suggest trying to eliminate the _& in VB and making the sql string one big long string and seeing if that eliminates your concatenation issue.

 

[lwilly]

I have already tried using a one long string with no sucess and I believe the last "&" is need to concatenate the closing ".

 

[lwilly]

Another clue in this puzzle is that when I replace CO_LN_NO with "001" it works fine.

 

[PCJock]

I assume that strKnzFSDataSQLPath is part of the path and is in the form of "[Server].[Database].[Owner]" , and the "FSExtract_HCPCFG" is the [TableName]. If so, you will have to add a period after [Owner] to complete the entire name of the table.

So the code should be :

 stsql = "SELECT CO_NUMBER as CoNumber,CO_LN_NO as Line,COMP_WC as Parent,COMP_WC as Item, " & _
"ITEM_DESC as ItemDesc,QUANTITY as AuthorizedQuantity, COMP_PRICE as ListPrice, QUANTITY as OriginalQuantity FROM " & strKnzFSDataSQLPath & ".FSExtract_HCPCFG WHERE CO_NUMBER = '" & _
txtWGSCONumber.Text & "'"

Also, if the CO_NUMBER variable is a number type you will not need to add the single quotes around it. SQL will probably convert it ok, but sometimes it can confuse things.

 

[lwilly]

Thanks for the response. The period is included in the path variable and CO_NUMBER is alpha numeric. Any other thoughts would be appreciated.

 

[PCJock]

I pasted your code into a vb form and could not find any error that jumps out at me. Perhaps it has to do with the way you set up the connection or called the query? What your doing seems simple enough, but I have never had this type of error before. I recommend posting this question to the VB group and see if they can help.