Article Link.

The other way to do it is to use the new "Quotename" function in SQL Server:

print quotename(@lastName, '''')

That's a little bit confusing, but what it returns is the value in @LastName with all of the apostrophe's escaped out with a double-apostrophe. You could also do this:

print quotename(@LastName, "'")

Quotename also does other interesting things, like this:

print quotename('This is a test', '{}')

will print
{This is a test}

Pretty nifty, eh?

rocketscientist

PLEASE HELP! I can't figure out exactly how to use your VB apostrophe fix. I keep getting this error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Jill O'Reilly','VISN3's Webmaster''.
/BegASPdatabases/chapter.10/nceconsult2.asp, line 314 (line 314 is the objCmd.Execute command!!!)

What am I doing wrong in my HTML page below):



<%
'Declare variables needed
Dim strInsert
Dim strValues
Dim adCmdText

'Set required variables
adCmdText = 1

'***********************************************************
'* If an Add was requested, add the new consult to the database
'***********************************************************

'change apostrophes to quote marks in text fields
Requestor_Name = replace (Requestor_Name,"'","''")
Requestor_Title = replace (Requestor_Title,"'","''")

If Request.Form("Action") = "Add" Then

'Start building the SQL strings with the required fields
strInsert = "Insert into NCEConsult (Requestor_Name,Requestor_Tel"
strValues = "Values('" & CStr(Request.Form("txtRequestorName")) & _
"','" & CStr(Request.Form("txtRequestorTel")) & "'"

'Add contact method if present
If Len(Request.Form("txtContactMethod")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",Contact_Method"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtContactMethod")) & "'"
End If

'Add requestor title if present
If Len(Request.Form("txtRequestorTitle")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",Requestor_Title"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtRequestorTitle")) & "'"
End If

'Add requestor address if present
If Len(Request.Form("txtRequestorAddress")) > 0 Then
'Add the column name to the insert string
strIns