' or " giving me problem

[FoxWing]

Hi,

I'm trying to execute a SP with parameters. All work fine until i put a ' or a " as part of the input field.


Example:

SQLStmt = "EXEC sp_master_no @acc_no = '3000/001'," + '@name = ' + "'" + Thisform.s_name.value + "'"

EXEC(1,SQLStmt) <-- Will fail if user key-in ' or " as part of the s_name textbox field.

What should i do here if i wich to allow the ' or " to be inserted into the MSSQL database ?

Thanks

 

[alvechurchdata]

You know you can use single and double quotes to mark strings in Fox but did you know you can use square brackets [ and ] as well. It allows you to use the other two types of quote in a string:

lcPhrase = [He said "I'm hungry"]

Not sure if it will help here but it's worth a try.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[FoxWing]

Geoff,

I had a go with ur appoarch, yes, it works for now.

Just curious, if i use your suggestion, there is no way system to accept [] as part of the string, right ?

lcPhrase = [He said "I'm hungry]"] <-- system don't like this one.

Thanks

 

[wgcs]

You can try a "parameterized query" instead. This simply means that VFP/ODBC is responsible for encoding the string so the server understands it, instead of you trying to figure out the appropriate use of quotes/escapes/etc:

lcAcc_No = '3000/001'
lcName   = Thisform.s_name.value 
SQLStmt  = "EXEC sp_master_no @acc_no = ?lcAcc_No, @name = ?lcName"
EXEC(1,SQLStmt)

The leading "?" alerts VFP/ODBC that what follows is a parameter name that must be bound by VFP calls to ODBC. VFP then matches the parameter name with a visible variable by the same name.
I can't promise this'll work: It does for SELECT/INSERT/DELETE queries, but I've never tried stored procedures.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[alvechurchdata]

Just curious, if i use your suggestion, there is no way system to accept [] as part of the string, right ?

Just the same as you can't have a single quote inside a string delimited with single quotes.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[wgcs]

On the other hand, with a parameterized query, there is no limit to what can be in the string.

- Bill

Get the best answers to your questions -- See FAQ481-4875.