HOWTO: Passing Single Quotes in a String to SQL Server using SQL Passthrough

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0

SUMMARY

This article describes how to pass a single quote in a string to a SQL Server table using SQL passthrough with a DSNless ODBC connection.

MORE INFORMATION

The following example assumes that the Pubs database exists in a SQL Server datasource called SQLSERVER. The example creates a DSNless ODBC connection (TESTCONNECTION) to the SQL Server datasource, creates a table named SINGLEQUOTE, and inserts the string text into the table with the single quote imbedded in the string.

*!* Start of the program
SET SAFETY OFF
CLEAR
CLOSE DATA ALL
CREATE DATABASE test

*!* DSNless Connection to a SQL Server database
*!* In the following statement "DBMSSONCN" is the TCP/IP library name
CREATE CONNECTION testconnection CONNSTRING ;
    "driver={sql server};server=SQLSERVER;uid=sa;pwd=;
     database=pubs;network=dbmssocn"
xDSNLess = SQLCONNECT("testconnection")

IF xDSNLess > 0
    *!*	*CHAR(39) is a single quote or apostrophe in SQL Server
    SQLEXEC(xDSNLess, "DROP TABLE singlequote")
    SQLEXEC(xDSNLess, "CREATE TABLE singlequote (singlequote char(30))")
    SQLEXEC(xDSNLess, "INSERT INTO singlequote ;
       VALUES ('single'+char(39)+'quotes '+char(39)+'in the '+;
       char(39)+ 'string')")
    SQLEXEC(xDSNLess, "SELECT * from singlequote")
    SQLEXEC(xDSNLess, "DROP TABLE singlequote")
    BROWSE NOWAIT
ENDIF

SQLDISCONNECT(xDSNLess)
SET SAFETY ON
*!* End of the program 

Additional query words: KBDSE

Keywords : kbSQL kbVFp300 kbVFp300b kbVFp500 kbVFp500a kbVFp600 kbGrpFox kbDSupport
Version : WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Platform : WINDOWS
Issue type : kbhowto