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.

“The greatest performance improvement of all is when a system goes from not-working to working.” - John Ousterhout