Trouble with commas and quotes in a SQL Insert statement

[DreewToo]

Hi All,

I'm trying to perform a VB6 Insert to an Access database of text data, but have run up against a snag. Some of the data has embedded commas, and SQL is getting consufed by them. The executed SQL is like this:

INSERT INTO tbl_TCLEASES (status, bldgid, leasid, occpname, contname, phoneno1, phoneno2, name, address, address2, city, state, zipcode, attent, suitid1, tentid, ttypid, daydue, lateflat, occpstat, prtstat, user1, user2, user3, Execute, rentstrt, occupncy, begin, expir, optterm1, optamt1, leadtim1, optterm2, optamt2, leadtim2, optterm3, optamt3, leadtim3, permitcr, vacate, dba, ncdep, mgmtrate, laterate, latecalc, delqopt, delqday, intstart, lastint, lastdate, userid, maxlate, Expr1, basecpi, cpipcnt, billcpi, cpieffmo, cpitype, cpimonth, cpibasey, cpibasem, cpidt, ptwbase, ptwbasey, ptwtype, basecalc, currcalc, pwfactor, numcopy, county, intrate, leasstop, addrid, cancdate, excl5yr, profile, renewal, allowamt, lbsqtyid, cpifreq, cpiefday, cpiinidt, cpibasef, vendid) VALUES ('OK', '74TRIN', '553130', 'ZAMS TOO INT'L., INC. ', 'MR. ZAMS TOO ', '2125555555 ', '2125555555 ', 'ZAMS TOO INT'L., INC. ', '74 TRINITY PLACE ', '18TH FLOOR ', 'NEW
YORK ', 'NY ', '10006 ', 'ACCOUNTS PAYABLE DEPARTMENT ', '74TRIN18001', ' ', ' ', ' 1', ' 0', 'I', 'Y', ' 0 ', ' 0 ', ' 0 ', '19910207', '19910201', '19910201', '19910201', '19950131', '000', '00000000', '000', '000', '00000000', '000', '000', '00000000', '000', 'Y', '19941231', ' ', ' ', ' 0', ' 0', ' ', 'A', ' ', ' ', '970324', 'TM ', '1', '0 ', ' 14000', '08500', 'P', '09', 'NYNE', ' 6', '1990', '08', '9606', ' 0', ' ', ' ', ' ', ' ', ' 0', ' ', ' ', ' ', ' ', ' 0', 'N', ' ', ' ', 'N', ' ', 'N', ' ', ' ', ' 12', ' 1', ' ', ' ')

The code that creates this is :

strCommand = "INSERT INTO tbl_TCLEASES (status, bldgid, leasid, occpname, "
strCommand = strCommand & "contname, phoneno1, phoneno2, name, "
strCommand = strCommand & "address, address2, city, state, zipcode, "
strCommand = strCommand & "attent, suitid1, tentid, ttypid, daydue, " (...abbreviated...)

strCommand = strCommand & " VALUES ('" & new_third_Rec.status
strCommand = strCommand & "', " & "'" & new_third_Rec.bldgid
strCommand = strCommand & "', " & "'" & new_third_Rec.leasid
strCommand = strCommand & "', " & "'" & new_third_Rec.occpname
strCommand = strCommand & "', " & "'" & new_third_Rec.CONTNAME
strCommand = strCommand & "', " & "'" & new_third_Rec.PHONENO1
strCommand = strCommand & "', " & "'" & new_third_Rec.PHONENO1 (... abbreviated, you get the idea...)


It's the fourth value field that's presenting the problem in this case, "'ZAMS TOO INT'L., INC. '." Is there a way, without going thru each field previous to the insert and cutting out the commas and single quotes, to make this field 'insert-friendly?'

Thanx in advance for your help,
Drew

 

[MarkSweetland]

I don't think it's the commas that are causing the problem, but the single quotes that surround the values. The value in question contains a single quote which ends the embedded opening quote :

'ZAMS TOO INT'L., INC.

Instead, use double quotes and the single quotes or commas won't affect it (AND is your data doesn't have any double quotes embedded therein

VALUES ("OK", "74TRIN", "553130", "ZAMS TOO INT'L, INC" ..)

Mark

 

[DreewToo]

Thank you, Mark, your explanation makes a lot of sense.

In constructing the SQL string, would I use something like :

strCommand = strCommand & " VALUES (" & vbQuote & new_third_Rec.status & vbQuote
strCommand = strCommand & ", " & vbQuote & new_third_Rec.bldgid & vbQuote
strCommand = strCommand & ", " & vbQuote & new_third_Rec.leasid & vbQuote

All those quotes are getting me dizzy...but enclosing the type fields in double quotes seems like it would interfere with the statement construction.

 

[DreewToo]

Lol, answered my own question Chr(34) or a double set of quotation marks will do the trick...can I star myself for my answer? jk

 

[DreewToo]

Doesn't it just figure...

The data turns out to have an occasional double quote in the hundred thousand records...Just my luck.

 

[jkb3]

I have had luck with the following bit of VB code when I am accepting values from an ASP page:

If Not IsNull(vntData(0)) Then
vntData(0) = Replace(vntData(0), "'", "`", 1, -1, _ vbTextCompare)
End If


If you threw all that stuff into a staging table first and then cleaned it up prior to placing it in your final production table, you will probably end up with cleaner data in the long run.