Ad:

ASP Discussion Thread
Unsolved
Views: 3106

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 12th, 2007

verify page not finding random records

Expand Post »

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]

nekesa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jan 2007

Permalink

Feb 12th, 2007

Re: verify page not finding random records

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

Reputation Points: 13

Solved Threads: 4

Junior Poster in Training

Offline

79 posts
since Dec 2006

Permalink

Feb 12th, 2007

Re: verify page not finding random records

Click to Expand / Collapse Quote 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.

nekesa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jan 2007

Permalink

Feb 12th, 2007

Re: verify page not finding random records

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.

nikkiH

Reputation Points: 13

Solved Threads: 4

Junior Poster in Training

Offline

79 posts
since Dec 2006

Permalink

Feb 12th, 2007

Re: verify page not finding random records

Click to Expand / Collapse Quote 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.

nekesa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jan 2007

Permalink

Feb 12th, 2007

Re: verify page not finding random records

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

nikkiH

Reputation Points: 13

Solved Threads: 4

Junior Poster in Training

Offline

79 posts
since Dec 2006

Permalink

Feb 13th, 2007

Re: verify page not finding random records

Click to Expand / Collapse Quote 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

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.

nekesa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jan 2007

Permalink

Feb 13th, 2007

Re: verify page not finding random records

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

i.e.

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

nikkiH

Reputation Points: 13

Solved Threads: 4

Junior Poster in Training

Offline

79 posts
since Dec 2006

Permalink

Feb 13th, 2007

Re: verify page not finding random records

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:

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.

nikkiH

Reputation Points: 13

Solved Threads: 4

Junior Poster in Training

Offline

79 posts
since Dec 2006

Permalink

Feb 13th, 2007

Re: verify page not finding random records

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

(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:

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'

madmital

Reputation Points: 10

Solved Threads: 5

Junior Poster

Offline

119 posts
since Jun 2005

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: Connecting ASP to Ms Access

Next Thread in ASP Forum Timeline: ASP: Sending a HTML form to an email address

DaniWeb Message

Cancel Changes

User Name
Password