Avatar of baxtalo
baxtalo
 asked on

JavaScript to insert Nulls in datetime field

The JavaScript below builds a weekly schedule submitting times to the following database colums: SunIn, SunOut, MonIn, MonOut, TueIn, TueOut, WedIn, WedOut, ThuIn, ThuOut, FriIn, FriOut, SatIn, SatOut.
For days off I’d like to be able to submit NULL values, instead of making all fields required.
How can this script be modified to let users submit Nulls instead of getting the alert "All fields must have values entered."
Thank you for your help.
<html>
<head>
<title>Submit Time</title>
<script type="text/JavaScript">

var days = "Sun,Mon,Tue,Wed,Thu,Fri,Sat".split(",");
var idx = {sun:0,mon:1,tue:2,wed:3,thu:4,fri:5,sat:6}

function tabField(elmnt){
  var next=document.getElementById("fld"+elmnt.tabIndex);
  if (next){
    next.focus();
  }
}
function pad() {
   var val = this.value;
   if (val.length==1) this.value="0"+val;
}
function isValid(txtBox) {
  var isHour = txtBox.name.indexOf("Hour") !=-1;
  var span = document.getElementById('invalid'+txtBox.name);
  span.style.display = 'none';
  if (isHour) {
    if (parseInt(txtBox.value,10) > 23) {
      txtBox.value = '';
      span.style.display = 'block';
      return false
    }
  }
  else {
    if (parseInt(txtBox.value) > 59) {
      txtBox.value = '';
      span.style.display = 'block';
      return false
    }
  }
  return true;
}

window.onload=function() {
  document.getElementById("form1").onsubmit=function() {
    for (var i=0;i<days.length;i++) {
      var startH = this["startHour_"+days[i]].value;
      var startM = this["startMinute_"+days[i]].value;
      var endH = this["endHour_"+days[i]].value;
      var endM = this["endMinute_"+days[i]].value;
      if (startH == '' || startM == '' || endH == '' || endM == '') {
        alert("All fields must have values entered.");
        return false;
      }
      else {
        if (startH.length == 1) {
          startH = '0' + startH;
        }
        if (startM.length == 1) {
          startM = '0' + startM;
        }
        if (endH.length == 1) {
          endH = '0' + endH;
        }
        if (endM.length == 1) {
          endM = '0' + endM;
        }
        this[days[i]+"In"].value = startH + ':' + startM;
        this[days[i]+"Out"].value = endH + ':' + endM;
      }
    }
    return true;
  }


  var formContent = document.getElementById("formContent");
  var strInnerHTMLData= '';
  var tabIndex = 1;
  strInnerHTMLData +="<TABLE border=0 style='border-collapse:collapse'>"
  strInnerHTMLData += "<TR>";
  strInnerHTMLData += "<TH>Day</th>";
  strInnerHTMLData += "<TH>In</th>";
  strInnerHTMLData += "<TH>Out</th><th>&nbsp;</th>";
  strInnerHTMLData += "</TR>";
  for (var i=0;i<days.length;i++) {

  	strInnerHTMLData += '<TR>';
    strInnerHTMLData += '<TD>'+days[i]+'</TD>'+
'<TD><input type="textbox" tabindex="'+(tabIndex++)+'" id="fld'+(tabIndex-2)+'" name="startHour_'+days[i]+'"  maxlength="2" style="width:18px" />'+
':<input type="textbox" tabindex="'+(tabIndex++)+'" id="fld'+(tabIndex-2)+'" name="startMinute_'+days[i]+'"  maxlength="2" style="width:18px" />'+

'</TD><TD>'+
'<input type="textbox" tabindex="'+(tabIndex++)+'" id="fld'+(tabIndex-2)+'" name="endHour_'+days[i]+'" maxlength="2" style="width:18px" />'+
':<input type="textbox" tabindex="'+(tabIndex++)+'" id="fld'+(tabIndex-2)+'" name="endMinute_'+days[i]+'" maxlength="2" style="width:18px" />'+

'<input type="hidden" tabindex="-1" name="'+days[i]+'In" id="startTime_'+days[i]+'" value="" />'+
'<input type="hidden" tabindex="-1" name="'+days[i]+'Out" id="endTime_'+days[i]+'" value="" /></td><td>'+


'<span id="invalidstartHour_'+days[i]+'" style="color:red;display:none">* Hour must be between 0 and 23</span>'+
'<span id="invalidstartMinute_'+days[i]+'" style="color:red;display:none">* Minute must be between 0 and 59</span>'+
'<span id="invalidendHour_'+days[i]+'" style="color:red;display:none">* Hour must be between 0 and 23</span>'+
'<span id="invalidendMinute_'+days[i]+'" style="color:red;display:none">* Minute must be between 0 and 59</span>'+


'</td>';
strInnerHTMLData += '</TR>';
  }
  strInnerHTMLData += '</table>';

  formContent.innerHTML = strInnerHTMLData;

  for (var i=0;i<days.length;i++) {
    var sth  = document.getElementsByName("startHour_"+days[i])[0],
        endh = document.getElementsByName("endHour_"+days[i])[0],
        stm  = document.getElementsByName("startMinute_"+days[i])[0],
        endm = document.getElementsByName("endMinute_"+days[i])[0];

    sth.onblur=endh.onblur=stm.onblur=endm.onblur=pad;
    sth.onkeyup=endh.onkeyup=stm.onkeyup=endm.onkeyup=function(evt) {
        if (isValid(this) && this.value.length==this.maxLength) tabField(this);
    }
    sth.onkeypress=endh.onkeypress=stm.onkeypress=endm.onkeypress=function(evt) {
      evt = evt || window.event;
      var charCode = evt.which || evt.keyCode;
      var charStr = String.fromCharCode(charCode);
      return /\d/.test(charStr);
    };

  }
}


</script>
</head>
<body>

<form action="SubmitTime_Send.asp" id="form1" method="POST">
<div id="formContent">

</div>
<input type="submit" value="Submit" />
</form>

</body>
</html>

Open in new window

JavaScript

Avatar of undefined
Last Comment
baxtalo

8/22/2022 - Mon
Kim Walker

Add a double forward slash "//" to the beginning of lines 47-50 above. If this gives you the results you're looking for, you can delete those lines permanently.
baxtalo

ASKER
If I add // to the beginning of lines 47-50 only the submit button will be visible on the page as an 'else' follows. Removing the if statement won't insert NULL in the database, but it will insert 00:00 instead. I wonder if inserting NULL is possible.
Kim Walker

Sorry didn't see that else on the next line. For me, a right curly brace on a line by itself is the end. If I have an else, I put it on the same line. Anyway, you could just comment out lines 48 and 49 with the double forward slashes.

Inserting a NULL instead of 00:00 is a function of the database. Is that field configured to allow NULLs? Probably not if it inserted 00:00. For MySQL, NULL is the implied default value when NULLs are allowed.
Your help has saved me hundreds of hours of internet surfing.
fblack61
baxtalo

ASKER
I'm sending the data to SQL Server database. I removed the IF statement so that I can submit the form without having to enter values in all fields. The fields that were left empty send the following to the database:
1900-01-01 00:00:00.000
My table is set to allow NULL values, that's why I would like to know if these NULL values can be sent to the database, instead of sending the value of 1900-01-01 00:00:00.000
In my SubmitTime_Send.asp I did the following, but I don't think this is the right way to change the 1900-01-01 00:00:00.000 values to NULL
 I wonder if this can be done with the help of the JavaScript, buy sending directly NULL to the database instead of updating after submission.
<!-- #include virtual="SQLConn.asp" -->
<%

'declare variables
Dim conn, strSQL, strStartTime, strEndTime

'Set values into variables
strStartTime = request.Form("StartTime")
strEndTime = request.Form("EndTime")
strSunIn = request.Form("SunIn")
strSunOut = request.Form("SunOut")
strMonIn = request.Form("MonIn")
strMonOut = request.Form("MonOut")
strTueIn = request.Form("TueIn")
strTueOut = request.Form("TueOut")
strWedIn = request.Form("WedIn")
strWedOut = request.Form("WedOut")
strThuIn = request.Form("ThuIn")
strThuOut = request.Form("ThuOut")
strFriIn = request.Form("FriIn")
strFriOut = request.Form("FriOut")
strSatIn = request.Form("SatIn")
strSatOut = request.Form("SatOut")


'create SQL string for inserting data into database
strSQL = "INSERT INTO Time_Table (StartTime, EndTime, SunIn, SunOut, MonIn, MonOut, TueIn, TueOut, WedIn, WedOut, ThuIn, ThuOut, FriIn, FriOut, SatIn, SatOut) VALUES ('"& strStartTime &"', '"& strEndTime &"', '"& strSunIn &"', '"& strSunOut &"', '"& strMonIn &"', '"& strMonOut &"', '"& strTueIn &"', '"& strTueOut &"', '"& strWedIn &"', '"& strWedOut &"', '"& strThuIn &"', '"& strThuOut &"', '"& strFriIn &"', '"& strFriOut &"', '"& strSatIn &"', '"& strSatOut &"')"

'now add data with Execute() method
conn.Execute(strSql)


StrSQL = "UPDATE Time_Table SET SunIn = NULL, SunOut = NULL WHERE SunIn = '1900-01-01 00:00:00.000' and SunOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET MonIn = NULL, MonOut = NULL WHERE MonIn = '1900-01-01 00:00:00.000' and MonOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET TueIn = NULL, TueOut = NULL WHERE TueIn = '1900-01-01 00:00:00.000' and TueOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET WedIn = NULL, WedOut = NULL WHERE WedIn = '1900-01-01 00:00:00.000' and WedOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET ThuIn = NULL, ThuOut = NULL WHERE ThuIn = '1900-01-01 00:00:00.000' and ThuOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET FriIn = NULL, FriOut = NULL WHERE FriIn = '1900-01-01 00:00:00.000' and FriOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL

StrSQL = "UPDATE Time_Table SET SatIn = NULL, SatOut = NULL WHERE SatIn = '1900-01-01 00:00:00.000' and SatOut = '1900-01-01 00:00:00.000'"
conn.Execute StrSQL


'close connection object
conn.Close
Set conn = Nothing


'Redirect to the completed.asp page
Response.Redirect "SubmitTimeConfirmation.asp"

%>

Open in new window

Kim Walker

The form is sending an empty string. ASP must be converting the empty string to a date.
ASKER CERTIFIED SOLUTION
Kim Walker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
baxtalo

ASKER
Thank you very much, it worked!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.