asked on

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!

Eric Sherman

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

Where are you hard coding it in at???

ET

Eric Sherman

Try this ...

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

ET

Eric Sherman

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

dyarosh

ASKER

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)

dyarosh

ASKER

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.

ASKER CERTIFIED SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Sherman

Did this work

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

????

ET

dyarosh

ASKER

Don't know why I have to do it this way but it worked! Thank you.

Eric Sherman

<<<<<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