asp ado sql server update a record

Posted on 2012-09-02
Last Modified: 2012-09-04
The below code fails using either the rs.update method and a select or the UPDATE sql
I get a return if Icomment out the update script so I know the record exisits.

How can I debug this?  Is there an err object?
What security considerations do I need to be aware of?
I am not the dba so any questions I can ask would be helpful.

      ' Set datasource
      data_source = "Provider=SQLOLEDB;Server=erpdb\;Database=production_finance;UID=SQLAdmin;PWD=;"

      ' Set sql query string
      sql_query = "SELECT TOP (1) * FROM pe_name_mstr  WHERE  unique_key =  '" &  Request("unique_key") & "'"
      sql_query = " UPDATE pe_name_mstr SET pe_url = 'test3'  WHERE  unique_key =  '" &  Request("unique_key") & "'"
      ' Create Connection Object & Recordset Object and open the database
      set con = Server.CreateObject("ADODB.Connection")
      'set rs = Server.CreateObject("ADODB.Recordset")

'      rs.Open sql_query, data_source
'      rs.moveFirst
'      rs.update("pe_url", "tst2")
      response.write "ADO Record updated "
      Set con = Nothing
Question by:KeithMcElroy
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    to use rs.update, I think you need to change the cursor type to dynamic.

    set conn=Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.recordset")
    sql="SELECT * FROM Customers"

    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockBatchOptimistic

    rs.Open sql, conn

    Author Comment

    are there constants I need to set.
    adding the three args makes it not work.

    Author Comment

    I figured out the cursor constant as follows, same neg results...

          ' Set datasource
          data_source = "Provider=SQLOLEDB;Server=erpdb\ONESolution;Database=production_finance;UID=;PWD=;"

          ' Set sql query string
          sql_query = "SELECT TOP (1) * FROM pe_name_mstr  WHERE  unique_key =  '" &  Request("unique_key") & "'"
          'sql_query = " UPDATE pe_name_mstr SET pe_url = 'test3'  WHERE  unique_key =  '" &  Request("unique_key") & "'"
          ' Create Connection Object & Recordset Object and open the database
          set con = Server.CreateObject("ADODB.Connection")
          set rs = Server.CreateObject("ADODB.Recordset")

    const adOpenStatic = 3

    'rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    'rs.LockType = adLockBatchOptimistic

          rs.Open sql_query, data_source
          rs.update("pe_url", "tst2")
          response.write "ADO Record updated " & rs("pe_url")
          Set con = Nothing
    LVL 52

    Accepted Solution

    Take a look at this example from 4guys

    Session("DatabasePath") = "C:\WINNT\Profiles\cmiller\Desktop\hooray\companyx.mdb"
    '-- Declare your variables
    Dim DataConnection, cmdDC, RecordSet
    Dim RecordToEdit, Updated
    '-- Create object and open database
    Set DataConnection = Server.CreateObject("ADODB.Connection")
    DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"
    Set cmdDC = Server.CreateObject("ADODB.Command")
    cmdDC.ActiveConnection = DataConnection
    '-- default SQL
    SQL = "SELECT * FROM tblEmployees"
    cmdDC.CommandText = SQL
    Set RecordSet = Server.CreateObject("ADODB.Recordset")
    '-- Cursor Type, Lock Type
    '-- ForwardOnly 0 - ReadOnly 1
    '-- KeySet 1 - Pessimistic 2
    '-- Dynamic 2 - Optimistic 3
    '-- Static 3 - BatchOptimistic 4
    RecordSet.Open cmdDC, , 0, 2
    If Request.Form("btnAdd") = "Add" Then
    RecordSet.Fields("Name") = Request.Form("txtName")
    RecordSet.Fields("Title") = Request.Form("txtTitle")
    RecordSet.Fields("Department") = Request.Form("txtDepartment")
    Added = "True"
    End If

    Open in new window

    Since it looks like you are taking user input, that way to update a table may be easy and not much extra coding, but it does leave you open to sql injection.  You should really use a paramater input and query.

    There is a good example here

    It does take some more coding but this is one step of preventing sql injections.  A little helper guide on the type of parameters

    To break down the below code from the link I provided, the first 19 rows should look familiar.  You are creating a connection and writing sql for an update.   Then all the lines below are ensuring the proper input.  

    Example:     cmd.Parameters.Append cmd.CreateParameter("@First_Name",129,1,30, request.Form("txtFirst_Name"))

    for every update, you can leave this part the same, "cmd.Parameters.Append cmd.CreateParameter"

    Inside the parenthesis, the @First_Name can be whatever you want to call it.  It is also common here to use @p1, @p2.... for each row.  The next number is the data type.  Using the w3schools link you can look up and see 129 is a string.  then the 30 represents the maximum number of characters.  For numeric data types you can leave that as 1as in "@qty",5,1,1, request.Form("qty")) where the 5 represents a double and the 1 represents unlimited character.  

    What will happen if you mark a field as a double and text is updated, it wil throw an error.  Or if you if you set a string for 30 length and 31 characters are submitted, it will throw an error.

    I still would not allow a simple request.form("somevalue") to be entered to the db though and suggest some scrubbing.

    You can escape single and double quotes with this function

    FUNCTION PreSubmit(p_sTargetString)
    PreSubmit = REPLACE(p_sTargetString,”textarea”,”")
    PreSubmit = REPLACE(PreSubmit,”%”,”%”)
    PreSubmit = REPLACE(PreSubmit,”‘”,”’”)
    PreSubmit = REPLACE(PreSubmit,”"”",”””)
    PreSubmit = Trim(PreSubmit)

    Open in new window

    And if you are not using a cms, you should also strip out html

    set cnn = server.CreateObject("ADODB.Connection")
         cnn.Open cnnstr()
        set cmd = server.CreateObject("ADODB.Command")
        cmd.ActiveConnection = cnn
        cmd.CommandText = _
            "UPDATE Users1 SET First_Name = @First_Name, " & _
            "Last_Name = @Last_Name, HPhone = @HPhone, " & _
            "WPhone = @WPhone, Cell_Phone = @Cell_Phone, " & _
            "Email = @Email, Move_Date = @Move_Date, " & _
            "[Password] = @Password, HearAbout = @HearAbout, " & _
            "Metro_Area = @Metro_Area, Area_Town = @Area_Town, " & _
            "Beds = @Beds, Price = @Price, Street_Address = @Street_Address, " & _
            "City = @City, State = @State, Zip_Code = @Zip_Code, " & _
            "SalesPerson = @SalesPerson, PropertyViewed = @PropertyViewed, " & _
            "Status = @Status, Curr_Date = @Curr_Date, Curr_Time = @Curr_Time, " & _
            "CreateDate = @CreateDate, Notes = @Notes " & _
            "WHERE UserID = " & request.Form("txtUserID")    
        cmd.CommandType = adCmdText 
        cmd.Parameters.Append cmd.CreateParameter("@First_Name",129,1,30, request.Form("txtFirst_Name")) 
        cmd.Parameters.Append cmd.CreateParameter("@Last_Name",129,1,30, request.Form("txtLast_Name"))
        cmd.Parameters.Append cmd.CreateParameter("@HPhone",129,1,15, request.Form("txtHPhone"))
        cmd.Parameters.Append cmd.CreateParameter("@WPhone",129,1,15, request.Form("txtWPhone"))
        cmd.Parameters.Append cmd.CreateParameter("@Cell_Phone",129,1,15, request.Form("txtCell_Phone"))
        cmd.Parameters.Append cmd.CreateParameter("@Email",129,1,70, request.Form("txtEmail"))
        cmd.Parameters.Append cmd.CreateParameter("@Move_Date",129,1,, request.Form("txtMove_Date"))
        cmd.Parameters.Append cmd.CreateParameter("@Password",129,1,20, request.Form("txtPassword"))
        cmd.Parameters.Append cmd.CreateParameter("@HearAbout",,1,, request.Form("cboHear_About"))
        cmd.Parameters.Append cmd.CreateParameter("@Metro_Area",129,1,20, request.Form("txtMetro_Area"))
        cmd.Parameters.Append cmd.CreateParameter("@Area_Town",,1,, request.Form("cboAreaTown"))
        cmd.Parameters.Append cmd.CreateParameter("@Beds",,1,, request.Form("cboBeds"))
        cmd.Parameters.Append cmd.CreateParameter("@Price",,1,, request.Form("cboPrice"))
        cmd.Parameters.Append cmd.CreateParameter("@Street_Address",129,1,150, request.Form("txtStreet_Address"))
        cmd.Parameters.Append cmd.CreateParameter("@City",129,1,30, request.Form("txtCity"))
        cmd.Parameters.Append cmd.CreateParameter("@State",129,1,2, request.Form("txtState"))
        cmd.Parameters.Append cmd.CreateParameter("@Zip_Code",129,1,10, request.Form("txtZip_Code"))
        cmd.Parameters.Append cmd.CreateParameter("@SalesPerson",129,1,50, request.Form("txtSalesPerson"))
        cmd.Parameters.Append cmd.CreateParameter("@PropertyViewed",129,1,50, request.Form("txtPropertyViewed"))
        cmd.Parameters.Append cmd.CreateParameter("@Status",129,1,15, request.Form("cboStatus"))
        cmd.Parameters.Append cmd.CreateParameter("@Curr_Date",133,1,, request.Form("txtCurr_Date"))
        cmd.Parameters.Append cmd.CreateParameter("@Curr_Time",137,1,, request.Form("txtCurr_Time"))
        cmd.Parameters.Append cmd.CreateParameter("@CreateDate",,1,, request.Form("txtCreateDate"))
        cmd.Parameters.Append cmd.CreateParameter("@Notes",129,1,, request.Form("txtNotes"))

    Open in new window

    LVL 82

    Expert Comment

    The comment on your initial code states:
     ' Create Connection Object & Recordset Object and open the database
          set con = Server.CreateObject("ADODB.Connection")
    but you are NOT opening the db before executing:

    What you needed to do was:

     ' Create Connection Object & Recordset Object and open the database
          set con = Server.CreateObject("ADODB.Connection")
    ' now you can execute your update query

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now