Check DB content against form data

[joestmc2]

Hi,
I'm working on a page that will check a database for a unique record containing EVERY one of the four elements that were submitted to the page through an HTML form.

If the record is located, the ASP variable "SecurityCheck" should change from 0 to 1.

Here is the page so far. I appreciate any comments on how sound the VB coding is. Second to that, any thoughts on how to finish the SQL query are appreciated also.

Here's the code:
__________________________________________________________
<%@ Language=VBScript %>

<%


dim adoConn
dim rsTest
dim strSQL
dim sProctorID
dim sProctorPW
dim sLMSCourseCode
dim sStudentID
dim sSecurityCheck


sProctorID=Verify.form("ProctorID")'
sProctorPW=Verify.form("ProctorPW")'
sLMSCourseCode=Verfiy.form("LMSCourseCode")
sStudentID=Verfiy.form("StudentID")
sSecurityCheck=0


Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "DBQ=" & Server.Mappath("ProtcorDB.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"


strSQL = "select ProctorID, ProctorPW, LMSCourseCode, StudentID From RequestRecords
where ProctorID = "&Verify.Form("ProctorID") AND
where ProctorPW = "&Verify.Form("ProctorPW") AND
where LMSCourseCode = "&Verify.Form("LMSCourseCode") AND
where StudentID = "&Verify.Form("StudentID");
"


Set rsTest = Conn.Execute(strSQL)
Set rsTest = Nothing


adoConn.Close
Set adoConn = Nothing

%>

 

[PHV]

Replace:
WHERE a=b AND WHERE c=d
with:
WHERE a=b AND c=d

Furthermore, replace this:
where ProctorID = "&Verify.Form("ProctorID") AND
with this:
where ProctorID = " & Verify.Form("ProctorID") & " AND

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

What is the "verify" object? Could it be "request" that you mean?

 

[joestmc2]

'verify' was the name of the form (named in Lectora, the HTML/course development software).

I have changed it to 'Request' though. Sounds like 'Rrequest' here is a command to 'request' form data?

 

[tsuji]

So is it request or is it not? I have no idea after your statement!
[tt]
strSQL = "select ProctorID, ProctorPW, LMSCourseCode, StudentID From RequestRecords
where " & _
"ProctorID =[highlight]'[/highlight]" & request.Form("ProctorID") & "[highlight]'[/highlight] AND " & _
"ProctorPW = '" & request.Form("ProctorPW") & "' AND " & _
"LMSCourseCode = '" & request.Form("LMSCourseCode") & "' AND " & _
"StudentID = '" & request.Form("StudentID") & "';"

Set rsTest = Conn.Execute(strSQL)
[blue]if rs.eof then
sSecurityCheck=0
else
sSecurityCheck=1
end if[/blue]
[/tt]

 

[tsuji]

>[self]if rs.eof then
should be read
[tt]if rs[red]Test[/red].eof then[/tt]

 

[joestmc2]

Yes,
It's a request. Basically here's what the SQL should (eventually) trigger.

1. Look for a record in the database that contains ALL of the variable data submitted through the HTML form.

2. IF RECORD IS LOCATED IN DB: The user is forwarded on to begintest.html

3. IF RECORED IS NOT LOCATED: Display an error message reading, "A record of your test request has not been located. Contact your test proctor for more information.

 

[joestmc2]

!!!!!!!!!!!!!!!!!!

Steps 2 is incorrect. It should read:

2. IF RECORD IS LOCATED IN DB: change value of SecurityCheck variable from 0 to 1.

 

[joestmc2]

Here's the latest version of the code. It's coming together well, thank you for all your feedback:

_______________________________

<%@ Language=VBScript %>

<%


dim adoConn
dim rsTest
dim strSQL
dim sProctorID
dim sProctorPW
dim sLMSCourseCode
dim sStudentID
dim sSecurityCheck


sProctorID=Request.form("ProctorID")
sProctorPW=Request.form("ProctorPW")
sLMSCourseCode=Request.form("LMSCourseCode")
sStudentID=Request.form("StudentID")
sSecurityCheck=0


Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "DBQ=" & Server.Mappath("ProtcorDB.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"


strSQL = "select Count* From RequestRecords
where ProctorID = sProctorID AND
ProctorPW = sProctorPW AND
LMSCourseCode = sLMSCourseCode AND
StudentID = sStudentID;
"

Set rsTest = Conn.Execute(strSQL)
if rsTest.eof then
sSecurityCheck=0
if else
rsTest(0)=0 then sSecurityCheck=0
else
rsTest(0).>0 then sSecurityCheck=1


adoConn.Close
Set adoConn = Nothing

%>

 

[PHV]

you have to rewrite the following:
strSQL = "select Count* From RequestRecords
where ProctorID = sProctorID AND
ProctorPW = sProctorPW AND
LMSCourseCode = sLMSCourseCode AND
StudentID = sStudentID;
"

and take care of the non numeric fields in RequestRecords.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joestmc2]

What do you mean rewrite that?

And how do you mean 'take care of'? Currently all database fields are text fields (even if they are numbers).

 

[PHV]

tsuji already gave you proper code for strSQL.

 

[tsuji]

>It's coming together well,...
If it is not pure rhetoric and that it means it is working well, I will bet my money on the other side.