How do I concatenate two strings with the Jet.OLEDB.4.0 provider

Question

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

Answer 1

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]