tags:

views:

604

answers:

1

I'm passing a query to an internal application that runs that query and returns the result, the connection is to a CSV file and I'm connecting with the Provider=Microsoft.Jet.OLEDB.4.0

I'd like to join to strings in to one column but I'm getting an error.

Can this be done, does anyone know how to do it?

Example of what I'm doing:

select 
 PurchaseOrderNo, 
 PurchaseOrderDate, 
 Description, 
 Quantity,
 ContractName + 'delimiter' + ContractNo as LinePrimaryKeys
from [POImport baseline.csv]

the error is: - Error - The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available, and the consumer had not yet set a new Double value.

From other reading it looks like not both of the values I'm joining are being recognized as strings.

for example replacing PurchaseOrderNo + 'delimiter' + ContractNo as LinePrimaryKeys with PurchaseOrderNo + 'delimiter' + PurchaseOrderNo as LinePrimaryKeys

stops the error. So now how do I Cast to string?

This doesn't work. ContractName + 'cn' + CAST(ContractName as nvarchar(50)) as LinePrimaryKeys

+2  A: 

You have to use & instead of +.

& does a string concatenation, + performs a (numeric) addition. Using & automatically casts all operands to strings.

select 
 PurchaseOrderNo, 
 PurchaseOrderDate, 
 Description, 
 Quantity,
 PurchaseOrderNo & 'delimiter' & ContractNo as LinePrimaryKeys
from [POImport baseline.csv]
DR
You get the error: - Error - An error occurred while parsing EntityName
Jiminy
That is an XML/XHTML error message. What are you doing :)
DR
You might be right, it looks like this error could be coming from the XML file I'm adding the query to.
Jiminy
DR
Thanks DR, that worked a treat
Jiminy