sinlge quotes replace problem

[Roy Adams]

Hi group I'm having trouble using the replace command
Here's my code below

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/conn.asp" -->

<%


if( String(Request.Form("ProductName")) != "undefined" ){//formfield
is not empty
var NavID = 1;

var ProductName = String(Request.Form("ProductName"));
var Price = String(Request.Form("Price"));
var Descript = String(Request.Form("Description"));
var ProductCode = String(Request.Form("ProductCode"));
//get the form fields and put into vars
var TableFields = "ProductName,Price,Description,NavID,ProductCode";

var FormFields = "'" + ProductName + "','" + Price + "','" + Descript
+ "','" + NavID + "','" + ProductCode+"'" ;

/// it works ok if i remove the replace
FormFields=FormFields.replace("'", "''");


conn = Server.CreateObject('ADODB.Command');

conn.ActiveConnection = conn_STRING;

conn.CommandText = ("insert into products ("+ TableFields +") values
('" + FormFields + ")" );


conn.Execute();
conn.ActiveConnection.Close();

}

%>

any suggestions?

 

[Slim]

Hi group I'm having trouble using the replace command
Here's my code below

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/conn.asp" -->

<%


if( String(Request.Form("ProductName")) != "undefined" ){//formfield
is not empty
var NavID = 1;

var ProductName = String(Request.Form("ProductName"));
var Price = String(Request.Form("Price"));
var Descript = String(Request.Form("Description"));
var ProductCode = String(Request.Form("ProductCode"));
//get the form fields and put into vars
var TableFields = "ProductName,Price,Description,NavID,ProductCode";

var FormFields = "'" + ProductName + "','" + Price + "','" + Descript
+ "','" + NavID + "','" + ProductCode+"'" ;

/// it works ok if i remove the replace
FormFields=FormFields.replace("'", "''");

try

FormFields=replace(FormFields,"'", "''");

 

[TomB]

This...
conn.CommandText = ("insert into products ("+ TableFields +") values
('" + FormFields + ")" );

Looks like it has an apostrophe right after the opening bracket.
You've replaced all of your form field delimiters with double apostrophes.
So your statement is going to look like.....
insert into products (ProductName,Price,Description,NavID,ProductCode)
values
(''productname'',''price'',''description'',''navid'',''productcode'')

I believe what you want to do is.....

Price.replace("'","''")
ProductName.Replace("'","''")
//ETC..

var FormFields = "'" + ProductName + "','" + Price + "','" + Descript
+ "','" + NavID + "','" + ProductCode+"'" ;

So that your result will look like
insert into products (ProductName,Price,Description,NavID,ProductCode)
values
('product''sname',price,'description',navid,'productcode')

Note the double apostrophe after the t in productsname. This will insert
product'sname into the ProductName field.
You'll also note that price has no apostrophes as I'm assuming that's a
number field.

 

[Roy]

Hi TomB
thanks for your help, you were exactly right,
but after i did that i found that it worked for the first single quote
but found now if a user inputs more than one single or double quote into
the text field it threw up errors again so i tried
ProductName=ProductName.replace(/'/g, "''");
which worked!!
cheers