I have a problem saveing data into a VFP database when there are date fields and the data being saved is blank. My code is as follows:
<code>
<%
function formatdate2(lddate)
if isdate(lddate) then
formatdate2 = lddate
else
set oRS = oConnection.Execute("select {} as blankdate from users")
session("blankdate") = oRS.Fields("blankdate").Value
formatdate2 = session("blankdate")
oRS.close
set oRS = nothing
end if
end function
sql = "select tip_id,st_name, "
sql = sql + "ss_scoping, s_scoping, time_scoping, as_scoping, a_scoping, "
sql = sql + "ss_planfile, s_planfile,time_stage2, as_planfile, a_planfile, "
sql = sql + "ss_envclr, s_envclr, time_env, as_envclr, a_envclr, "
sql = sql + "ss_rwclr,s_rwclr,time_rwclr,as_rwclr,a_rwclr, "
sql = sql + "ss_const,s_const,time_const,as_const,a_const "
sql = sql + "from tip where tip_id = " & lntip_id
oPage.Open sql, oConnection, adOpenKeyset, adLockBatchOptimistic, adCmdText
with oPage
.fields("ss_scoping").value = formatdate2(request.form("ss_scoping"))
.fields("s_scoping").value = formatdate2(request.form("s_scoping"))
.fields("time_scoping").value = nz(request.form("time_scoping"))
.fields("as_scoping").value = formatdate2(request.form("as_scoping"))
.fields("a_scoping").value = formatdate2(request.form("a_scoping"))
.updatebatch
.fields("ss_envclr").value = formatdate2(request.form("ss_envclr"))
.fields("s_envclr").value = formatdate2(request.form("s_envclr"))
.fields("time_env").value = nz(request.form("time_env"))
.fields("as_envclr").value = formatdate2(request.form("as_envclr"))
.fields("a_envclr").value = formatdate2(request.form("a_envclr"))
.updatebatch
.fields("ss_rwclr").value = formatdate2(request.form("ss_rwclr"))
.fields("s_rwclr").value = formatdate2(request.form("s_rwclr"))
.fields("time_rwclr").value = nz(request.form("time_rwclr"))
.fields("as_rwclr").value = formatdate2(request.form("as_rwclr"))
.fields("a_rwclr").value = formatdate2(request.form("a_rwclr"))
.updatebatch
%>
The update fails on the second update. Notice that the second group of fields is the same as the first. This was for testing only. It puzzles me why the update would work the first time through and then fail on the exact same code for the second. It seems that regardless of the fields involved, it will fail after the first update. Also if I drop the multiple updates, it will fail even on the single update at the end. I'm pretty sure this has something to do with the blank values in the date fields but have been unable to zero in on it. Any help will be appreciated.
<code>
<%
function formatdate2(lddate)
if isdate(lddate) then
formatdate2 = lddate
else
set oRS = oConnection.Execute("select {} as blankdate from users")
session("blankdate") = oRS.Fields("blankdate").Value
formatdate2 = session("blankdate")
oRS.close
set oRS = nothing
end if
end function
sql = "select tip_id,st_name, "
sql = sql + "ss_scoping, s_scoping, time_scoping, as_scoping, a_scoping, "
sql = sql + "ss_planfile, s_planfile,time_stage2, as_planfile, a_planfile, "
sql = sql + "ss_envclr, s_envclr, time_env, as_envclr, a_envclr, "
sql = sql + "ss_rwclr,s_rwclr,time_rwclr,as_rwclr,a_rwclr, "
sql = sql + "ss_const,s_const,time_const,as_const,a_const "
sql = sql + "from tip where tip_id = " & lntip_id
oPage.Open sql, oConnection, adOpenKeyset, adLockBatchOptimistic, adCmdText
with oPage
.fields("ss_scoping").value = formatdate2(request.form("ss_scoping"))
.fields("s_scoping").value = formatdate2(request.form("s_scoping"))
.fields("time_scoping").value = nz(request.form("time_scoping"))
.fields("as_scoping").value = formatdate2(request.form("as_scoping"))
.fields("a_scoping").value = formatdate2(request.form("a_scoping"))
.updatebatch
.fields("ss_envclr").value = formatdate2(request.form("ss_envclr"))
.fields("s_envclr").value = formatdate2(request.form("s_envclr"))
.fields("time_env").value = nz(request.form("time_env"))
.fields("as_envclr").value = formatdate2(request.form("as_envclr"))
.fields("a_envclr").value = formatdate2(request.form("a_envclr"))
.updatebatch
.fields("ss_rwclr").value = formatdate2(request.form("ss_rwclr"))
.fields("s_rwclr").value = formatdate2(request.form("s_rwclr"))
.fields("time_rwclr").value = nz(request.form("time_rwclr"))
.fields("as_rwclr").value = formatdate2(request.form("as_rwclr"))
.fields("a_rwclr").value = formatdate2(request.form("a_rwclr"))
.updatebatch
%>
The update fails on the second update. Notice that the second group of fields is the same as the first. This was for testing only. It puzzles me why the update would work the first time through and then fail on the exact same code for the second. It seems that regardless of the fields involved, it will fail after the first update. Also if I drop the multiple updates, it will fail even on the single update at the end. I'm pretty sure this has something to do with the blank values in the date fields but have been unable to zero in on it. Any help will be appreciated.
