Excel SQL INSERT Query needs to have strings with & in them

[starl]

Generating SQL Queries to add data to Quickbooks. Fairly standard queries, but some of the strings being added need '&' in the name (eg. Tom & Jerry).
I cannot figure out how - from Excel to a db - I can do this. I'm also using a 3rd party connector for this, so I cannot use the "set define on/off" stuff. I'm limited to making a change to the text in INSERT query that will translate properly when added to the database. For example, using Replace to replace the & with something else that turns into &.

 

[Micron]

try using Ascii code for ampersand

ASCII Table - ASCII Character Codes, HTML, Octal, Hex, Decimal

Ascii character table - What is ascii - Complete tables including hex, octal, html, decimal conversions

www.asciitable.com

EDIT - as in Chr(38)

 

[starl]

I tried that but either it didn't work or I had the syntax wrong (highly possible!)
What's the syntax for using ascii codes in an sql query?
full disclosure.. i'm self-taught on queries. thankfully, what i've had to do has been simple.

 

[Micron]

Concatenation. This might not be correct for your situation but along the lines of

"... WHERE myTable.myField = 'Tom " & Chr(38) & " Jerry' AND ..."

Output: ... WHERE myTable.myField = 'Tom & Jerry' AND ...

You should state what sql version this is for. I may be out to lunch if this is for TSql, PostgreSql and the like. I work pretty exclusively with Access sql so that's what my answer is based on. One thing you could do is write a sub, pass your concatenation to a string variable and debug.print the variable to see if it looks right. You should be able to then copy/paste into sql view and see if it runs.

 

[starl]

I have no idea what sql version or how to figure that out.. It's Excel to QB through CData. I can't find any info on their site about the version.
I've asked CData what to do - see if they get back to me. Else, I'll experiment with your suggestion next time I connect with the client.
Thanks!

 

[Micron]

Is there a reason why you haven't posted the sql code for anyone to attempt this? Maybe it's confidential?
I guess you can't just import or export. I have no experience with what you're working with & I assume QB means Quickbooks.

 

[starl]

It's not typical SQL code - bunch of class modules and functions building the Insert string. And I send it through a 3rd party api that then sends it to QB (yes, QuickBooks).

Here's a string that would generate an error because of the ampersand. I've highlighted the relevant in red. But, I realized after your last message I should contact CData (the 3rd party api). See - I send the sql to their api and I'm betting it breaks things down (notice all the fields, such as <Item Description>) .. though frankly, their function never handled apostrophe's which need special handling.. so I thought I could do the same (that I coded apostrophe's for) for ampersands.. but since it's within the field... I think I'm just confusing myself. oh - I contacted CData and I"m stuck talking to an entry level developer... he's not seeing the issue because he's not experienced with their library and the way they require me to set things up to add to the InvoiceLineItems library. So anyway - I've been hoping if I could learn the rule for ampersands in SQL, I might be able to make the change here. Such as using && would tell sql that it's a string & and not a directive. But Google didn't return anything I could use - everyone talks about using a Set xx Off and I cannot do that.

INSERT INTO Invoices (CustomerId, PONumber, Memo, Date, ShipDate, BillingLine1, BillingLine2, BillingLine3, BillingLine4,ShippingLine1, ShippingLine2, ShippingLine3, ShippingLine4, ItemAggregate) VALUES ('800004DE-1428956389', '', '6826', '1/5/2023', '', '5 Star Dairy', '8115- 147th SE', '', '', 'Eulyla Foster 24267 421 Ave Fulton SD 5','','','','<InvoiceLineItems><Row><ItemName>MXS:150 C BROILER- DUCK STARTER</ItemName><ItemDescription>[COLOR=rgb(209, 72, 65)][B](Tom & Jerry) 50lb Bags[/B][/COLOR]</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:150 C BROILER- DUCK STARTER</ItemName><ItemDescription>() 250# Plastic Tubs</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:MISC FORMULA</ItemName><ItemDescription>() 50lb Bags</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:MISC FORMULA</ItemName><ItemDescription>() 50lb Bags</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row></InvoiceLineItems>')

 

[Micron]

Sorry, out of my league. Judging by the <InvoiceLineItems><Row><ItemName> I'd say it's not Access sql.
Good luck!

 

[starl]

Sorry, out of my league. Judging by the <InvoiceLineItems><Row><ItemName> I'd say it's not Access sql.
Good luck!

no, it's not. Was hoping here was some kind of basic sql rule I could use.

 

[RoryA]

It looks to me like it's probably more of an XML issue than a SQL one? Have you tried replacing it with &?