Count and display number of records using classic asp Options

[Pluribus]

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]

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]

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]

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