VB 2010 Retrieve Null Date into Masked Text Box 2

[Christineeve]

Dear Experts:

I've looked high and low all over the web. I'm stuck with handling this null date being retrieved with DAL from my MS Access Database. I have researched this for a long time. Specific help would mean a lot to me.

Dim cmdRevEmp As New OleDbCommand
Dim daRevEmp As New OleDbDataAdapter
Dim dsRevEmp As New DataSet
Dim dtRevEmp As New DataTable
Dim DataSource As String = gMyDataSource

Dim m_cnADONetConnection As New OleDb.OleDbConnection
            m_cnADONetConnection.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataSource & ";User ID=Admin;Password=;")

m_cnADONetConnection.Open()

cmdRevEmp = m_cnADONetConnection.CreateCommand
cmdRevEmp.CommandText = "SELECT * FROM tblEmpInfo Where pk_EmpInfoID=" & empID

'Create the command object and the dataset to hold all the employee data from tblEmpInfo
daRevEmp.SelectCommand = cmdRevEmp
daRevEmp.Fill(dsRevEmp, "RevEmps")
dtRevEmp = dsRevEmp.Tables("RevEmps")
dsRevEmp = New DataSet
daRevEmp.Fill(dsRevEmp, "RevEmps")

If (dtRevEmp.Rows.Count > 0) Then

'Retrieve the data from the Access database and populate some textboxes, comboboxes, and masked text boxes.

txtPrNationlty.Text = dtRevEmp.Rows(m_rowPosition)("empCountry").ToString()

Here the database date field is empty. The code crashes. I'd like to handle the error and pass in a default date of
#1/1/1985#

mtxtPrBirth.Text = dtRevEmp.Rows(m_rowPosition)("empbirthDate")

I don't know how to handle the error or what way is best for handling the retrieval of this error.

Thank you so much!
Christine

 

[jebenson]

Try this:

Dim DefaultDate As Date = #1/1/1985#
mtxtPrBirth.Text = IIf(IsDBNull(dtRevEmp.Rows(m_rowPosition)("empbirthDate")), DefaultDate, dtRevEmp.Rows(m_rowPosition)("empbirthDate"))

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Christineeve]

You rock. Despite your signature, I'm certain that you most certainly rock.

I was using dbnull all wrong. I could not resolve it w/o an error. Your example worked perfectly and better yet, you've taught me something I struggled with for months.

 

[ousoonerjoe]

jebenson has the right idea. Look for the NULL and replace it with something else.

Here is a function I wrote many, many moons ago to deal with exactly that problem:

	Public Function NotNull(ByVal dbArg As Object, Optional ByVal DefaultVal As String = "") As String
		NotNull = ""
		If dbArg Is Nothing Then
			NotNull = DefaultVal
		Else
			If String.IsNullOrEmpty(dbArg.ToString) = True Then
				NotNull = DefaultVal
			Else
				NotNull = dbArg.ToString
			End If
		End If
	End Function

Usage (sample is a copy and paste of code from an active form):

txtRecvDate.Text = NotNull(InqDt.Rows(0).Item("RecvDate"))
[COLOR=green][i]'Note, that the default value when not specified equals "".[/i][/color]

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[Christineeve]

Ousoonerjoe,

Thank you for the "not null" code.

I will work with your code until I have "learned it," and apply it to my projects.

Thank you so much for taking time to share your knowledge with me.

 

[Christineeve]

All of a sudden, this doesn't work anymore. It highlights the default date and says, "Date cannot be converted to Boolean."

I don't know what I changed to break this code. It's broken throughout my project now.

Any ideas?

 

[ousoonerjoe]

What values are you passing into it? Can you post the calling line as well as the function(if you made any changes to it)?

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[Christineeve]

Hi,
I found the error. It was something simple. Some code that I had tested earlier had become uncommented and that caused the error.

The code is working as recommended. My apologies.