sending null date values to an ms access db from asp page

peadar86
peadar86 used Ask the Experts™
on
I have a web form that is populated using asp, by entering the ID of a record in the access database. The users can then edit the fields (inc 20 date fields).

The problem arises when a date field is left blank (many of the dates will only be known later). I get a "type mismatch" or "field cannot be a zero length string" error when I try to update.

Access doesn't have an option to allow zero length string for date/time fields and I can't convert the date data type to text with an input mask as there are calculated fields in a related query.

Any help would be appreciated.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
When you use the asp page to insert data into the table, use a "null" value instead of passing a blank from the form.  For exameple, use SQL statement like this:

If varDate=Request.Form("txtDate")="" Then
      varDate=Null
Else varDate = Request.Form("txtDate")
End If

Insert Into myTable
Values (myid, varDate,varValue1,...)
Sorry, typo.  Line:

If varDate=Request.Form("txtDate")="" Then

Should be:

Dim varDate
If Request.Form("txtDate")="" Then

Commented:
Keystrokes is right.

A date-field in a database is never a string, not even een zero-length string. But it can be null (contain nothing), if you don't set the field to be required.

You could also use the IsDate() function to check if the string can be converted to a valid date.

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
Thanks keystrokes
 
I create a recordset object (via a SQL select that returns the record in question for updating) and then use the syntax RS("FieldName") = SomeValue.

My asp update page is below.
Anything with ..due , ..rec, or ..issued is a date field.

How would I insert your code?

<%

ID=request.form("ID")
Surname=request.form("Surname")
Forename=request.form("Forename")
DOB=request.form("DOB")
SA1rec=request.form("SA1rec")
Ref=request.form("Ref")
RefNote=request.form("RefNote")
SA2=request.form("SA2")
SA2del=request.form("SA2del")
SA4due=request.form("SA4due")
SA4issued=request.form("SA4issued")
AdvDue=request.form("AdvDue")
wk5=request.form("wk5")
wk8=request.form("wk8")
LastAdvRec=request.form("LastAdvRec")
Admin=request.form("Admin")
PS=request.form("PS")
PSdel=request.form("PSdel")
SA7due=request.form("SA7due")
SA7follow=request.form("SA7follow")
SA7rec=request.form("SA7rec")
ReqSch=request.form("ReqSch")
RespDue=request.form("RespDue")
RespRec=request.form("RespRec")
SchFollow=request.form("SchFollow")
Delay=request.form("Delay")
FSdue=request.form("FSdue")
FSissued=request.form("FSissued")
Lieu=request.form("Lieu")

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "special"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = conn

rs.CursorType=3  'Static cursor
rs.LockType=3    ' Optimistic lock
 
rs.open "Select * from data where [Pupil ID] = "& ID &"   ", conn


%>



<html>

<head>
<title>Pupil</title>

<style type="text/css">

table {color:navy;font-family:verdana;font-size:14;font-weight:bold}


</style>

</head>

<body background="images/cnvbkgnd.jpg">

<div align="center">
  <center>
 
  <%
 
rs("Pupil ID")=ID
rs("Surname")=Surname
rs("Forename")=Forename
rs("Date of Birth")=DOB
rs("SA1 Rec")=SA1rec
rs("Ref By")=Ref
rs("Notes")=RefNote
rs("SA2 to EWO")=SA2
rs("SA2 Del")=SA2del
rs("SA4 Due")=SA4due
rs("SA4 Issued")=SA4issued
rs("Advice Due")=AdvDue
rs("Five Week Reminder")=wk5
rs("Eight Week Reminder")=wk8
rs("Last Advice Received")=LastAdvRec
rs("Return to Admin Office")=Admin
rs("PS to EWO")=PS
rs("PS Delivered")=PSdel
rs("SA7 Due")=SA7due
'rs("SA7 Follow up")=SA7follow
rs("SA7 Received")=SA7rec
rs("Request to School")=ReqSch
rs("Response from School Due")=RespDue
rs("Response Rec")=RespRec
rs("Request to School follow-up")=SchFollow
rs("Reason for Delay")=Delay
rs("FS Due")=FSdue
rs("FS Issued")=FSissued
rs("Note in Lieu Issued")=Lieu
 
rs.update

rs.close
conn.close

%>


Commented:
<%

ID=request.form("ID")
Surname=request.form("Surname")
Forename=request.form("Forename")
DOB=request.form("DOB")
SA1rec=request.form("SA1rec")
if SA1rec = "" then
SA1rec = null
end if
Ref=request.form("Ref")
RefNote=request.form("RefNote")
SA2=request.form("SA2")
SA2del=request.form("SA2del")
SA4due=request.form("SA4due")
if SA14due = "" then
SA4due = null
end if
SA4issued=request.form("SA4issued")
if SA14issued = "" then
SA14issued = null
end if
AdvDue=request.form("AdvDue")
if AdvDue = "" then
AdvDue = null
end if
wk5=request.form("wk5")
wk8=request.form("wk8")
LastAdvRec=request.form("LastAdvRec")
Admin=request.form("Admin")
PS=request.form("PS")
PSdel=request.form("PSdel")
SA7due=request.form("SA7due")
SA7follow=request.form("SA7follow")
SA7rec=request.form("SA7rec")
ReqSch=request.form("ReqSch")
RespDue=request.form("RespDue")
RespRec=request.form("RespRec")
SchFollow=request.form("SchFollow")
Delay=request.form("Delay")
FSdue=request.form("FSdue")
FSissued=request.form("FSissued")
Lieu=request.form("Lieu")



Commented:
hmm, for long forms like this, it pays out to give the formfields the same name as the fields in the table, and then do something like:

<%
For i = 0 To rs.fields.count


Next

%>

Commented:
hmm, for long forms like this, it pays out to give the formfields the same name as the fields in the table, and then do something like:

<%
For i = 0 To rs.fields.count
    sFieldName = rs.fields(i).Name
    If Request.Form(sFieldName).Count > 0 Then
        Select Case fields(i).Type
            CASE 2,3,17: rs(i) = Clng(Request.Form(sFieldName))
            CASE 4,5,6,131: rs(i) = Request.Form(sFieldName)
            CASE 11: rs(i) = CheckBoolean(Request.Form(sFieldName))
            CASE 7,135: rs(i) = CheckDate(Request.Form(sFieldName))

        End Select
    End If

Next

Function CheckBoolean(sIn)
    CheckBoolean = False
    Select Case VarType(var)
        Case 11,17: If var Then CheckBoolean = True
        Case 2,3: If var = 1 OR var = -1 Then CheckBoolean = True
        Case 8: If UCase(var) = "ON" Or UCase(var) = "YES" Or UCase(var) = "TRUE" OR TRIM(var) = "1" Then CheckBoolean = True
    End Select
End Function

Function CheckDate(var)
    CheckDate = null
    If IsDate(var) Then CheckDate = CDate(var)
End Function

%>

Author

Commented:
Thanks for all the help,

I eventually changed all the dates to text, and within Access used the iif(),IsNull() and Cdate() functions when creating the calculated fields.

eg Advice Due: IIf(IsNull([data]![SA4 Issued]),"",CDate([data]![SA4 Issued])+42.

My one reamining problem is that the calculated field dates are displyed on the results page in mm/dd/yyyy format. I need dd/mm/yyyy.

I had this problem earlier with the other fields and solved it by adding ..Session.LCID=2057 to the global.asa file.
For dates and times, i put together a replacement for the VB Format() function.  You can use it like ome of the following...

sDate = DTFormat([data]![SA4], "d2/M2/y4") ' - OR -
sDate = DTFormat([data]![SA4], "d2-M3-y4") ' - OR -
sDate = DTFormat([data]![SA4], "d2-M4-y4") ' - OR -
sDate = DTFormat([data]![SA4], "M4, d1, y4") ' - OR -
sDate = DTFormat([data]![SA4], "d2-M3-y4 h1:m2:s2 t2") ' - OR -
sDate = DTFormat([data]![SA4], "#d2-M3-y4 H2:m2:s2#") 'for DB literals
and so on!

formats are...
d1 Numeric representation of the day of the month, without leading zeros (d)
d2 Numeric representation of the day of the month, with leading zeros (dd)
M1 Numeric representation of the month, without leading zeros (M)
M2 Numeric representation of the month, with leading zeros (MM)
M3 Three-letter abbreviation for the name of the month (MMM)
M4 Full name of the month (MMMM)
y1 Year represented by only the last two digits, without leading zeros if less than 10 (y)
y2 Year represented by only the last two digits (yy)
y4 Year represented by all four digits (y4)
h1 Hours without leading zeros (12-hour clock) (h)
h2 Hours with leading zeros (12-hour clock) (h2)
H1 Hours without leading zeros (24-hour clock) (H)
H2 Hours with leading zeros (24-hour clock) (HH)
m1 Minutes without leading zeros (m)
m2 Minutes with leading zeros (mm)
s1 Seconds without leading zeros (s)
s2 Seconds with leading zeros (ss)
t1 One-character time-marker string (for example, "a" and "p") (t)
t2 Multicharacter time-marker string (for example, "AM" and "PM") (tt)


'Heres the function
Public Function DTFormat(ByVal sDate, ByVal sFormat)
Dim HourAdjust, t, tt
If IsDate(sDate) = False Then
    'a non valid date has been sent!
    'Do what you need to here - this example returns date Now()
    sDate = Now()
End If
If DatePart("h", sDate) > 12 Then
    HourAdjust = 12
    t = "p"
    tt = "PM"
Else
    t = "a"
    tt = "AM"
End If

'To Do...
'    "d3" (Abv Day Name)
'    "d4" (Full Day Name)

sFormat = Replace(sFormat, "d2", Right("0" & DatePart("d", sDate), 2)) 'Day with leading "0"
sFormat = Replace(sFormat, "d1", DatePart("d", sDate)) 'Day of month
sFormat = Replace(sFormat, "M4", MonthName(DatePart("m", sDate), False)) ''Full month name
sFormat = Replace(sFormat, "M3", MonthName(DatePart("m", sDate), True)) 'ABV Month name
sFormat = Replace(sFormat, "M2", Right("0" & DatePart("m", sDate), 2)) 'Month with leading "0"
sFormat = Replace(sFormat, "M1", DatePart("m", sDate)) 'Month as a number
sFormat = Replace(sFormat, "y4", DatePart("yyyy", sDate)) 'Full Year
sFormat = Replace(sFormat, "y2", Right(DatePart("yyyy", sDate), 2)) '2 digit year
sFormat = Replace(sFormat, "y1", CInt(Right(DatePart("yyyy", sDate), 2))) 'last 2 digits Year without leading "0"
sFormat = Replace(sFormat, "H2", Right("0" & (DatePart("h", sDate)), 2)) '24h Hour with leading "0"
sFormat = Replace(sFormat, "H1", DatePart("h", sDate)) '24h Hour
sFormat = Replace(sFormat, "h2", Right("0" & DatePart("h", sDate) - HourAdjust, 2)) '12h Hour with leading "0"
sFormat = Replace(sFormat, "h1", DatePart("h", sDate) - HourAdjust) '12h Hour
sFormat = Replace(sFormat, "m2", Right("0" & DatePart("n", sDate), 2)) 'Min with leading "0"
sFormat = Replace(sFormat, "m1", DatePart("n", sDate)) 'Min
sFormat = Replace(sFormat, "s2", Right("0" & DatePart("s", sDate), 2)) 'Seconds with leading "0"
sFormat = Replace(sFormat, "s1", DatePart("s", sDate)) 'Seconds
sFormat = Replace(sFormat, "t2", tt) 'AM/PM
sFormat = Replace(sFormat, "t1", t) 'a/p
DTFormat = sFormat

End Function


History...
I initially used the STD dd-MMM-yyyy hh:mm:ss tt formatting characters but i had more code to prevent this sort of problem...
sDate = DTFormat(Now(),"The time now is 'hh:mm:ss tt'")
sDate would contain "T9e ai27e now i15 '09:27:15'"
because h=9  t=a  m=27 s=15

anyway, no great hardship the formats are essentially the same MMMM = M4, yyyy = y4, t = t1, mm = m2  and so on!

Use it if you like it, loose it if you don't :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial