Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL UPDATE Query in ASP

Reply
Thread Tools

SQL UPDATE Query in ASP

 
 
Nano
Guest
Posts: n/a
 
      11-12-2007
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

 
Reply With Quote
 
 
 
 
daddywhite
Guest
Posts: n/a
 
      11-12-2007
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


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-12-2007
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"


 
Reply With Quote
 
Nano
Guest
Posts: n/a
 
      11-13-2007
On Nov 12, 5:03 pm, "Bob Barrows [MVP]" <(E-Mail Removed)>
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:

>
> > ================================================== =========================*=======

>
> > <%@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-....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/...etserver.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&l...8&selm=e6lLVvO...
>
> http://groups.google.com/groups?hl=e...ff=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,

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-13-2007
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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-13-2007
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"


 
Reply With Quote
 
Nano
Guest
Posts: n/a
 
      11-14-2007
On Nov 13, 5:22 pm, "Bob Barrows [MVP]" <(E-Mail Removed)>
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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update the sql query data in the background silently? WuBin ASP .Net 2 08-21-2008 06:04 PM
unable to update multiple rows with sql query or trigger(URGENT) sanju ASP .Net 0 12-18-2006 09:18 AM
What Happened to Sql Enterprise Manager and Sql Query Analyser in Visual Studio 2005 Edward ASP .Net 4 04-10-2006 04:15 PM
Build dynamic sql query for JSTL <sql:query> Anonymous Java 0 10-13-2005 10:01 PM



Advertisments