Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

asp ado sql server update a record

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
  • 2
  • 2
1 Solution
Scott Fell, EE MVEDeveloperCommented:
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
KeithMcElroyAuthor Commented:
are there constants I need to set.
adding the three args makes it not work.
KeithMcElroyAuthor Commented:
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
Scott Fell, EE MVEDeveloperCommented:
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 http://www.tek-tips.com/viewthread.cfm?qid=878887

It does take some more coding but this is one step of preventing sql injections.  A little helper guide on the type of parameters http://www.w3schools.com/ado/prop_para_type.asp

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 http://www.4guysfromrolla.com/webtech/042501-1.shtml

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

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")
      con.open data_source
' now you can execute your update query

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now