SQL UPDATE Query in ASP

[Nano]

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;"

Rs.Source="UPDATE Products SET 'Product Name' = @product_name"
Rs.open

%>

==================================================================================

It will be very kindful if someone help me with this.

Regards,
Hasnain Raja

 

[daddywhite]

Rs.Source="UPDATE Products SET [Product Name] = '" & product_name &
"'"
Rs.open

I think that should work. Unless soemthing else is wrng with it. Its
nice to Response.Write your SQL command to check that it would run
independantly in access anyway

 

[Bob Barrows [MVP]]

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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

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&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Nano]

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....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/microsoft.public.inetserver.asp.d...

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&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvO...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYx...

--
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"- Hide quoted text -

- Show quoted text -

Thanks Bob Barrows and daddywhite for your prompt reply, I really
appriciate this.

@Bob Barrows I have sent you an email regarding the problem kindly
reply if you get time.

Regards,

 

[Bob Barrows [MVP]]

Thanks Bob Barrows and daddywhite for your prompt reply, I really
appriciate this.

@Bob Barrows I have sent you an email regarding the problem kindly
reply if you get time.

You might as well save us both the time and post your question here. It is
very rare that I will respond to an emailed question from a newsgroup user.
The idea of using newsgroups is that everyone gets to benefit from the
answers to questions, not just the person asking it. Email definitely
defeats that purpose.

 

[Bob Barrows [MVP]]

Thanks Bob Barrows and daddywhite for your prompt reply, I really
appriciate this.

@Bob Barrows I have sent you an email regarding the problem kindly
reply if you get time.

Here is the emailed question:

have a database with following details
Table: Product
Columns: Product ID(Auto Number), Product Name(Text),
Product Category(Text)
I have created a form and have taken the values in variables.Let the
variables be:
Variables: product_name, product_cat
* I have not taken Product ID in variable since its Autonumber
Now on button click I want to as INSERT function, which insert new
values in database. Can you kindly inform me how should I
procede with it ?

This would be a minor variation of the code provided in the my initial
reply, using an INSERT statement rather than an UPDATE statement:

Dim cn, sql, cmd, arParms, product_name,product_cat
product_name="Hard Drive
product_cat = "some category"
arParms = Array(product_name, product_cat)
sql ="INSERT INTO Products ([Name],Category)" & _
" VALUES (?,?)"
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

 

[Nano]

Thanks Bob Barrows and daddywhite for your prompt reply, I really
appriciate this.

@Bob Barrows I have sent you an email regarding the problem kindly
reply if you get time.

Here is the emailed question:

have a database with following details
Table: Product
Columns: Product ID(Auto Number), Product Name(Text),
Product Category(Text)
I have created a form and have taken the values in variables.Let the
variables be:
Variables: product_name, product_cat
* I have not taken Product ID in variable since its Autonumber
Now on button click I want to as INSERT function, which insert new
values in database. Can you kindly inform me how should I
procede with it ?

This would be a minor variation of the code provided in the my initial
reply, using an INSERT statement rather than an UPDATE statement:

Dim cn, sql, cmd, arParms, product_name,product_cat
product_name="Hard Drive
product_cat = "some category"
arParms = Array(product_name, product_cat)
sql ="INSERT INTO Products ([Name],Category)" & _
" VALUES (?,?)"
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

--
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"

Thanks Bob, thanks for the help, its working fine.

And yes your idea of sharingn with the whole group is also
appreciative