SQL UPDATE Query in ASP

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

Re: SQL UPDATE Query in ASP

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

Re: SQL UPDATE Query in ASP

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/microsoft.public.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.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"

Re: SQL UPDATE Query in ASP

On Nov 12, 5:03 pm, "Bob Barrows [MVP]"
wrote:
> 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:
>
> > ==================== ===3D=
==================== =====3D=
==================== =====3D=
==­=======
>
> > <%@Language =3D VBScript %>
> > <% Option Explicit %>
>
> > <%
>
> > Dim Rs
>
> > dim product_name
> > product_name=3D"Hard Drive"
>
> > Set Rs=3DServer.CreateObject("ADODB.Recordset")
> > Rs.ActiveConnection=3D"Provider=3DMicrosoft.Jet.OLEDB.4.0;Da ta Source=
=3DC:
> > \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=3Dcreatobject("adodb.connection")
> cn.open "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
> "Data Source=3DC:\Inetpub\wwwroot\MyWeb\db.mdb;"
>
>
>
> > Rs.Source=3D"UPDATE Products SET 'Product Name' =3D @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 A=
DO
> not to bother creating a recordset behind the scenes.
>
> At least you are attempting to avoid using dynamic sql (the common term f=
or
> using string concatenation to create sql statements), the use of which can
> leave you vulnerable to hackers using sql injection to attack your databa=
se
> and website.:http://mvp.unixwiz.net/techtips/sql-injection.htmlh ttp://www=
..sqlsecurity.com/DesktopDefault.aspx?tabid=3D23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:http://groups-beta.google.com/group/microsoft.public .in=
etserver.asp.d...
>
> Applying this to your situation would yield:
>
> *************************************************
> Dim cn, sql, cmd, arParms, product_name
> product_name=3D"Hard Drive
> arParms =3D Array(product_name)
> sql =3D"UPDATE Products SET [Product Name] =3D ?"
> Set cn=3Dcreatobject("adodb.connection")
> cn.open "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
> "Data Source=3DC:\Inetpub\wwwroot\MyWeb\db.mdb;"
> Set cmd=3DCreateObject("adodb.command")
> With cmd
> Set .ActiveConnection =3D cn
> .CommandType=3D1 'adCmdText
> .CommandText =3D sql
> .Execute ,arParms, 128 '128=3DadExecuteNoRecords
> End With
> cn.Close
> Set cn=3Dnothing
> *************************************************
>
> Personally, I prefer using stored procedures, or saved parameter queries
> as
> they are known in Access:
>
> Access:http://www.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8 &oe=3DUTF-8&s=
elm=3De6lLVvO...
>
> http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&c2c off=3D1&selm=
=3DeHYx...
>
> --
> 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,

Re: SQL UPDATE Query in ASP

Nano wrote:
>
> 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.
--
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"

Re: SQL UPDATE Query in ASP

Nano wrote:
> 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"

Re: SQL UPDATE Query in ASP

On Nov 13, 5:22 pm, "Bob Barrows [MVP]"
wrote:
> Nano wrote:
> > 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 :)