|
 |
asp_databases thread: RE: Apostrophe issue
Message #1 by "Joshua Krause" <jkrause@t...> on Tue, 9 Apr 2002 12:26:38 -0400
|
|
Worked like a charm Owen thanks
Joshua
Tech #10190
High Speed Data Services
Time Warner Cable - Greensboro Division
----- Original Message -----
From: "Owen Mortensen" <ojm@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 08, 2002 8:57 PM
Subject: [asp_databases] RE: Apostrophe issue
You have to replace any single quotes (apostrophe's) with double single
quotes (in SQL, a single quote ends a string!). I see you're doing the
replace for sString, but you have to make sure none of the other fields
have a single quote in them, too.
I usually end up doing something like this:
rrRst.Fields("Account") = Replace(Request.Form("Account"), "'", "''")
Owen
-----Original Message-----
From: Joshua Krause [mailto:jkrause@t...]
Sent: Monday, April 08, 2002 11:46 PM
To: ASP Databases
Subject: [asp_databases] Apostrophe issue
I am having an issue with apostrophe's in all my text fields. If i
input
some text that has an apostrophe and then goto save my changes i get
this
error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC
Microsoft Access Driver] Syntax error (missing operator)
in query expression ''ZyXEL, not uBR - customer MUST arrange specific
date/time for change (cannot overlap IP's on ZyXEL). -stu', Pre_Conf_On
= '', Post_Conf_On = '', Pre_Call_Done = '', Post_Call_Done = '',
Updated_by = '', Date_Updated = '' WHERE ID = 28'.
/ipmigration/writeto.asp, line 23
This is my code:
Language="VBScript" %>
<%
Set RRConn = Server.CreateObject("ADODB.Connection")
RRConnstring = "DSN=Contact; UID=; PWD="
RRConn.Open(RRConnstring)
FUNCTION theFIX( theVariable )
theFIX=Replace(theVariable, "'", "´")
END FUNCTION
If Request.Form("Type") = "Delete" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sqlstring = "DELETE FROM [Contact] WHERE [ID] =" &
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
RRConn.Close
Response.Write "<center><b>Record was
deleted</center></b>"
End If
If Request.Form("Type") = "Modify" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sString = Request.Form("Notes")
sString = Replace(sString, "'", "''")
sqlstring = "UPDATE Contact SET Account = '" &
Request.Form
("Account") & "', Customer = '" & Request.Form("Customer") & "',
Street_Address = '" & Request.Form("Street_Address") & "', City = '" &
Request.Form("City") & "', State = '" & Request.Form("State") & "',
Zip_Code = '" & Request.Form("Zip_Code") & "', Phone = '" &
Request.Form
("Phone") & "', Batch = '" & Request.Form("Batch") & "',
Technical_Contact
= '" & Request.Form("Technical_Contact") & "', Email = '" & Request.Form
("Email") & "', Old_IP = '" & Request.Form("Old_IP") & "', New_IP = '" &
Request.Form("New_IP") & "', Subnet = '" & Request.Form("Subnet") & "',
Domain_Name = '" & Request.Form("Domain_Name") & "', DNS = '" &
Request.Form("DNS") & "', MAC_Addy = '" & Request.Form("MAC_Addy") &
"',
Node = '" & Request.Form("Node") & "', CMTS = '" & Request.Form("CMTS")
& "', GW_IP = '" & Request.Form("GW_IP") & "', Start = '" & Request.Form
("Start") & "', E_n_d = '" & Request.Form("E_n_d") & "', NAT_GW_IP = '"
&
Request.Form("NAT_GW_IP") & "', Static_NAT = '" & Request.Form
("Static_NAT") & "', Port_Fwds = '" & Request.Form("Port_Fwds") & "',
DHCP
= '" & Request.Form("DHCP") & "', Notes = '" & Request.Form("Notes") &
"',
Pre_Conf_On = '" & Request.Form("Pre_Conf_On") & "', Post_Conf_On = '" &
Request.Form("Post_Conf_On") & "', Pre_Call_Done = '" & Request.Form
("Pre_Call_Done") & "', Post_Call_Done = '" & Request.Form
("Post_Call_Done") & "', Updated_by = '" & Request.Form("Updated_by")
& "', Date_Updated = '" & Request.Form("Date_Updated") & "' WHERE ID = "
&
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
' Set MyRst = Nothing
RRConn.Close
'Set MyConn = Nothing
Response.Write "<b><center>Record was
modified</center></b>"
End If
If Request.Form("Type") = "Add" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sqlstring = "SELECT * FROM [Contact] "
rrRst.Open sqlstring, RRConn, 3, 3
rrRst.AddNew
rrRst.Fields("Account") = Request.Form("Account")
rrRst.Fields("Customer") = Request.Form("Customer")
rrRst.Fields("Street_Address") = Request.Form
("Street_Address")
rrRst.Fields("City") = Request.Form("City")
rrRst.Fields("State") = Request.Form("State")
rrRst.Fields("Zip_Code") = Request.Form("Zip_Code")
rrRst.Fields("Phone") = Request.Form("Phone")
rrRst.Fields("Batch") = Request.Form("Batch")
rrRst.Fields("Technical_Contact") = Request.Form
("Technical_Contact")
rrRst.Fields("Email") = Request.Form("Email")
rrRst.Fields("Old_IP") = Request.Form("Old_IP")
rrRst.Fields("New_IP") = Request.Form("New_IP")
rrRst.Fields("Subnet") = Request.Form("Subnet")
rrRst.Fields("Domain_Name")
Request.Form("Domain_Name")
rrRst.Fields("DNS") = Request.Form("DNS")
rrRst.Fields("MAC_Addy") = Request.Form("MAC_Addy")
rrRst.Fields("Node") = Request.Form("Node")
rrRst.Fields("CMTS") = Request.Form("CMTS")
rrRst.Fields("GW_IP") = Request.Form("GW_IP")
rrRst.Fields("Start") = Request.Form("Start")
rrRst.Fields("E_n_d") = Request.Form("E_n_d")
rrRst.Fields("NAT_GW_IP") = Request.Form("NAT_GW_IP")
rrRst.Fields("Static_NAT") = Request.Form("Static_NAT")
rrRst.Fields("Port_Fwds") = Request.Form("Port_Fwds")
rrRst.Fields("DHCP") = Request.Form("DHCP")
rrRst.Fields("Notes") = Request.Form("Notes")
rrRst.Fields("Pre_Conf_On")
Request.Form("Pre_Conf_On")
rrRst.Fields("Post_Conf_On")
Request.Form("Post_Conf_On")
rrRst.Fields("Pre_Call_Done") = Request.Form
("Pre_Call_Done")
rrRst.Fields("Post_Call_Done") = Request.Form
("Post_Call_Done")
rrRst.Fields("Updated_by") = Request.Form("Updated_by")
rrRst.Fields("Date_Updated")
Request.Form("Date_Updated")
rrRst.Update
rrRst.Close
'Set MyRst = Nothing
RRConn.Close
'Set MyConn = Nothing
Response.Write"<center><b>Record was added</b></center>"
End If
'MyConn.Close
'Set MyConn = Nothing
%>
<html>
<head>
<title>Write to DB</title>
</head>
<body bgcolor="#182831" text="#FFFFFF" link="#FFFF00" vlink="#FFFF00"
alink="#FF0000">
<p align="center">Please Follow Link back to welcome page </p> <p
align="center"> </p> <p align="center"><a
href="welcome.asp">Home</a></p>
</body>
</html>
Any help is always appreciated.
Message #2 by "Ken Schaefer" <ken@a...> on Tue, 9 Apr 2002 11:53:47 +1000
|
|
You ned to escape a single quote by doubling it:
http://www.adopenstatic.com/resources/code/SafeSQL.asp
www.adopenstatic.com/faq/80040e14.asp covers the actual error you are
receiving and why.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Joshua Krause" <jkrause@t...>
Subject: [asp_databases] Apostrophe issue
: I am having an issue with apostrophe's in all my text fields. If i input
: some text that has an apostrophe and then goto save my changes i get this
: error:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
: in query expression ''ZyXEL, not uBR - customer MUST arrange specific
: date/time for change (cannot overlap IP's on ZyXEL). -stu', Pre_Conf_On
: = '', Post_Conf_On = '', Pre_Call_Done = '', Post_Call_Done = '',
: Updated_by = '', Date_Updated = '' WHERE ID = 28'.
: /ipmigration/writeto.asp, line 23
:
: This is my code:
:
: Language="VBScript" %>
: <%
: Set RRConn = Server.CreateObject("ADODB.Connection")
: RRConnstring = "DSN=Contact; UID=; PWD="
: RRConn.Open(RRConnstring)
:
: FUNCTION theFIX( theVariable )
: theFIX=Replace(theVariable, "'", "´")
: END FUNCTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<snip>
Message #3 by "Owen Mortensen" <ojm@a...> on Mon, 8 Apr 2002 17:57:05 -0700
|
|
You have to replace any single quotes (apostrophe's) with double single
quotes (in SQL, a single quote ends a string!). I see you're doing the
replace for sString, but you have to make sure none of the other fields
have a single quote in them, too.
I usually end up doing something like this:
rrRst.Fields("Account") =3D Replace(Request.Form("Account"), "'", "''")
Owen
-----Original Message-----
From: Joshua Krause [mailto:jkrause@t...]
Sent: Monday, April 08, 2002 11:46 PM
To: ASP Databases
Subject: [asp_databases] Apostrophe issue
I am having an issue with apostrophe's in all my text fields. If i
input
some text that has an apostrophe and then goto save my changes i get
this
error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC
Microsoft Access Driver] Syntax error (missing operator)
in query expression ''ZyXEL, not uBR - customer MUST arrange specific
date/time for change (cannot overlap IP's on ZyXEL). -stu', Pre_Conf_On
=3D '', Post_Conf_On =3D '', Pre_Call_Done =3D '', Post_Call_Done =3D
'',
Updated_by =3D '', Date_Updated =3D '' WHERE ID =3D 28'.
/ipmigration/writeto.asp, line 23
This is my code:
Language=3D"VBScript" %>
<% =09
Set RRConn =3D Server.CreateObject("ADODB.Connection")
RRConnstring =3D "DSN=3DContact; UID=3D; PWD=3D"
RRConn.Open(RRConnstring)
=09
FUNCTION theFIX( theVariable )
theFIX=3DReplace(theVariable, "'", "=B4")
END FUNCTION
=09
If Request.Form("Type") =3D "Delete" Then
Set rrRst =3D Server.CreateObject("ADODB.Recordset")
sqlstring =3D "DELETE FROM [Contact] WHERE [ID] =3D" &
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
RRConn.Close
Response.Write "<center><b>Record was
deleted</center></b>"
End If
If Request.Form("Type") =3D "Modify" Then
Set rrRst =3D Server.CreateObject("ADODB.Recordset")
sString =3D Request.Form("Notes")
sString =3D Replace(sString, "'", "''")
sqlstring =3D "UPDATE Contact SET Account =3D '" &
Request.Form
("Account") & "', Customer =3D '" & Request.Form("Customer") & "',
Street_Address =3D '" & Request.Form("Street_Address") & "', City =3D
'" &
Request.Form("City") & "', State =3D '" & Request.Form("State") & "',
Zip_Code =3D '" & Request.Form("Zip_Code") & "', Phone =3D '" &
Request.Form
("Phone") & "', Batch =3D '" & Request.Form("Batch") & "',
Technical_Contact
=3D '" & Request.Form("Technical_Contact") & "', Email =3D '" &
Request.Form
("Email") & "', Old_IP =3D '" & Request.Form("Old_IP") & "', New_IP =3D
'" &
Request.Form("New_IP") & "', Subnet =3D '" & Request.Form("Subnet") &
"',
Domain_Name =3D '" & Request.Form("Domain_Name") & "', DNS =3D '" &
Request.Form("DNS") & "', MAC_Addy =3D '" & Request.Form("MAC_Addy") &
"',
Node =3D '" & Request.Form("Node") & "', CMTS =3D '" &
Request.Form("CMTS")
& "', GW_IP =3D '" & Request.Form("GW_IP") & "', Start =3D '" &
Request.Form
("Start") & "', E_n_d =3D '" & Request.Form("E_n_d") & "', NAT_GW_IP =3D
'"
&
Request.Form("NAT_GW_IP") & "', Static_NAT =3D '" & Request.Form
("Static_NAT") & "', Port_Fwds =3D '" & Request.Form("Port_Fwds") & "',
DHCP
=3D '" & Request.Form("DHCP") & "', Notes =3D '" & Request.Form("Notes")
&
"',
Pre_Conf_On =3D '" & Request.Form("Pre_Conf_On") & "', Post_Conf_On =3D
'" &
Request.Form("Post_Conf_On") & "', Pre_Call_Done =3D '" & Request.Form
("Pre_Call_Done") & "', Post_Call_Done =3D '" & Request.Form
("Post_Call_Done") & "', Updated_by =3D '" & Request.Form("Updated_by")
& "', Date_Updated =3D '" & Request.Form("Date_Updated") & "' WHERE ID
=3D "
&
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
' Set MyRst =3D Nothing
RRConn.Close
'Set MyConn =3D Nothing
Response.Write "<b><center>Record was
modified</center></b>"
End If
If Request.Form("Type") =3D "Add" Then
Set rrRst =3D Server.CreateObject("ADODB.Recordset")
sqlstring =3D "SELECT * FROM [Contact] "
rrRst.Open sqlstring, RRConn, 3, 3
rrRst.AddNew
rrRst.Fields("Account") =3D Request.Form("Account")
rrRst.Fields("Customer") =3D Request.Form("Customer")
rrRst.Fields("Street_Address") =3D Request.Form
("Street_Address")
rrRst.Fields("City") =3D Request.Form("City")
rrRst.Fields("State") =3D Request.Form("State")
rrRst.Fields("Zip_Code") =3D Request.Form("Zip_Code")
rrRst.Fields("Phone") =3D Request.Form("Phone")
rrRst.Fields("Batch") =3D Request.Form("Batch")
rrRst.Fields("Technical_Contact") =3D Request.Form
("Technical_Contact")
rrRst.Fields("Email") =3D Request.Form("Email")
rrRst.Fields("Old_IP") =3D Request.Form("Old_IP")
rrRst.Fields("New_IP") =3D Request.Form("New_IP")
rrRst.Fields("Subnet") =3D Request.Form("Subnet")
rrRst.Fields("Domain_Name") =3D
Request.Form("Domain_Name")
rrRst.Fields("DNS") =3D Request.Form("DNS")
rrRst.Fields("MAC_Addy") =3D Request.Form("MAC_Addy")
rrRst.Fields("Node") =3D Request.Form("Node")
rrRst.Fields("CMTS") =3D Request.Form("CMTS")
rrRst.Fields("GW_IP") =3D Request.Form("GW_IP")
rrRst.Fields("Start") =3D Request.Form("Start")
rrRst.Fields("E_n_d") =3D Request.Form("E_n_d")
rrRst.Fields("NAT_GW_IP") =3D Request.Form("NAT_GW_IP")
rrRst.Fields("Static_NAT") =3D Request.Form("Static_NAT")
rrRst.Fields("Port_Fwds") =3D Request.Form("Port_Fwds")
rrRst.Fields("DHCP") =3D Request.Form("DHCP")
rrRst.Fields("Notes") =3D Request.Form("Notes")
rrRst.Fields("Pre_Conf_On") =3D
Request.Form("Pre_Conf_On")
rrRst.Fields("Post_Conf_On") =3D
Request.Form("Post_Conf_On")
rrRst.Fields("Pre_Call_Done") =3D Request.Form
("Pre_Call_Done")
rrRst.Fields("Post_Call_Done") =3D Request.Form
("Post_Call_Done")
rrRst.Fields("Updated_by") =3D Request.Form("Updated_by")
rrRst.Fields("Date_Updated") =3D
Request.Form("Date_Updated")
rrRst.Update
rrRst.Close
'Set MyRst =3D Nothing
RRConn.Close
'Set MyConn =3D Nothing
Response.Write"<center><b>Record was added</b></center>"
=09
End If
'MyConn.Close
'Set MyConn =3D Nothing
%>
=09
=09
<html>
<head>
<title>Write to DB</title>
</head>
<body bgcolor=3D"#182831" text=3D"#FFFFFF" link=3D"#FFFF00"
vlink=3D"#FFFF00"
alink=3D"#FF0000">
<p align=3D"center">Please Follow Link back to welcome page </p> <p
align=3D"center"> </p> <p align=3D"center"><a
href=3D"welcome.asp">Home</a></p>
</body>
</html>
Any help is always appreciated.
Message #4 by "Joshua Krause" <jkrause@t...> on Mon, 8 Apr 2002 23:46:03
|
|
I am having an issue with apostrophe's in all my text fields. If i input
some text that has an apostrophe and then goto save my changes i get this
error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression ''ZyXEL, not uBR - customer MUST arrange specific
date/time for change (cannot overlap IP's on ZyXEL). -stu', Pre_Conf_On
= '', Post_Conf_On = '', Pre_Call_Done = '', Post_Call_Done = '',
Updated_by = '', Date_Updated = '' WHERE ID = 28'.
/ipmigration/writeto.asp, line 23
This is my code:
Language="VBScript" %>
<%
Set RRConn = Server.CreateObject("ADODB.Connection")
RRConnstring = "DSN=Contact; UID=; PWD="
RRConn.Open(RRConnstring)
FUNCTION theFIX( theVariable )
theFIX=Replace(theVariable, "'", "´")
END FUNCTION
If Request.Form("Type") = "Delete" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sqlstring = "DELETE FROM [Contact] WHERE [ID] =" &
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
RRConn.Close
Response.Write "<center><b>Record was deleted</center></b>"
End If
If Request.Form("Type") = "Modify" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sString = Request.Form("Notes")
sString = Replace(sString, "'", "''")
sqlstring = "UPDATE Contact SET Account = '" & Request.Form
("Account") & "', Customer = '" & Request.Form("Customer") & "',
Street_Address = '" & Request.Form("Street_Address") & "', City = '" &
Request.Form("City") & "', State = '" & Request.Form("State") & "',
Zip_Code = '" & Request.Form("Zip_Code") & "', Phone = '" & Request.Form
("Phone") & "', Batch = '" & Request.Form("Batch") & "', Technical_Contact
= '" & Request.Form("Technical_Contact") & "', Email = '" & Request.Form
("Email") & "', Old_IP = '" & Request.Form("Old_IP") & "', New_IP = '" &
Request.Form("New_IP") & "', Subnet = '" & Request.Form("Subnet") & "',
Domain_Name = '" & Request.Form("Domain_Name") & "', DNS = '" &
Request.Form("DNS") & "', MAC_Addy = '" & Request.Form("MAC_Addy") & "',
Node = '" & Request.Form("Node") & "', CMTS = '" & Request.Form("CMTS")
& "', GW_IP = '" & Request.Form("GW_IP") & "', Start = '" & Request.Form
("Start") & "', E_n_d = '" & Request.Form("E_n_d") & "', NAT_GW_IP = '" &
Request.Form("NAT_GW_IP") & "', Static_NAT = '" & Request.Form
("Static_NAT") & "', Port_Fwds = '" & Request.Form("Port_Fwds") & "', DHCP
= '" & Request.Form("DHCP") & "', Notes = '" & Request.Form("Notes") & "',
Pre_Conf_On = '" & Request.Form("Pre_Conf_On") & "', Post_Conf_On = '" &
Request.Form("Post_Conf_On") & "', Pre_Call_Done = '" & Request.Form
("Pre_Call_Done") & "', Post_Call_Done = '" & Request.Form
("Post_Call_Done") & "', Updated_by = '" & Request.Form("Updated_by")
& "', Date_Updated = '" & Request.Form("Date_Updated") & "' WHERE ID = " &
Request.Form("Contact")
rrRst.Open sqlstring, RRConn, 3, 3
' Set MyRst = Nothing
RRConn.Close
'Set MyConn = Nothing
Response.Write "<b><center>Record was
modified</center></b>"
End If
If Request.Form("Type") = "Add" Then
Set rrRst = Server.CreateObject("ADODB.Recordset")
sqlstring = "SELECT * FROM [Contact] "
rrRst.Open sqlstring, RRConn, 3, 3
rrRst.AddNew
rrRst.Fields("Account") = Request.Form("Account")
rrRst.Fields("Customer") = Request.Form("Customer")
rrRst.Fields("Street_Address") = Request.Form
("Street_Address")
rrRst.Fields("City") = Request.Form("City")
rrRst.Fields("State") = Request.Form("State")
rrRst.Fields("Zip_Code") = Request.Form("Zip_Code")
rrRst.Fields("Phone") = Request.Form("Phone")
rrRst.Fields("Batch") = Request.Form("Batch")
rrRst.Fields("Technical_Contact") = Request.Form
("Technical_Contact")
rrRst.Fields("Email") = Request.Form("Email")
rrRst.Fields("Old_IP") = Request.Form("Old_IP")
rrRst.Fields("New_IP") = Request.Form("New_IP")
rrRst.Fields("Subnet") = Request.Form("Subnet")
rrRst.Fields("Domain_Name") = Request.Form("Domain_Name")
rrRst.Fields("DNS") = Request.Form("DNS")
rrRst.Fields("MAC_Addy") = Request.Form("MAC_Addy")
rrRst.Fields("Node") = Request.Form("Node")
rrRst.Fields("CMTS") = Request.Form("CMTS")
rrRst.Fields("GW_IP") = Request.Form("GW_IP")
rrRst.Fields("Start") = Request.Form("Start")
rrRst.Fields("E_n_d") = Request.Form("E_n_d")
rrRst.Fields("NAT_GW_IP") = Request.Form("NAT_GW_IP")
rrRst.Fields("Static_NAT") = Request.Form("Static_NAT")
rrRst.Fields("Port_Fwds") = Request.Form("Port_Fwds")
rrRst.Fields("DHCP") = Request.Form("DHCP")
rrRst.Fields("Notes") = Request.Form("Notes")
rrRst.Fields("Pre_Conf_On") = Request.Form("Pre_Conf_On")
rrRst.Fields("Post_Conf_On") = Request.Form("Post_Conf_On")
rrRst.Fields("Pre_Call_Done") = Request.Form
("Pre_Call_Done")
rrRst.Fields("Post_Call_Done") = Request.Form
("Post_Call_Done")
rrRst.Fields("Updated_by") = Request.Form("Updated_by")
rrRst.Fields("Date_Updated") = Request.Form("Date_Updated")
rrRst.Update
rrRst.Close
'Set MyRst = Nothing
RRConn.Close
'Set MyConn = Nothing
Response.Write"<center><b>Record was added</b></center>"
End If
'MyConn.Close
'Set MyConn = Nothing
%>
<html>
<head>
<title>Write to DB</title>
</head>
<body bgcolor="#182831" text="#FFFFFF" link="#FFFF00" vlink="#FFFF00"
alink="#FF0000">
<p align="center">Please Follow Link back to welcome page </p>
<p align="center"> </p>
<p align="center"><a href="welcome.asp">Home</a></p>
</body>
</html>
Any help is always appreciated.
|
|
 |