[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problem inserting into Access Database using VB.NET 4.0

Posted on 2012-09-20
Medium Priority
Last Modified: 2012-09-21
I have been working on this for days trying to figure out why I am getting this error and can't find it.  I am trying to do an insert into my Access DB using a TableAdapter as shown below:
                                                            IIf(IsDBNull(drRowEmployees("DepartmentNumber")), "", drRowEmployees("DepartmentNumber")),
                                                            IIf(IsDBNull(drRowEmployees("HireDate")), dtDefault, drRowEmployees("HireDate")),
                                                            IIf(IsDBNull(drRowEmployees("TerminationDate")), dtDefault, drRowEmployees("TerminationDate")),
                                                            IIf(IsDBNull(drRowEmployees("EffectiveDateforLifeInsurance")), dtDefault, drRowEmployees("EffectiveDateforLifeInsurance")),
                                                            IIf(IsDBNull(drRowEmployees("LifeInsuranceFaceAmount")), decZero, CDec(drRowEmployees("LifeInsuranceFaceAmount"))),
                                                            IIf(IsDBNull(drRowEmployees("ActiveDateInPlan")), dtDefault, drRowEmployees("ActiveDateInPlan")),
                                                            IIf(IsDBNull(drRowEmployees("BenefitFormsRecevied")), dtDefault, drRowEmployees("BenefitFormsRecevied")),
                                                            IIf(IsDBNull(drRowEmployees("AccountValuation")), dtDefault, drRowEmployees("AccountValuation")),
                                                            IIf(IsDBNull(drRowEmployees("CheckWritten")), dtDefault, drRowEmployees("CheckWritten")),
                                                            IIf(IsDBNull(drRowEmployees("CheckNo")), "", drRowEmployees("CheckNo")),

I am getting the following error and I can't figure out where the error is coming from:
Conversion from type 'DBNull' to type 'Decimal' is not valid.

The only variables that are defined as Decimal are being set to 0.0 using a variable defined as follows:  Dim decZero As Decimal = 0.0

Can someone tell me how to find out which field this is failing on?
Question by:dyarosh
LVL 35

Expert Comment

ID: 38420540
Have you tried replacing some of the expressions with hard-coded values?

Obviously that won't solve the problem, but might help find which field the problem is with.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38421009
You might try String.IsNullOrEmpty instead:

IIf(String.IsNullOrEmpty(drRowEmployees("DepartmentNumber")), "", drRowEmployees("DepartmentNumber")),

Or if drRowEmployees is a DataRow, you can try the IsNull method:

IIF(drRowEmployees.IsNull("DepartmentNumber"), "TrueValue", "FalseValue")

Author Closing Comment

ID: 38424039
I ended up using the String.IsNullOrEmpty and that fixed my problem.  Thanks.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

834 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