March 15, 2002 at 9:05 am
Hai All,
I taking the details of employees through VB6 SQL Server2000 application using ADO 2.6,
I want to search the database by address. I passed the parameters to stored procedure but
if the parameter contains a (') I get error. Is there a work around. I am facing the same error
when doing the insert.
Code given below.
declare @sql nvarchar(4000)
set @sql=N'select * from emp where employee_address=''' (plus) cast (@address as nvarchar) (plus) '''
exec sp_executesql @sql
**(plus) means plus sign
error is
Unclosed quotation mark before the character string ''.
March 15, 2002 at 9:52 am
March 15, 2002 at 12:42 pm
More specifically you have to do this to the variable on the VB side.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 21, 2002 at 1:03 am
Whenever faced with this problem, I always wrap the variables up in double apostrophe's.
Here is some code that you can use a library function on the vb side:
Public Function DoubleApostrophe(ByVal sBr)
Dim lPos 'As Long
Dim sBl 'As String
If Len(sBr) = 0 Then Exit Function
lPos = InStr(sBr, Chr(39))
While lPos <> 0
sBl = sBl & Left(sBr, lPos) & Chr(39)
sBr = Right(sBr, Len(sBr) - lPos)
lPos = InStr(sBr, Chr(39))
Wend
DoubleApostrophe = sBl & sBr
End Function
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply