By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,719 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,719 IT Pros & Developers. It's quick & easy.

Incorrect syntax near the keyword 'WHERE'.

P: n/a
Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@ Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez --->
<link rel="stylesheet" type="text/css" href="../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display --->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>

Mar 10 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
sql=sql & "PhoneNumber='" & PhoneNumber & "',"

It looks like the syntax error is due to the extraneous comma after the last
column.

I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:

Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200

Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")

Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"

Command.ActiveConnection = Conn

Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"

Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter

Command.Execute

Conn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DaveF" <je****@excite.comwrote in message
news:11**********************@t69g2000cwt.googlegr oups.com...
Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@ Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez --->
<link rel="stylesheet" type="text/css" href="../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display --->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>
Mar 10 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.