[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Invalid Cast Error when inserting data into Database

Posted on 2012-09-18
9
Medium Priority
?
453 Views
Last Modified: 2012-09-18
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!
0
Comment
Question by:dyarosh
  • 6
  • 3
9 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38412136
<<<<< accepts "#01/01/1900#" if I hard-code it>>>>>

Where are you hard coding it in at???


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38412141
Try this ...

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

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38412154
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:dyarosh
ID: 38412170
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
 

Author Comment

by:dyarosh
ID: 38412175
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
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 2000 total points
ID: 38412185
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38412196
Did this work

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

????


ET
0
 

Author Closing Comment

by:dyarosh
ID: 38412230
Don't know why I have to do it this way but it worked!  Thank you.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38412290
<<<<<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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question