Microsoft KB Archive/236126

= How to pass a single quotation mark in a string to a table in SQL Server by using SQL passthrough =

Article ID: 236126

Article Last Modified on 3/17/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 9.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 3.0 Standard Edition

-



This article was previously published under Q236126



SUMMARY
This article describes how to pass a single quotation mark in a string to a table in Microsoft SQL Server by using SQL passthrough together with a DSNless ODBC connection.



MORE INFORMATION
The following code example assumes that the Pubs database exists in a SQL Server data source that is named SQLSERVER. This example also assumes that the user has the correct permissions, IDs, and passwords.

This example does the following:
 * It creates a DSNless ODBC connection to the SQL Server data source. The connection is named TESTCONNECTION.
 * It creates a table that is named SINGLEQUOTE.
 * It inserts the string text into the table. A single quotation mark is embedded in the string.

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

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

IF xDSNLess > 0 *!* *CHAR(39) is a single quotation mark 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: kbhowto kbsqlprog KB236126

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.