Insert date as 20th February 2007

[SteveHigh]

Hello

I have an MS Access database which includes two tables (one called 'users' and the other
sIPAddresses).

The 'users' table stores information which the user has input in an online form (name, email, etc),
while the sIPAddresses stores the users IP address.

If possible, I would like to introduce a 'date' field in each table without the user having to
input it anywhere.

In the 'users' table I have the following:

<%
' Declare variables

Dim fullname, email, business, country, message

'Open MS Access database, store form field values, and close

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\form.mdb;"
set rs = Server.CreateObject("ADODB.recordset")

SQL="INSERT INTO users (fullname, email, business, country, message) VALUES ('" & _
fullname & "', '" & email & "', '" & business & "', '" & country & "', '" & message & "')"

rs.Open SQL, conn

Set rs=Nothing

conn.Close
Set conn=Nothing
%>

and in the 'sIPAddresses' table I have:

<%
' Declare variables

Dim sIPAddress

sIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If sIPAddress="" Then sIPAddress = Request.ServerVariables("REMOTE_ADDR")


'Open MS Access database, store form field values

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\form.mdb;"
set rs = Server.CreateObject("ADODB.recordset")

SQL="INSERT INTO sIPAddresses (IPaddress) VALUES ('" & sIPaddress & "')"

rs.Open SQL, conn

Set rs=Nothing

' Close the connection

conn.Close
Set conn=Nothing
%>

Neither of these tables stores the date anywhere, so I am not able to recognise when a form
was submitted or the site accessed. Any advice, please?

Thanks

Steve

 

[Tarwn]

The easiest way might be to create a date column in both tables and give it a default value of Now(). This way when you insert a new record it will automatically fill the date column for that record with the current date, saving you the trouble of doing it from the ASP side.

When you get the date out of the table you will need to do a little extra work to get the format you want, since it isn't a standard format and VBScript is limited in how it can format date. Something like:

Function FormatMyDate(aDate)
   If Not IsDate(aDate) Then FormatMyDate = aDate

   If Day(aDate) >= 11 And Day(aDate) <= 13 Then
      FormatMyDate = Day(aDate) & "th "
   ElseIf Right(Day(aDate),1) = 1 Then
      FormatMyDate = Day(adate) & "st "
   ElseIf Right(Day(aDate),1) = 2 Then
      FormatMyDate = Day(adate) & "nd "
   ElseIf Right(Day(aDate),1) = 3 Then
      FormatMyDate = Day(adate) & "rd "
   Else
      FormatMyDate = Day(aDate) & "th "
   End If

   FormatMyDate = MonthName(Month(aDate)) & " " & Year(aDate)
End Function

Hope this helps,
-T

 

[SteveHigh]

Hello Tarwn

Thank you for your reply.

Yes, that was what I wanted: for the date to automatically go into the date fields in each table.

I imagine, by this, you mean I can avoid statements such as:

SQL="INSERT INTO sIPAddresses (Date) VALUES ('" & Date & "')" etc etc.

Very many thanks for your script.

I'll try it and get back to you.

Cheers

Steve