Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringAdmin Email List

Author : Andy Humm
Subject : Admin Email List - Sorting Option
Posted : 09 June 2008, 03:38
Message :

Using the admin_emaillist.asp I notice that the listing is centered around the user name and sorted by their MEMBER_ID. Initially I have changed the sort option from the Member_ID to sort the list by the Members Name alphabetically. (M_NAME) You will see the four options I have added, three which have been commented for reference.

admin_emaillist.asp lines 59-64
'## Forum_SQL - Find all records with the search criteria in them
strSql = "SELECT M_NAME, M_EMAIL, M_POSTS "
strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS "
strSql3 = " WHERE M_STATUS = " & 1
strSql4 = " ORDER BY M_NAME ASC " '##sort by member name
'strSql4 = " ORDER BY MEMBER_ID ASC " '##sort by member id
'strSql4 = " ORDER BY M_POSTS ASC " '##sort by number of posts
'strSql4 = " ORDER BY M_EMAIL ASC " '##sort by email address

To make the admail_emaillist.asp more flexible to the user, I am looking to add a pull down option where we can select the sort choice based on a value from strSql4. I have grabbed this piece of code and adjusted the names in option colours. I wonder if someone could assist is showing us how to tie the option box so that we utilise the different strSql4's. I am stumped here how to tie the two together maybe an 'if' statement is required..
Secondly I will be looking to add this new option box on the same line as the NOTE: The following table will show you a list of all users of this forum, and their e-mail addresses.
Any further help will be appreciated.
" <select style=""width:140;"" name=""emailsort"">" & VBNewLine & _
" <option value=""0""" & checkSelect(stremailsort,"0") & ">" Sort by User name "</option>" & VBNewLine & _
" <option value=""1""" & checkSelect(stremailsort,"1") & ">" Sort by Member ID "</option>" & VBNewLine & _
" <option value=""2""" & checkSelect(stremailsort,"2") & ">" Sort by Posts "</option>" & VBNewLine & _
" <option value=""3""" & checkSelect(stremailsort,"3") & ">" Sort by Posts "</option>" & VBNewLine & _
" </select>" & VBNewLine & _
Thank you andy

<

Reply Author: Carefree
Replied on: 09 June 2008, 04:12
Message:

Let's start with the easy one. See this line?

Code:

" <option value=""3""" & checkSelect(stremailsort,"3") & ">" Sort by Posts "</option>" & VBNewLine & _

I'm fairly sure you mean "Sort by Email Address ".
Back to the initial query. Using an if/then series; you could do something like this:

Code:

Option Explicit
strSort = Request.Form("emailsort")
if strSort > "" then
	if strSort = "0" then strSql4 = " ORDER BY M_NAME ASC "
	else if strSort = "1" then strSql4 = " ORDER BY MEMBER_ID ASC "
	else if strSort = "2" then strSql4 = " ORDER BY M_POSTS ASC "
	else strSql4 = " ORDER BY M_EMAIL ASC "
	end if
strSql = "SELECT M_NAME, M_EMAIL, M_POSTS "
strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS "
strSql3 = " WHERE M_STATUS = " & 1
	

	
		
All the stuff you want to do with the results in here...	
	
	
else	
" <select style=""width:140;"" name=""emailsort"">" & VBNewLine & _
	" <option value=""0""" & checkSelect(stremailsort,"0") & ">" Sort by User Name "</option>" & VBNewLine & _
	" <option value=""1""" & checkSelect(stremailsort,"1") & ">" Sort by Member ID "</option>" & VBNewLine & _
	" <option value=""2""" & checkSelect(stremailsort,"2") & ">" Sort by Posts "</option>" & VBNewLine & _
	" <option value=""3""" & checkSelect(stremailsort,"3") & ">" Sort by EMail "</option>" & VBNewLine & _
" </select>" & VBNewLine & _
end if

However, I believe that if you get this mod, it'll save you a lot of work: EMail Search <

Reply Author: Andy Humm
Replied on: 09 June 2008, 04:26
Message:

Carefree absolutely quick on the response and I thank you.. Yes the 'easy one' you were right in my intenstions, missed it on the cut and paste.. Just the hard bit now getting this routine incorporated in the page. Ideally I would like to add the option pull down into the page on the same line as the NOTE: The following table will show you a list of all users of this forum, and their e-mail addresses. thank you so far
BTW here is my admin_emaillist.asp text link for reference
<

Reply Author: Andy Humm
Replied on: 09 June 2008, 05:37
Message:

Further to above, I have created a new cell where I would like the option box placed on image where the 'ss' is (top left corner)

The code around the table head is:

Code:

		"        </tr>" & vbNewLine & _
		"        <tr>" & vbNewLine & _
		"        <td width=""20%"" ><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>ss</td>" & vbNewLine & _
		"          <td align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>The following table shows a list of all users of this forum, and their e-mail addresses.</font></td>" & vbNewLine
if maxpages > 1 then
	Response.Write	"          <td width=""15%"" align=""right"">" & vbNewLine & _
			"            <table border=""0"" align=""right"">" & vbNewLine & _
			"              <tr>" & vbNewLine
	Call DropDownPaging(1)
	Response.Write	"              </tr>" & vbNewLine & _
			"            </table>" & vbNewLine & _
			"          </td>" & vbNewLine
end if

<

Reply Author: Carefree
Replied on: 09 June 2008, 15:34
Message:

Here you go - I have everything done except the selection isn't being sent to the form for action.
EMail List<

Reply Author: Andy Humm
Replied on: 09 June 2008, 16:50
Message:

So far we are getting a little further, thank you carefree.. The page link you provided has a few table formating differences, which I have revamped to the latest. As carefree has said we have a pull down option box but it is not functioning to do the sort. Can anyone assist in our pursuit.. This is what we have:

And the admin_emaillist.asp latest code is here
The lines associated with the changes so far:
59-81 sql
153-159 Option Box

Thank you andy
<

Reply Author: Andy Humm
Replied on: 11 June 2008, 13:03
Message:

Carefree
Initially when I tried your suggested code, I did have a few display errors, ie fonts and layout was peculiar. Looking at the code again, I noticed the following:
" </table" & vbNewLine & _
"<form method=""post"" ID=""EMSR"" action Apologies I have no line numbers as I do not have access to my main computer with editing software..
If table tag is wrong ie </table> and this sorts the layout, could we remove the Member_ID column and stay with the original three coloumns (Member Name, Email Address and Posts)
I wonder if we can get the pulldown option to work. Thank you
Andy<

Reply Author: Carefree
Replied on: 11 June 2008, 14:23
Message:

I'll have the pull-down working today. I'll send you the new code in the next couple of hours.<

Reply Author: Andy Humm
Replied on: 11 June 2008, 15:35
Message:

cheers<

Reply Author: Carefree
Replied on: 11 June 2008, 16:09
Message:

Well, I have the page number pull-down fixed. The sorter is missing something important.
Email List<

Reply Author: Andy Humm
Replied on: 12 June 2008, 07:29
Message:

We had problems getting onto snitz since yesterday and hey presto were back online. At the moment I am not at worksation with access to my forum software.

Carefree: Can yoy confirm the latest txt version is working with the sorter pulldown working for the sorts as you state "I have the page number pull-down fixed. The sorter is missing something important".
Secondly, has this new code remove the Member_ID column? Thank you so much
Andy<

Reply Author: philsbbs
Replied on: 13 June 2008, 14:58
Message:

Just tired this and when changing the page numbers nothing happened.<

Reply Author: Andy Humm
Replied on: 13 June 2008, 17:44
Message:

philsbbs, initially it was not the page numbers that was the problem. I requested to see if we could add a new pulldown option box that provides sorting for the Members Name, Email Address and Number of Posts. (top left hand corner)

From my text file I have the pulldown box there but we can not get it to operate. There is the right sql code for the sorting but we need to tie this into the option box. admin_emaillist.asp text link. This code the pages pulldown option box works okay..
It would be great to get the other pulldown to work..<

Reply Author: Carefree
Replied on: 14 June 2008, 09:29
Message:

It'd probably be easier to just convert the column headings into sorting links, that would also let you reverse sort (which the pulldown box won't do).<

Reply Author: Carefree
Replied on: 15 June 2008, 14:33
Message:

Hey Andy, try this for a starting point. You can sort ascending or descending, any column (including Email), filter by member level or first letter of user name, etc.
EM Search
For those who may be interested, I also rewrote the IP Search page to give it the same functionality.
IP Search
<

Reply Author: Andy Humm
Replied on: 15 June 2008, 17:07
Message:

Carefree, thanks for the great work, I'll download and amend tomorrow..<

Reply Author: Andy Humm
Replied on: 16 June 2008, 03:49
Message:

Carefree Well done I like the work. Thank you
Just an observation, in the IP search, you have saved the IP text file as admin_ip_search including all the references/links to admin_ip_search.asp. The filters and links will not work unless they are changed to the original file name of admin_search_ip.asp<

Reply Author: Andy Humm
Replied on: 16 June 2008, 04:50
Message:

Carefree
I have noticed you get an error when you select a pull down page on the option box Page: 1 of 12 (admin_search_ip.asp)

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/forum/admin_search_ip.asp, line 245
The block of code around line 245 (245 in red)
set rs = Server.CreateObject("ADODB.Recordset")
rs.cachesize = strPageSize
if strSql6 > "" then
rs.open strSql & strSql2 & strSql3 & strSql6, my_Conn, adOpenStatic else
rs.open strSql & strSql2 & strSql3, my_Conn, adOpenStatic
end if
If not (rs.EOF or rs.BOF) then
rs.movefirst
rs.pagesize = strPageSize
rs.absolutepage = mypage '**
maxpages = cLng(rs.pagecount)
arrMemberData = rs.Getrows(strPageSize)
iMemberCount = UBound(arrMemberData, 2)
else
iMemberCount = ""
end if
rs.Close
set rs = nothing
end if

Incidentally the same error occurs on admin_em_search.asp

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/forum/admin_em_search.asp, line 245

Carefree to help with the investigation.
If you open either the Email Search or IP search and have all the members listed selecting the pull down you will receive the error above. However, prior to selecting the page pull down, if you click on one of the sort headers ie members name, then select the pull down page option do do not get the error.
<

Reply Author: Carefree
Replied on: 16 June 2008, 07:20
Message:

Originally posted by Andy Humm
Carefree Well done I like the work. Thank you
Just an observation, in the IP search, you have saved the IP text file as admin_ip_search including all the references/links to admin_ip_search.asp. The filters and links will not work unless they are changed to the original file name of admin_search_ip.asp

Sure they will. I changed 100% of them (including for the filtering and sorting links/routines); so none of them should be looking for the old file name.<

Reply Author: Carefree
Replied on: 16 June 2008, 07:40
Message:

The issue was in strSql6, I fixed it.
The links in my earlier post have had the files replaced. Help yourself.
<

Reply Author: Andy Humm
Replied on: 16 June 2008, 10:00
Message:

thank you... 10 out of ten!<

Reply Author: Andy Humm
Replied on: 17 June 2008, 03:16
Message:

Carefree, I thought we had it sorted, excuse the pun! Since the fix was done regarding the pages pulldown (strsql6) I have noticed te column headers are not sorting???? I have taken the ID column out to allow the page to show in the older computer screens without seeing the text all jam up. Could I ask you to have a butchers at my admin_search_em.txt to see what is glaringly wrong..Incidentally the same has occurred on the admin_search_ip.asp (since the strsql6 fix)
Sorry for hinderance
Ps I have also tried your latest file (admin_em_search.asp, with no changes) too, and the same occurs ..<

Reply Author: Andy Humm
Replied on: 18 June 2008, 04:41
Message:

Carefree I have tried the updated mods on snitzbitz, are you aware that the header sorting will not work on these downloads too.. Email and IP kindest regards andy<

Reply Author: Carefree
Replied on: 18 June 2008, 06:25
Message:

In both:

Change lines 183-185 to say:

Code:


	if strSql6 = "" then
			strSql6 = " ORDER BY M_NAME ASC"
	end if

In EM search, change line 107 to say:

Code:


SearchEM = trim(Request.QueryString("M_EMAIL"))

<

Reply Author: Andy Humm
Replied on: 18 June 2008, 07:00
Message:

Carefree, removing the 0 and placing "" has led us back to
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/forum/admin_search_em.asp, line 248

With the "" every header sort works except Posts M_LEVEL

search ip line 370
if Request.QueryString("method") = "M_LEVEL ASC" then Response.Write("M_LEVEL DESC") else Response.Write("M_LEVEL ASC")
Response.Write """" & dWStatus("Sort by Post Count") & "><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Posts</font></b></a></td>" & vbNewLine & _
I believe this should work looking at the style of the other sort routines..

Only an novice but will the strsql3 have anything to do with it..
from line 157
if isNumeric(varFilter) then
if varFilter > 0 then
strSql3 = strSql3 & " AND M_LEVEL=" & varFilter then strsql3 is used lines 172, 176 180 for M_DATE M_LASTPOSTDATE etc( sorry if this is a red herring)

That strsql6 is upsetting something!<

Reply Author: Carefree
Replied on: 18 June 2008, 07:27
Message:

I didn't just change the 0 to a double-quote, I changed the variable as well. Try using the line provided.<

Reply Author: Andy Humm
Replied on: 18 June 2008, 07:43
Message:

Hi Carefree
I have cut and paste the two suggestions, as seen, and as soon as you open the admin_search and select another page from pull down, the result is the error. However, strange, if you do a sort before the page pull down its okay

Initially opening adminsearch the resultant urls;
http://www.domain.com/forum/admin_search_em.asp Do page pull down
http://www.domain.com/forum/admin_search_em.asp?method=postsdesc&whichpage=3with error
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/forum/admin_search_em.asp, line 248

Reopen a fresh admin search page
http://www.domain.com/forum/admin_search_em.asp select member sort (members sorted asc)
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&MEMBER_IP=&method=M_NAME%20ASC Select another pull down page 3 (redirected to page 3 of 12)
http://www.domain.com/forum/admin_search_em.asp?method=M_NAME+ASC&mode=search&whichpage=3 page reads ok but in both occasions the Number of Posts will not sort

<

Reply Author: Andy Humm
Replied on: 18 June 2008, 07:58
Message:

Notice the following sorting/selecting the various Header Links
Member Name
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&MEMBER_IP=&method=M_NAME%20ASC Email Search
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&M_EMail=&method=M_EMAIL%20ASC IP sort
http://www.domain.com/forum/admin_search_em.asp?link=order&mode=search&method=M_LAST_IP%20ASC Posts
http://www.domain.com/forum/admin_search_em.asp?link=order&mode=search&method=M_LEVEL%20ASC Last Post
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&MEMBER_IP=&method=M_LASTPOSTDATE%20ASC Joined
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&MEMBER_IP=&method=M_DATE%20ASC Country
http://www.domain.com/forum/admin_search_em.asp?link=sort&mode=search&MEMBER_IP=&method=M_COUNTRY%20ASC Last Visit
http://www.domain.com/forum/admin_search_em.asp?method=M_LASTHEREDATE%20ASC

Looking at the way members.asp sorts (which all work and page pull down too) Members.asp sorting member name
http://www.domain.com/forum/members.asp?UserName=&FirstName=0&LastName=0&INITIAL=&link=sort&mode=search&M_NAME=%20&method=nameasc Posts
http://www.domain.com/forum/members.asp?UserName=&FirstName=0&LastName=0&INITIAL=&link=sort&mode=search&M_NAME=%20&method=lastpostdatedesc
any clues?<

Reply Author: Carefree
Replied on: 18 June 2008, 08:15
Message:

Look at line 582, I forgot to change a value from the old sort method.
Change that bit to say:

Code:


		if SortMethod = "" then
			sMethod = "M_NAME asc"
		else
			sMethod = SortMethod
		end if

<

Reply Author: Andy Humm
Replied on: 18 June 2008, 09:42
Message:

Thank you carefree for your patience and endeavour.
I resolved the 'Number of Posts' sort, the sqlstr had M_LEVEL instead of M_POSTS

Did you notice that on the members.asp if your filter all the database names beginning with a letter ie 'D', then with these ouputed names, you sort a heading, say Posts, then only the outputed names beginning with D are sorted.
Wheresas with the Admin_Search_Email and Admin_Search_IP if you filter the names 'D' and then sort by a header again Posts, the complete database is sorted by Posts instead of the filtered output..????? Is this the next puzzle?<

Reply Author: Carefree
Replied on: 18 June 2008, 10:00
Message:

So the question becomes - has the admin changed his mind and selected a different sort or does he want a multiple-level filter & sort? Whichever way it's programmed, there'll be some who want the other.<

Reply Author: Andy Humm
Replied on: 18 June 2008, 11:04
Message:

True.. No I'm more than happy as we have it now.. It's just an observation of the way it operates, in comparision to members.asp ..I appreciate the support you provide<

Admin Email List - Sorting Option

Topic

Replies ...