peadar86
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.
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.
Sorry, typo. Line:
If varDate=Request.Form("txtD ate")="" Then
Should be:
Dim varDate
If Request.Form("txtDate")="" Then
If varDate=Request.Form("txtD
Should be:
Dim varDate
If Request.Form("txtDate")=""
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.
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.
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("Surn ame")
Forename=request.form("For ename")
DOB=request.form("DOB")
SA1rec=request.form("SA1re c")
Ref=request.form("Ref")
RefNote=request.form("RefN ote")
SA2=request.form("SA2")
SA2del=request.form("SA2de l")
SA4due=request.form("SA4du e")
SA4issued=request.form("SA 4issued")
AdvDue=request.form("AdvDu e")
wk5=request.form("wk5")
wk8=request.form("wk8")
LastAdvRec=request.form("L astAdvRec" )
Admin=request.form("Admin" )
PS=request.form("PS")
PSdel=request.form("PSdel" )
SA7due=request.form("SA7du e")
SA7follow=request.form("SA 7follow")
SA7rec=request.form("SA7re c")
ReqSch=request.form("ReqSc h")
RespDue=request.form("Resp Due")
RespRec=request.form("Resp Rec")
SchFollow=request.form("Sc hFollow")
Delay=request.form("Delay" )
FSdue=request.form("FSdue" )
FSissued=request.form("FSi ssued")
Lieu=request.form("Lieu")
Set conn = Server.CreateObject("ADODB .Connectio n")
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:ve rdana;font -size:14;f ont-weight :bold}
</style>
</head>
<body background="images/cnvbkgn d.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
%>
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("Surn
Forename=request.form("For
DOB=request.form("DOB")
SA1rec=request.form("SA1re
Ref=request.form("Ref")
RefNote=request.form("RefN
SA2=request.form("SA2")
SA2del=request.form("SA2de
SA4due=request.form("SA4du
SA4issued=request.form("SA
AdvDue=request.form("AdvDu
wk5=request.form("wk5")
wk8=request.form("wk8")
LastAdvRec=request.form("L
Admin=request.form("Admin"
PS=request.form("PS")
PSdel=request.form("PSdel"
SA7due=request.form("SA7du
SA7follow=request.form("SA
SA7rec=request.form("SA7re
ReqSch=request.form("ReqSc
RespDue=request.form("Resp
RespRec=request.form("Resp
SchFollow=request.form("Sc
Delay=request.form("Delay"
FSdue=request.form("FSdue"
FSissued=request.form("FSi
Lieu=request.form("Lieu")
Set conn = Server.CreateObject("ADODB
conn.Open "special"
Set rs = Server.CreateObject("ADODB
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:ve
</style>
</head>
<body background="images/cnvbkgn
<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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
%>
<%
For i = 0 To rs.fields.count
Next
%>
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).C ount > 0 Then
Select Case fields(i).Type
CASE 2,3,17: rs(i) = Clng(Request.Form(sFieldNa me))
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(sFi eldName))
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
%>
<%
For i = 0 To rs.fields.count
sFieldName = rs.fields(i).Name
If Request.Form(sFieldName).C
Select Case fields(i).Type
CASE 2,3,17: rs(i) = Clng(Request.Form(sFieldNa
CASE 4,5,6,131: rs(i) = Request.Form(sFieldName)
CASE 11: rs(i) = CheckBoolean(Request.Form(
CASE 7,135: rs(i) = CheckDate(Request.Form(sFi
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
%>
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.
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]![
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 :)
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"
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 :)
If varDate=Request.Form("txtD
varDate=Null
Else varDate = Request.Form("txtDate")
End If
Insert Into myTable
Values (myid, varDate,varValue1,...)