AnsweredAssumed Answered

calculated sql inserts and commas, quotes, etc

Question asked by arosario on Jan 18, 2012

Title

calculated sql inserts and commas, quotes, etc

Post

I have a script I've been running for some time but it blows up when the address or name fileds contain commas, ampersands,  or quotes in them (123 MAIN, APT 10, Schwab & CO, etc) due to the way the insert statement is built. I just monitor and take them out but some of our customers may be offended that we are changing their name. Is there a way around this??? 

Script Segment:

"INSERT INTO Customer

(Account, CustName, Address1, Address2, City, State, Zip, Phone, Fax,CreditHoldFlag,DefaultTaxJurisdiction, BillName, BillAddress1, BillAddress2, BillCity, BillState, BillZip, BillContact, ActiveFlag, SalesRepCode,csr, notes, Status, paymentmethod, TermsCode, BillAddress3)
VALUES ("

&"'"&Companies::EN_ID_Company&"'"&","
&"N'"&Companies::Company&"'"&","
&"N'"&Companies::Address_1&"'"&","
&"N'"&Companies::Address_2&"'"&","
&"N'"&Companies::City&"'"&","
&"N'"&Companies::State&"'"&","
&"'"&Companies::EN_Zip_Calc&"'"&","
&"'"&Companies::Phone&"'"&","
&"'"&Companies::Fax&"'"&","

Outcomes