Using Apostrophes in text boxes--not supported by SQL

Hello,

Anyone know a way to be able to use apostrophes in text boxes?

I'm using SQL server 2000, ASP and JScript/JavaScript but SQL doesn't support the character ' , it has to be replaced with ' ' for example:

==================
if (typeof Request.Form(txtRequestorName).Item == "string") {
str = Request.Form(txtRequestorName).Item.replace(/\'/g, "''")
} else {
str = ""
}
=================

Thank you,
Barnes

If you use the appropriate replace statement for the language that you are using, when inserting into the database, it should automatically only insert one single quote, and not two, so that you don't have to worry about two single quotes being stored in your database.

You HAVE TO REPLACE THEM WITH ''. There is no other way. Hopefully this helps.

Yes, I've been reading that, unfortunately, the replace() method is the only way to do this. But it gets tricky, at least for me...thank you for your help.

Has anyone successfully implemented code for the replace() method in JScript?

function replaceMe(myString)
{
var pattern = /\'/g;
var newString = myString.replace(pattern,"''");
}

By using this function, this should effectively take out all single quotes in your text boxes and replace them with two single quotes so that it will not interfer with your SQL statement.

Keep in mind this is a function and that it returns a value (specifically a string). You can use the function within the sql statement.

Hmm,

I must be using the function incorrectly:

-----------------------------------
function replace(txtRequestorName)
{
var pattern = /\'/g;
var newString = txtReqestorName.replace(pattern,"''");
}
---------------------

because I'm getting this error:

-------------------------
Microsoft OLE DB Provider for ODBC Driverserror '80040e14'


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'. /ProdEvalReq/ER_AddPost.asp, line 91
-------------------------

This tells me that SQL is not catching the apostrophe.

Here is how I have the code:
------------------------
<%
function replace(txtRequestorName)
{
var pattern = /\'/g;
var newString = txtReqestorName.replace(pattern, "''");
}

//create a database connection.
...

//Database.Open ...

...
%>
-----------------------------

Any ideas?

Okay, I'll look into why that isn't working for you...I'm not exactly the Javascript person, but I'll try to help you find the answers. Just as a question, why are you using Javascript for ASP? I know that it can be done, but ASP is usually programmed in VBScript, and there is a lot more documentation for VBScript ASP than Javascript. Just a thought.

Okay, try this function. Forgot that you have to explicitly tell it to return a value, my bad! Anyway, this should work for you though. Hope that it does!

function replace(txtRequestorName)
{
var pattern = /\'/g;
var newString = txtRequestorName.replace(pattern, "''");
return newString
}

Thank you so much for helping me with this!

You asked in your previous post why we are using JavaScript with ASP...mainly because we haven't adopted the VBScript language here and since the developers knew JavaScript, we decided to go with that.

The function you provided looks like it would work with the return. However, when I plugged it in, I received the same error. But it's probably because I neglected to include that this is a form and that the txtRequestorName is being called from a form. I should probably be using request.form("txtRequestorName").

I think we are very close here. I tried to use the following but received the error again.
-------------------
function replace(txtRequestorName)
{
var pattern = /\'/g;
var newString = request.form(txtRequestorName.replace(pattern, "''"));
return newString
}

------------------

Also, I realized that this function should work for all text boxes not just the txtRequestorName. So in that case, can we just say function replace(String)?:
--------------------------
function replace(String)
{
var pattern = /\'/g;
var newString = String.replace(pattern, "''");
return newString
}
--------------------------

I apologize for not "getting it" but again, this is new to me and I'm the RAD path.

Any additional help you can provide would be most appreciated.

Thank you!!
B~

Okay, you are passing the function a string, it doesn't make a difference what you want to call it in the function.

function replace(blah)
{
var pattern = /\'/g;
var newString = blah.replace(pattern, "''");
return newString
}

As you can see you can name it whatever you want for the function, but you MUST pass it a string:

Response.Write(replace(txtRequestorName))...or what ever you are using. it is a function, it returns a string value, and it has a string for an argument. Pass it the string you want to convert and it will return the converted string.

But then the error says that blah (or whatever is inthe parenthesis, in my case I named it string) is undefined.
-----------------
Microsoft JScript runtime error '800a1391'


'string' is undefined /ProdEvalReq/ER_AddPost.asp, line 89

Okay, try replace(Request.Form("txtRequestName")), or however you call a form field using Javascript in ASP. What that is telling you is that something is not being passed.

Ok, I'll try that.

Thanks again for all your help. I'll let you know once it's solved.

B~

I'm glad that I can help you (or at least try ). Let me know how it goes...I'm curious about how your going to get it all to work.

Funny thing--now I can't get the data to advance in the SQL table to see of the testing of the form works! UGH!!

What problem are you running into when trying to move from record to record?