Home > SQL Development > rstSearch.RecordCount works but get no records returned in table

rstSearch.RecordCount works but get no records returned in table

Originally posted by : Lee Farrant (leefarrant@hotmail.com) below is some sample code which I have added to. Since I changed from a dsnless to a DSN connection I am having some problems. the count of records get displayed in my webpages fine so the RecordCount works so the SQL must be running, but it will not display the records in a table. It does not error. Just displays the table header cells. Can anyone see what I have done wrong. Thanks Lee <% ' Constants ripped from adovbs.inc: Const adOpenStatic = 3 Const adLockReadOnly = 1 Const adCmdText = &H0001 ' Our own constants: Const PAGE_SIZE = 5 ' The size of our pages. ' Declare our variables... always good practice! Dim strURL ' The URL of this page so the form will work ' no matter what this file is named. Dim cnnSearch ' ADO connection Dim rstSearch ' ADO recordset Dim strDBPath ' path to our Access database (*.mdb) file Dim strSQL ' The SQL Query we build on the fly Dim ukstrSQL Dim londonstrSQL Dim strSearch ' The text being looked for Dim iPageCurrent ' The page we're currently on Dim iPageCount ' Number of pages of records Dim iRecordCount ' Count of the records returned Dim I ' Standard looping variable Dim county Dim londonzone Dim weeklyrent strURL = Request.ServerVariables("URL") county = Request.QueryString("county") londonzone = Request.Querystring("londonzone") weeklyrent = Request.Querystring("weeklyrent") gocheck = Request.Querystring("county") + Request.Querystring("londonzone") searchoptions = Request.QueryString("searchoptions") ' Retrieve page to show or default to the first If Request.QueryString("page") = "" Then iPageCurrent = 1 Else iPageCurrent = CInt(Request.QueryString("page")) End If ' Since I'm doing this all in one page I need to see if anyone ' has searched for something. If they have we hit the DB. ' O/W I just show the search form and quit. %> <form action="<%= strURL %>" method="get" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1"> <img border="0" src="../images/signin.gif"><a target="_top" href="http://www.blabla.com/"><img border="0" src="../images/Room_Logo_trans.gif" align="left"></a> <select size="1" name="county"> <option selected value="none">UK County</option> <option value="Avon">England Avon</option> <option value="Bedfordshire">England Bedfordshire</option> <option value="Berkshire">England Berkshire</option> <option value="Buckinghamshire">England Buckinghamshire</option> <option value="Cambridgeshire">England Cambridgeshire</option> <option value="Cheshire">England Cheshire</option> <option value="Cleveland">England Cleveland</option> <option value="Cornwall">England Cornwall</option> <option value="Cumbria">England Cumbria</option> <option value="Derbyshire">England Derbyshire</option> <option value="Devon">England Devon</option> <option value="Dorset">England Dorset</option> <option value="Durham">England Durham</option> <option value="East Sussex">England East Sussex</option> <option value="Essex">England Essex</option> <option value="Gloucestershire">England Gloucestershire</option> <option value="Greater Manchester">England Greater Manchester</option> <option value="Hampshire">England Hampshire</option> <option value="Herefordshire">England Herefordshire</option> <option value="Humberside">England Humberside</option> <option value="Isle of Wight">England Isle of Wight</option> <option value="Isle of Scilly">England Isle of Scilly</option> <option value="Kent">England Kent</option> <option value="Lancashire">England Lancashire</option> <option value="Leicestershire">England Leicestershire</option> <option value="Lincolnshire">England Lincolnshire</option> <option value="Merseyside">England Merseyside</option> <option value="Norfolk">England Norfolk</option> <option value="North Yorkshire">England North Yorkshire</option> <option value="Northamptonshire">England Northamptonshire</option> <option value="Northumberland">England Northumberland</option> <option value="Nottinghamshire">England Nottinghamshire <option value="Oxfordshire">England Oxfordshire</option> <option value="Rutland">England Rutland</option> <option value="Shropshire">England Shropshire</option> <option value="Somerset">England Somerset</option> <option value="South Yorkshire">England South Yorkshire</option> <option value="Staffordshire">England Staffordshire</option> <option value="Suffolk">England Suffolk</option> <option value="Surrey">England Surrey</option> <option value="Tyne and Wear">England Tyne and Wear</option> <option value="Warwickshire">England Warwickshire</option> <option value="West Midlands">England West Midlands</option> <option value="West Sussex">England West Sussex</option> <option value="West Yorkshire">England West Yorkshire</option> <option value="Wiltshire">England Wiltshire</option> <option value="Worcestershire">England Worcestershire</option> <option value="Clwyd">Wales Clwyd</option> <option value="Dyfed">Wales Dyfed</option> <option value="Gwent">Wales Gwent</option> <option value="Gwynedd County">Wales Gwynedd County</option> <option value="Mid Glamorgan">Wales Mid Glamorgan</option> <option value="Powys">Wales Powys</option> <option value="South Glamorgan">Wales South Glamorgan</option> <option value="West Glamorgan">Wales West Glamorgan</option> <option value="Borders">Scotland Borders</option> <option value="Central">Scotland Central</option> <option value="Dumfries and Galloway">Scotland Dumfries and Galloway</option> <option value="Fife">Scotland Fife</option> <option value="Grampian">Scotland Grampian</option> <option value="Highlands and Islands">Scotland Highlands and Islands</option> <option value="Lothian">Scotland Lothian</option> <option value="Strathclyde">Scotland Strathclyde</option> <option value="Tayside">Scotland Tayside</option> <option value="County Antrim">Northern Ireland County Antrim</option> <option value="County Armagh">Northern Ireland County Armagh</option> <option value="County Down">Northern Ireland County Down</option> <option value="County Fermanagh">Northern Ireland County Fermanagh</option> <option value="County Londonderry">Northern Ireland County Londonderry</option> <option value="County Tyrone">Northern Ireland County Tyrone</option> </select> or <select size="1" name="londonzone"> <option selected value="none">London Zone</option> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> <option>6</option> </select> and <select size="1" name="weeklyrent"> <option selected>Max. Weekly Rent</option> <option>40</option> <option>50</option> <option>60</option> <option>70</option> <option>80</option> <option>90</option> <option>100</option> <option>110</option> <option>120</option> <option>130</option> <option>140</option> <option>150</option> <option>160</option> <option>170</option> <option>180</option> <option>190</option> <option>200</option> <option>210</option> <option>220</option> <option>230</option> <option>240</option> <option>250</option> <option>260</option> </select> Search: <input type="radio" value="all" checked name="searchoptions"> All <input type="radio" name="searchoptions" value="professionals">Professionals <input type="radio" name="searchoptions" value="students"> Students <input type="radio" name="searchoptions" value="lg"> lg <input type="submit" value="Search" name="submit" /> <input type="reset" value="Reset" name="B1"> <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="903"> <tr> <td width="901" colspan="3"> <hr color="#FF9900" size="1"> </td> </tr> <tr> <td width="113"> <a target="_top" href="http://www.blabla.com/professionals/"> Professionals</a> </td> <td width="110"> <a target="_top" href="http://www.blabla.com/students/">Students</a></td> <td width="680"> <a target="_top" href="http://www.blabla.com/lg/">lg</a> (Professionals & Students)</td> </tr> </table> </form> <% If gocheck <> "nonenone" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. ' strDBPath = Server.MapPath("test.mdb") ' Create an ADO Connection to connect to the sample database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") cnnSearch.open "DSN=profiles" ' This line is for the Access sample database: ' cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";" ' We're actually using SQL Server so we use this line instead: ' cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _ ' & "Initial Catalog=samples;User Id=samples;Password=password;" _ ' & "Connect Timeout=15;Network Library=dbmssocn;" dim searchcat select case searchoptions case "all" searchcat = "" case "professionals" searchcat = " and category2 = 'professionals'" case "students" searchcat = " and category2 = 'students'" case "lg" searchcat = " and category2 = 'lg'" end select dim searchareacat searchareacat = " county =" & "'" & county & "'" & " and category = 'uk'" select case county case "none" searchareacat = " londonzone = " & londonzone & " and category = 'london'" end select ' strSQL = "SELECT * " & "FROM roomprofiles " & "where londonzone = " & londonzone & " and weeklyrent <=" & weeklyrent & searchcat strSQL = "SELECT category2,id,addeddate,county,londonzone,location, weeklyrent,gender,map,preferredcontactmethod,mobil e,telephone,email " & "FROM roomprofiles " _ & "where " & searchareacat & " and weeklyrent <=" & weeklyrent & searchcat & " and date() <= expireDate" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = Server.CreateObject("ADODB.Recordset") rstSearch.ActiveConnection = cnnSearch rstSearch.PageSize = PAGE_SIZE rstSearch.CacheSize = PAGE_SIZE ' Open our recordset rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText ' Get a count of the number of records and pages ' for use in building the header and footer text. iRecordCount = rstSearch.RecordCount iPageCount = rstSearch.PageCount If iRecordCount = 0 Then ' Display no records error. %> Sorry no rooms match your requirement at the moment. See our overview of available <a target="_top" href="londonsummary.asp">London</a> or <a target="_top" href="uksummary.asp">UK</a> rooms. <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited <% Else Response.Write(strSQL) ' Move to the page we need to show. rstSearch.AbsolutePage = iPageCurrent ' Show a quick status line letting people know where they are: %> <%= iRecordCount %> Room(s) Found. Page <%= iPageCurrent %> of <%= iPageCount %>: <% ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showwing you can do more then ' just spit out the data in the form it is in in the table. %> <table border="0"> <tr bgcolor="#fffoe8"> <th>Room Category</th> <th>Ref.</th> <th>Date Room Added</th> <th>County</th> <th>London Zone</th> <th>Location</th> <th>Weekly Rent</th> <th>Male or Female Wanted</th> <th>Map</th> <th>Preferred Contact Method</th> <th>Mobile</th> <th>Telephone</th> <th>Email</th> </tr> <% rowcount=0 Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent if rowcount mod 2 then bgcolor = "FFF0E8" else bgcolor = "ECFFEC" end if %> <tr bgcolor="#<%=bgcolor%>"> <td><%= rstSearch.Fields("category2").Value %> </td> <td><%= rstSearch.Fields("id").Value %> </td> <td><%= rstSearch.Fields("addeddate").Value %> </td> <td><%= rstSearch.Fields("county").Value %> </td> <td><%= rstSearch.Fields("londonzone").Value %> </td> <td><%= rstSearch.Fields("location").Value %> </td> <td><%= rstSearch.Fields("weeklyrent").Value %> </td> <td><%= rstSearch.Fields("gender").Value %> </td> <td><%= rstSearch.Fields("map").Value %> </td> <td><%= rstSearch.Fields("preferredcontactmethod").Value %> </td> <td><%= rstSearch.Fields("mobile").Value %> </td> <td><%= rstSearch.Fields("telephone").Value %> </td> <td><%= rstSearch.Fields("email").Value %> </td> </tr> <% rowcount=rowcount+1 rstSearch.MoveNext Loop %> </table> <% ' Now we need to show our navigation links: ' Sho "previous" and "next" page links which pass the page to ' view our search parameter. You could also use form buttons ' but I find this looks better. If iPageCurrent > 1 Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent - 1 %>">[<< Prev]</a> <% End If ' You can also show page numbers: For I = 1 To iPageCount If I = iPageCurrent Then %> <%= I %> <% Else %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= I %>"><%= I %></a> <% End If Next 'I If iPageCurrent < iPageCount Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent + 1 %>">[Next >>]</a> <% End If %> <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited <% End If ' Close our recordset and connection and dispose of the objects rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing End If %>

Guest

Originally posted by : Zeeshan Ahmad (shani_786@hotmail.com) Use this line between creating recordset object and open recordset object "Rs.Cursor=adUserClient" ------------ Lee Farrant at 3/30/2002 1:29:07 PM below is some sample code which I have added to. Since I changed from a dsnless to a DSN connection I am having some problems. the count of records get displayed in my webpages fine so the RecordCount works so the SQL must be running, but it will not display the records in a table. It does not error. Just displays the table header cells. Can anyone see what I have done wrong. Thanks Lee <% ' Constants ripped from adovbs.inc: Const adOpenStatic = 3 Const adLockReadOnly = 1 Const adCmdText = &H0001 ' Our own constants: Const PAGE_SIZE = 5 ' The size of our pages. ' Declare our variables... always good practice! Dim strURL ' The URL of this page so the form will work ' no matter what this file is named. Dim cnnSearch ' ADO connection Dim rstSearch ' ADO recordset Dim strDBPath ' path to our Access database (*.mdb) file Dim strSQL ' The SQL Query we build on the fly Dim ukstrSQL Dim londonstrSQL Dim strSearch ' The text being looked for Dim iPageCurrent ' The page we're currently on Dim iPageCount ' Number of pages of records Dim iRecordCount ' Count of the records returned Dim I ' Standard looping variable Dim county Dim londonzone Dim weeklyrent strURL = Request.ServerVariables("URL") county = Request.QueryString("county") londonzone = Request.Querystring("londonzone") weeklyrent = Request.Querystring("weeklyrent") gocheck = Request.Querystring("county") + Request.Querystring("londonzone") searchoptions = Request.QueryString("searchoptions") ' Retrieve page to show or default to the first If Request.QueryString("page") = "" Then iPageCurrent = 1 Else iPageCurrent = CInt(Request.QueryString("page")) End If ' Since I'm doing this all in one page I need to see if anyone ' has searched for something. If they have we hit the DB. ' O/W I just show the search form and quit. %> <form action="<%= strURL %>" method="get" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1"> <img border="0" src="../images/signin.gif"><a target="_top" href="http://www.blabla.com/"><img border="0" src="../images/Room_Logo_trans.gif" align="left"></a> * <select size="1" name="county"> <option selected value="none">UK County</option> <option value="Avon">England Avon</option> <option value="Bedfordshire">England Bedfordshire</option> <option value="Berkshire">England Berkshire</option> <option value="Buckinghamshire">England Buckinghamshire</option> <option value="Cambridgeshire">England Cambridgeshire</option> <option value="Cheshire">England Cheshire</option> <option value="Cleveland">England Cleveland</option> <option value="Cornwall">England Cornwall</option> <option value="Cumbria">England Cumbria</option> <option value="Derbyshire">England Derbyshire</option> <option value="Devon">England Devon</option> <option value="Dorset">England Dorset</option> <option value="Durham">England Durham</option> <option value="East Sussex">England East Sussex</option> <option value="Essex">England Essex</option> <option value="Gloucestershire">England Gloucestershire</option> <option value="Greater Manchester">England Greater Manchester</option> <option value="Hampshire">England Hampshire</option> <option value="Herefordshire">England Herefordshire</option> <option value="Humberside">England Humberside</option> <option value="Isle of Wight">England Isle of Wight</option> <option value="Isle of Scilly">England Isle of Scilly</option> <option value="Kent">England Kent</option> <option value="Lancashire">England Lancashire</option> <option value="Leicestershire">England Leicestershire</option> <option value="Lincolnshire">England Lincolnshire</option> <option value="Merseyside">England Merseyside</option> <option value="Norfolk">England Norfolk</option> <option value="North Yorkshire">England North Yorkshire</option> <option value="Northamptonshire">England Northamptonshire</option> <option value="Northumberland">England Northumberland</option> <option value="Nottinghamshire">England Nottinghamshire <option value="Oxfordshire">England Oxfordshire</option> <option value="Rutland">England Rutland</option> <option value="Shropshire">England Shropshire</option> <option value="Somerset">England Somerset</option> <option value="South Yorkshire">England South Yorkshire</option> <option value="Staffordshire">England Staffordshire</option> <option value="Suffolk">England Suffolk</option> <option value="Surrey">England Surrey</option> <option value="Tyne and Wear">England Tyne and Wear</option> <option value="Warwickshire">England Warwickshire</option> <option value="West Midlands">England West Midlands</option> <option value="West Sussex">England West Sussex</option> <option value="West Yorkshire">England West Yorkshire</option> <option value="Wiltshire">England Wiltshire</option> <option value="Worcestershire">England Worcestershire</option> <option value="Clwyd">Wales Clwyd</option> <option value="Dyfed">Wales Dyfed</option> <option value="Gwent">Wales Gwent</option> <option value="Gwynedd County">Wales Gwynedd County</option> <option value="Mid Glamorgan">Wales Mid Glamorgan</option> <option value="Powys">Wales Powys</option> <option value="South Glamorgan">Wales South Glamorgan</option> <option value="West Glamorgan">Wales West Glamorgan</option> <option value="Borders">Scotland Borders</option> <option value="Central">Scotland Central</option> <option value="Dumfries and Galloway">Scotland Dumfries and Galloway</option> <option value="Fife">Scotland Fife</option> <option value="Grampian">Scotland Grampian</option> <option value="Highlands and Islands">Scotland Highlands and Islands</option> <option value="Lothian">Scotland Lothian</option> <option value="Strathclyde">Scotland Strathclyde</option> <option value="Tayside">Scotland Tayside</option> <option value="County Antrim">Northern Ireland County Antrim</option> <option value="County Armagh">Northern Ireland County Armagh</option> <option value="County Down">Northern Ireland County Down</option> <option value="County Fermanagh">Northern Ireland County Fermanagh</option> <option value="County Londonderry">Northern Ireland County Londonderry</option> <option value="County Tyrone">Northern Ireland County Tyrone</option> </select>* or <select size="1" name="londonzone"> <option selected value="none">London Zone</option> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> <option>6</option> </select> and <select size="1" name="weeklyrent"> <option selected>Max. Weekly Rent</option> <option>40</option> <option>50</option> <option>60</option> <option>70</option> <option>80</option> <option>90</option> <option>100</option> <option>110</option> <option>120</option> <option>130</option> <option>140</option> <option>150</option> <option>160</option> <option>170</option> <option>180</option> <option>190</option> <option>200</option> <option>210</option> <option>220</option> <option>230</option> <option>240</option> <option>250</option> <option>260</option> </select> Search: <input type="radio" value="all" checked name="searchoptions"> All <input type="radio" name="searchoptions" value="professionals">Professionals <input type="radio" name="searchoptions" value="students"> Students* <input type="radio" name="searchoptions" value="lg"> lg *<input type="submit" value="Search" name="submit" /> <input type="reset" value="Reset" name="B1"> <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="903"> <tr> <td width="901" colspan="3"> *<hr color="#FF9900" size="1"> </td> </tr> <tr> <td width="113"> <a target="_top" href="http://www.blabla.com/professionals/"> Professionals</a>* </td> <td width="110"> ** <a target="_top" href="http://www.blabla.com/students/">Students</a></td> <td width="680"> <a target="_top" href="http://www.blabla.com/lg/">lg</a> (Professionals & Students)</td> </tr> </table> </form> *<% If gocheck <> "nonenone" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. ' strDBPath = Server.MapPath("test.mdb") ' Create an ADO Connection to connect to the sample database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") cnnSearch.open "DSN=profiles" ' This line is for the Access sample database: ' cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";" ' We're actually using SQL Server so we use this line instead: ' cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _ ' & "Initial Catalog=samples;User Id=samples;Password=password;" _ ' & "Connect Timeout=15;Network Library=dbmssocn;" dim searchcat select case searchoptions case "all" searchcat = "" case "professionals" searchcat = " and category2 = 'professionals'" case "students" searchcat = " and category2 = 'students'" case "lg" searchcat = " and category2 = 'lg'" end select dim searchareacat searchareacat = " county =" & "'" & county & "'" & " and category = 'uk'" select case county case "none" searchareacat = " londonzone = " & londonzone & " and category = 'london'" end select ' strSQL = "SELECT * " & "FROM roomprofiles " & "where londonzone = " & londonzone & " and weeklyrent <=" & weeklyrent & searchcat strSQL = "SELECT category2,id,addeddate,county,londonzone,location, weeklyrent,gender,map,preferredcontactmethod,mobil e,telephone,email " & "FROM roomprofiles " _ & "where " & searchareacat & " and weeklyrent <=" & weeklyrent & searchcat & " and date() <= expireDate" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = Server.CreateObject("ADODB.Recordset") rstSearch.ActiveConnection = cnnSearch rstSearch.PageSize = PAGE_SIZE rstSearch.CacheSize = PAGE_SIZE ' Open our recordset rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText ' Get a count of the number of records and pages ' for use in building the header and footer text. iRecordCount = rstSearch.RecordCount iPageCount = rstSearch.PageCount If iRecordCount = 0 Then ' Display no records error. %> Sorry no rooms match your requirement at the moment. See our overview of available <a target="_top" href="londonsummary.asp">London</a> or <a target="_top" href="uksummary.asp">UK</a> rooms. <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited * <% Else Response.Write(strSQL) ' Move to the page we need to show. rstSearch.AbsolutePage = iPageCurrent ' Show a quick status line letting people know where they are: %> <%= iRecordCount %> Room(s) Found. Page <%= iPageCurrent %> of <%= iPageCount %>: <% ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showwing you can do more then ' just spit out the data in the form it is in in the table. %> <table border="0"> <tr bgcolor="#fffoe8"> <th>Room Category</th> <th>Ref.</th> <th>Date Room Added</th> <th>County</th> <th>London Zone</th> <th>Location</th> <th>Weekly Rent</th> <th>Male or Female Wanted</th> <th>Map</th> <th>Preferred Contact Method</th> <th>Mobile</th> <th>Telephone</th> <th>Email</th> </tr> <% rowcount=0 Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent if rowcount mod 2 then bgcolor = "FFF0E8" else bgcolor = "ECFFEC" end if %> <tr bgcolor="#<%=bgcolor%>"> <td><%= rstSearch.Fields("category2").Value %>*</td> <td><%= rstSearch.Fields("id").Value %>*</td> <td><%= rstSearch.Fields("addeddate").Value %>*</td> <td><%= rstSearch.Fields("county").Value %>*</td> <td><%= rstSearch.Fields("londonzone").Value %>*</td> <td><%= rstSearch.Fields("location").Value %>*</td> <td><%= rstSearch.Fields("weeklyrent").Value %>*</td> <td><%= rstSearch.Fields("gender").Value %>*</td> <td><%= rstSearch.Fields("map").Value %>*</td> <td><%= rstSearch.Fields("preferredcontactmethod").Value %>*</td> <td><%= rstSearch.Fields("mobile").Value %>*</td> <td><%= rstSearch.Fields("telephone").Value %>*</td> <td><%= rstSearch.Fields("email").Value %>*</td> </tr> <% rowcount=rowcount+1 rstSearch.MoveNext Loop %> </table> <% ' Now we need to show our navigation links: ' Show "previous" and "next" page links which pass the page to ' view our search parameter. You could also use form buttons ' but I find this looks better. If iPageCurrent > 1 Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent - 1 %>">[<< Prev]</a> <% End If ' You can also show page numbers: For I = 1 To iPageCount If I = iPageCurrent Then %> <%= I %> <% Else %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= I %>"><%= I %></a> <% End If Next 'I If iPageCurrent < iPageCount Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent + 1 %>">[Next >>]</a> <% End If %> <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited * <% End If ' Close our recordset and connection and dispose of the objects rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing End If %>

Was this answer helpful ? Yes No

Guest

Originally posted by : Lee Farrant (leefarrant@hotmail.com) Hello Zeeshan, Thank you for taking the time out to reply to my rather long message. I placed your suggestion here rstSearch.Cursor=adUseClient ' Open our recordset rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText but I get an error Error Type: Microsoft VBScript runtime (0x800A01B6) Object doesn't support this property or method: 'Cursor' Have a placed it incorrectly? Lee ------------ Zeeshan Ahmad at 4/2/2002 12:14:50 AM Use this line between creating recordset object and open recordset object "Rs.Cursor=adUserClient" ------------ Lee Farrant at 3/30/2002 1:29:07 PM below is some sample code which I have added to. Since I changed from a dsnless to a DSN connection I am having some problems. the count of records get displayed in my webpages fine so the RecordCount works so the SQL must be running, but it will not display the records in a table. It does not error. Just displays the table header cells. Can anyone see what I have done wrong. Thanks Lee <% ' Constants ripped from adovbs.inc: Const adOpenStatic = 3 Const adLockReadOnly = 1 Const adCmdText = &H0001 ' Our own constants: Const PAGE_SIZE = 5 ' The size of our pages. ' Declare our variables... always good practice! Dim strURL ' The URL of this page so the form will work ' no matter what this file is named. Dim cnnSearch ' ADO connection Dim rstSearch ' ADO recordset Dim strDBPath ' path to our Access database (*.mdb) file Dim strSQL ' The SQL Query we build on the fly Dim ukstrSQL Dim londonstrSQL Dim strSearch ' The text being looked for Dim iPageCurrent ' The page we're currently on Dim iPageCount ' Number of pages of records Dim iRecordCount ' Count of the records returned Dim I ' Standard looping variable Dim county Dim londonzone Dim weeklyrent strURL = Request.ServerVariables("URL") county = Request.QueryString("county") londonzone = Request.Querystring("londonzone") weeklyrent = Request.Querystring("weeklyrent") gocheck = Request.Querystring("county") + Request.Querystring("londonzone") searchoptions = Request.QueryString("searchoptions") ' Retrieve page to show or default to the first If Request.QueryString("page") = "" Then iPageCurrent = 1 Else iPageCurrent = CInt(Request.QueryString("page")) End If ' Since I'm doing this all in one page I need to see if anyone ' has searched for something. If they have we hit the DB. ' O/W I just show the search form and quit. %> <form action="<%= strURL %>" method="get" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1"> <img border="0" src="../images/signin.gif"><a target="_top" href="http://www.blabla.com/"><img border="0" src="../images/Room_Logo_trans.gif" align="left"></a> * <select size="1" name="county"> <option selected value="none">UK County</option> <option value="Avon">England Avon</option> <option value="Bedfordshire">England Bedfordshire</option> <option value="Berkshire">England Berkshire</option> <option value="Buckinghamshire">England Buckinghamshire</option> <option value="Cambridgeshire">England Cambridgeshire</option> <option value="Cheshire">England Cheshire</option> <option value="Cleveland">England Cleveland</option> <option value="Cornwall">England Cornwall</option> <option value="Cumbria">England Cumbria</option> <option value="Derbyshire">England Derbyshire</option> <option value="Devon">England Devon</option> <option value="Dorset">England Dorset</option> <option value="Durham">England Durham</option> <option value="East Sussex">England East Sussex</option> <option value="Essex">England Essex</option> <option value="Gloucestershire">England Gloucestershire</option> <option value="Greater Manchester">England Greater Manchester</option> <option value="Hampshire">England Hampshire</option> <option value="Herefordshire">England Herefordshire</option> <option value="Humberside">England Humberside</option> <option value="Isle of Wight">England Isle of Wight</option> <option value="Isle of Scilly">England Isle of Scilly</option> <option value="Kent">England Kent</option> <option value="Lancashire">England Lancashire</option> <option value="Leicestershire">England Leicestershire</option> <option value="Lincolnshire">England Lincolnshire</option> <option value="Merseyside">England Merseyside</option> <option value="Norfolk">England Norfolk</option> <option value="North Yorkshire">England North Yorkshire</option> <option value="Northamptonshire">England Northamptonshire</option> <option value="Northumberland">England Northumberland</option> <option value="Nottinghamshire">England Nottinghamshire <option value="Oxfordshire">England Oxfordshire</option> <option value="Rutland">England Rutland</option> <option value="Shropshire">England Shropshire</option> <option value="Somerset">England Somerset</option> <option value="South Yorkshire">England South Yorkshire</option> <option value="Staffordshire">England Staffordshire</option> <option value="Suffolk">England Suffolk</option> <option value="Surrey">England Surrey</option> <option value="Tyne and Wear">England Tyne and Wear</option> <option value="Warwickshire">England Warwickshire</option> <option value="West Midlands">England West Midlands</option> <option value="West Sussex">England West Sussex</option> <option value="West Yorkshire">England West Yorkshire</option> <option value="Wiltshire">England Wiltshire</option> <option value="Worcestershire">England Worcestershire</option> <option value="Clwyd">Wales Clwyd</option> <option value="Dyfed">Wales Dyfed</option> <option value="Gwent">Wales Gwent</option> <option value="Gwynedd County">Wales Gwynedd County</option> <option value="Mid Glamorgan">Wales Mid Glamorgan</option> <option value="Powys">Wales Powys</option> <option value="South Glamorgan">Wales South Glamorgan</option> <option value="West Glamorgan">Wales West Glamorgan</option> <option value="Borders">Scotland Borders</option> <option value="Central">Scotland Central</option> <option value="Dumfries and Galloway">Scotland Dumfries and Galloway</option> <option value="Fife">Scotland Fife</option> <option value="Grampian">Scotland Grampian</option> <option value="Highlands and Islands">Scotland Highlands and Islands</option> <option value="Lothian">Scotland Lothian</option> <option value="Strathclyde">Scotland Strathclyde</option> <option value="Tayside">Scotland Tayside</option> <option value="County Antrim">Northern Ireland County Antrim</option> <option value="County Armagh">Northern Ireland County Armagh</option> <option value="County Down">Northern Ireland County Down</option> <option value="County Fermanagh">Northern Ireland County Fermanagh</option> <option value="County Londonderry">Northern Ireland County Londonderry</option> <option value="County Tyrone">Northern Ireland County Tyrone</option> </select>* or <select size="1" name="londonzone"> <option selected value="none">London Zone</option> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> <option>6</option> </select> and <select size="1" name="weeklyrent"> <option selected>Max. Weekly Rent</option> <option>40</option> <option>50</option> <option>60</option> <option>70</option> <option>80</option> <option>90</option> <option>100</option> <option>110</option> <option>120</option> <option>130</option> <option>140</option> <option>150</option> <option>160</option> <option>170</option> <option>180</option> <option>190</option> <option>200</option> <option>210</option> <option>220</option> <option>230</option> <option>240</option> <option>250</option> <option>260</option> </select> Search: <input type="radio" value="all" checked name="searchoptions"> All <input type="radio" name="searchoptions" value="professionals">Professionals <input type="radio" name="searchoptions" value="students"> Students* <input type="radio" name="searchoptions" value="lg"> lg *<input type="submit" value="Search" name="submit" /> <input type="reset" value="Reset" name="B1"> <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="903"> <tr> <td width="901" colspan="3"> *<hr color="#FF9900" size="1"> </td> </tr> <tr> <td width="113"> <a target="_top" href="http://www.blabla.com/professionals/"> Professionals</a>* </td> <td width="110"> ** <a target="_top" href="http://www.blabla.com/students/">Students</a></td> <td width="680"> <a target="_top" href="http://www.blabla.com/lg/">lg</a> (Professionals & Students)</td> </tr> </table> </form> *<% If gocheck <> "nonenone" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. ' strDBPath = Server.MapPath("test.mdb") ' Create an ADO Connection to connect to the sample database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") cnnSearch.open "DSN=profiles" ' This line is for the Access sample database: ' cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";" ' We're actually using SQL Server so we use this line instead: ' cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _ ' & "Initial Catalog=samples;User Id=samples;Password=password;" _ ' & "Connect Timeout=15;Network Library=dbmssocn;" dim searchcat select case searchoptions case "all" searchcat = "" case "professionals" searchcat = " and category2 = 'professionals'" case "students" searchcat = " and category2 = 'students'" case "lg" searchcat = " and category2 = 'lg'" end select dim searchareacat searchareacat = " county =" & "'" & county & "'" & " and category = 'uk'" select case county case "none" searchareacat = " londonzone = " & londonzone & " and category = 'london'" end select ' strSQL = "SELECT * " & "FROM roomprofiles " & "where londonzone = " & londonzone & " and weeklyrent <=" & weeklyrent & searchcat strSQL = "SELECT category2,id,addeddate,county,londonzone,location, weeklyrent,gender,map,preferredcontactmethod,mobil e,telephone,email " & "FROM roomprofiles " _ & "where " & searchareacat & " and weeklyrent <=" & weeklyrent & searchcat & " and date() <= expireDate" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = Server.CreateObject("ADODB.Recordset") rstSearch.ActiveConnection = cnnSearch rstSearch.PageSize = PAGE_SIZE rstSearch.CacheSize = PAGE_SIZE ' Open our recordset rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText ' Get a count of the number of records and pages ' for use in building the header and footer text. iRecordCount = rstSearch.RecordCount iPageCount = rstSearch.PageCount If iRecordCount = 0 Then ' Display no records error. %> Sorry no rooms match your requirement at the moment. See our overview of available <a target="_top" href="londonsummary.asp">London</a> or <a target="_top" href="uksummary.asp">UK</a> rooms. <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited * <% Else Response.Write(strSQL) ' Move to the page we need to show. rstSearch.AbsolutePage = iPageCurrent ' Show a quick status line letting people know where they are: %> <%= iRecordCount %> Room(s) Found. Page <%= iPageCurrent %> of <%= iPageCount %>: <% ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showwing you can do more then ' just spit out the data in the form it is in in the table. %> <table border="0"> <tr bgcolor="#fffoe8"> <th>Room Category</th> <th>Ref.</th> <th>Date Room Added</th> <th>County</th> <th>London Zone</th> <th>Location</th> <th>Weekly Rent</th> <th>Male or Female Wanted</th> <th>Map</th> <th>Preferred Contact Method</th> <th>Mobile</th> <th>Telephone</th> <th>Email</th> </tr> <% rowcount=0 Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent if rowcount mod 2 then bgcolor = "FFF0E8" else bgcolor = "ECFFEC" end if %> <tr bgcolor="#<%=bgcolor%>"> <td><%= rstSearch.Fields("category2").Value %>*</td> <td><%= rstSearch.Fields("id").Value %>*</td> <td><%= rstSearch.Fields("addeddate").Value %>*</td> <td><%= rstSearch.Fields("county").Value %>*</td> <td><%= rstSearch.Fields("londonzone").Value %>*</td> <td><%= rstSearch.Fields("location").Value %>*</td> <td><%= rstSearch.Fi lds("weeklyrent").Value %>*</td> <td><%= rstSearch.Fields("gender").Value %>*</td> <td><%= rstSearch.Fields("map").Value %>*</td> <td><%= rstSearch.Fields("preferredcontactmethod").Value %>*</td> <td><%= rstSearch.Fields("mobile").Value %>*</td> <td><%= rstSearch.Fields("telephone").Value %>*</td> <td><%= rstSearch.Fields("email").Value %>*</td> </tr> <% rowcount=rowcount+1 rstSearch.MoveNext Loop %> </table> <% ' Now we need to show our navigation links: ' Show "previous" and "next" page links which pass the page to ' view our search parameter. You could also use form buttons ' but I find this looks better. If iPageCurrent > 1 Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent - 1 %>">[<< Prev]</a> <% End If ' You can also show page numbers: For I = 1 To iPageCount If I = iPageCurrent Then %> <%= I %> <% Else %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= I %>"><%= I %></a> <% End If Next 'I If iPageCurrent < iPageCount Then %> <a href="<%= strURL %>?county=<%= Server.URLEncode(county) %>&londonzone=<%= Server.URLEncode(londonzone) %>&weeklyrent=<%= Server.URLEncode(weeklyrent) %>&searchoptions=<%= Server.URLEncode(searchoptions) %>&page=<%= iPageCurrent + 1 %>">[Next >>]</a> <% End If %> <a target="_top" href="addaroom.asp">Add a Room</a> - <a target="_top" href="affiliate.asp">Our Rooms on Your Site (Affiliate)</a> - Room Hosting - Free Email - Free Webspace ?2002 Cyber Ethos Limited * <% End If ' Close our recordset and connection and dispose of the objects rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing End If %>

Was this answer helpful ? Yes No

Guest

rstSearch.Cursor=adUseClient -- this line is not needed . Just comment this line

Rest is fine

' Open our recordset

rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText

I would suggest u to study throughly the ADO prgramming where you can get a good idea about recordset

Was this answer helpful ? Yes No

Guest

Jump to the last record (rs.MoveLast) then rs.RecordCount contains the number of records. Don't forget to jump back to the beginning if you want to loop thru your records afterwards.

Was this answer helpful ? Yes No

Guest

Answer it

Ask Question About SQL Development

rstSearch.RecordCount works but get no records returned in table

Programming

Related Pages