Date Comparison

[jbricher]

Hi everyone,

I need to know a couple of things regarding dates. First of all, where do I get a date that is universal when someone enters an entry into my database that tells the date that they entered (time is not important). I don't think I can rely on the system date because this can be altered to suit the needs of the person submitting the entry, unless there is a way to use the date of the system the database is located at. Anyways, the next thing I need to know is how to compare the date that is entered with two other dates that are stored in a database (under the date/time format). The user will enter an entry and if the date of that entry is between the beginning date for submission and the end date of submission, then the entry will be permitted, otherwise it will be tossed out. I'm sorry for bugging everyone but I have no idea how to approach this problem and my web research hasn't yielded anything, so any help would be appreciated. Thanks in advance.

- Joe

[Phil Weber]

If you're using SQL Server, you can use the GetDate() function to obtain the date on the server. If you're using MS Access, use the Date() function. In either case, you should be able to use < and > to compare dates in SQL.

[jbricher]

Okay, I tried that and for some reason the dates are not being recognized. I think I may have coded the syntax incorrectly, or I am not properly calling the functions I have put in. If someone could please look over my code and tell me where I am going wrong I would appreciate it. Thanks in advance.

- Joe

Code:

Sub BtnSendClicked(Sender as Object, e as EventArgs)

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.MapPath("CPP_v6.mdb") & ";"
Dim MyConn as New OleDBConnection (strConn)
Dim MySQL As String
Dim NewSQL As String

Dim intCount as Integer
NewSQL = "SELECT COUNT(*) FROM FROM Dates WHERE Date() > Start.Date AND < End.Date"
MYSQL = "SELECT COUNT(*) FROM Eligible WHERE StudentID= '" & StudentID.Text & "'"
Dim cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")

If NOT intCount > 0 Then

Response.Redirect("ParkingLotteryFailurePage.html")

Else

MySQL = "INSERT INTO LotteryEntry (NetworkID, StudentID, FirstName, LastName, DayPhone, EveningPhone, CellPhone, Address1, Address2, AptNumber, City, State, Zip, Email, Handicap) VALUES ('" & NetworkID.Text & "','" & StudentID.Text & "','" & FirstName.Text & "','" & LastName.Text & "','" & DayPhone.Text & "','" & EveningPhone.Text & "','" & CellPhone.Text & "','" & Address1.Text & "','" & Address2.Text & "','" & AptNumber.Text & "','" & City.Text & "','" & State.selectedItem.value & "','" & Zip.text & "','" & Email.text & "','" & Handicap.selectedItem.value & "')"

cmd = New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
intCount = cmd.ExecuteScalar()
MyConn.Close ()
Response.Redirect("ParkingLotterySuccessPage.html")
End If

End Sub