sqlexec insert sql server single quote no like 2

[robsuttonjr]

I am using SQLEXEC to INSERT data into tables on a MS SQL 7 server. I am trying to figure out how to handle when a user enters special characters that will kick back an error. For example:

gcVendorName = "Rob's Stuff"
SQLEXEC(gnHandle, "EXEC sp_Insert_Customer '" +gcVendorName+ "'&quot

In the above example the gcVendorName has a single quote stored in it. Because of this it throws off sql server and kicks back an error. Is the solution to only allow alphanumeric data? How can I set the fields to only allow alphanumeric data? Is there a better way? Thanks!

 

[skuhlman]

this code should solve your problem:

MyString = "Rob's Stuff"
gcVendorName = strtran(MyString,"'","''&quot
SQLEXEC(gnHandle, "EXEC sp_Insert_Customer '" +gcVendorName+ "'&quot

The only thing added in is the STRTRAN() function to convert a single quote into two single quotes.

 

[Naoto]

Have you tried this:

gcVendorName = "Rob's Stuff"
SQLEXEC(gnHandle, "EXEC sp_Insert_Customer ?gcVendorName&quot

 

[danceman]

to pass through a single quote you need to add another quote to the string
strtran("single ' quote","'","''&quot
Attitude is Everything

 

[fluteplr]

Personally, I take a different tack.

Most of the time people are typing single quotes in strings what they really mean to type is an apostrophe. That is the [`] character that is usually to the left of the [1] key on most keyboards.

So in my textbox and editbox base classes when ever they type a single quote I replace it with an apostrophe. (See code below.)

They almost never notice, when they do I explain it to them and the database is happy.


Part of larger case statement in keypress event:

CASE m.nKeyCode = 39 AND m.nShiftAltCtrl = 0
IF !.ReadOnly
** Replace ' keystroke with ` keystroke because
** single quote marks cause problems in SQL Server.
**
NODEFAULT
KEYBOARD "`"
ENDIF