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="http://links.10026.com/?link=../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>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" <jeacdf@.excite.comwrote in message
news:1173540573.073247.128620@.t69g2000cwt.googlegr oups.com...
Quote:
Originally Posted by
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="http://links.10026.com/?link=../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>
>
