asked on

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

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.

keystrokes

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,...)

keystrokes

Sorry, typo. Line:

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

Should be:

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

sybe

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.

peadar86

ASKER

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

%>

ASKER CERTIFIED SOLUTION

jekl2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sybe

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

%>

sybe

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

%>

peadar86

ASKER

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.

stevie_mac

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 :)