verify page not finding random records

[nekesa]

<%
Dim rs,strsql,conn,sql_update,ssn,hdDate,PageDate
set conn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")

'DSN less connection
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\shared\Casework.mdb"

strsql = "Select ssn From cwct07 where ssn = '" & _
Request.Form("ssn") & "'"
set rs = conn.Execute (strsql)

If (not rs.BOF) and (not rs.EOF) then
session("ssn") = Request.Form("ssn")
Session("datetime") = Request.Form("hdDate")
Session("today") = Request.Form("PageDate")

Response.Redirect "http://cwpsalem.pdx.edu/staff/track/Completelogin.asp"
else
Response.Redirect "http://cwpsalem.pdx.edu/staff/track/beginAgain.asp"
end if

'close the recordset
rs.close
set rs = nothing

'close the connection
conn.close
set conn = nothing
%>

[nikkiH]

Is that a text field?
If not, remove the single quotes around the value.
If so, check that there are no "hidden" spaces or tabs in the actual DB values.

[nekesa]

Is that a text field? If not, remove the single quotes around the value. If so, check that there are no "hidden" spaces or tabs in the actual DB values.

It is a text field. I have checked for any space of tab or anything. I have also cleared the record and retyped it. Should I make it a number field? I will try that.

[nikkiH]

You can try that, but it's better to troubleshoot the real problem.
We had a problem with some newlines hiding in our text fields; for some reason, when we edited, and hit enter to go to the next record, the newline was also saved. Something to check for.
Also, ssn often has dashes, so leaving it text allows for more flexibility.

Also, you've got code here after a redirect. The way the code is written, a redirect of some sort will always occur. The code after it won't run. Just FYI.

[nekesa]

You can try that, but it's better to troubleshoot the real problem. We had a problem with some newlines hiding in our text fields; for some reason, when we edited, and hit enter to go to the next record, the newline was also saved. Something to check for. Also, ssn often has dashes, so leaving it text allows for more flexibility.

Also, you've got code here after a redirect. The way the code is written, a redirect of some sort will always occur. The code after it won't run. Just FYI.

I just created 10 new records. 7 of them work. 3 of them don't. I input them all in exactly the same way. I also tried having indexing on and off, reordering the table by the ssn field, and restricting the number of characters in the field to 5. All to no avail. The numbers that don't work do not appear to have anything in common. There are no hyphens in the input as we are only asking for the last 5 digits. This is really frustrating.

[nikkiH]

You don't have an On Error Resume Next in that code anywhere, do you?

[nekesa]

You don't have an On Error Resume Next in that code anywhere, do you?

Not on this page. It is on the following page which includes an update statement and a very long Case statement.

If I remove the conditional statement like this

strsql = "Select ssn From cwct07 where ssn = '" & _ 
Request.Form("ssn") & "'"
set rs = conn.Execute (strsql)
    session("ssn") = Request.Form("ssn")
    Session("datetime") = Request.Form("hdDate")
    Session("today") = Request.Form("PageDate")
   Response.Redirect "http://cwpsalem.pdx.edu/staff/track/Completelogin.asp" 
rs.close
set rs = nothing
conn.close
set conn = nothing
On Error Resume Next
%>

The ssn is recognized correctly for all valid numbers and will update the correct record with a datestamp in the correct column.
When I include the conditional it will not recognize the input number consistently in random cases.

Is there a different way to do the conditional which might yield different results?
Thanks for your time.

[nikkiH]

Well, when I expect only one match, I test only for EOF, not BOF.

i.e.

Set rs = Server.CreateObject("ADODB.recordset")
rs.Open strSQL, objSpecs, adOpenStatic, adLockReadOnly, 1

if rs.EOF then 
   Response.Write "<p><b>Error</b>: No matches found in the database for record </p>"
else
   ...
end if

[nikkiH]

Oh, and since it's a query, not a stored procedure, I don't use execute. I use open, which means I can get a record count if I want. You can't do that with execute.

Here's another example from some other code I have that might do either an exec or an open depending on other factors:

objRS.Open strSQL, objConn, adOpenStatic,adLockReadOnly, 1
...
' check for SQL errors
if Err.number <> 0 Then
   Response.Write "<h2>An Error Occurred</h2>"
...
end if
on error goto 0
...
if objRS.RecordCount <= 0 then
   recordcount = 0
   if Not ObjRs.EOF then
       while Not ObjRS.EOF
           recordcount = recordcount + 1
            ObjRS.MoveNext
       Wend
       ObjRS.MoveFirst
   end if
else
   recordcount = objRS.RecordCount
end if

debugOut "Record count is " & recordcount & ""

[madmital]

Depending on the conn-driver somtimes you can get unwanted results by evaluating with "=" on string values.
I personally always use "LIKE" as in:

strsql = "Select ssn From cwct07 where ssn <strong>LIKE</strong> '" & Request.Form("ssn") & "'"

On another note it is good practice to close your objects before redirecting:

rs.close : set rs = nothing : conn.close : set conn = nothing
Response.Redirect "http://cwpsalem.pdx.edu/staff/track/Completelogin.asp" 
else
rs.close : set rs = nothing : conn.close : set conn = nothing
Response.Redirect "http://cwpsalem.pdx.edu/staff/track/beginAgain.asp"
end if

If you find it tedious to make a lot of closing statements just declare a few public variables and make two general functions for creating/opening af returning a recordset and one for closing. That's a time saver for ya'

[nekesa]

I can't explain it but using only EOF worked. I am going to try multiple records to see if I can get it to mess up again, but so far that is all it needed.

Thanks alot for your help nikkiH. I will try to implement the record count code on the weekend.

Well, when I expect only one match, I test only for EOF, not BOF.

i.e.

Set rs = Server.CreateObject("ADODB.recordset")
rs.Open strSQL, objSpecs, adOpenStatic, adLockReadOnly, 1

if rs.EOF then 
   Response.Write "<p><b>Error</b>: No matches found in the database for record </p>"
else
   ...
end if