Invalid Cast Error when inserting data into Database

I am using a TableAdapter to insert data into an MS Access Database and I am getting an Invalid Cast Error when I try and insert a record that has a DBNull value for a date field.  The Insert is being populated by the records of another table.  I am using the ISDBNull function to check for DBNull and if true, replace the DBNull with 01/01/1900 as the date.  So I have two questions:

1.  What is wrong with this statement that it doesn't work: (as part of my TableAdapter.Insert call)
IIf(IsDBNull(drRowEmployees("CheckWritten")), "#01/01/1900#", drRowEmployees("CheckWritten")),
 
      IsDBNull(drRowEmployees("CheckWritten")) = true  (verified with MsgBox)

      accepts "#01/01/1900#" if I hard-code it

       Database field is defined as DateTime and allows Nulls

2.  What I really want is if the date field is Null to keep it Null but I can't seem to insert a Null value into the table even though the field is setup to allow null values.  Is there a way to do this?

Any help would be greatly appreciated!
dyaroshAsked:
Who is Participating?
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Not sure how table adapters work but seems like the problem is in the format of the default date although that is the correct structure when working in Access.

Can you store the default date to a Date variable and use that in the IsDBNull conditional test or store it in a field like CheckWritten???

dteDefaultDate = 01/01/1900

Then ...

IIf(IsDBNull(drRowEmployees("CheckWritten")), dteDefaultDate, drRowEmployees("CheckWritten"))

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
<<<<< accepts "#01/01/1900#" if I hard-code it>>>>>

Where are you hard coding it in at???


ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Try this ...

IIf(IsDBNull(drRowEmployees("CheckWritten")), NULL, drRowEmployees("CheckWritten"))

ET
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Eric ShermanAccountant/DeveloperCommented:
IIf(IsDBNull(drRowEmployees("CheckWritten")), "#01/01/1900#", drRowEmployees("CheckWritten")) <--------------

Since you have the date already hard coded in, could it be that the False parameter is what's throwing the error???


Id so, you will need to enclose the CheckWritten date with ##'s.  How do you concatenate with your table adapter???  In Visual Basic you would use the & sign as shown below.

IIf(IsDBNull(drRowEmployees("CheckWritten")), "#01/01/1900#", "#" & drRowEmployees("CheckWritten") & "#")


ET
0
 
dyaroshAuthor Commented:
Here is my full Insert.  There are other date fields that are not null and they work.  I only have the problem when the date field has a null in it:


                Me.NewEmployeeEmployerInfoTableAdapter.Insert(EEID,
                                                             drRowEmployees("EmployeeID"),
                                                             drRowEmployees("EmployerID"),
                                                             IIf(IsDBNull(drRowEmployees("DepartmentNumber")), "", drRowEmployees("DepartmentNumber")),
                                                             drRowEmployees("EmployeeType"),
                                                             drRowEmployees("EmployeeStatus"),
                                                             IIf(IsDBNull(drRowEmployees("HireDate")), "#01/01/1900#", drRowEmployees("HireDate")),
                                                             "#01/01/1900#",
                                                             IIf(IsDBNull(drRowEmployees("EffectiveDateforLifeInsurance")), "#01/01/1900#", drRowEmployees("EffectiveDateforLifeInsurance")),
                                                             drRowEmployees("LifeInsuranceFaceAmount"),
                                                             IIf(IsDBNull(drRowEmployees("ActiveDateInPlan")), "#01/01/1900#", drRowEmployees("ActiveDateInPlan")),
                                                             drRowEmployees("PlanStatus"),
                                                             IIf(IsDBNull(drRowEmployees("PS58")), 0.0, CDec(drRowEmployees("PS58"))),
                                                             "#01/01/1900#",
                                                             "#01/01/1900#",
                                                             IIf(IsDBNull(drRowEmployees("CheckWritten")), "Null", drRowEmployees("CheckWritten")),
                                                             drRowEmployees("CheckNo"),
                                                             0)
0
 
dyaroshAuthor Commented:
The places where I have #01/01/1900#" are places where the record has a date field that is Null.  In trying to figure out where it was failing I had hard coded all of the fields and replaced them one by one.  THe CheckWritten field contains a null value.  As you can see, I've tried various approaches to get this to work and have not been successful.
0
 
Eric ShermanAccountant/DeveloperCommented:
Did this work

IIf(IsDBNull(drRowEmployees("CheckWritten")), NULL, drRowEmployees("CheckWritten"))

????


ET
0
 
dyaroshAuthor Commented:
Don't know why I have to do it this way but it worked!  Thank you.
0
 
Eric ShermanAccountant/DeveloperCommented:
<<<<<Don't know why I have to do it this way but it worked!  Thank you.>>>>>

It's probably based on how dates are interpreted or translated in the table adapter going into Access.  Storing it to the date variable seems to work.

Just curious ... does this syntax work ... without the ##'s  ?

IIf(IsDBNull(drRowEmployees("CheckWritten")), "01/01/1900",  drRowEmployees("CheckWritten"))


Thanks for the points.

ET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.