Escaping in VBScript

[bobbyballgame]

I am having a problem calling Stored Procedures:

....
dim MyValue, MyOtherValue

MyValue = "Bobby's value"
MyOtherValue = Bobby's other value"

rs.Open "exec MyStoredProc """ & MyValue & """, """ & MyOtherValue & """",
Conn
' I have also tried the "'" & MyValue & "'" format as well
....

the problem appears to be related to the single quote, as ones without
quotes seemed to work. Is there escaping and is their URI escaping?

Thanks,BB

 

[Ray at]

http://www.aspfaq.com/2035

Ray at work

 

[Bob Barrows]

You can avoid all these delimiter-and-escaping headaches by using this
method to call your procedure:

Conn.MyStoredProc MyValue,MyOtherValue

HTH,
Bob Barrows
PS. Do not use this technique if you are planning to run this page via
dotnet.

 

[Cowboy \(Gregory A. Beamer\)]

All ' become '' (two single quotes)

MyValue = "Bobby's value"
MyOtherValue = Bobby's other value"

rs.Open "exec MyStoredProc """ & ConvertSingleQuotes(MyValue) & """, """ &
ConvertSingleQuotes(MyOtherValue) & """",
Conn
' I have also tried the "'" & MyValue & "'" format as well


Function ConvertSingleQuotes(InputVal)
ConvertSingleQuotes = Replace(InputVal,"'","''")
End Function

 

[bobbyballgame]

Bob,
Thanks, I would like to avoid delimiter, escaping, encoding, charsets -->
whatever I can. However, using your method it is not clear how you would get
the resultSet. Is there a way?

Thanks,
BB

 

[Bob Barrows]

I'm sorry. i showed you how to execute a non-records-returning procedure. Te
execute a procedure that returns records, first instantiate a recordset
object:
set rs=server.createobject("adodb.recordset")

then use the recordset variable as the last argument in the procedure call:
Conn.MyStoredProc MyValue,MyOtherValue, rs

HTH,
Bob Barrows