Count and display number of records using classic asp |
Count and display number of records using classic asp |
Pluribus |
Apr 6 2014, 12:03 PM
Post
#1
|
Member Group: Members Posts: 81 Joined: 6-February 09 Member No.: 7,737 |
Hello everyone
I cant quite work out how to get the count statement to work correctly on my classic asp site. I am displaying a page of manufacturers logos and would like to display a number indicating the number of items I stock from that manufacturer. All information is held within an Access database. Here is the code for the relevant part of the page: CODE <table width="100%"> <tr> <td width="100%"> <table width="100%" border="0"> <% Set adoCon = Server.CreateObject("ADODB.connection")' initialize connection Set ors = Server.CreateObject("ADODB.RecordSet")' initialize record set Const COLUMN_COUNT = 1 strSQL = "SELECT Distinct Manufacturer FROM Details ORDER BY Manufacturer ASC" coll_width=100/COLUMN_COUNT adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("stockdetails.mdb") column = 0 ' initialize counter ors.open strSQL,adoCon,1,1 'open record set ' Count how many records exist DIM iRecordCount iRecordCount = 0 Do While Not ors.EOF iRecordCount = iRecordCount + 1 If column = 0 Then Response.Write "<TR>" %> <td width="<%=coll_width%>%" valign="top" > <table width="100%" border="0"> <tr> <td width="50%" height="100%"> <ul style="list-style: none; margin: 0; padding: 0;"> <li class="manufacturers_logo"> <img src="images/logos/<% = oRs.Fields("Manufacturer") %>.gif" alt="<% = oRs.Fields("Manufacturer") %> Logo" title="<% = oRs.Fields("Manufacturer") %> Logo" width="180" /> <a class="fl_left" href="manufacturerlist.asp?pl=<% = oRs.Fields("Manufacturer") %>"><% = oRs.Fields("Manufacturer") %></a> <a class="fl_right" href="manufacturerlist.asp?pl=<% = oRs.Fields("Manufacturer") %>"><% Response.Write "(" & iRecordCount & ")" %> products</a> </li> </ul> </td> </tr> </table> </td> <% column = column + 1 If column = COLUMN_COUNT Then Response.Write "</TR>" & vbNewLine column = 0 ' start over! End If oRS.MoveNext Loop %> <% ors.Close 'Close the database connection 'SET ors = Nothing 'Clean up after yourself %> </table> </td> </tr> </table> I am using the irecordcount variable as thats what a particular tutorial told me to do, but all its doing is starting at 1, moving on to 2, 3, 4 etc in order - its not counting the number of records as it should. What I want to happen is to display the number of items each manufacturer has listed in the database, according to the manufacturers name. In this example, the first result returned shows 1 product when in fact there are 38 in the db. Can someone please help? Thanks Ian |
CharlesEF |
Apr 7 2014, 08:06 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
It has been many years since I have used ASP and ADO but I seem to remember that recordcount is only valid when used with a certain cursor type. Sorry, I don't remember which cursor type it is. Also, using 'Distinct' in your select statement means you will only get 1 manufacturer/product (assuming your 'Manufacturer' contains all the products for each manufacturer).
This post has been edited by CharlesEF: Apr 7 2014, 08:12 PM |
Christian J |
Apr 7 2014, 08:25 PM
Post
#3
|
. Group: WDG Moderators Posts: 9,658 Joined: 10-August 06 Member No.: 7 |
In PHP you'd just use the count() function.
I don't know ASP at all, but it seems iRecordCount is just a variable with the default value 0. Then the script loops through the RecordSet "ors", incrementing iRecordCount by 1 each pass. So once the loop has finished, the final iRecordCount value should indicate the number of ors items. Alas I can't tell when the loop has finished in the code example (is there no way to use curly braces or code indenting to make it more readable?) --does the script print the iRecordCount value after the loop? Also check that the DB query is correct --is the HTML table markup printed properly? |
CharlesEF |
Apr 7 2014, 08:47 PM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Another thought, if you are using the correct cursor type that supports recordcount then you could use the results of your 'Distinct' select to open a 2nd select for the current manufacturer only. This way the recordcount of the 2nd select will return all products by that manufacturer. Of course, you need to do the 2nd select for each manufacturer.
One thing I don't see in your code is the proper use of recordcount. Since your select object is called 'ors', if I remember correctly, you would use a statement like this: count = ors.recordcount (not sure about the Case - RecordCount recordCount, etc..., check the docs.) One other way might be to select all manufacturers and use the GROUP BY. I don't think this will give you a recordcount by manufacturer (unless you count them in your code) but until you provide some more details about your DB setup I'm just guessing. This post has been edited by CharlesEF: Apr 7 2014, 09:21 PM |
Lo-Fi Version | Time is now: 26th April 2024 - 02:27 PM |