| Author |
 Topic  |
|
RArch
Junior Member
United Kingdom
103 Posts |
 Posted - 04 December 2006 : 07:48:53
|
Try changing
strSql = strSql & ", M_SPONSORDATE = " & cDate("0" & Request.Form("SPONSORDATE"))
to
strSql = strSql & ", M_SPONSORDATE = " & cDate(Request.Form("SPONSORDATE"))
< |
 |
|
|
stwilson
Junior Member
USA
385 Posts |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 04 December 2006 : 15:05:58
|
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?
< |
Edited by - RArch on 04 December 2006 15:08:24 |
|
|
|
stwilson
Junior Member
USA
385 Posts |
Posted - 04 December 2006 : 15:18:58
|
quote:
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.< |
Shannon
RidingArizona.com |
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
stwilson
Junior Member
USA
385 Posts |
Posted - 04 December 2006 : 15:34:00
|
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.< |
Shannon
RidingArizona.com |
|
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 04 December 2006 : 15:41:57
|
"M_SPONSORDATE = 1/1/2007,"
What datatype is M_SPONSORDATE in the database, is it a date or a string?
< |
|
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 04 December 2006 : 16:05:09
|
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'"
< |
|
|
|
stwilson
Junior Member
USA
385 Posts |
Posted - 04 December 2006 : 18:08:34
|
| 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?< |
Shannon
RidingArizona.com |
|
|
|
stwilson
Junior Member
USA
385 Posts |
Posted - 04 December 2006 : 18:17:37
|
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
< |
Shannon
RidingArizona.com |
Edited by - stwilson on 04 December 2006 18:18:24 |
|
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 05 December 2006 : 01:58:33
|
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.
< |
|
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 05 December 2006 : 02:14:57
|
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")))
< |
|
|
|
stwilson
Junior Member
USA
385 Posts |
Posted - 05 December 2006 : 13:19:11
|
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.< |
Shannon
RidingArizona.com |
|
|
|
RArch
Junior Member
United Kingdom
103 Posts |
Posted - 05 December 2006 : 14:58:44
|
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.
< |
|
|
|
Topic |
|
Oxle's Sponsor Mod - Add Expiration - Help Needed
