• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1520
  • Last Modified:

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

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

0
RickyGtz
Asked:
RickyGtz
  • 6
  • 5
  • 3
  • +3
1 Solution
 
isaackhaziCommented:
To check if the recordset is empty

re.recordcount == 0

then the recordset is empty

if (re.recordcount == 0)
{
response.writeline('Error")
}
0
 
RickyGtzAuthor 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

0
 
AsishRajCommented:
try

If rs4.errors.count> 0 then
 ' error message
Else
 ' Display details
End if
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

0
 
RickyGtzAuthor 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

0
 
danataylorCommented:
Try using rs4.EOF instead
0
 
RickyGtzAuthor 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

0
 
b0lsc0ttCommented:
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
0
 
RickyGtzAuthor 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

0
 
b0lsc0ttCommented:
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
0
 
Wayne BarronCommented:
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
0
 
RickyGtzAuthor Commented:
Question closed beacuse was not well defined, isaackhazi was the closest one since thats indeed how do I check for zero records .
0
 
b0lsc0ttCommented:
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
0
 
AsishRajCommented:
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
0
 
b0lsc0ttCommented:
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
0
 
RickyGtzAuthor 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
0
 
b0lsc0ttCommented:
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

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now