Inserting Rows that Contains an Apostrophe

Keywords:   apostrophe quote marks quotes

---

Question:

I am creating a DBaseIV table using the Microsoft .dbf ODBC Driver. In trying to Insert a row the program bombs when the data I am putting into column_1 includes an apostrophe. The column was set up as Char(50) when the Table was CREATED. I could remove all apostrophes but I would prefer to have the exact name print on invoices.

retcode = qExecDirect(hstmt,INSERT Into MyTable (Column_1) VALUES ('Joe's Bar and Grill'))

Answer:

Enclosed is a simple OLE script that creates and populates the dBASE IV file.

First, I just think OLE code is smaller in size and complexity, and since WB's OLE Extender parallels VB Script, you can use the VB trick of doubling quotes or apostrophes to get a single one in a field.

Just change the path variable to suit your situation, and this assumes you have MDAC 2.5 or 2.6 installed.

BoxOpen("Test Script","Creating/Inserting dBase IV")
cPath = "E:\WBDEMO\
If FileExist("%cpath%DBF.DBF")
   FileDelete("%cpath%DBF.DBF")
Endif
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cPath%;Extended Properties=dBASE IV"
DB  = ObjectOpen("ADODB.Connection")
DB.Open(cConn)
step=1
cSQL = "CREATE TABLE DB4 ( COLUMN_1 TEXT(50) );"
DB.Execute(cSQL)
;this works
;cSQL = "INSERT INTO DB4 ( COLUMN_1 ) VALUES ('Joes Bar and Grill');"

;or this to include apostrophe in field
var="Joe''s Bar and Grill"
cSQL = "INSERT INTO DB4 ( COLUMN_1 ) VALUES ('%var%');"
DB.Execute(cSQL)

DB.close()
ObjectClose(DB)
BoxShut()
exit

---

Article ID:   W14914