SQL UPDATE Query in ASP

Discussion in 'ASP General' started by Nano, Nov 12, 2007.

  1. Nano

    Nano Guest

    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
    Nano, Nov 12, 2007
    #1
    1. Advertising

  2. Nano

    daddywhite Guest

    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
    daddywhite, Nov 12, 2007
    #2
    1. Advertising

  3. 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.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&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    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"
    Bob Barrows [MVP], Nov 12, 2007
    #3
  4. Nano

    Nano Guest

    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:

    >
    > > ===========================================================================­=======

    >
    > > <%@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,
    Nano, Nov 13, 2007
    #4
  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.
    >

    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"
    Bob Barrows [MVP], Nov 13, 2007
    #5
  6. 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"
    Bob Barrows [MVP], Nov 13, 2007
    #6
  7. Nano

    Nano Guest

    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 :)
    Nano, Nov 14, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonymous
    Replies:
    0
    Views:
    1,455
    Anonymous
    Oct 13, 2005
  2. sanju
    Replies:
    0
    Views:
    379
    sanju
    Dec 18, 2006
  3. WuBin
    Replies:
    2
    Views:
    789
    Jeff Dillon
    Aug 21, 2008
  4. Lisa

    SQL Update Query Error

    Lisa, Jul 24, 2003, in forum: ASP General
    Replies:
    4
    Views:
    150
  5. David Shorthouse

    Update query doesn't update table

    David Shorthouse, Jun 17, 2005, in forum: ASP General
    Replies:
    6
    Views:
    253
    David Shorthouse
    Jun 17, 2005
Loading...

Share This Page