DaniWeb Forum Index > Web Development > ASP

verify page not finding random records

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Solved Threads: 0

nekesa

Offline

Newbie Poster

verify page not finding random records

Feb 12th, 2007

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

[code:ASP]
<%@ 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>
[/code]

•

Join Date: Dec 2006

Posts: 79

Reputation: nikkiH is an unknown quantity at this point

Solved Threads: 4

nikkiH

Offline

Junior Poster in Training

Re: verify page not finding random records

Feb 12th, 2007

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.

Google is your friend. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Solved Threads: 0

nekesa

Offline

Newbie Poster

Re: verify page not finding random records

Feb 12th, 2007

•

Originally Posted by 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.

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.

•

Join Date: Dec 2006

Posts: 79

Reputation: nikkiH is an unknown quantity at this point

Solved Threads: 4

nikkiH

Offline

Junior Poster in Training

Re: verify page not finding random records

Feb 12th, 2007

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.

Last edited by nikkiH; Feb 12th, 2007 at 4:07 pm.

Google is your friend. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Solved Threads: 0

nekesa

Offline

Newbie Poster

Re: verify page not finding random records

Feb 12th, 2007

•

Originally Posted by 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.

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.

•

Join Date: Dec 2006

Posts: 79

Reputation: nikkiH is an unknown quantity at this point

Solved Threads: 4

nikkiH

Offline

Junior Poster in Training

Re: verify page not finding random records

Feb 12th, 2007

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

Google is your friend. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Solved Threads: 0

nekesa

Offline

Newbie Poster

Re: verify page not finding random records

Feb 13th, 2007

•

Originally Posted by nikkiH

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

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
%>
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.

•

Join Date: Dec 2006

Posts: 79

Reputation: nikkiH is an unknown quantity at this point

Solved Threads: 4

nikkiH

Offline

Junior Poster in Training

Re: verify page not finding random records

Feb 13th, 2007

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

i.e.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
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

Google is your friend. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/

•

Join Date: Dec 2006

Posts: 79

Reputation: nikkiH is an unknown quantity at this point

Solved Threads: 4

nikkiH

Offline

Junior Poster in Training

Re: verify page not finding random records

Feb 13th, 2007

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:

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
      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>"
      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>"

Last edited by nikkiH; Feb 13th, 2007 at 1:24 pm.

Google is your friend. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/

•

Join Date: Jun 2005

Posts: 107

Reputation: madmital is an unknown quantity at this point

Solved Threads: 3

madmital

Offline

Junior Poster

Re: verify page not finding random records

#10

Feb 13th, 2007

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

Help with Code Tags

(Toggle Plain Text)

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

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

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
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'

Web Developer
When something seems too good to be true...it usually is