sql=sql & "PhoneNumbe r='" & 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("A DODB.Connection ")
Set Command = CreateObject("A DODB.Command")
Conn.Open _
"Provider=SQLOL EDB;" & _
"Data Source=(local); " & _
"Integrated Security=SSPI;" & _
"Initial Catalog=Friends ContactInfo;" & _
"App=AspRun ner Professional Application"
Command.ActiveC onnection = Conn
Command.Command Text = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=? ," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"
Set parameter = Command.CreateP arameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"StreetAddress" , _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"PhoneNumbe r", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Paramet ers.Append parameter
Set parameter = Command.CreateP arameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Paramet ers.Append parameter
Command.Execute
Conn.Close
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <je****@excite. comwrote in message
news:11******** **************@ t69g2000cwt.goo glegroups.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=VBScri pt %>
<% 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="#FFFFF F">
<!--#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("I d")
Name = request.form("N ame")
StreetAddress = request.form("S treetAddress")
City = request.form("C ity")
State = request.form("S tate")
Zip = request.form("Z ip")
PhoneNumber = request.form("P honeNumber")
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.open =("DRIVER=SQL Server;SERVER=( local);UID=;APP =AspRunner
Professional
Application;WSI D=COMPAQAM;DATA BASE=FriendsCon tactInfo;Truste d_Connection=Ye s")
'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 & "PhoneNumbe r='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id
set oRS=Conn.Execut e (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>Th e 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='abou tus.htm'"><br>
<button onClick="window .location='menu 1_1.asp'">Updat e another
record</button>
</form>
</center>
</body>
</html>
