I am having some SQL problems with asp/mysql.  I am trying to write my script for mysql now.  Can you point me in the right direction for some examples on date formating with mysql/asp before I go and ask a question about my code.

thanks

--Todd

http://www.codetoad.com/asp/format_date_time.asp - http://www.codetoad.com/asp/format_date_time.asp

http://www.asp-help.com/articles/dates.asp - http://www.asp-help.com/articles/dates.asp

http://www.4guysfromrolla.com/webtech/011701-1.shtml - http://www.4guysfromrolla.com/webtech/011701-1.shtml

Edit: Forgot to add one.

Ok I looked at that and tried some things but I am still having some problems.  No value gets entered in the table.  All I see is 0000-00-00 00:00:00

If I response.write onlinedate before I format I see 1/13/2004 5:06:14 PM

If I response.write onlinedate after format I see 13-1-2004 17:6:14

I am using mysql and I am using the datetime type for the two columns Logon_time, Last_Seen

Here is my code

<!--#include file='dbconnection.inc'-->
<%

onlinedate = now()
OnlineUserIp = Request.ServerVariables("REMOTE_ADDR")
strUserID = Session("userName")

onlinedate = Day(onlinedate) & "-" & Month(onlinedate) & "-" & Year(onlinedate) & " " & Hour(onlinedate) & ":" & Minute(onlinedate) & ":" & Second(onlinedate)

lastseen = Day(onlinedate) & "-" & Month(onlinedate) & "-" & Year(onlinedate) & " " & Hour(onlinedate) & ":" & Minute(onlinedate) & ":" & Second(onlinedate)

'Set timeout in minutes
strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,onlinedate)

StrSql = "Select * From Active_Users Where User_ID='" & strUserID & "'"
Set rs1 = adoCon.Execute (StrSql)

'Check if users id exists in active users database
if rs1.eof then
'Add user to database because they dont exist
StrSql = "INSERT INTO active_Users (Logon_time,User_ip,Last_Seen,Last_Page,User_ID) " &_
"VALUES ('" & onlinedate & "','" & OnlineUserIp & "','" & lastseen & "','" &_
request.servervariables("path_info") & "','" & strUserID & "')"
adoCon.Execute (StrSql)
else
'There Still active so lets update their info
StrSql = "UPDATE active_users SET Last_Seen='" & lastseen & "', Last_Page='" &_
request.servervariables("path_info") & "' WHERE User_ID='" & strUserID & "'"
adoCon.execute (StrSql)
end if

'Remove Users Who Have Timed Out
StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
adoCon.execute (StrSql)

%>

OK I think I have it.  But how can I still do the compare on my delete line?

<!--#include file='dbconnection.inc'-->

<%
function mySqlDate(myDate)

    mySqlDate = Year(myDate) & "-" & Month(myDate) & "-" & Day(myDate) & " " & Hour(myDate) & ":" & Minute(myDate)  & ":" & Second(myDate)

end function
%>

<%

onlinedate = now()

OnlineUserIp = Request.ServerVariables("REMOTE_ADDR")
strUserID = Session("userName")

'Set timeout in minutes
strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,onlinedate)

StrSql = "Select * From Active_Users Where User_ID='" & strUserID & "'"
Set rs1 = adoCon.Execute (StrSql)

'Check if users id exists in active users database
if rs1.eof then
'Add user to database because they dont exist
StrSql = "INSERT INTO active_Users (Logon_time,User_ip,Last_Seen,Last_Page,User_ID) " &_
"VALUES ('" & onlinedate & "','" & OnlineUserIp & "','" & mySqlDate(now()) & "','" &_
request.servervariables("path_info") & "','" & strUserID & "')"
adoCon.Execute (StrSql)
else
'There Still active so lets update their info
StrSql = "UPDATE active_users SET Last_Seen='" & mySqlDate(now()) & "', Last_Page='" &_
request.servervariables("path_info") & "' WHERE User_ID='" & strUserID & "'"
adoCon.execute (StrSql)
end if

'Remove Users Who Have Timed Out
'StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
'adoCon.execute (StrSql)

%>

This is the part I am talking about with the compare and delete

strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,mySqlDate(onlinedate))

strOnlineTimedout = mySqlDate(StrOnlineTimedout) 

'StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
'adoCon.execute (StrSql)

I am real close just can't get it though.  When I response.write the value I see what I want.  I just get this error with the above code.

Syntax error

/login/mysql/activeusers.asp, line 42

StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout  &
----------------------------------------------------- -----------------------^

Got it

StrSql = "DELETE FROM active_users WHERE Last_Seen < '" & StrOnlineTimedout & "'"