i am building an auction site. i am stuck with the item expiry part.
can any one help.
its runing with access database, in the table i have auctionstart (as the auction starting date and time) and auctionend(as auction finished time) and auctionfinished(default as no)
what i want to do is when the actionend is = to the time it ends then i want asp to write into the auctionfinished filed in access.
can any one help on this one.
cheers

here's a simple way to do that:

i assume that you're using these following fieldnames & datatype

field name <==> datatype

AUCTION.MDB structure
===============
item - Text
dateStart - Date/Time
dateEnd - Date/Time
timeEnd - Date/Time
Status - Text

the codes:

AUCTION.ASP
============
<%

// Database Connection Procedures

Dim objConn
Dim strProvider

Sub OpenDb

strProvider = ""
strProvider = strProvider & "Provider=Microsoft.Jet.OLEDB.4.0;"
strProvider = strProvider & "Data Source=" & Server.MapPath("AUCTION.mdb" & ";"
strProvider = strProvider & "Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection"

objConn.Open strProvider

End Sub

Sub CloseDb
objConn.Close
Set objConn = Nothing
End Sub

%>


<%

// Procedure the calculates the expiration date of the auction

Sub expiredDate()
Dim strSQL,expMsg

expMsg = "Auction expired!"

If rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" = Time() OR rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" < Time() Then

// Update database

strSQL = "UPDATE auction SET status = '" & expMsg & "' WHERE id=" & itemId
Objconn.Execute(strSQL)

End If

response.write "Auction Status: "
response.write rsAuction("status" & "<br><br>"

End Sub

%>

<%

Dim rsAuction,itemId

// require db connection

CAll OpenDb()

Set rsAuction = ObjConn.Execute("SELECT * FROM auction ORDER BY ID"

response.write "Current Date & Time: " & Now() & "<br><br>"

While NOT rsAuction.EOF

itemId = rsAuction("id"

response.write "Item ID: "
response.write rsAuction("item" & "<br>"

response.write "Date Start: "
response.write rsAuction("dateStart" & "<br>"

response.write "Date End: "
response.write rsAuction("dateEnd" & "<br>"

response.write "Time End: "
response.write rsAuction("timeEnd" & "<br>"

Call expiredDate()


rsAuction.MoveNext
WEnd

rsAuction.Close
Set rsAuction = Nothing

// Close DB

CAll CloseDb()
%>

i've tested it, so basically, it's a running script.

if you got any questions or suggestions, please let me know.

Micah Santos

thanks for that

hi Micah

how would i get this script run automaticlly. would i include in default.asp?
or in another file.
thanks for your help once again

well, it depends on where you're gonna display all the records. here's how it works:

these parts should be on the top of your codes depending if you want to insert this in your default page or create another file.
<%

// Database Connection Procedures

Dim objConn
Dim strProvider

Sub OpenDb

strProvider = ""
strProvider = strProvider & "Provider=Microsoft.Jet.OLEDB.4.0;"
strProvider = strProvider & "Data Source=" & Server.MapPath("AUCTION.mdb" & ";"
strProvider = strProvider & "Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection"

objConn.Open strProvider

End Sub

Sub CloseDb
objConn.Close
Set objConn = Nothing
End Sub

%>


<%

// Procedure the calculates the expiration date of the auction

Sub expiredDate()
Dim strSQL,expMsg

expMsg = "Auction expired!"

If rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" = Time() OR rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" < Time() Then

// Update database

strSQL = "UPDATE auction SET status = '" & expMsg & "' WHERE id=" & itemId
Objconn.Execute(strSQL)

End If

response.write "Auction Status: "
response.write rsAuction("status" & "<br><br>"

End Sub

%>

and this last part is where you display the current auction availability. what's important is they should be in the same file together where it has to run.

<%

Dim rsAuction,itemId

// require db connection

CAll OpenDb()

Set rsAuction = ObjConn.Execute("SELECT * FROM auction ORDER BY ID"

response.write "Current Date & Time: " & Now() & "<br><br>"

While NOT rsAuction.EOF

itemId = rsAuction("id"

response.write "Item ID: "
response.write rsAuction("item" & "<br>"

response.write "Date Start: "
response.write rsAuction("dateStart" & "<br>"

response.write "Date End: "
response.write rsAuction("dateEnd" & "<br>"

response.write "Time End: "
response.write rsAuction("timeEnd" & "<br>"

Call expiredDate()


rsAuction.MoveNext
WEnd

rsAuction.Close
Set rsAuction = Nothing

// Close DB

CAll CloseDb()
%>

could you please update this procedure part:

instead of:
<%
If rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" = Time() OR rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" < Time() Then

// Update database

strSQL = "UPDATE auction SET status = '" & expMsg & "' WHERE id=" & itemId
Objconn.Execute(strSQL)

%>

==============================

change it to like this:

<%
If rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" = Time() OR rsAuction("dateEnd" = Date() AND rsAuction("timeEnd" < Time() OR rsAuction("dateEnd" < Date() Then

// Update database

If trim(lcase(rsAuction("status")) <> trim(lcase(expMsg)) Then

strSQL = "UPDATE auction SET status = '" & expMsg & "' WHERE id=" & itemId
Objconn.Execute(strSQL)

End If

%>

ok i will give it a try..
and thanks for your kind help

o yeah one more issue, my webserver is in us, and i am in new zealand, so there is a time difference, so the now() or date() would not work correctly. is there a way around that..
cheers...

well, that's a tough one... lol! let me give it a try okay.

well, i've been thinking about what you're asking me. i've been searching codes to get the time zones using ASP. fortunately, i have found one that would fit to your question. below is the link and i want u to check it out.

www.ourline.com/asp/findip.asp

however, when I ran the script using a local machine, it takes a while to locate for the IP address because the MDB filesize is almost 3.5 mb. this database file contains IP addresses that pertains to the country of an specified user, which is basically what you needed.

on the other hand, if you only consider this online auction within US and New Zealand territories, i have this alternative solution that doesn't require dbase access. this involves page redirection asking a user to select what country he/she is from using a dropdown list, then, deduct the time difference between your US web server and the timezone of the user.

let me know which one of these solutions you prefer and i'll work on the script, okay?

i just wonder if we add the <%=dateadd("h",+16,now()%> would work?

that's the second solution i was talking about. that's why i asked you if you're considering US and New Zealand countries as your basis to get the time difference.

however, you still need a string variable to identify a specific user's country in order for you to set the DateAdd function.

here's the pseudo code:

if current_user is equal to New Zealand then

timeZone is equal to dateadd("h",+16,now())

end if

now, instead of using Now() or Date(), use the timeZone public variable for your if and else...

so how would the script go if my target is only for new zealand would it be alot easyer??

it would be more a lot easier to use the second solution rather than the first one.

you can include countries as many as you want. but, this is how it works:

create a page redirection where a user can select what country he/she is from.
if it is more than 3 countries i suggest you use dropdown list, then, click GO to another page.

by doing that way, you can apply the DATEADD function corresponding to the specified country
selected by the user, right?

then, let's create a SELECT CASE function:

<%

usrCountry = Request.Form("glCountry"

Select CASE usrCountry

CASE "New Zealand"

timeZone = dateadd("h",+16,now())

CASE "US"

timeZone = Date()

End Select
%>

i'll work on this later, okay.

so far, i ended up doing this:

DEMO: mspinay.somee.com/freescripts/asp/auction/country.asp
ZIP File: micah.web1000.com/freescripts/auction.zip

here's the my final coding:

AUCTION.ASP
=================
<!--#include file="dbconn.asp"-->
<!--#include file="config.asp"-->
<%

If Request.Form("glTimeZone" = "" Then
response.redirect "country.asp"

Else

// Time Zone query string
Dim strDate,strTime,fTimeZone
Dim tmpDate,tmpTime

fTimeZone = Request.Form("glTimeZone"

// Add Time Zone

tmpDate = DateAdd("h",+16,Now())

// TIMEZONE CASE function

Select CASE fTimeZone

CASE "US"

strDate = Date()
strTime = Time()

CASE "NZ"

strDate = cStr(DatePart("m",tmpDate)) + "/" + cStr(DatePart("d",tmpDate)) + "/" + cStr(DatePart("yyyy",tmpDate))
strTime = DatePart("h",tmpDate)

// Military hours

Dim tmpMin,tmpSecs

If DatePart("n",tmpDate) <= 9 Then
tmpMin = "0" + cStr(DatePart("n",tmpDate))
Else
tmpMin = cStr(DatePart("n",tmpDate))
End If

If DatePart("s",tmpDate) <= 9 Then
tmpSecs = "0" + cStr(DatePart("s",tmpDate))
Else
tmpSecs = cStr(DatePart("s",tmpDate))
End If


If strTime >= 13 Then
tmpTime = strTime - 12

strTime = cStr(tmpTime) + ":" + tmpMin + ":" + tmpSecs + " PM"

Else
If strTime = 0 Then

strTime = "12" + ":" + tmpMin + ":" + tmpSecs + " AM"

Else
strTime = cStr(strTime) + ":" + tmpMin + ":" + tmpSecs + " AM"

End If
End If

End Select

%>

<%

// Procedure the calculates the expiration date of the auction

Sub expiredDate()
Dim rsStatus,rsCountry
Dim strSQL

Set rsStatus = ObjConn.Execute("SELECT * FROM expired WHERE auc_id=" & itemId & ";"

response.write "Auction Status: "

If rsAuction("dateEnd" = cDate(strDate) AND rsAuction("timeEnd" = strTime OR rsAuction("dateEnd" = cDate(strDate) AND rsAuction("timeEnd" < cDate(strTime) OR rsAuction("dateEnd" < cDate(strDate) Then

// Item is expired
// Before proceed to data insertion, check first if auc_id is already registered in EXPIRED table

rsStatus.Close
set rsStatus = Nothing

Set rsStatus = ObjConn.Execute("SELECT * FROM expired WHERE auc_id=" & itemId & " AND country='" & fTimeZone & "' ;"


If rsStatus.EOF Then

strSQL = "INSERT INTO EXPIRED (auc_id,item,status,country) values ('" & itemId & "', '" & rsAuction("item" & "', '" & localMsg & "', '" & fTimeZone & "')"
ObjConn.Execute(strSQL)

response.write localMsg & "<br><br>"

Else

response.write localMsg & "<br><br>"

End If

Else

If rsStatus.EOF Then
response.write auctionOnline & "<br><br>"
Else
If rsStatus("country" = fTimeZone Then
response.write rsStatus("status" & "<br><br>"
Else
response.write auctionOnline & "<br><br>"
End If
End If

End If

rsStatus.Close
Set rsStatus = Nothing

End Sub
%>

<%

Dim rsAuction,itemId

// require db connection

CAll OpenDb()

Set rsAuction = ObjConn.Execute("SELECT * FROM auction ORDER BY auc_ID"

response.write "Time Zone: " & fTimeZone & "<br>"
response.write "Current Date & Time: " & strDate & " " & strTime & "<br>"
response.write "<b>Note:</b> "
response.write "Expiration dates for online auctions will be based on your current Time Zone.<br><br>"

While NOT rsAuction.EOF

itemId = rsAuction("auc_id"

response.write "Item ID: "
response.write rsAuction("item" & "<br>"

response.write "Date Start: "
response.write rsAuction("dateStart" & "<br>"

response.write "Date End: "
response.write rsAuction("dateEnd" & "<br>"


response.write "Time End: "
response.write rsAuction("timeEnd" & "<br>"

CAll expiredDate()


rsAuction.MoveNext
WEnd

rsAuction.Close
Set rsAuction = Nothing

// Close DB

CAll CloseDb()

End If
%>

hi Micah

can you write one just for new zealand. and put it in a zip file like you did.
cheers.
thank you very much

sure thing! wait for my next update ok.

ZIP FILE: micah.web1000.com/freescripts/auction.zip

this time US Time Zone will be automatically convert to NZ time zone. there's no need anymore for redirection.

that's all! good luck!

thank you so much for all your help.
so do you think the best way to get this script run automaticlly is include it in a defualt.asp or is there is better way to do it.

it is better to include that file in your default.asp because it only shows the records and the one
that i sent you doesn't have a proper layout.

so, basically, all you need to do is to set a font style before that include file tag.

cheers! good to know my hand-coded scripts is working... lol(",)

good luck!

sure does..
thank you

Hi Micah

if i want to have a auto email notify would i do a redirect at the end of
// Procedure the calculates the expiration date of the auction
?

if you want to get notify if a certain item is already expired, yes, under this condition:

<%
If rsAuction("dateEnd" = cDate(strDate) AND rsAuction("timeEnd" = strTime OR rsAuction("dateEnd" = cDate(strDate) AND rsAuction("timeEnd" < cDate(strTime) OR rsAuction("dateEnd" < cDate(strDate) Then

// auto email notify
%>

will this go just under the // Procedure the calculates the expiration date of the auction
?

yes it does.

Try this from your database

cDate(CloseDateTime) > NOW()

That works really well for me.