count records in asp

asp Slovenia
  • 13 years ago

    Hello all,
    I couldn't find ASP forum so I decided to post it here.

    I'm trying to add # of records per each category in my DB.
    Now I was searching the web, but no luck.So If you guys can help me, cuz I'm total newbie in asp.

    Example:
    1. Category1 (5)
    2. Category2 (4)
    3. Category3 (0)

    I'm using access db.

    Here's my code:
      <%
    rowscategoryAD = 6
    cols
    categoryAD = -1
    Set oglasKat = MXLRecordset(categoryAD, rowscategoryAD, colscategoryAD)
    For i = 0 to rows
    categoryAD-1
        For j = 0 to colscategoryAD-1
            currentIndex = i + rows
    categoryAD*j
            If categoryAD.Move(currentIndex, 1) Then
    %>
      <td><img src="/sks/drazba1/slike/folder.gif"> <a href="/sks/drazba1/?SubcatID=<%=(categoryAD.Fields.Item("catID").Value)%>"><%=(categoryAD.Fields.Item("Kategorija").Value)%></a>
    (
    Here I would like to add number of records per category</b>.)


    Categories are displayed from DB with repeated region.

    And my recordset:

    &lt;%
    Dim categoryAD
    Dim categoryADnumRows

    Set categoryAD= Server.CreateObject("ADODB.Recordset")
    categoryAD.ActiveConnection = MM
    dirSTRING
    categoryAD.Source = "SELECT * FROM CATAD WHERE SubCatID = " + Replace(categoryAD
    MMColParam, "'", "''") + " ORDER BY name ASC"
    categoryAD.CursorType = 0
    categoryAD.CursorLocation = 2
    categoryAD.LockType = 1
    categoryAD.Open()

    categoryAD
    numRows = 0
    %&gt;





















  • 13 years ago

    Hi Uros,

    Welcome to Developer Fusion!

    I moved this thread for you because I think you will attract a better response here.  You should be able to use something like:

    Set rstRecordSet = conConnection.Execute("Select count(*) from your_table where category = '" & criteria & "'")
    Cnt = rstRecordSet.Fields(0)


    Bob      

  • 13 years ago

    syncorswim wrote:

    Hi Uros,

    Welcome to Developer Fusion!

    I moved this thread for you because I think you will attract a better response here.  You should be able to use something like:

    Set rstRecordSet = conConnection.Execute("Select count(*) from your_table where category = '" & criteria & "'")
    Cnt = rstRecordSet.Fields(0)


    Bob      


    Hi Bob
    So if I understand you correctly the only thing what I must add is
    <% Set rstRecordSet = conConnection.Execute("Select count(*) from your_table where category = '" & criteria & "'")
    Cnt = rstRecordSet.Fields(0) %>?

    Please note, I don't understand the asp code yet...I'm still learning, and to write the correct code would be going to Mount Everest :). Maybe someday I'll know :)
    Bust still if you can explain me or any1 else how this works, I would very much appreciated.

    Thankds again










  • 13 years ago

    I'm not sure what your database/tables/fields are called, replace what I've written in red!!

    First create a recordset and connection

    <%
    dim rsrecordset, cnconnection, dbpath
    dbPath = "c:\inetpub\wwwroot\.....\...\Details2.mdb"
    Set cnconnection = Server.CreateObject("ADODB.Connection")
    Set rsrecordset = Server.CreateObject("ADODB.Recordset")
    cnconnection.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath






    'Then fill the recordset with data

    set rsrecordset = cnconnection.Execute("select count(*) from your_table where the_category_field = '" & criteria & "'"
    %>Category<%response.write(criteria)%> = <%response.write(rsrecordset.Fields(0))%>

    'Where

    'your_table = the tablename of the table that holds the data

    'the_category_field = the name of the field that holds the category_id

    'criteria = the value of the category_id that you wish to count

    In order to get started using ASP there are lots of tutorials on the web, this one is a really straightforward introduction

    Also

    http://www.w3schools.com is a good source of tutorials and example scripts

  • 13 years ago

    Thanks for the code....but I have some problems...I get Expected error all the time.

    My Db has following fields:
    1. tblProducts.
         prodID
         catID
         SubCatID
        Description

    2. tblCats
       catID ( Autonumber)
       SubcatID
       CategoryName ( note: from # 1-12 are catID from 13-30 are SubCat ID)
    Example:
    Category1
       Subcategory
    Category2
    ......

    I decided to copy my code here, so you can see how the page that I'm tring to do will look. I know my code is a mess and still have a lot to learn but I hope you'll understand :).


    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1250"%>
    <!--#include file="../includes/mxl/MXLRecordset.asp" -->
    <!--#include file="../includes/breadcrumbs/recordset.inc.asp"-->
    <!--#include file="../config/config.asp" -->
    <!--#include file="../config/utility.asp" -->
    <!--#include file="../Connections/dir.asp" -->
    <%
    Dim oglasKatMMColParam
    oglasKat
    MMColParam = "1"
    If (Request.QueryString("SubCatID") <> "") Then
      oglasKatMMColParam = Request.QueryString("SubCatID")
    End If
    %>
    <%
    Dim oglasKat
    Dim oglasKat
    numRows

    Set oglasKat = Server.CreateObject("ADODB.Recordset")
    oglasKat.ActiveConnection = MMdirSTRING
    oglasKat.Source = "SELECT * FROM OGLASNAKATEGORIJE WHERE SubCatID = " + Replace(oglasKat
    MMColParam, "'", "''") + " ORDER BY Kategorija ASC"
    oglasKat.CursorType = 0
    oglasKat.CursorLocation = 2
    oglasKat.LockType = 1
    oglasKat.Open()

    oglasKatnumRows = 0
    %>




    <%
    Dim oglasiMMColParam
    oglasi
    MMColParam = "1"
    If (Request.QueryString("SubCatID") <> "") Then
      oglasiMMColParam = Request.QueryString("SubCatID")
    End If
    %>
    <%
    Dim oglasi
    Dim oglasi
    numRows

    Set oglasi = Server.CreateObject("ADODB.Recordset")
    oglasi.ActiveConnection = MMdirSTRING
    oglasi.Source = "SELECT   FROM OGLASNADESKA  WHERE SubCatID = " + Replace(oglasi
    MMColParam, "'", "''") + " AND potrdi=true  ORDER BY oglasID DESC"
    oglasi.CursorType = 0
    oglasi.CursorLocation = 2
    oglasi.LockType = 1
    oglasi.Open()

    oglasi_numRows = 0
    %>
    <%
    Dim RepeatBreadcrumbsnumRows
    Dim RepeatBreadcrumbs
    index

    RepeatBreadcrumbsnumRows = -1
    RepeatBreadcrumbs
    index = 0
    Breadcrumbs_numRows = Breadcrumbs_numRows + RepeatBreadcrumbsnumRows
    %>
    <%
    Dim Repeat1
    numRows
    Dim Repeat1index

    Repeat1
    numRows = 20
    Repeat1index = 0
    oglasi_numRows = oglasi_numRows + Repeat1
    numRows
    %>

    <%
    Dim Breadcrumbs
    Dim Breadcrumbs_numRows

    Set Breadcrumbs = BreadcrumbRecordset ("OGLASNAKATEGORIJE","catID","SubCatID","Kategorija","SubCatID",MM_dir_STRING)

    Breadcrumbs_numRows = 0
    %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

    <html>
    <head><link rel="shortcut icon" href="../wal_ico.ico">
    <title>Spletna Knjižnica Streliva</title>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1250">
    <style type="text/css">
    <!--
    body {
        margin-left: 0px;
        margin-top: 1px;
        margin-right: 0px;
        margin-bottom: 0px;
    }
    -->
    </style>
    <link href="../style.css" rel="stylesheet" type="text/css">
    </head>

    <body>
    <!--#include file="../includes/header.asp" -->


    <table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td width="160" height="500" valign="top" class="leftside"><!--#include file="../includes/link.asp" --></td>
        <td width="100%" valign="top" style="padding:5px;"><br><table width="100%" border="0" cellpadding="0" cellspacing="0">
                  <!--DWLayoutTable-->
                  <tr>
    <td width="75%" >
      <div align="left"><img src="/sks/img/oglasnaDeska.gif"></div></td>
    <td width="25%" style="padding:2px;"><div align="right"><a href="/sks/drazba1/dodajOglas.asp"><b>Dodaj oglas</b></a></div></td>
     
            </tr>
    </table>
    <table width="100%" border="0" cellpadding="0" cellspacing="0" class="mainbody">
                  <!--DWLayoutTable-->
            <tr>
    <td width="75%"style="padding:3px;" bgcolor="">
      <div align="left">Navigacija:&nbsp;<a href="?SubCatID=0">Oglasna Deska</a>
        <%
    While ((RepeatBreadcrumbsnumRows <> 0) AND (NOT Breadcrumbs.EOF))
    %>
          <% If Not Breadcrumbs.EOF Or Not Breadcrumbs.BOF Then %>
            <font size="1">&gt;</font> <a href="?SubCatID=<%=(Breadcrumbs.Fields.Item("catID").Value)%>"><%=(Breadcrumbs.Fields.Item("Kategorija").Value)%></a>
            <% End If ' end Not Breadcrumbs.EOF Or NOT Breadcrumbs.BOF %>
          <%
      RepeatBreadcrumbs
    index=RepeatBreadcrumbsindex+1
      RepeatBreadcrumbs
    numRows=RepeatBreadcrumbsnumRows-1
      Breadcrumbs.MoveNext()
    Wend
    %>
      </div></td>
     <tr>
    <td width="100%" bgcolor=""style="padding:3px;">
     
      <div align="left">
                  <p>

    <table border="0" width="90%" align="center">
                    <tr>
                      <%
    rows_oglasKat = 6
    cols_oglasKat = -1
    Set oglasKat = MXL_Recordset(oglasKat, rows_oglasKat, cols_oglasKat)
    For i = 0 to rows_oglasKat-1
        For j = 0 to cols_oglasKat-1
            currentIndex = i + rows_oglasKat
    j
            If oglasKat.Move(currentIndex, 1) Then
    %>
                        <td><img src="/sks/drazba1/slike/folder.gif">&nbsp;<a href="/sks/drazba1/?SubcatID=<%=(oglasKat.Fields.Item("catID").Value)%>"><%=(oglasKat.Fields.Item("Kategorija").Value)%></a>&nbsp;
    (# )









    </td>

                        <%
            Else
                Response.write "<td>&nbsp;</td>"
            End If   
        Next
        If i <> rowsoglasKat - 1 Then
            Response.write "</tr><tr>"
        End If
    Next
    %>
                    </tr>
                </table>
                  <br>
               
                <% SubCatid = request.querystring("SubCatid")
    if NOT SubCatID = 0 then
    %>
        <% if oglasi.EOF or oglasi.BOF then
        Response.write("")
        Else %>
       
        <table border="0" width="100%" align="center"bgcolor="#A9A58B" style="border-top:1px solid #000000;border-left:1px solid #000000;border-right:1px solid #000000;">
    <tr>
    <td width="20%"><div align="left"><strong>Izdelek</strong></div></td>
    <td width="20%"><div align="center"><strong>Začetek oglasa</strong></div></td>
    <td width="20%"><div align="center"><strong>Konec oglasa</strong></div></td>
    <td width="20%"><div align="center"><strong>Ogledov</strong></div></td>
    <td width="20%"><div align="center"><strong>Cena</strong></div></td>
    </tr></table>


        <%
    While ((Repeat1
    numRows <> 0) AND (NOT oglasi.EOF))
    %>
          <table border="0" width="100%" align="center"STYLE="BORDER-top:1PX SOLID #000000;BORDER-left:1PX SOLID #000000;BORDER-right:1PX SOLID #000000; padding:2px;"cellpadding="0" cellspacing="0">
            <tr><%If (Repeat1numRows Mod 2) Then%> <tr style="background-color:#f4f4f4">
          <%Else%>
        <tr style="background-color:#ffffff">
          <%End If%>         
              <td width="20%"><div align="left"><a href="/sks/drazba1/detail.asp?oglasID=<%=(oglasi.Fields.Item("oglasID").Value)%>&PF=<%=(oglasi.Fields.Item("author
    id").Value)%>"><%=(oglasi.Fields.Item("Izdelek").Value)%></a>&nbsp;&nbsp;         
            <%   If Len(Trim(oglasi.Fields("slika"))) > 0 Then
                    Response.Write "<IMG SRC=""/sks/drazba1/slike/pic.gif"">&nbsp;"
                    End If
                 If oglasi.Fields("hits") >= 10 Then
                    Response.Write "<IMG SRC=""/sks/drazba1/slike/hot.gif"" >"
                End If
                %>
                   
             
              </div></td>
          <td width="20%"><div align="center"><font size="1"><%=(oglasi.Fields.Item("datumZacetek").Value)%></font></div></td>
          <td width="20%"><div align="center"><font size="1"><%=(oglasi.Fields.Item("datumKonec").Value)%></font></div></td>
          <td width="20%"><div align="center"><%=(oglasi.Fields.Item("Hits").Value)%></div></td>
          <td width="20%"><div align="right"><%= FormatCurrency((oglasi.Fields.Item("cena").Value), 2, -2, -2, -2) %></div></td>
                    </tr>
          </table>
          <%
      Repeat1
    index=Repeat1index+1
      Repeat1
    numRows=Repeat1_numRows-1
      oglasi.MoveNext()
    Wend
    %>
    <table width="100%" align="center"height="1" border="0" cellpadding="0" cellspacing="0" bgcolor="#000000">
                  <!--DWLayoutTable-->
                  <tr>
    <td width="100%">
    </td></tr></table><%end if%> <% end if ' response %>
                  <br>
                  <br>
                 
                  </p>
    </div></td>
    </tr>
    </table><br></td>
         <td width="160" align="center" valign="top">


    <!--#include file="desna
    stran.asp" -->
    <br></td>
      </tr>
    </table></td>
      </tr>
    <!--#include file="../includes/footer.asp" -->
    </body>
    </html>
    <%
    oglasKat.Close()
    Set oglasKat = Nothing
    %>
    <%
    oglasi.Close()
    Set oglasi = Nothing
    %>


    Text in red is where my categories are displayed and # is where I would like to show numbers.


































































































































































































































































Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are 10 types of people in the world, those who can read binary, and those who can't.”