Showing count of table next to menu item

Tips submitted by ASPMaker users

Showing count of table next to menu item

Postby roctt » Sat Jul 07, 2007 10:47 am

Employee (26 ) <-----menu item showing count of employees in database
Records (1200) <-----menu item showing count of records in database


Open header.asp in v5+ or ewmenu.asp in v6

and place asp code next to menu item


example: Employee <% code below %>


<%
xDb_Conn_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("yourdatabase.mdb") & ";Jet OLEDB:Database Password=xxxx;"

'now we will set up an sql statement to do the counting
Dim mySQL2
mySQL2 = "Select Count(*) AS myTotal FROM Employee"

'here's a recordset being setup to run the connection
'and to run the sql statement thus doing all our math
Dim myRS2
Set myRS2 = Server.CreateObject("ADODB.RecordSet")
myRS2.Open mySQL2, xDb_Conn_Str

'and now we write it to the page
Response.Write myRS2("myTotal")

' and we ALWAYS want to close our connections and recordsets!!!
myRS2.Close
Set myRS2 = Nothing

%>




IMPORTANT: if adding another count eg. Records <%code below%>
just duplicate code and change all instances of mySQL2 & myRS2 to mySQL3 & myRS3 and so on......
also change the select count to reflect the table in the database from Employee to Records
roctt
 

autogenerate the count by modifying the template

Postby Squeek » Mon Oct 08, 2007 9:35 am

unzip ewmenu.asp from the template

change this:

<!--##=sTableSecChkStart##-->
<tr><td><span class="aspmaker"><a href="<!--##=sFn##-->?cmd=resetall"><!--##=TABLE.TblCaption##--></a></span></td></tr>
<!--##=sTableSecChkEnd##-->

to this:

<!--##=sTableSecChkStart##-->
<tr><td><span class="aspmaker"><a href="<!--##=sFn##-->?cmd=resetall"><!--##=TABLE.TblCaption##--></a> (<%=getcount("<!--##=TABLE.TblName##-->")%>) </span></td></tr>
<!--##=sTableSecChkEnd##-->

at the end of the script, insert these lines just before the last line (which is <!--##/session##-->):

<%
function getcount(tablename)
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = EW_CURSORLOCATION
rs.Open "select count(*) as rcount from " & tablename, conn, 1, 2
getcount=rs("rcount")
end function
%>


Update the zip file with the new version of fsmenu.asp and regenerate.
Squeek
 

oops...

Postby Squeek » Mon Oct 08, 2007 9:55 am

ok, I guess we should rs.close and set rs=nothing before the end function.

<%
function getcount(tablename)
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = EW_CURSORLOCATION
rs.Open "select count(*) as rcount from " & tablename, conn, 1, 2
getcount=rs("rcount")
rs.close
set rs=nothing
end function
%>
Squeek
 

final complete list of changes required

Postby Squeek » Fri Nov 02, 2007 1:50 pm

ewmenu.asp:
Modified line 46 from this:
<tr><td><span class="aspmaker"><a href="<!--##=sFn##-->?cmd=resetall"><!--##=TABLE.TblCaption##--></a></span></td></tr>

to this:

<tr><td><span class="aspmaker"><a href="<!--##=sFn##-->?cmd=resetall"><!--##=TABLE.TblCaption##--></a>
<!--## if TABLE.TblType="TABLE" or TABLE.TblType="VIEW" then##-->
(<%=getcount("[<!--##=TABLE.TblName##-->]")%>)
<!--##End If##-->
</span></td></tr>

to include record count for each table or view
Also added these lines after (what is now) line 73:

<%
function getcount(tablename)
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = EW_CURSORLOCATION
rs.Open "select count(*) as rcount from " & tablename, conn, 1, 2
getcount=rs("rcount")
rs.close
set rs=nothing
end function
%>
Squeek
 

Re: Showing count of table next to menu item

Postby shwetanain » Thu Mar 20, 2008 12:49 am

This CODE WORK PROPERLY FOR TABLES
but if your project include any VIEWS (Queries) they got corrupted and does'nt display result

Please Fix if anyone Can
shwetanain
 

Re: Showing count of table next to menu item

Postby Squeek » Fri May 02, 2008 3:31 am

[quote="shwetanain"]This CODE WORK PROPERLY FOR TABLES
but if your project include any VIEWS (Queries) they got corrupted and does'nt display result

Please Fix if anyone Can[/quote]

So far it has worked fine for my views, but if you're having trouble, you can prevent it from doing the recordcount easily. Change this line:

<!--## if TABLE.TblType="TABLE" or TABLE.TblType="VIEW" then##-->


to this:

<!--## if TABLE.TblType="TABLE" then##-->
Squeek
 

Re: Showing count of table next to menu item

Postby mithanbaba » Tue Jan 20, 2015 2:58 pm

For the Count, Average functions and all other math functions, SQL or math criteria can be added to Count or average only specific Values called from a table (Look Up table for the criteria or the where statement of the function)

Eg: Sum or count in Add and Edit, View and List




________________
atif
mithanbaba
 
Posts: 1
Joined: Tue Jan 20, 2015 2:56 pm


Return to User Submitted Tips (ASPMaker)