This idea is in regards to Oxle's sponsoring member mod found here:

http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=51932

Has anyone updated this mod to include a start/stop date for the sponsorhip status? I have this mod installed but would like to grant sponsorship status annually. So when a member makes a site donation I would like to also enter a start and stop date. Has anyone see this done with this mod?<

Good thought ; I was thinking of something similar myself....haven't found the time and inspiration to make it.<

Just what I was thinking about a few days ago! Haven't had the time to look into it any more though.. Could anyone just give a push in the right direction on how to do this? <

How about this? I will PayPal $25 (I know it's not much but hopefully more will offer up some help) to the developer who provides the tweek to this MOD. I hope Marcel takes this...I know your busy but this would really be a good mod to this MOD.<

Shannon, I'd be happy to do it, if I could to it. But, as I said before to other people who asked me to do something ; I am no programmer. The only thing I can do is copy/paste programming ... <

Any other takers?<

Well, I created this mod based on the 'Custom Member fields mod', so I guess the only thing that needs to be done is create two new columns in the FORUM_MEMBERS table, M_SPONSORSTART and M_SPONSOREND (or just one, M_SPONSOREND?)
After that, we need an option for admins to sét these values, together with the M_SPONSORLEVEL (that's already in there...)
If that's done, we need to change the function that checks for the sponsorlevel ;
Just a wild guess...<

Still looking for a solution to this. Any takers?<

It shouldn't be that hard and do you really need a start date?!? I do not have this mod installed but chances are that somewhere within this mod an sql statemement is being made that resembles the following:
strSql = "SELECT * FROM " & strMemberTablePrefix & "MEMBERS
strSql = strSql & " WHERE MEMBER_ID = " & intMemberID
set rs = my_Conn.Execute (strSql)

Add the following beneath the set command:
mStDate = rs("M_START")
mStDate = rs("M_END")

strSql = "update " & strMemberTablePrefix & " MEMBERS set M_START= " & DateToStr(dtDateTime) & " WHERE MEMBER_ID = " & intMemberID
my_Conn.Execute (strSql)

strSql = "update " & strMemberTablePrefix & " MEMBERS set M_END= " & (DateToStr(dtDateTime))+ 10000000000) & " WHERE MEMBER_ID = " & intMemberID
my_Conn.Execute (strSql)

I am not going to install this mod into my forum but something along those lines otta work. Then all you would need to do is run an update statement if end date is < then todays day set end date to 0 and start date to 0<

What do it do I added this mod and the only thing I can see that was added was the drop down box in the members profile. What else dose it do or show?<

Marcel.....any chance you added the end-date to your sponsorship MOD? I still would pay for the update to this MOD. Any takers?<

I'm still looking for someone to take on the job of adding a stop date for this sponsorship mod. What I hope to have is the ability to turn on the sponsorship for a period of time, like one year. Any takers interested in coding this up for some PayPal funds? I just don't have the time.<

OK. I am finally getting around to attemping to add an expiration date to the Sponsorship Mod. Here is what I have done so far. I will post what I did and then my question:

1. Added the following (in red) to inc_header:
strSql = "SELECT " & strMemberTablePrefix & "MEMBERS.M_SPONSORLEVEL"
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " WHERE " & strDBNTSQLName & " = '" & ChkString(strDBNTUserName, "SQLString") & "'"
strSql = strSql & " AND M_SPONSORDATE > '" & strForumTimeAdjust & "'" set rsSponsor = my_Conn.Execute (strSql)

2a. Added the following to pop_profile:
if mLev > 0 then '## is Member
if mLev = 4 then
'## Forum_SQL
strSql = "SELECT " & strMemberTablePrefix & "MEMBERS.MEMBER_ID"
'---------- MarcelG: Start SponsorMOD
strsql = strSql & ", " & strMemberTablePrefix & "MEMBERS.M_SPONSORLEVEL"
strsql = strSql & ", " & strMemberTablePrefix & "MEMBERS.M_SPONSORDATE" '--------- MarcelG: End SponsorMOD
strsql = strsql & ", " & strMemberTablePrefix & "MEMBERS.M_AGE"

2b. AND furter down in pop_profile.asp I added the following:
strSql = strSql & ", M_SPONSORLEVEL = " & cLng("0" & Request.Form("Sponsorlevel"))
strSql = strSql & ", M_SPONSORDATE = " & cDate("0" & Request.Form("SPONSORDATE"))
3a. In inc-profile I did the following, again in red:
if rs("M_SPONSORLEVEL") = 1 then Response.Write(" selected")
Response.Write ">Supporting Member</option>" & vbNewLine & _
" <option value=""2"""
if rs("M_SPONSORLEVEL") = 2 then Response.Write(" selected")
Response.Write ">Gold Member</option>" & vbNewLine & _
" <option value=""3"""
if rs("M_SPONSORLEVEL") = 3 then Response.Write(" selected")
Response.Write ">Site Sponsor</option>" & vbNewLine & _
" </select>" & vbNewLine
Response.Write " </td>" & vbNewLine & _
" </tr>" & vbNewLine
Response.Write " <tr>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor& """ align=right valign=""top"" nowrap><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>Sponsorship Expiration: </font></b></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ valign=""top"">" & vbNewLine
Response.Write " <input name=""SPONSORDATE"" size=""16"" value=""" & rs("M_SPONSORDATE") & """></font>"
vbNewLine Response.Write " </td>" & vbNewLine & _
" </tr>" & vbNewLine
end if
4. I added a new field to my table called M_SPONSORDATE. It is of the DateTime type and is 16 characters long (per my table editor)

My problem....drum roll please....it doesn't work. I do not get any errors. I can edit a member profile. I can see the new field I added called Sponsorship Expiration. I can see the default value that was entered when the field was created in my table. I enter a new date and submit without any errors but the new date does not write to the database. What am I missing?? Should I not have used the DateTime type for this expiration data? Thank you to anyone who can help.<

Search for the word "update" in pop_profile, after it you will find a series of lines containing "strSql = strSql &...". This is code that creates the update statement for the member and writes it to the database, you need to add your SPONSORDATE to this update.
The update exists twice.
<

RArch,

Thanks for the post. In my post, #2a, is in the update section. I see no other place where I would include it again. Anyone else????<

Here is a link to my pop_profile.asp file:

http://www.ridingarizona.com/new_forum/pop_profile.txt<

Try changing

strSql = strSql & ", M_SPONSORDATE = " & cDate("0" & Request.Form("SPONSORDATE"))

to

strSql = strSql & ", M_SPONSORDATE = " & cDate(Request.Form("SPONSORDATE"))

<

I tried it, same result. I can submit but nothing writes to the DB and no errors. Here are links to my inc_header.asp and inc_profile.asp files.
http://www.ridingarizona.com/new_forum/inc_profile.txt
http://www.ridingarizona.com/new_forum/inc_header.txt

Makes no sense to me.<

Shannon,

Have you added the field M_SPONSORDATE to the database?<

Try putting this line in pop_profile

Response.Write strSql

just before the my_Conn.Execute on line 1680 then post the result back here, it will make sure of two things:

1. When you save the member after modifying that your update code is in the right area. 2. It will give us the update script so that we can see if its correct.
Are you logged in as Admin and modifying another members record? <

Originally posted by MarcelG
Shannon,

Have you added the field M_SPONSORDATE to the database?

Marcel,

Yes, my #4 item was the addition of the M_SPONSORDATE field to the DB.<

Oops...sorry, missed that.<

OK. Here is what I see:
----------------------------
UPDATE FORUM_MEMBERS SET M_NAME = 'gmoney', M_NEWEMAIL = 'greg@cox.net', M_KEY = ' ', M_RECEIVE_EMAIL = 1 , M_TITLE = 'Vice President', M_POSTS = 5046 , M_COUNTRY = ' ', M_HOMEPAGE = 'http://www.ra.com', M_SIG = 'Greg', M_LEVEL = 3, M_SPONSORLEVEL = 1, M_SPONSORDATE = 1/1/2007, M_FIRSTNAME = 'Greg', M_LASTNAME = 'B', M_CITY = 'Chandler', M_STATE = 'AZ', M_PHOTO_URL = 'http://r.com/forum/gmoney_bio2.jpg', M_LINK1 = 'http://www.motorcyclebooks.com/info.html', M_LINK2 = 'http://www.trailrider.com/', M_MARSTATUS = '33.29703193438285,-111.87395095825195', M_BIO = ' ', M_AVATAR = 'http://ra.com/gmoney/gmoney_icon2.jpg', M_AVATAR_WIDTH = '64', M_AVATAR_HEIGHT = '62', M_AUHIDE = '1' WHERE MEMBER_ID = 2
----------------------------------
I see the M_SPONSORDATE in there with the new date I supplied but it does not get written to the DB.<

"M_SPONSORDATE = 1/1/2007,"

What datatype is M_SPONSORDATE in the database, is it a date or a string? <

Ahh I read back "4. I added a new field to my table called M_SPONSORDATE. It is of the DateTime type and is 16 characters long (per my table editor)"

Dates are a pain to work with.
To insert a datetime you need the time element too and surrounded in quotes ie. it needs to look like '1/1/2007 00:00:00' for it to work.
Try this:

strSql = strSql & '", M_SPONSORDATE = " & cDate(Request.Form("SPONSORDATE")) & " 00:00:00'"
<

I will try that now. Quick question....if my plan is to compare this date against the forum date to see which is larger, should I use a different data type or will the date type be the best fit?<

When I enter what you provided I get this error when I load the profile page:

Microsoft VBScript compilation error '800a03ea'

Syntax error

/new_forum/pop_profile.asp, line 1623

strSql = strSql & '", M_SPONSORDATE = " & cDate(Request.Form("SPONSORDATE")) & " 00:00:00'"


I thought one of the single quotes was out of order so I changed your idea with this:

strSql = strSql & "', M_SPONSORDATE = " & cDate(Request.Form("SPONSORDATE")) & " 00:00:00'"

This allowed me to load the profile page but upon submitting the page with a change to the SPONSORDATE I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 2: Incorrect syntax near ', M_SPONSORDATE = 1/1/2007 00:00:00'.

/new_forum/pop_profile.asp, line 1680
<

Sorry it should have read:

strSql = strSql & ", M_SPONSORDATE = '" & cDate(Request.Form("SPONSORDATE")) & " 00:00:00'"

If you need to compare dates then I would have used the same data type as snitz does, which is basically a string in the format of "20070101000000" then you will be able to use the forums built in functions to convert and compare with other dates. <

If you change the data type of M_SPONSORDATE to Text(14) then the following should work for you:

strSql = strSql & ", M_SPONSORDATE = " & DatetoStr(cDate(Request.Form("SPONSORDATE")))
<

RArch,

OK. That worked. I am now able to write to the DB using the text data type. I changed my variable to SPONSORENDS. I also changed:

strSql = strSql & ", M_SPONSORDATE = " & DatetoStr(cDate(Request.Form("SPONSORDATE")))

to this:

strSql = strSql & ", M_SPONSORDATE = " & DatetoStr(cStr(Request.Form("SPONSORDATE")))

If I am to now compare this new data field against the current time of the forum what is the correct forum variable/function to compare against. What I am trying to do is see of their expiration date is greater than the current date. If they have not expired I want to show them as a sponsor. If they have expired I want to just show them as a normal member.
Here is the code I have in inc_header.asp to set them as a current sponsor but the result still shows them all as a sponsors, even though most have nothing in the SPONSOREND field:

'## Forum_SQL
strSql = "SELECT " & strMemberTablePrefix & "MEMBERS.M_SPONSORLEVEL"
strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " WHERE " & strDBNTSQLName & " = '" & ChkString(strDBNTUserName, "SQLString") & "'"
strSql = strSql & " AND M_SPONSORENDS > '" & strForumTimeAdjust & "'"
set rsSponsor = my_Conn.Execute (strSql)
if rsSponsor.EOF or rsSponsor.BOF then
sLev = 0
else
sLev = rsSponsor("M_SPONSORLEVEL")
end if
rsSponsor.close
set rsSponsor = nothing

Should I move the check for the exipiration to this part of the above code. My thinking was if they were expired then set the sLev to 0. What forum variable should I use for the forum time:

if rsSponsor.EOF or rsSponsor.BOF then
sLev = 0


With all that said...THANK YOU GREATLY for your help. I do appreciate it.<

Its great this trial and error style of coding But we will get there in the end...
Again I would put a Response.Write strSql after your select statement and check that the output is what you expect.

Instead of using strDBNTSQLName in your where clause you could try MemberID so that you dont have to run the ChkString function.
<

OK. I'm getting really close now. Question for you...am I supposed to use strForumTimeAdjust as the variable that I compare my expiration field against? The reason I ask is that then I added the Response.Write strSql to my output it shows the date as 12/5/2006 not a string of 200612050000.<

I have added the following code to show the supporing member status under the member name in the left colum. Small issue though, it still shows every member that is marked as a Sponsor member, even if their expiration date has passed:

if Reply_MemberSponsorDate > strForumTimeAdjust AND Reply_MemberSponsorlevel = 1 then
Response.Write " <font color=""" & strForumFontColor & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><b><small><a href=""http://ridingarizona.com/store/scripts/prodview.asp?idProduct=36"", target=""_blank"", title=""This member is a RA site supporter. Click here for more information on becoming a site supporter.""><img border=""0"" src=""http://ridingarizona.com/forum/images/supporting_member.gif"" width=""64"" height=""64""></a></small></b></font><br />" & vbNewLine
end if

Any suggestions on how to get this to show ONLY those marked as supporting members AND whose expiration date has not passed?<

Try this:

strSql = strSql & " AND M_SPONSORENDS > '" & StrToDate(strForumTimeAdjust)& "'"
<

RArch,

Thanks for the help. I still cannot get it to compare the expiration date against the forum date and show a seal if the member is still an active supporting member. Here is a link to my topic.asp so you can see what I was attempting to do:

http://www.ridingarizona.com/new_forum/topic.txt

You can see around lines 790 (for replies) and 1018 (for topic starter) where I attempted to see if a member was a supporting member that had not expired and to show a graphic if they were. As it sits, it shows the seal for everyone who was has a supporting member status of 1 (supporting member for my site), regardless of it they are expired or not. What am I missing???<

We must not be comparing like values. Can you tell what values are set to the following:

Reply_MemberSponsorEnds
Member_SponsorEnds

You can display these by adding a Response.write variable name directly after your code within the file.
For example this is what I get:
Response.Write datetostr(strForumTimeAdjust) = 20061207235305 (YYYYMMDDHHMMSS)
Response.Write strtodate(strForumTimeAdjust) = 7/12/2006 23:53:5 (DD/MM/YYYY HH:MM:S)
Response.Write strForumTimeAdjust & vbNewLine = 07/12/2006 23:53:05 (DD/MM/YYYY HH:MM:SS)

Note the differences, when you compare your two variables they need to be in the same format. So use the datetostr or strtodate functions to make them match or drop the function as appropriate.
<

RArch,

Thank you SO much for your help. It took me a while but I figured it out. You were correct....dates are tough. The problem was my date formats were not matching. Once I figured out how to spit them out in the same format I was able to make it work. THANK YOU. I sincerely appreciate your help!!!!<

Glad to have helped and we learned a bit a long the way <

Shannon,

good to read you got it working!!! Planning on releasing it as an addition for the mod?<

Marcel,

Me? Release a mod/addition? Just the thought of that scares me to death. As you can see from this thread, I could not support it. However, I will write up what I have done and will gladly share it with anyone who wants to beat it up, test it and use it. Just shoot me a PM with your email address.<

Shannon,

I sent you an email as this forum doesn't support PMs. Maybe you should just post what you have here....

Rob<

Originally posted by stwilson
Marcel,

Me? Release a mod/addition? Just the thought of that scares me to death. As you can see from this thread, I could not support it. However, I will write up what I have done and will gladly share it with anyone who wants to beat it up, test it and use it. Just shoot me a PM with your email address.

Shannon, if you just write down the steps you took, I'm very happy to whip up a documented mod of it! You can find me at marcel at oxle.com.<

Can't wait to be able to add this to my forum, thought of doing it myself but haven't had the time. Thanks alot guys! =)
<

OK. I will gladly work with Marcel and get this documented as an add-on to his MOD. It will take me another day or two because the wife (I am Mr. Shannon) wants the Christmas cards sent out BEFORE I take any more time on website stuff. So I have to do a Word mail-merge for her 120 labels before I can play.
I did manage to get this implemented on my site and it works great. The worst part of it was figuring out all the existing supporting member's expiration dates. That took about five hours of research/updating.<

Hehe, I don't have to do that check since I'm starting on a fresh version of the site and we haven't gotten any payments from any members for over a year =)
<