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

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-injection.htmlhttp://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.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 -

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"