Trap dbnull in OledbCommand ExecuteScalar

Hi,
i am trying to connect to a table in access combining a few fields including a currency field, seems to be when asking for the currency field but having no vaklue it returns a DBnull Exception, so my question is how i can parse the field before sending it and howi can get over it,

thanks in advance,
LVL 1
Joseph KrauszCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Check for DBNull before you try and assign your data.

e.g.

OleDbCommand cmd = // initialized
decimal currencyValue;

object result = cmd.ExecuteScalar();

if (result != DBNull.Value)
{
    currencyValue = (decimal)result;
}

Open in new window

0
Joseph KrauszCEOAuthor Commented:
this will not work as the sql command includes a few fields including non decimal field so my question is if its possible to parse or trap before executing the sql like nz(amount,0) in access, or it does not have any functions to trap
0
käµfm³d 👽Commented:
Can you show your query?
0
Joseph KrauszCEOAuthor Commented:
I oriented  myself that using parameters in the SQL string instead of just field names will help me but still didn't tested,
As I'm not so familiar with it please help me out
SQL= "select firstname+' ' +lastname+' '+amount from members";
I assume to use the oledbparameters class to assign the field name after testing the value
So should be like select@firstname...
But I can still use some help for completing it
Thanks.
0
käµfm³d 👽Commented:
With OleDb you use question marks ( ? ) as placeholders for where in the query your parameters should go. You must add your parameters to the command object in the order they appear within the query. For example:

OleDbCommand cmd = // initialize command;

cmd.CommandText = "select firstname + ' ' + lastname + ' ' + amount from members where userId = ? AND dateEntered < ?";

cmd.Parameters.AddWithValue("@userId", 1);  // This comes first since it is the first parameter in the query (reading left-to-right)
cmd.Parameters.AddWithValue("@date", DateTime.Now);  // This comes second because it occurs after the userId parameter in the query

Open in new window


For your original question, I suspect that you are asking about the firstname, lastname, and amount being null. For those, and as you mentioned, you can use the Nz function to ensure that you don't get a null in the result.

e.g.

cmd.CommandText = "select Nz(firstname, '') + ' ' + Nz(lastname, '') + ' ' + Nz(amount, 0) from members where userId = ? AND dateEntered < ?";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.