We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How do I check if recordset is empty or null and display error msg ??

Medium Priority
2,450 Views
Last Modified: 2012-05-06
I am having problems detecting empty record on this page . I want to display table with error message iof there is no players on selected course . Much Help is  appreciated.
<% 
set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Players where CourseID="& session("scourseid") &""
rs4.Open sql4, conSQL
 
 
 if rs4.BOF then %>
 
 
      <table width="50%" border="0" align="center" cellpadding="0" cellspacing="0" class="text">
        <tr> 
          <td align="center" class="errtext">No Players Found on Course .</td>
        </tr>
        <tr> 
        </table>
      
      <% End If %>
      
      
      <br>    <form name="form1" method="post" action="enterScores.asp">
       <table width="585" border="0" align="center" cellpadding="0" cellspacing="0" >
          <tr> 
            <td width="136" valign="top"><%if courseID <> -1 then%>
              <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr>
                  <td><table width="200" height="69" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1"> Select Player </td>
                      </tr>
                      <%
						   do while not rs4.eof
					   %>
                       
                      <tr >
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
				loop 
			  %>
                  </table></td>
                </tr>
            </table><% end if %></td>
            <td width="24">&nbsp;</td>

Open in new window

Comment
Watch Question

To check if the recordset is empty

re.recordcount == 0

then the recordset is empty

if (re.recordcount == 0)
{
response.writeline('Error")
}

Author

Commented:
It seems I am not catching error still, what can be wrong?
<% 
set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Players where CourseID="& session("scourseid") &""
rs4.Open sql4, conSQL
 
 
 if rs4.recordcount = 0 then %>
 
 
      <table width="50%" border="0" align="center" cellpadding="0" cellspacing="0" class="text">
        <tr> 
          <td align="center" class="errtext">No Players Found on Course .</td>
        </tr>
        <tr> 
        </table>
      
      <% response.End()
	  
	  End If %>
      
      
      <br>    <form name="form1" method="post" action="enterScores.asp">
       <table width="585" border="0" align="center" cellpadding="0" cellspacing="0" >
          <tr> 
            <td width="136" valign="top"><%if courseID <> -1 then%>
              <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr>
                  <td><table width="200" height="69" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1"> Select Player </td>
                      </tr>
                      <%
						   do while not rs4.eof
					   %>
                       
                      <tr >
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
				loop 
			  %>
                  </table></td>
                </tr>
            </table><% end if %></td>

Open in new window

Commented:
try

If rs4.errors.count> 0 then
 ' error message
Else
 ' Display details
End if

Commented:
Also note you below code will be executed whether there is an error or not.

if you do not want the below code to be execute when there is error then use

If rs4.errors.count> 0 then
 ' error message
Else
 ' Below code
End if
<br>    <form name="form1" method="post" action="enterScores.asp">
       <table width="585" border="0" align="center" cellpadding="0" cellspacing="0" >
          <tr> 
            <td width="136" valign="top"><%if courseID <> -1 then%>
              <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr>
                  <td><table width="200" height="69" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1"> Select Player </td>
                      </tr>
                      <%
                                                   do while not rs4.eof
                                           %>
                       
                      <tr >
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
                                loop 
                          %>
                  </table></td>
                </tr>
            </table><% end if %></td>

Open in new window

Author

Commented:
I am sorry , I dont explain correctly waht I need to do. I mean error , is when is no record to dipslay, no players, but the code I ty dont seem to work, or the logic I am follow is not alright.


<table width="200" height="69" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1"> Select Player </td>
                      </tr>
                      <%
						   do while not rs4.eof
					   %>
 
if rs4.BOF then %>
 
 
      <table width="50%" border="0" align="center" cellpadding="0" cellspacing="0" class="text">
        <tr> 
          <td align="center" class="errtext">No Players Found on Course .</td>
        </tr>
        <tr> 
        </table>
      
      <% End If %>
 
 
 
 
                       
                      <tr >
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
				loop 
			  %>
                  </table>

Open in new window

Try using rs4.EOF instead

Author

Commented:
I cant get to detect eof,,, someone should be wrong..

can you please take a look to whole page ?
<%@LANGUAGE="VBSCRIPT"%>
<% level="../../"%>
<!-- #include file="../logincheck.asp"-->
<!--#include file="../include/intialize.inc"-->
<!--include file="../include/connection.asp"-->
<!--#include file="../../../Connections/sqlConn1.asp" -->
<%
Dim rs5__MMColParam
rs5__MMColParam = "1"
If (Request.QueryString("CourseID") <> "") Then 
  rs5__MMColParam = Request.QueryString("CourseID")
End If
%>
<%
Dim rs5
Dim rs5_cmd
Dim rs5_numRows
 
Set rs5_cmd = Server.CreateObject ("ADODB.Command")
rs5_cmd.ActiveConnection = MM_sqlConn1_STRING
rs5_cmd.CommandText = "SELECT * FROM dbo.Players WHERE CourseID = ?" 
rs5_cmd.Prepared = true
rs5_cmd.Parameters.Append rs5_cmd.CreateParameter("param1", 5, 1, -1, rs5__MMColParam) ' adDouble
 
Set rs5 = rs5_cmd.Execute
rs5_numRows = 0
%>
<%
dim courseID
if request.QueryString("courseID") = "" then 
courseID = -1  
else courseID = request.QueryString("courseID") 
session("scourseid")=request.QueryString("courseID")
end if
'response.Write session("scourseid")
dim SSN
if request.QueryString("SSN") = "" then
 SSN = -1  
 else SSN = request.QueryString("SSN") 
 session("user")=request.QueryString("SSN")
 end if
 
set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * from Courses ORDER by Name"
rs.Open sql, conSQL
if not rs.EOF then rs.movefirst
 
set rs2 = Server.CreateObject("ADODB.Recordset")
sql2 = "SELECT * from Courses WHERE CourseID = " & courseID
rs2.open sql2, conSQL
if not rs2.EOF then rs2.movefirst
 
%>
 
 
 
<html>
<head>
<title><%=PageTitle%></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../style/style.css" rel="stylesheet" type="text/css">
</head>
 
 
<body background="<%=level%>images/bg.jpg" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="740" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr> 
    <td colspan="2"><!--#include file="../include/header.asp"--></td>
  </tr>
  <tr> 
    <td width="" valign="top" bgcolor="#ffffff"> 
      <%
	if (session("adminname")<>"") then 
	%>
      <!--#include file="../include/loggedmenu.asp"--> 
      <%
	end if	
	%>
      <table width="772" border="1" cellspacing="0" cellpadding="1">
        <tr class="textbold">
          <td class="Link1"><a href="Courselisting.asp" class="loggedmenulink">Back to Course Listing</a></td>
        </tr>
      </table>
     
      <p>
        <% 
set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Players where CourseID="& session("scourseid") &""
rs4.Open sql4, conSQL
 
 
%>   
      </p>
      <p><span class="tableheader1">Course - <%=rs2("Name")%></span><br>    
      </p>
      <form name="form1" method="post" action="enterScores.asp">
      <table width="585" border="0" align="center" cellpadding="0" cellspacing="0" >
          <tr> 
            <td width="136" valign="top">
         
            
             <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr> 
               
                  
                 
            <% ' now check if quesrystring is not empty do not show table
			if courseID = "" then
			%>       <td>
              <table width="200" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                <tr bgcolor="#EEEEEE"> 
                  <td height="15" class="tableheader1"> Select Course</td>
                </tr>
                <% do while not rs.eof %>
                <tr > 
                  <td  class="bgcolor5"> <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?courseID=<%=rs("CourseID")%>"><%=rs("Name")%></a></font></div></td>
                </tr>
                <%  rs.MoveNext 
				loop 
			  %>
              </table>
              
             <% End If %> 
              
              
              
              </td></tr></table>
              <br><%if courseID <> -1 then%>
              <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr>
                  <td><table width="200" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1">  Member List</td>
                      </tr>
                      <%
					  set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Players where CourseID="& session("scourseid") &""
rs4.Open sql4, conSQL
					   do while not rs4.eof
					   %>
                      <tr >
                      <% If rs4.EOF Then %>
                      
                      <td  class="bgcolor5">
                          <div align="center">
                          <span class="errtext"><font color="#666666" size="2" face="Tahoma">Not Players Found</span></td>
                          <% End If %>
                      
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
				loop 
			  %>
                  </table></td>
                </tr>
              </table><% end if %></td>
            <td width="24">&nbsp;</td>
            <td width="600"> 
              <%if SSN <> -1 then%>
			  <table width="365" border="1" align="center" cellpadding="0" cellspacing="0" 
			  bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF">
                <tr> 
                  <td>
              <table width="365" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                <tr> 
                  <td colspan="7" class="tableheader1"><%=rs2("Name")%></td>
                </tr>
                <tr bgcolor="#EEEEEE"> 
                  <td width="47"> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Tee 
                      Box </font></strong></font></div></td>
                  <td width="25"> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Par</font></strong></font></div></td>
                  <td width="42"> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Rating</font></strong></font></div></td>
                  <td width="40"> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Slope</font></strong></font></div></td>
                  <td> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">All 
                      18</font></strong></font></div></td>
                  <td> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Front 
                      9</font></strong></font></div></td>
                  <td> <div align="center"><font color="#003366"><strong><font size="2" face="Tahoma">Back 
                      9</font></strong></font></div></td>
                </tr>
                
                
                <%
				set rs3 = Server.CreateObject("ADODB.Recordset")
sql3 = "SELECT * from course_rating where courseid="&session("scourseid")&""
rs3.Open sql3, conSQL
				do while not rs3.eof 
				set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Colors where id="&rs3("colorid")&""
rs4.Open sql4, conSQL
%>
                <tr bgcolor="<%=rs4("color")%>"> 
                  <td> <div align="center"><font size="2" face="Tahoma"><strong><font color="#000000"><%=rs4("cname")%></font></strong></font></div></td>
                  
<% '---------------------------------------------------------'
' I am assuming total par is equals front par + back par               
totalPar = cInt(rs2("frontpar").value + rs2("backpar").value)       
%>
 
                  
 <td> <div align="center"><font size="2" face="Tahoma"><strong><font color="#000000"><%=totalPar%></font></strong></font></div></td>
                  <td> <div align="center"><font size="2" face="Tahoma"><strong><font color="#000000"><%=rs3("rating_value")%></font></strong></font></div></td>
                  <td> <div align="center"><font size="2" face="Tahoma"><strong><font color="#000000"><%=rs3("slope_value")%></font></strong></font></div></td>
                  <td width="51"> <div align="center"><font size="2" face="Tahoma"> 
                      <input type="radio" name="radTee" value="<%=rs4("cname")&18%>">
                      </font></div></td>
                  <td width="49"> <div align="center"><font size="2" face="Tahoma"> 
                      <input type="radio" name="radTee" value="<%=rs4("cname")&F9%>">
                      </font></div></td>
                  <td width="59"> <div align="center"><font size="2" face="Tahoma"> 
                      <input type="radio" name="radTee" value="<%=rs4("cname")&B9%>">
                      </font></div></td>
					  </tr><%  rs3.MoveNext 
				loop 
			  %>
                <tr bgcolor="#EEEEEE"> 
                  <td colspan="7" bgcolor="#EEEEEE"> <div align="right"> </div>
                    <div align="center"> 
                      <input name="Submit" type="submit" class="button1" value="Submit">
                      <input name="hidCourseID" type="hidden" id="hidCourseID" value="<%=courseID%>">
                      <input name="hidCourseName" type="hidden" id="hidCourseName" value="<%=rs2("Name")%>">
                      <input name="hidFrontPar" type="hidden" id="hidFrontPar" value="<%=rs2("FrontPar")%>">
                      <input name="hidBackPar" type="hidden" id="hidBackPar" value="<%=rs2("BackPar")%>">
                    </div></td>
                </tr>
              </table></td></tr></table>
 
              <%end if%>
            </td>
          </tr>
        </table>
        <br>
      </form></td>
    <td width="1" valign="top" bgcolor="#285E8E"></td>
  </tr>
  <tr> 
    <td colspan="2"><!--#include file="../include/footer.asp"--></td>
  </tr>
</table>
<% rs.close
   set rs = nothing
   conSQL.close
   set conSQL = nothing
%>
</body>
</html>
<%
rs5.Close()
Set rs5 = Nothing
%>

Open in new window

b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
You need to move the "If RS4.EOF then" line to OUTSIDE the loop for when records are found.  In other words if your method to implement the expert's suggestion in http:#a23695785 was the line you added in the snippet above at line 142 then line 139 above it will prevent it from working.  You need to move your test for no records to above the loop to handle when records are found.
If you don't know what I mean or need details please first clarify which part of all that code you need this test.  You use RS4 in at least 3 places in the last snippet you posted.  I assume the lines I mentioned above are where you want this done but please confirm before I try to be specific in how to do it in code.  The expert post I mentioned is the way to do it though.
bol

Author

Commented:
Well, yes I've tried almost everything. So dont really know whats the problem here. Here is the soluytion you mentioned? or I did not understood it?


    <br><%if courseID <> -1 then%>
              <table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#5eb020" bordercolorlight="#5eb020" bordercolordark="#FFFFFF" >
                <tr>
                  <td><table width="200" border="0" cellpadding="3" cellspacing="1" bordercolor="#CCCCCC" style="border-collapse:collapse">
                      <tr bgcolor="#EEEEEE">
                        <td height="15" class="tableheader1">  Member List</td>
                      </tr>
                      <%
					  set rs4 = Server.CreateObject("ADODB.Recordset")
sql4 = "SELECT * from Players where CourseID="& session("scourseid") &""
rs4.Open sql4, conSQL %>
 
 
<tr >
<% If rs4.EOF Then %>
 
<td  class="bgcolor5">
  <div align="center">
  <span class="errtext"><font color="#666666" size="2" face="Tahoma">Not Players Found</span></td>
  <% End If %>
 
<%  do while not rs4.eof  %>
                      
                        <td  class="bgcolor5">
                          <div align="center"><font color="#666666" size="2" face="Tahoma"><a href="selectCourse.asp?SSN=<%=rs4("SSN")%>&courseID=<%=courseID%>"><%=rs4("SSN")%></a></font></div></td>
                      </tr>
                      <%  rs4.MoveNext 
				loop 
			  %>

Open in new window

b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
The snippet looks good.  What is the result of that?  You could put the Do While part in an Else of that If (the If rs4.EOF part).  When you do that you should either see just the div or the results?
Do you have a complete html table if there is no result?  Look at the html source.  If there is no result do you have anything for a table?  It isn't real clear what you do get when this doesn't work.  Examine the html source and see if that helps.
bol
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Hey Ricky.

Can you please let me know where you stand in this issue?
If this issue still exist, please explain to me exactly what it is that you want to do in this?
Be specific to the point, OK?
This way I can better assist you in getting this issue resolved, if it is not already.

Have a good one.
Carrzkiss

Author

Commented:
Question closed beacuse was not well defined, isaackhazi was the closest one since thats indeed how do I check for zero records .
b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
RickyGtz,
Thanks for your response. I have asked for a third party ZA or moderator to review this. As a participant in this there may seem to be a conflict of interest in me acting as a zone advisor. I would ask you actually test the comment you are defending in your own ASP code. You should see the issues I hinted at in my admin comment. It isn't a way to check for zero records returned or show an error message. I believe you may be confusing languages and sections of your code. Using EOF is the appropriate way to make sure a recordset has records and isn't empty.
bol

Commented:
OK Ricky,  Let get back to work, What is the exact problem, please streamline it so that one of the experts here can help you out. please interact with the experts to solve your questions
b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
RickyGtz,
If you do wish to work on this again make sure you respond to what was asked in the comments below when you respond to AsishRaj:
http:#a23741261
http:#a23877222
I am not opposed to working on this again but there is no reason to start over or ignore the answers you did get.  If they didn't work then responding to those comments should help us get them working.
If anything in those comments isn't clear then post to let us know; don't just ignore it since it could be a key to getting this working for you in this page.
bol

Author

Commented:
well the problem is to detect when  the rs4 is empty , in other words if there is no players to show with that course ID, so I tried

if rs4.eof or rs4.bof
but not luck , on msql server i can see no players with a course ID and the condition is not met
IT Manager
CERTIFIED EXPERT
Commented:
You just need to try
If rs4.EOF then
That is used to "catch" the situation you mentioned.  The code in the If is run when the recordset did not get any records from the query.
When you tried it before you were using it in the wrong place.  It seems like that is corrected but you still may have a problem like that.  The line above isn't the problem though but how or more likely where it is used.  That is why I asked what I did above (and still no answer to it).
To test what I mean make a new, simple ASP page that will just make the DB connection, use the query used in rs4 and see if the If I provided works.  A sample of how it can be used is in the snippet below.  Notice in the snippet if I "mess up" the query and use DD instead of DVD for the value for the Type field then no records are returned and I would see the "No records found" message.  You can duplicate this same type of thing in a new, simplified page to test what I mean for your own DB and query.
If you need help getting it working in the page above then please provide the answers and details I asked for.
bol

strSQL = "SELECT `Artist/Movie_Title` FROM `DVD-CD_List` WHERE Type = 'DD' ORDER BY Disc_Number"
mdbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbpath
mdbRS.Open strSQL, mdbConn, 3
 
If mdbRS.EOF then
	Response.Write "No records found"
	Response.End
End if

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.