verify page not finding random records

Question

nekesa 0 Newbie Poster

17 Years Ago

Here is a pretty standard Verify script which searches through a database for specific records and sends the user to the next page if the record is found. The problem the script seems to be unable to find certain records (6 our of 28 in this case). I have tried to reinput the numbers in the database and bounced back and forth signing in with a recognized number then trying one of the unrecognized ones but it consistently wont find certain numbers.

This is vexing. Does anyone have any idea why this would be happening?

This is really causing problems. I would really appreciate any help or direction.
Thanks

<%@ Language=VBScript %>
<%Response.Buffer=true%>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>


<%
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
%>
</BODY></HTML>

asp

Edited 11 Years Ago by Dani because: Fixed formatting

3 Contributors
10 Replies
199 Views
1 Day Discussion Span
Latest Post 17 Years Ago Latest Post by nekesa

nikkiH 2 Junior Poster in Training

17 Years Ago

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.

nikkiH 2 Junior Poster in Training

17 Years Ago

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.

nikkiH 2 Junior Poster in Training

17 Years Ago

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

nekesa commented: Real persistance in troubleshooting. +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

nekesa 0 Newbie Poster · Answer 1 · 2007-02-13T02:02:14+00:00

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.

nekesa 0 Newbie Poster · Answer 2 · 2007-02-13T03:54:06+00:00

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 2 Junior Poster in Training · Answer 3 · 2007-02-13T04:08:59+00:00

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

nekesa 0 Newbie Poster · Answer 4 · 2007-02-13T22:51:50+00:00

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 2 Junior Poster in Training · Answer 5 · 2007-02-13T23:20:38+00:00

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 & "<br>"

madmital 0 Junior Poster · Answer 6 · 2007-02-13T23:51:43+00:00

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 [B]LIKE[/B] '" & 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 0 Newbie Poster · Answer 7 · 2007-02-14T00:06:18+00:00

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