Finding specific record useing an active database connection?

[Ovatvvon]

I am wondering how to find a specific record using an active connection.

I want to move directly to record 30 (or the current user's PK) to check for a valid password entry, and if valid then change the password while only in one connection to the database.

My current connection consists of this:

<!-- #include file=&quot;adovbs.inc&quot; -->
<%
Dim oldPassword, newPassword

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open connString
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open &quot;users&quot;, conn, , , adCmdTable

If oldpassword = rs(&quot;userPassword&quot Then
rs(&quot;userPassword&quot = newPassword
End If

rs.Close : conn.Close
Set rs = Nothing : Set conn = Nothing
%>


Does anyone know how to move directly to a record to verify and change the field without using rs.MoveNext to check every row to see if it equals the desired number?

Thanks in advance.
-Ovatvvon :-Q

 

[jfrost10]

Why not use a sql statement like:
&quot;Select UserName, UserPassword From Users Where
UserName = '&quot; & username & &quot;' And UserPassword = '&quot;
& oldpassword & &quot;'&quot;

This would give you the record for the person and the entered password. If the password entered is invalid, then no records would be returned. You can then just use the

If Not rs.eof Then
'put code here to update with new password
End If

Jack

 

[Ovatvvon]

I can do it that way, but didn't want to make 2 seperate database connections. Want it to be one connection (actively), verify if the password is correct...if it is, then change it to the new password.

Don't need a user ID and password because the member is already logged into the member area. And, I have their Primary Key stored in a temporary Session object session(&quot;userPK&quot. Therefore, I already know what record they are in the database. All I need to do it move to that record and perform my objectives.

Make Sense? -Ovatvvon :-Q

 

[Ovatvvon]

Anyone? [^ Bump ^] -Ovatvvon :-Q

 

[jfrost10]

k, I could be wrong here, so lets step through it:
1. you make a connection
2. you open a connection
3. you perform the sql command
4. you execute the code on the recordset for the password stuff
5. you close the connection

best as I can see, thats one connection.

And there's two ways to approach the record part of it:
the way you're doing it, you're accessing the entire table, and you want to start at the main record right? well, it might be better to just execute a sql statement which grabs the record you want. That way you don't have to worry about moving through records upon records, and you won't have the resource hit.

Jack

 

[Ovatvvon]

Well, I do want to perform the least resource-straining opporation I can. You are right in that it is only 1 &quot;connection&quot;, however I guess the way I'm trying to explain it is that performing it this way, once you get to the record, you are working with it specifically and then ending the connection. The other way (as you have mentioned, when you execute the sql select statement to &quot;find&quot; the record, you still have to scroll through all the records just like I'm doing here...the difference is that then you will additionally have to stop the recordset interface, execute another sql statement to update the password if the old one was verified properly. If I do it this way (the way I have been trying), I don't have to execute two seperate recordset queries...I just simply verify the password and then change it.

It seams this would be faster...do you disagree? (I could be wrong...have been before ;-))

p.s. I'm not flaming ya', just curious how to do this, and now additionally if others believe this would be faster, or if your method would be faster. (?)
-Ovatvvon :-Q

 

[jfrost10]

heh, no worries man, sorry if I came off a little strong on my last post.


k, let's look at both ways:
yours (code you gave above)
1. open a conn
2. set the recordset to be the entire users table
3. cycle through and if old password is found, assign it
the new one.
4. close connection

my suggestion:
1. open a conn
2. Set recordset to be result of sql query
3. Perform If Not rs.EOF statement
4a. if rs.eof is true, then no record was found to match.
4b. if rs.eof is not true, then one record was found, and
the password can be changed and updated using the
recordset's .Update command (no second sql statement
is needed)
5. Close Connection

So here's the main performance difference:
First way: you're loading an entire table into a recordset thats being stored in memory. You're then cycling through all the records until you find a match. Also, if you do find a match, there's nothing telilng the code to exit, so if the first record is a match you're still cycling through all the table. Also, you probably only need a few columns from the table, so extra data is being loaded in memory that isn't necessary.

Second way: you're still searching through all the records in the table, but insted of returning the ENTIRE table, you're only returning one record. That means that there's less memory being utilized, and you only work with the data you need. Plus, with the recordset's update method, a second sql command isn't actually needed.

I'm guessing you're using access, otherwise I'd say use a T-SQL statement instead.

Ah recordsets...so glad I'm rid of them!


Jack