473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Nov 12 '07 #1
6 13266
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
Nov 12 '07 #2
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"
Nov 12 '07 #3
On Nov 12, 5:03 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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,

Nov 13 '07 #4
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"
Nov 13 '07 #5
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"
Nov 13 '07 #6
On Nov 13, 5:22 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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 :)
Nov 14 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: David Shorthouse | last post by:
Hello folks, I have a problem with an update query on an asp not updating the table in an Access db. The code runs, I have no errors, but when I examine the table, nothing was updated. The query...
3
by: Bill Clark | last post by:
I have about 20,000 records pulled from Excel that I need to update. What I need to do is run an update query that bascially says: If a field is null, update it with the previous record value of...
5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
5
by: Andrew | last post by:
I've got a list box that selects a record on a subform, and in that subform are a few text fiels and a button that runs an update query. How do I have the update query button run and only update...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
2
by: bobabooey2k | last post by:
I have an update query with one field having in its "Update to" cell a DLookup statement. This query takes 2-3 minutes on 3000 records. Can I avoid dlookup here using multiple queries? An...
5
by: colleen1980 | last post by:
Hi: In my table there is a field of type checkbox. I create a button on my form and wants to deselect all the checkboxes in that field (PrintQueue). Table: Research_New PrintQueue Format Yes/No...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.