Select a specific record and update it

[CrazyBone]

I am using an access db with asp, the db is named patriots.mdb and table is named Users

what I have been attempting to do is pass 3 form entries from this page:
<form action="verifymeaction.asp" name="frmVerify" method="post">
<input name="UserName" type=text size=30 maxlength=20>
<input name="Password" type=text size=30 maxlength=26>
<input name="Pin" type=text size=30 maxlength=26>
<INPUT TYPE="Submit" VALUE="Submit" name="Submit">
</form>

the page verifymeaction.asp

So basically user enters the three form entries and hits submit
The form info goes to verifymeaction.asp
The verifymeaction.asp page locates a record that has all 3 or these items, not just one, all three.

If it has all three, it then what it does is updates the field named FVerify to TRUE for the record that matched all 3
(it is a yes/no checkbox in the access db

If it can't find all three in their respective fields, it goes to whatever.asp

Here is the wreck of code I have been trying to make work, sorry it is so messed up, if you want to just re-write it go ahead, but I am posting my work so you know I am not just looking for an easy way out, I have been trying to make this work for a couple hours, keep in mind I am still learning ;)

All I ask is that the db connection is left the same as I use it in several other pages.

<%
sFile = request.ServerVariables("PATH_TRANSLATED")
sSplit = split(sFile, "\")
for iCtr = 0 to uBound(sSplit) - 1
sDir = sDir & sSplit(ictr) & "\"
next

Dim objConn
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & sDir & "patriots.mdb"
%>
<%
Dim rsUsers, uname, upin
set rsUsers = Server.CreateObject("ADODB.Recordset")
rsUsers.Open "Users", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
'rsUsers.Filter = "FPin = " & Request.Form("Pin")

uname = Request.Form("Username")
'rsUsers.Filter = Request.Form("Username")
rsUsers.Source = "SELECT (FUsername) FROM Users WHERE FUsername = 'uname' ORDER BY FUsername ASC"

'If Request.Form("Pin") = rsUsers("FPin") AND Request.Form("Username") = rsUsers("FUsername") Then
' rsUsers("FVerify") = True
' rsUsers.Update
' Response.Redirect "updated.asp"
'Else
'Response.Redirect "sorry.asp"
Response.Write "" & upin & ""
Response.Write "" & uname & ""
Response.Write rsUnique("FUsername")
'End If

rsUsers.Close
set rsUsers = Nothing
objConn.Close
set objConn = Nothing
%>

[hielo]

read through the comments:

<%

'NO! Use Server.MapPath() to determine the full path of the db
'you do not need to do all these
'
'sFile = request.ServerVariables("PATH_TRANSLATED")
'sSplit = split(sFile, "\")
'for iCtr = 0 to uBound(sSplit) - 1
'	sDir = sDir & sSplit(ictr) & "\"
'next

Dim objConn, sql, affectedRecords
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("patriots.mdb")

'on the code you posted you are not using password anywhere. My guess it that on your form is named
'FPassword, but if not be sure to use the correct field name
sql="UPDATE [Users] SET [FVerify]=True WHERE [FPin]='" & Request.Form("pin") & "' AND [FUsername]='" & Request.Form("username") & "' AND [FPassword]='" & Request.Form("password") & "'"

objcommand.Execute ra,parameters,options

objConn.Execute sql, affectedRecords

objConn.Close
Set objConn = Nothing

If 0 = affectedRecords Then
	Response.Redirect "whatever.asp"
End If
%>

[murthis]

What I am doing is updating a field in a set of records. The most frequent next step is to generate

my list report that will include all records with similar updates to that same field (it is a todo field and

other records with information in their todo field are the set of companies that I have to work on).

When I generate the list report that includes the "find" I end up with a nice list but I am looking at

a different record which is disconcerting. I can use the Scroll Window step to end up looking at the

top of the list but the list is sorted by When the ToDo is due and not when the records were modified

(which I suppose is another way can do this).

I just wondered if there is a way say if I set a variable to the Account Field in the record I initially update

can I "land" on that record in the found set instead of the default first record?

SNIP