Record Set

[tekyge]

I am trying to update a record in an access database with the following code but I keep getting and error?

'--------
sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]=" &Trim(session("myvar111"))
rs.open sql, conn, 3, 3
'--
strPassword=Trim(Request.Form("password"))
'--
rs.Edit
rs("Password")=strPassword
rs.Update
rs.Close

'-------

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Edit'

/cs/login/ma/processing.asp, line 109

 

[tekyge]

I got it.

sql = "SELECT * FROM [Clients] WHERE [Clients].[ClientID]=" &Trim(session("myvar111"))
rs.open sql, conn, 3, 3
rs("Password")=Trim(Request.Form("password"))
rs.Update
rs.Close

 

[Tarwn]

A couple comments:
Right now you are downloading the entire record to ASP (incuding having ASP create Recordset objects, etc) just to update a single field. On top of that, when you select * you force the database to, in essence, do two queries. It has to first query to see what the fieldnames are for the table or tales your asking for, then it has to execute the query your asked for. For future reference, listing all the field only takes a minute or so while your programming, but puts less load on the database.

A better method of updating this password would be to use a SQL UPDATE statement to change the field value. It exists for things like this. Basically you would alter your existing code to do somehting like:

'you may have already done this
Set conn = Server.CVreateObject("ADODB.Connection")
conn.Open "connection string"

'new SQL statement
sql = "UPDATE [Clients] SET password = '" & Trim(Request.Form("password")) & "' WHERE [ClientID] = " & Trim(Session("myvar111"))

'execute SQL statement
conn.Execute sql

The other thing you should consider doing is creating a function to clean up values before trying to insert them. One of the most common methods of hacking a form (or inadvertantly breaking a form) is entering a value with a single quote in it. Since the single quote is the character used around strings, when you execute a SQL statement based on form values you leave yourself open to someone putting in a single quote and attempting to execute addition SQL commands where you thought they entered their username/password/whatever.
The simplest way to clean this up is to just escape any single quotes they pass in (to escape single quotes just double them up). If you have to deal with multiple text fields, it's probably better to encapsulate this in a function like so:

Function CleanSQL(str)
   CleanSQL = str
   CleanSQL = Trim(CleanSQL)
   CleanSQL = Replace(CleanSQL,"'","''")
End Function

[b]Then to use it[/b]
sql = "UPDATE [Clients] SET password = '" & [highlight]CleanSQL(Request.Form("password"))[/highlight] & "' WHERE [ClientID] = " & Trim(Session("myvar111"))

In any case, just thought this might help,
-T