While I agree count(*) will return 0 if no recordsets are found,
- because of the complex conditions it could be for reasons other than anticipated,
(including if that recordset was say still open from some other code)
- so a check for EOF is always a good practice to prevent errors on the page
Plus there is a syntax error in the 1st code snippet for ELSEIF syntax
Else IF oRsRadiologyResults(0) = 0 Then
The correct syntax is
ElseIF oRsRadiologyResults(0) = 0 Then
To debug code it is always best to add in some Debug Response.Writes to see what the Page & DB is returning when (and initially to
heavily comment the code to understand what is hapening when)
Below is a combination of the 1 code snippet and the second code snippet that should help in debugging
- care should be taken on line wrapping from the news reader
<%
'Check to see if there are Radiology Results for this patient and build the navigation link.
'using SELECT COUNT(* is all fields/records) FROM (table_name) WHERE (some_conditions_are_met)
'Debug only below comment out below when done testing
'See what variables we really have
Response.write "Session("RadiologyResultsExist"): " & Session("RadiologyResultsExist")
Response.write "varsqlstmt: " & varsqlstmt
Response.write "Session("sHospital"): " & Session("sHospital")
Response.write "varAccount: " & varAccount
Response.write "varMedicalRecord: " & varMedicalRecord
'Debug only above comment out above when done testing
IF Session("RadiologyResultsExist")="" THEN 'If Session variable is empty Get Recordset
Dim oRsRadiologyResults, sqlRadiologyResults
sqlRadiologyResults="SELECT count(*) FROM "& varsqlstmt & "BSYDTAB.CXRMRDU7 CXR " &_
" WHERE CXR.RDHSP#=" & Session("sHospital") & " AND " &_
" CXR.RDACCT=" & varAccount & " AND " &_
" CXR.RDRAD#=" & varMedicalRecord & " AND " &_
" CXR.RDORD#<>0"
Set oRsRadiologyResults=oConnCHWC400.Execute(sqlRadiologyResults)
'Debug only below comment out below when done testing
IF NOT oRsRadiologyResults.EOF Then
Response.write "db results: " & oRsRadiologyResults
ELSE
Response.write "NO db results FOUND"
END IF
'Debug only above comment out above when done testing
Dim navRadiologyResults
IF oRsRadiologyResults.EOF Then 'No records found
navRadiologyResults="<font face='Arial, Helvetica, sans-serif' size=2 color=#009966><A href='" & Session("URLSSL") &
"radiologyresults.asp?medrec=" & varMedicalRecord & "&acct=" & varAccount & "&d=past'>Past Radiology Results</a></font>"
ElseIF oRsRadiologyResults(0)=0 Then ' Count is=0 (no records found)
navRadiologyResults="<font face='Arial, Helvetica, sans-serif' size=2 color=#d3d3d3>Radiology Results</font>"
Else 'A recorset was found
navRadiologyResults="<font face='Arial, Helvetica, sans-serif' size=2 color=#009966><A href='" & Session("URLSSL") &
"radiologyresults.asp?medrec=" & varMedicalRecord & "&acct=" & varAccount & "'>Radiology Results</a></font>"
End If 'oRsRadiologyResults Check
'Debug only below comment out below when done testing
IF LEN(navRadiologyResults)<>0 THEN
Response.write "nav results: " & navRadiologyResults 'Debug only
ELSE
Response.write "NO nav results CREATED"
END IF
'Debug only above comment out above when done testing
ELSE 'Session("RadiologyResultsExist")<>"" The Session variable was not empty
Response.write "ERROR - Session Radiology Results Already Exist"
END IF 'Session("RadiologyResultsExist") Check
%>
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________
| oRsRadiologyResults will always contain exactly one record, so checking
| for EOF is a waste of time - it will always fail, which leaves the 2
| remaining options.
|
| The record returned will have a value of 0 - no records found
| (indicating a new patient who is not yet on the database), or a positive
| integer representing the number of times a single account number (as in
| the variable varAccount) occurs in the database. There is nothing there
| that I can see that distinguishes a current record from a past record
| (but I am not privy to the meaning of some fields.)
| --
| Ron Symonds - Microsoft MVP (FrontPage)
| Reply only to group - emails will be deleted unread.
| FrontPage Support:
http://www.frontpagemvps.com/
|
http://www.rxs-enterprises.org/fp
|
|
|
| |
| > What I refer to as a past record is as follows. A patient has a
| > Medical Record number (which is supposed to always be linked to that
| > patient) and then an account number which a patient gets a new one for
| > each new visit. So yes a patient can have both past and current, but
| > they might not always have past (1st time patient) or a patient might
| > come in for something unrelated and not have results on their account
| > number, but have results on their Medical record number. I will
| > answer your questions as best as I can, but this was all coded and
| > operational when I started this job, I'm just now having to learn and
| > modify as I go.
| > Here is the SQL statement.
| >
| > 'Check to see if there are Radiology Results for this patient and
| > build the navigation link.
| > IF Session("RadiologyResultsExist") = "" Then
| > Dim oRsRadiologyResults, sqlRadiologyResults
| > sqlRadiologyResults = "SELECT count(*) FROM "&
| > varsqlstmt&"BSYDTAB.CXRMRDU7 CXR " &_
| > " WHERE CXR.RDHSP# = " & Session("sHospital") & " AND " &_
| > " CXR.RDACCT = " & varAccount & " AND " &_
| > " CXR.RDRAD# = " & varMedicalRecord & " AND " &_
| > " CXR.RDORD# <> 0 "
| >
| > Set oRsRadiologyResults = oConnCHWC400.Execute(sqlRadiologyResults)
| >
| >
| > > How do you determine the difference between a current record and a past
| > > record? Is the record set sorted in any way? There is nothing in your
| > > code snippets that show that.
| > > It would be useful to see the SQL statement.
| > > Can the patient have both past and current records?
| > > --
| > > Ron Symonds - Microsoft MVP (FrontPage)
| > > Reply only to group - emails will be deleted unread.
| > > FrontPage Support:
http://www.frontpagemvps.com/http://www.rxs-enterprises.org/fp
| > >
| > >
| > > | > >
| > > > Hello everyone...first thanks for taking the time to read this. I'm
| > > > using the FrontPage software to modify my code which is why I'm
| > > > posting in here, but this is probably an ASP issue.
| > >
| > > > The code I am modifying was written by a former employee and I'm
| > > > struggling greatly.
| > >
| > > > Basically what I'm trying to do is to use an IF statement to sort
| > > > through 3 possible outcomes. A simple example is below.
| > >
| > > > If this do A
| > >
| > > > else if this do B
| > >
| > > > else do C
| > >
| > > > Where
| > > > If this do A means If there is a current record for patient use this
| > > > link.
| > > > If this do B means If there is a past record for the patient use this
| > > > different link and link description.
| > > > If this do C means If there are no records for this patient Grey Out
| > > > the Link
| > >
| > > > The thing is, when I visit the web page I'm only getting 2 of the 3 to
| > > > work. A works all the time.
| > > > If I modify my code either B will work or C will work.
| > >
| > > > I've tried all sorts of things and can not get it to work. Here is my
| > > > code currently, my first if statement isn't working. I will either
| > > > get a link because there is a current record or I will have a greyed
| > > > out link even though I know a patient has radiology records. Any help
| > > > is greatly appreciated.
| > >
| > > > Dim navRadiologyResults
| > > > IF
| > > > oRsRadiologyResults.EOF Then
| > > > navRadiologyResults = "<font face='Arial, Helvetica, sans-serif'
| > > > size=2 color=#009966><A href='" & Session("URLSSL") &
| > > > "radiologyresults.asp?medrec=" & varMedicalRecord & "&acct=" &
| > > > varAccount & "&d=past'>Past Radiology Results</a></font>"
| > >
| > > > Else IF
| > > > oRsRadiologyResults(0) = 0 Then
| > > > navRadiologyResults = "<font face='Arial, Helvetica, sans-serif'
| > > > size=2 color=#d3d3d3>Radiology Results</font>"
| > >
| > > > Else
| > > > navRadiologyResults = "<font face='Arial, Helvetica, sans-serif'
| > > > size=2 color=#009966><A href='" & Session("URLSSL") &
| > > > "radiologyresults.asp?medrec=" & varMedicalRecord & "&acct=" &
| > > > varAccount & "'>Radiology Results</a></font>"
| > > > End If
| > > > End If
|
