Nano wrote:
I want to update a MS Access Table using ASP, I have made the
connection with the database but I am unable to update it. I am using
the following code:
================================================== ================================
<%@Language = VBScript %>
<% Option Explicit %>
<%
Dim Rs
dim product_name
product_name="Hard Drive"
Set Rs=Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection="Provider=Microsoft.Jet.OLEDB. 4.0;Data Source=C:
\Inetpub\wwwroot\MyWeb\db.mdb;"
This is a very bad practice. Setting Activeconnection to a string causes ADO
to create an implicit connection over which you have no control. This can
invalidate connection pooling and also lead to memory leaks in certain
situations.
Always create an explicit Connection object and use it to perform all your
database activities. Like this:
Dim cn
Set cn=creatobject("adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\MyWeb\db.mdb;"
>
Rs.Source="UPDATE Products SET 'Product Name' = @product_name"
You want to set ALL the records in Products to the SAME product name??? I
think you need a WHERE clause on this sql statement.
The other issue is that the column name should be bracketed, not quoted:
[Product Name]
Rs.open
Another bad practice:
1. using an expensive and unnecessary recordset object to execute a query
that does not return records. Instead, either use the Connection object's
Execute method, or explicitly create a Command object and use its Execute
method, in either case specifying the adExecuteNoRecords option to tell ADO
not to bother creating a recordset behind the scenes.
At least you are attempting to avoid using dynamic sql (the common term for
using string concatenation to create sql statements), the use of which can
leave you vulnerable to hackers using sql injection to attack your database
and website.:
http://mvp.unixwiz.net/techtips/sql-injection.html http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e
Applying this to your situation would yield:
*************************************************
Dim cn, sql, cmd, arParms, product_name
product_name="Hard Drive
arParms = Array(product_name)
sql ="UPDATE Products SET [Product Name] = ?"
Set cn=creatobject("adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\MyWeb\db.mdb;"
Set cmd=CreateObject("adodb.command")
With cmd
Set .ActiveConnection = cn
.CommandType=1 'adCmdText
.CommandText = sql
.Execute ,arParms, 128 '128=adExecuteNoRecords
End With
cn.Close
Set cn=nothing
*************************************************
Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:
Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl http://groups.google.com/groups?hl=e...tngp13.phx.gbl
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"