Tipu
|
| Posted: 05/31/2002, 8:44 PM |
|
I am using this statement in my page to count all the records. but when i try to use any of the search options it never show the total count.basically it show me that i have 100 users but when i say show me all the active onecs. it list all of them but won't show total count using {TotalMembers}.if you think there is any other way of doing this please help?????
-----------------(line 440)
if sWhere="" then sWhere="1"
sWhere=replace(sWhere,"WHERE","")
setvar "test", dlookup("members ","count(*)",sWhere)
--------------------
here is the complete page
-------------------------
<%
'
' Filename: AdminMembersList.asp
' Generated with CodeCharge 2.0.5
' ASP 2.0 & Templates.ccp build 11/30/2001
'
'-------------------------------
' AdminMembersList CustomIncludes begin
%>
<!-- #INCLUDE FILE="../zzz/common.asp" -->
<%
' AdminMembersList CustomIncludes end
'-------------------------------
'===============================
' Save Page and File Name available into variables
'-------------------------------
sFileName = "AdminMembersList.asp"
sTemplateFileName = "AdminMembersList.html"
'===============================
'===============================
' AdminMembersList PageSecurity begin
CheckSecurity(3)
' AdminMembersList PageSecurity end
'===============================
'===============================
' AdminMembersList Open Event begin
' AdminMembersList Open Event end
'===============================
'===============================
' AdminMembersList OpenAnyPage Event begin
' AdminMembersList OpenAnyPage Event end
'===============================
'===============================
'Save the name of the form and type of action into the variables
'-------------------------------
sAction = GetParam("FormAction")
sForm = GetParam("FormName")
'===============================
' AdminMembersList Show begin
'===============================
' Display page
'-------------------------------
' Load HTML template for this page
'-------------------------------
LoadTemplate sAppPath & sTemplateFileName, "main"
'-------------------------------
' Load HTML template of Header and Footer
'-------------------------------
'-------------------------------
SetVar "FileName", sFileName
'-------------------------------
' Step through each form
'-------------------------------
Search_Show
members_Show
'-------------------------------
' Process page templates
'-------------------------------
Parse "main", False
'-------------------------------
' Output the page to the browser
'-------------------------------
Response.write PrintVar("main")
' AdminMembersList Show end
'-------------------------------
' Destroy all object variables
'-------------------------------
' AdminMembersList Close Event begin
' AdminMembersList Close Event end
cn.Close
Set cn = Nothing
UnloadTemplate
'===============================
'===============================
' Display Search Form
'-------------------------------
Sub Search_Show()
Dim sFormTitle: sFormTitle = "Search"
Dim sActionFileName: sActionFileName = "AdminMembersList.asp"
Dim ss_security_level_idDisplayValue: ss_security_level_idDisplayValue = "All"
Dim ss_members_status_idDisplayValue: ss_members_status_idDisplayValue = "All"
Dim smembes_showDisplayValue: smembes_showDisplayValue = "All"
'-------------------------------
' Search Open Event begin
' Search Open Event end
'-------------------------------
SetVar "FormTitle", sFormTitle
SetVar "ActionPage", sActionFileName
'-------------------------------
' Set variables with search parameters
'-------------------------------
flds_members_first_name = GetParam("s_members_first_name")
flds_members_last_name = GetParam("s_members_last_name")
flds_members_company = GetParam("s_members_company")
flds_members_email = GetParam("s_members_email")
flds_security_level_id = GetParam("s_security_level_id")
flds_members_status_id = GetParam("s_members_status_id")
flds_members_x_date = GetParam("s_members_x_date")
flds_xtra_info = GetParam("s_xtra_info")
fldmembes_show = GetParam("membes_show")
'-------------------------------
' Search CustomShow Event begin
'-------------------------------
SetVar "s_members_first_name", ToHTML(flds_members_first_name)
SetVar "s_members_last_name", ToHTML(flds_members_last_name)
SetVar "s_members_company", ToHTML(flds_members_company)
SetVar "s_members_email", ToHTML(flds_members_email)
SetVar "SearchLBs_security_level_id", ""
SetVar "Selected", ""
SetVar "ID", ""
SetVar "Value", ss_security_level_idDisplayValue
Parse "SearchLBs_security_level_id", True
LOV = Split("0;Xed;1;Active Member;2;Editors;3;Staff;4;Admin", ";")
if (ubound(LOV) mod 2) = 1 then
for i = 0 to ubound(LOV) step 2
SetVar "ID", LOV(i) : SetVar "Value", LOV(i+1)
if cstr(LOV(i)) = cstr(flds_security_level_id) then SetVar "Selected", "SELECTED" else SetVar "Selected", ""
Parse "SearchLBs_security_level_id", True
next
end if
SetVar "SearchLBs_members_status_id", ""
SetVar "Selected", ""
SetVar "ID", ""
SetVar "Value", ss_members_status_idDisplayValue
Parse "SearchLBs_members_status_id", True
openrs rss_members_status_id, "select club_status_id, club_status_name from members_statuses order by 2"
while not rss_members_status_id.EOF
SetVar "ID", GetValue(rss_members_status_id, 0) : SetVar "Value", GetValue(rss_members_status_id, 1)
if cstr(GetValue(rss_members_status_id, 0)) = cstr(flds_members_status_id) then SetVar "Selected", "SELECTED" else SetVar "Selected", ""
Parse "SearchLBs_members_status_id", True
rss_members_status_id.MoveNext
wend
set rss_members_status_id = nothing
SetVar "s_members_x_date", ToHTML(flds_members_x_date)
SetVar "s_xtra_info", ToHTML(flds_xtra_info)
SetVar "SearchLBmembes_show", ""
SetVar "Selected", ""
SetVar "ID", ""
SetVar "Value", smembes_showDisplayValue
Parse "SearchLBmembes_show", True
LOV = Split("0;xed;1;Active Members", ";")
if (ubound(LOV) mod 2) = 1 then
for i = 0 to ubound(LOV) step 2
SetVar "ID", LOV(i) : SetVar "Value", LOV(i+1)
if cstr(LOV(i)) = cstr(fldmembes_show) then SetVar "Selected", "SELECTED" else SetVar "Selected", ""
Parse "SearchLBmembes_show", True
next
end if
'-------------------------------
' Search CustomShow Event end
'-------------------------------
'-------------------------------
' Search Close Event begin
' Search Close Event end
'-------------------------------
Parse "FormSearch", False
End Sub
'===============================
'===============================
' Display Grid Form
'-------------------------------
Sub members_Show()
'-------------------------------
' Initialize variables
'-------------------------------
Dim rs
Dim sWhere : sWhere = ""
Dim sOrder : sOrder = ""
Dim sSQL : sSQL = ""
Dim sFormTitle: sFormTitle = "SVASE Members Section"
Dim HasParam : HasParam = false
Dim iSort : iSort = ""
Dim iSorted : iSorted = ""
Dim sDirection : sDirection = ""
Dim sSortParams : sSortParams = ""
Dim iRecordsPerPage : iRecordsPerPage = 50
Dim iCounter : iCounter = 0
Dim iPage : iPage = 0
Dim bEof : bEof = False
Dim iTmpI : iTmpI = 0
Dim iTmpJ : iTmpJ = 0
Dim sCountSQL : sCountSQL = ""
Dim sActionFileName : sActionFileName = "AdminMembersEdit.asp"
SetVar "TransitParams", "membes_show=" & ToURL(GetParam("membes_show")) & "&s_members_company=" & ToURL(GetParam("s_members_company")) & "&s_members_email=" & ToURL(GetParam("s_members_email")) & "&s_members_first_name=" & ToURL(GetParam("s_members_first_name")) & "&s_members_last_name=" & ToURL(GetParam("s_members_last_name")) & "&s_members_status_id=" & ToURL(GetParam("s_members_status_id")) & "&s_members_x_date=" & ToURL(GetParam("s_members_x_date")) & "&s_security_level_id=" & ToURL(GetParam("s_security_level_id")) & "&s_xtra_info=" & ToURL(GetParam("s_xtra_info")) & "&"
SetVar "FormParams", "membes_show=" & ToURL(GetParam("membes_show")) & "&s_members_company=" & ToURL(GetParam("s_members_company")) & "&s_members_email=" & ToURL(GetParam("s_members_email")) & "&s_members_first_name=" & ToURL(GetParam("s_members_first_name")) & "&s_members_last_name=" & ToURL(GetParam("s_members_last_name")) & "&s_members_status_id=" & ToURL(GetParam("s_members_status_id")) & "&s_members_x_date=" & ToURL(GetParam("s_members_x_date")) & "&s_security_level_id=" & ToURL(GetParam("s_security_level_id")) & "&s_xtra_info=" & ToURL(GetParam("s_xtra_info")) & "&"
'-------------------------------
' Build WHERE statement
'-------------------------------
pmembes_show = GetParam("membes_show")
if IsNumeric(pmembes_show) and not isEmpty(pmembes_show) then pmembes_show = ToSQL(pmembes_show, "Number") else pmembes_show = Empty
if not isEmpty(pmembes_show) then
HasParam = true
sWhere = sWhere & "m.[membes_show]=" & pmembes_show
end if
ps_members_company = GetParam("s_members_company")
if not isEmpty(ps_members_company) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_company] like '%" & replace(ps_members_company, "'", "''") & "%'"
end if
ps_members_email = GetParam("s_members_email")
if not isEmpty(ps_members_email) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_email] like '%" & replace(ps_members_email, "'", "''") & "%'"
end if
ps_members_first_name = GetParam("s_members_first_name")
if not isEmpty(ps_members_first_name) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_first_name] like '%" & replace(ps_members_first_name, "'", "''") & "%'"
end if
ps_members_last_name = GetParam("s_members_last_name")
if not isEmpty(ps_members_last_name) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_last_name] like '%" & replace(ps_members_last_name, "'", "''") & "%'"
end if
ps_members_status_id = GetParam("s_members_status_id")
if IsNumeric(ps_members_status_id) and not isEmpty(ps_members_status_id) then ps_members_status_id = ToSQL(ps_members_status_id, "Number") else ps_members_status_id = Empty
if not isEmpty(ps_members_status_id) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_status_id]=" & ps_members_status_id
end if
ps_members_x_date = GetParam("s_members_x_date")
if not isEmpty(ps_members_x_date) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[members_x_date] like '%" & replace(ps_members_x_date, "'", "''") & "%'"
end if
ps_security_level_id = GetParam("s_security_level_id")
if IsNumeric(ps_security_level_id) and not isEmpty(ps_security_level_id) then ps_security_level_id = ToSQL(ps_security_level_id, "Number") else ps_security_level_id = Empty
if not isEmpty(ps_security_level_id) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[security_level_id]=" & ps_security_level_id
end if
ps_xtra_info = GetParam("s_xtra_info")
if not isEmpty(ps_xtra_info) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "m.[xtra_info] like '%" & replace(ps_xtra_info, "'", "''") & "%'"
end if
if HasParam then
sWhere = " AND (" & sWhere & ")"
end if
'-------------------------------
' Build ORDER BY statement
'-------------------------------
sOrder = " order by m.members_x_date Asc"
iSort = GetParam("Formmembers_Sorting")
iSorted = GetParam("Formmembers_Sorted")
sDirection = ""
if IsEmpty(iSort) then
SetVar "Form_Sorting", ""
else
if iSort = iSorted then
SetVar "Form_Sorting", ""
sDirection = " DESC"
sSortParams = "Formmembers_Sorting=" & iSort & "&Formmembers_Sorted=" & iSort & "&"
else
SetVar "Form_Sorting", iSort
sDirection = " ASC"
sSortParams = "Formmembers_Sorting=" & iSort & "&Formmembers_Sorted=" & "&"
end if
if iSort = 1 then sOrder = " order by m.[members_first_name]" & sDirection
if iSort = 2 then sOrder = " order by m.[members_last_name]" & sDirection
if iSort = 3 then sOrder = " order by m.[members_company]" & sDirection
if iSort = 4 then sOrder = " order by m.[members_x_date]" & sDirection
if iSort = 5 then sOrder = " order by m1.[club_status_name]" & sDirection
if iSort = 6 then sOrder = " order by m.[security_level_id]" & sDirection
if iSort = 7 then sOrder = " order by m.[xtra_info]" & sDirection
end if
'-------------------------------
' Build base SQL statement
'-------------------------------
sSQL = "select [m].[members_ID] as m_members_ID, " & _
"[m].[members_company] as m_members_company, " & _
"[m].[members_email] as m_members_email, " & _
"[m].[members_first_name] as m_members_first_name, " & _
"[m].[members_last_name] as m_members_last_name, " & _
"[m].[members_status_id] as m_members_status_id, " & _
"[m].[members_x_date] as m_members_x_date, " & _
"[m].[membes_show] as m_membes_show, " & _
"[m].[security_level_id] as m_security_level_id, " & _
"[m].[xtra_info] as m_xtra_info, " & _
"[m1].[club_status_id] as m1_club_status_id, " & _
"[m1].[club_status_name] as m1_club_status_name " & _
" from [members] m, [members_statuses] m1" & _
" where [m1].[club_status_id]=m.[members_status_id] "
'-------------------------------
'-------------------------------
' members Open Event begin
' members Open Event end
'-------------------------------
'-------------------------------
' Assemble full SQL statement
'-------------------------------
sSQL = sSQL & sWhere & sOrder
if sCountSQL = "" then
iTmpI = instr(lcase(sSQL), "select")
iTmpJ = instr(lcase(sSQL), "from") - 1
sCountSQL = replace(sSQL, mid(sSQL, iTmpI + 6, iTmpJ - 6), " count(*) ")
iTmpI = instr(lcase(sCountSQl), "order by")
if iTmpI > 1 then sCountSQL = left(sCountSQL, iTmpI - 1)
end if
'-------------------------------
SetVar "FormTitle", sFormTitle
'-------------------------------
' Process the link to the record page
'-------------------------------
SetVar "FormAction", sActionFileName
'-------------------------------
'-------------------------------
' Process the parameters for sorting
'-------------------------------
SetVar "SortParams", sSortParams
'-------------------------------
'-------------------------------
' Open the recordset
'-------------------------------
openrs rs, sSQL
'-------------------------------
'-------------------------------
' Process empty recordset
'-------------------------------
if rs.eof then
set rs = nothing
SetVar "DListmembers", ""
Parse "membersNoRecords", False
SetVar "membersNavigator", ""
Parse "Formmembers", False
exit sub
end if
'-------------------------------
'-------------------------------
' Prepare the lists of values
'-------------------------------
asecurity_level_id = Split("0;xed;1;Active Member;2;Editors;3;Staff;4;Admin", ";")
'-------------------------------
'-------------------------------
' Initialize page counter and records per page
'-------------------------------
iRecordsPerPage = 50
iCounter = 0
'-------------------------------
'-------------------------------
' Process page scroller
'-------------------------------
iPage = GetParam("Formmembers_Page")
openrs rsCount, sCountSQL
dResult = CLng(rsCount.fields(0).value) / iRecordsPerPage
iPageCount = int(dResult)
if iPageCount < dResult then iPageCount = iPageCount + 1
SetVar "membersPageCount", iPageCount
set rsCount = nothing
if IsEmpty(iPage) then
iPage = 1
else
if iPage = "last" then iPage = iPageCount
end if
while not rs.eof and iCounter < (iPage-1)*iRecordsPerPage
rs.movenext
iCounter = iCounter + 1
wend
iCounter = 0
'-------------------------------
'-------------------------------
' Display grid based on recordset
'-------------------------------
while not rs.EOF and iCounter < iRecordsPerPage
'-------------------------------
' Create field variables based on database fields
'-------------------------------
fldView_URLLink = "AdminMembersEdit.asp"
fldView_members_ID = GetValue(rs, "m_members_ID")
fldmembers_company = GetValue(rs, "m_members_company")
fldmembers_email = GetValue(rs, "m_members_email")
fldmembers_first_name = GetValue(rs, "m_members_first_name")
fldmembers_ID = GetValue(rs, "m_members_ID")
fldmembers_last_name = GetValue(rs, "m_members_last_name")
fldmembers_status_id = GetValue(rs, "m1_club_status_name")
fldmembers_x_date = GetValue(rs, "m_members_x_date")
fldsecurity_level_id = GetValue(rs, "m_security_level_id")
fldxtra_info = GetValue(rs, "m_xtra_info")
fldView= "View"
'-------------------------------
' members CustomShow Event begin
'-------------------------------
' members Show Event begin
'-------------------------------
if sWhere="" then sWhere="1"
sWhere=replace(sWhere,"WHERE","")
setvar "test", dlookup("members ","count(*)",sWhere)
fldmembers_first_name = "<a href=""mailto:" & fldmembers_email & """>" & fldmembers_first_name & "</a>"
' members Show Event end
'-------------------------------
'-------------------------------
'-------------------------------
' Replace Template fields with database values
'-------------------------------
SetVar "members_ID", ToHTML(fldmembers_ID)
SetVar "View", ToHTML(fldView)
SetVar "View_URLLink", fldView_URLLink
SetVar "PrmView_members_ID", ToURL(fldView_members_ID)
SetVar "members_first_name", (fldmembers_first_name)
SetVar "members_last_name", ToHTML(fldmembers_last_name)
SetVar "members_company", ToHTML(fldmembers_company)
SetVar "members_email", (fldmembers_email)
SetVar "members_x_date", ToHTML(fldmembers_x_date)
SetVar "members_status_id", ToHTML(fldmembers_status_id)
fldsecurity_level_id = getValFromLOV(fldsecurity_level_id, asecurity_level_id)
SetVar "security_level_id", ToHTML(fldsecurity_level_id)
SetVar "xtra_info", ToHTML(fldxtra_info)
Parse "DListmembers", True
'-------------------------------
' members CustomShow Event end
'-------------------------------
'-------------------------------
' Move to the next record and increase record counter
'-------------------------------
rs.MoveNext
iCounter = iCounter + 1
wend
'-------------------------------
'-------------------------------
' members Navigation begin
'-------------------------------
bEof = rs.eof
if rs.eof and iPage = 1 then
SetVar "membersNavigator", ""
else
if bEof then
SetVar "membersNavigatorLastPage", "_"
else
SetVar "NextPage", (iPage + 1)
end if
if iPage = 1 then
SetVar "membersNavigatorFirstPage", "_"
else
SetVar "PrevPage", (iPage - 1)
end if
SetVar "membersCurrentPage", iPage
Parse "membersNavigator", False
end if
'-------------------------------
' members Navigation end
'-------------------------------
'-------------------------------
' Finish form processing
'-------------------------------
set rs = nothing
SetVar "membersNoRecords", ""
Parse "Formmembers", False
'-------------------------------
' members Close Event begin
' members Close Event end
'-------------------------------
End Sub
'===============================
%>
|
|
|
 |
Nicole
|
| Posted: 06/01/2002, 3:16 AM |
|
Tipu,
to get correct number of counted records you should use the same WHERE clause both for Grid form and look up function. Please check Where to be the same. Note, that count records code is better to place in form Open event, because Before Show event code is executed as many times as many records are displayed on grid.
|
|
|
|
Tipu
|
| Posted: 06/01/2002, 3:41 PM |
|
I wish i Knew wat you were talking about.
can you please tell me wat should i do to count the record here. i tried the book store example but for some reason that didn't work and when i put this statement in open event it gives error
|
|
|
|
|
