Apostrophes in SQL Statements 2

[ndalli]

Hi,

I am opening a recordset containing a number of records through a sql statement:

strVariable = _
"SELECT * FROM CardTypeTable WHERE CardDescription = '" & CheckCardType & "'"
rsTemp.Open (strVariable)

It happens that the contents of the field CheckCardType may have words with an apostrophe. e.g. "VISA'S".

The above statements will give an error due to this apostrophe.

Can anyone please help out with this?

Thanks in advance

 

[TheInsider]

this is one of those pain in the *** situations where you have to parse the apostrophes before you can append the variable to the SQL string.

 

[addsd]

Hi,

Have small suggestion it may help.

Use Instr function to find out whether that coulmn conatin apostrophe. If so, discard that position before you assign to varibale. Try it!...

Thanks
Ahmed

 

[WilMead]

You say will give, not gave...
Are you sure the quoted apostrophe will cause a problem?

If you're sure, there must be a method to represent the apostrophe in your data:
Use the REPLACE function to substitute the proper apostrophe representative. For example two apostrophes or \' etc... Of course, applying the fix on your SQL statement ruins it. You have to apply the fix to your value sources when you build your statement.




Wil Mead
wmead@optonline.net

 

[brailian]

Use the replace function to replace single quotes with two single quotes on strings you wish to add to your SQL statement. Most SQL compilers will treat 2 quotes in a row as one.

For example:
UPDATE mytable SET myfield = 'isn''t'
will set myfield to the value "isn't"

Replace Example:
CheckCardType = Replace(CheckCardType, "'", "''&quot

This assumes CheckCardType is a variable and not a function. If it is a function, assign it to a variable, and then use Replace on the variable and add the variable to your SQL statement.

Hope this helps,
AJ

 

[chiph]

ndalli -

You can do as Brailian suggests and replace the single-quotes with two single-quotes:
[tt]' -> ''[/tt]

Don't forget to do the same for double-quote characters.
[tt]" -> ""[/tt]

Another way of doing this is to use ADO Parameter objects that are part of ADO Command objects. They take of this for you.
[tt]
sSQL = ""
sSQL = sSql & " SELECT *"
sSql = sSql & " FROM CardTypeTable"
sSql = sSql & " WHERE CardDescription=?"

adoCommand.CommandText = sSQL
adoCommand.Parameters.Append adoCommand.CreateParameter("CardDescription", adVarChar, adParamInput, 50, CheckCardType)

adoRS = adoCommand.Execute ,, adCmdtext
[/tt]

Chip H.

 

[chiph]

Darn word-wrap!

The CreateParamter statement ought to be on the same line as the Parameters.Append method call.

Chip H.

 

[iwebslinger]

Try this:

"SELECT * FROM Table WHERE field = '" _
& adhHandleQuotes(Variable, "'&quot

Here are the functions that go with it.


Function adhHandleQuotes(ByVal varValue As Variant, ByVal strDelimiter As String) As Variant



' Replace all instances of strdelimiter with varValue with TWO instances,
' thereby handling the darned quote issue once and for all.

' Returns Null if varValue was Null, otherwise
' returns varValue with all instances of strDelimiter
' replaced with two of each.
' adhHandleQuotes("This 'is' a test", "'&quot returns
' "This ''is'' a test"

adhHandleQuotes = adhReplace(varValue, strDelimiter, strDelimiter & strDelimiter)
End Function

Function adhReplace(ByVal varValue As Variant, ByVal strFind As String, ByVal strReplace As String) As Variant


Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer

If IsNull(varValue) Then
adhReplace = Null
Else
intLenFind = Len(strFind)
intLenReplace = Len(strReplace)

intPos = 1
Do
intPos = InStr(intPos, varValue, strFind)
If intPos > 0 Then
varValue = Left(varValue, intPos - 1) & strReplace & Mid(varValue, intPos + intLenFind)
intPos = intPos + intLenReplace
End If
Loop Until intPos = 0
End If
adhReplace = varValue
End Function

 

[jjwild]

We tried what Chip H wrote as follows:

Dim sSql As String
Dim adoCommand As New ADODB.Command
Dim adoRS As New ADODB.Recordset

sSql = "SELECT * FROM tSUEnt WHERE EntFormName = ?"

Set adoCommand = New ADODB.Command
Set adoCommand.ActiveConnection = gconDataConn
adoCommand.CommandText = sSql

adoCommand.Parameters.Append _
adoCommand.CreateParameter("strEntFormName", adVarChar, _
adParamInput, 30, strPassedEntFormName)

Set adoRS = adoCommand.Execute(, , adCmdText)

and adoRS.Recordcount was set to -1.

I tried changing the ? in sSQL to an actual value and commented out the .Parameters.Append line. We still received adoRS.Recordcount set to -1.

What did we do incorrectly?

 

[LazyMe]

Did you do a movelast before you peeked at the recordcount?

The recordcount property will initially be -1, that is undetermined. If you want to know if there are records in the resultset, you can better check if neither the BOF nor the EOF is true.
Greetings,
Rick

 

[jjwild]

I've had to do a MoveLast before checking the recordcount when going against a Jet database, but never with a SQL Server database which is what we are using.

Just to be sure, I tried it. MoveLast gave an error message about not being able to change the rowset (?) position. Apparently, the records retrieved in this way are restricted. MoveFirst and MoveNext were able to be done, but recordcount stayed at -1.

Thanks,

John

 

[SemperFiDownUnda]

Simply using the Replace() will surfice.

SQL Server does not need double quotes to be turned into two double quotes.

iwebslinger code, from glancing at it will do the same as the Replace function.

if you had a string that was
sTmp = "This is a test to see if three single quotes ''' will be handled properly"

and did
sTmp = REPLACE(sTmp,"'","''&quot
then sTmp would =
"This is a test to see if three single quotes '''''' will be handled properly"

which SQL server would then revert back to
"This is a test to see if three single quotes ''' will be handled properly"

you

 

[bros]

this solve in vb programming, but how about the crystal report??