DateTime Editor does not update table on save of DBNull

[JimF (Jim Fairchild)]

I have been tasked with trapping and displaying dates when CheckOffs are made in DMR Processing. I created a DateTimeEditor for each CheckOff on the Header/Detail tab and modified the GroupBox as shown below, with the code used below that. When I check/uncheck the Checkoff box, the DateTimeEditor reacts as expected. But, when trying to save the data after a checkbox has been unchecked, the DBNull date does not save to the table, and the DateTimeEditor reverts to the previously saved date. Can anyone see what I am doing wrong, or have a better idea how to accomplish the task?

	private void DMRHead_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
	{
		EpiCheckBox cb1;
		EpiCheckBox cb2;
		EpiCheckBox cb3;
		EpiCheckBox cb4;
		EpiCheckBox cb5;

		// ** Argument Properties and Uses **
		// args.Row["FieldName"]
		// args.Column, args.ProposedValue, args.Row
		// Add Event Handler Code

		switch (args.Column.ColumnName)
		{
			case "CheckOff1":  // QA
				cb1 = (EpiCheckBox)csm.GetNativeControlReference("ea8efcbd-5436-42be-948a-4dd483688a74");
				if (cb1.Checked)
					edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff2":  // Engineering
				cb2 = (EpiCheckBox)csm.GetNativeControlReference("2a1cb4ee-9467-4f01-a0fd-89953b9e9eeb");
				if (cb2.Checked)
					edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff3":  // Production
				cb3 = (EpiCheckBox)csm.GetNativeControlReference("abfe4275-fc90-4d61-9b41-596c95ed494e");
				if (cb3.Checked)
					edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DateTime.Now;
				else
				{
					dte3.Text = string.Empty;
					edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DBNull.Value;
				}
				break;
			case "CheckOff4": // Purchasing
				cb4 = (EpiCheckBox)csm.GetNativeControlReference("76010db2-fcef-4c16-8700-304f4f074893");
				if (cb4.Checked)
					edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff5":  // QA Final
				cb5 = (EpiCheckBox)csm.GetNativeControlReference("de908c59-0a45-4a7f-a679-fd656e7bc756");
				if (cb5.Checked)
					edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DBNull.Value;
				break;
		}
	}

[jtownsend (John Townsend)]

I’ve got a fair number of these, but they’re all implemented as BPM’s, not as form customizations. I strongly suggest going that route instead.

[JimF (Jim Fairchild)]

Thanks John. Not sure why we should add a customization for the controls and not be able to complete the task in the same customization, but it looks like that will be the path I have to take. I appreciate you sharing your experience and saving me some headache investigating.

[jtownsend (John Townsend)]

It’s for separating out client-side UI behavior from server-side business logic. Yeah, you can blur that line by putting server calls in the client. That keeps everything in one place, but it’s not considered best practice. Also, in my experience, BPM customizations are simpler and break less often when upgrading.

[TomAlexander]

@JimF , I looked at a code example I had where I used DBNull.Value in a customization. The only real difference between my code and yours was that I used the BeginEdit() and EndEdit() methods on the EpiDataView data row before setting the UD field.

I’m not sure why that would make a difference for this issue, but could be worth a try. It’s also possible my code has the same issue but no one ever noticed it…

[JimF (Jim Fairchild)]

Thanks Tom. I actually found some other example code that used BeginEdit() and EndEdit() and tried using them but got the same results. That fact that it works for you though means that there is probably something else you are doing that I am not. I know I have done this with other controls without issue, I am pretty sure it has something to do with the EpiDateTimeEditor and setting it from a value to a DBNull. You could be right if no one ever tried to blank/reset the control from code or externally from the control itself, it would very well have gone unnoticed. I haven’t gotten to creating the BPM yet. If you think of anything else I might be missing, let me know. Thanks!

[hkeric.wci (Haso Keric)]

You code should work fine.

I would not read cb.Checked but instead args.ProposedValue and then set the value on args.Row

case "CheckBox10": // isPrinted Override

	if (Convert.ToBoolean(args.ProposedValue) == true)
	{
		args.Row["Date10"] = DateTime.Now;
	}
	else {
		args.Row["Date10"] = DBNull.Value;
	}

break;

[JimF (Jim Fairchild)]

Thanks @hkeric.wci . For whatever reason that rendered the same results. I’m sure I am either doing something wrong or have things setup incorrectly, but it is easy enough to create a BPM to handle it. That is the road I’ll take. Thanks again for taking the time guys.

[JimF (Jim Fairchild)]

@hkeric.wci I was called into another project before completing this one, but I am now back on it. Do you have any customizations that use this logic? I am having a devil of a time getting the DateTimeEdtor/bound UD field to act as expected. If I use the customization code to set the values, when the checkbox is checked the DateTimeEditor reacts as expected (Sets to today’s date) both immediately and on Save, when the checkbox is unchecked, the DateTimeEditor value blanks as expected, but when the record is saved, the DTE reverts to the previously saved vale and on examining the DB, the UD field was never updated. When I use a directive, the DB UD Field is updated, but the DTE on the UI does not update and I have to hit Refresh to see the proper value. It seems to me to be an issue with the DateTimeEditor. Any help or experience with this would be greatly appreciated. Thanks.

Current Code:

	private void DMRHead_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
	{
		// ** Argument Properties and Uses **
		// args.Row["FieldName"]
		// args.Column, args.ProposedValue, args.Row
		// Add Event Handler Code

		switch (args.Column.ColumnName)
		{
			case "CheckOff1":  // QA
				if (Convert.ToBoolean(args.ProposedValue) == true)
					edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff2":  // Engineering
				if (Convert.ToBoolean(args.ProposedValue) == true)
					edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff3":  // Production
				if (Convert.ToBoolean(args.ProposedValue) == true)
					edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff4": // Purchasing
				if (Convert.ToBoolean(args.ProposedValue) == true)
					edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DBNull.Value;
				break;
			case "CheckOff5":  // QA Final
				if (Convert.ToBoolean(args.ProposedValue) == true)
					edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DateTime.Now;
				else
					edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DBNull.Value;
				break;
		}
	}

CheckBox Checked and Date correct:

After CheckBox is unchecked:

After Save is pressed:

[aarong (Aaron Gulley)]

I have had a similar problem. I had to move it to a BPM to resolve the issue.

If check if the flag has changed and update the UDField with the data you need.

Ice.Lib.CompanyTime.Today()

We went a little extra and added a person next to it.

Db.UserFile.Where(u => u.DcdUserID == Session.UserID).Select(x => x.Name).First()

Selects their name from UserFile.

If the check box changed to false from true… I would just reverse above and clear everything. I did mine from an action menu

[JimF (Jim Fairchild)]

Thanks Aaron. I have a directive that works, but the issue with it is that when the field is updated, the DTE doesn’t reflect the change. I have to hit refresh to see the value. It’s like the control is not properly bound to the UD Field.

With the BPM:
After the CheckBox is unchecked:

After save is pressed:

I check the UD Field:

Then hit refresh:

[aarong (Aaron Gulley)]

Try using at the end of each switch.

oTrans.Update();

It seems like the screen is reflecting the change but db isnt.

edit: This may be a bad idea because if a user changes something on the screen. The date is always going to reflective today.

Another method is to utilise a dummy boolean to tell the BPM something changed then you can fire your bpm within the code or a row rule or something…

Obviously not a solid solution but its tests you could try.

[JimF (Jim Fairchild)]

Since (I believe) the oTrans.Upate() is essentially the same as pressing save, adding it just causes the DTE to display the date that happened after pressing Save.

After much testing, it seems that if you bind a DB DateTime field to a EpiDateTimeEditor and set the value of the bound field to null, it will not update the DB DateTime field. I don’t think the EpiDateTimeEditor is the issue as far as the update is concerned as I am just attempting to set the row/column value to DBNull.Value.

You can update from a BPM, but then have to refresh the UI to reflect the change which as a side note, seems to be a known issue:

UD Date / DateTime fields cannot be nulled from a BPM

Just ran across this bug which seems to be around for a while (It is present in 10.2.200 and 10.2.300 at least) If you have a UD Field of data type (DateTime) or (Date) it appears that you cannot NULL out that date from a BPM and have it show refreshed on the screen without refreshing the screen. Steps to Replicate Create a Date or Date Time Field (UD) Set the field on the UI of a form Populate the field with a date and hit save Write a BPM that sets that date to null Go back to the screen a…

I am still working on this. If I find a reasonable solution I will post here.

[JimF (Jim Fairchild)]

Looks like I had to revert to the ol’ hidden control trick. Place a control (in this case a read-only blank textbox) behind the control you want to ‘blank’ and use BringToFront() and SendToBack(). A lot of nonsense to fix what probably should work natively, but in conjunction with a directive to update the DB, works in a pinch, no refresh needed.

CheckOff1 (QA) Checked:

CheckOff1 (QA) Unchecked:

CheckOff1 (QA) Saved (after unchecked):

Code:

	private void DMRHead_AfterFieldChange(object sender, DataColumnChangeEventArgs args)
	{
		// ** Argument Properties and Uses **
		// args.Row["FieldName"]
		// args.Column, args.ProposedValue, args.Row
		// Add Event Handler Code

		switch (args.Column.ColumnName)
		{
			case "CheckOff1":  // QA
				if (Convert.ToBoolean(args.ProposedValue) == true)
				{
					dteQALocal.BringToFront();
					txtQANullLocal.SendToBack();
					edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DateTime.Now;
				}
				else
				{
					dteQALocal.SendToBack();
					txtQANullLocal.BringToFront();
					//edvDMRHead.dataView[edvDMRHead.Row]["QACheckOffDate_c"] = DBNull.Value;
				}
				break;
			case "CheckOff2":  // Engineering
				if (Convert.ToBoolean(args.ProposedValue) == true)
				{
					dteEngLocal.BringToFront();
					txtEngNullLocal.SendToBack();
					edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DateTime.Now;
				}
				else
				{
					dteEngLocal.SendToBack();
					txtEngNullLocal.BringToFront();
					//edvDMRHead.dataView[edvDMRHead.Row]["EngCheckOffDate_c"] = DBNull.Value;
				}
				break;
			case "CheckOff3":  // Production
				if (Convert.ToBoolean(args.ProposedValue) == true)
				{
					dteProdLocal.BringToFront();
					txtProdNullLocal.SendToBack();
					edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DateTime.Now;
				}
				else
				{
					dteProdLocal.SendToBack();
					txtProdNullLocal.BringToFront();
					//edvDMRHead.dataView[edvDMRHead.Row]["ProdCheckOffDate_c"] = DBNull.Value;
				}
				break;
			case "CheckOff4": // Purchasing
				if (Convert.ToBoolean(args.ProposedValue) == true)
				{
					dtePurchLocal.BringToFront();
					txtPurchNullLocal.SendToBack();
					edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DateTime.Now;
				}
				else
				{
					dtePurchLocal.SendToBack();
					txtPurchNullLocal.BringToFront();
					//edvDMRHead.dataView[edvDMRHead.Row]["PurchCheckOffDate_c"] = DBNull.Value;
				}
				break;
			case "CheckOff5":  // QA Final
				if (Convert.ToBoolean(args.ProposedValue) == true)
				{
					dteQAFinalLocal.BringToFront();
					txtQAFinalNullLocal.SendToBack();
					edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DateTime.Now;
				}
				else
				{
					dteQAFinalLocal.SendToBack();
					txtQAFinalNullLocal.BringToFront();
					//edvDMRHead.dataView[edvDMRHead.Row]["QAFinalCheckOffDate_c"] = DBNull.Value;
				}
				break;
		}
	}