Hi,

In one of my forms I am getting a value from a sql table by using a scalar query.
When the table is empty, the query returns a NULL

How can I test for this? I've tried:

if scalarquery is system.dbnull
if scalarquery.value is system.dbnull
if scalarquery is system.dbnull.value
if scalarquery.value is system.dbnull.value

And the same using = instead of IS

All of them give an error with intellisense

And my other suggestion of checking for Nothing did not work?

If scalarquery is nothing then ...

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Nope, that didn't work either.

I just found a different way round it, instead of the query, i'm filling the table adapter and doing a bindingsource.count.
If it's zero then the table's empty... and I use '1' as the ID, if it's not empty then I'm using the scalarquery to get the value & adding 1 to it

Not quite the way I wanted to do it, but it's working...

Thanks for the help though!

Try System.DbNull.Value.

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

When you say scalar query, do you mean you are calling ExecuteScalar?

This is from the help system:

"The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters."

The link is here:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

So this would imply that you need to check for Nothing and not a dbNull.

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Try System.DbNull.Value.

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Tried it, both:

= system.dbnull.value
and
IS system.dbnull.value

With combinations of scalarquery and scalarquery.value

What is scalarquery defined to be?

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

The snippet is inserting a row into a table.
One of the columns in the table (ID) is a primary key - so any row I add must have a unique number.

I have a query in the table adapter that does:

select max(ID) from [table]

Then in the insert line I'm inserting '{the value above} +1' into the new row.

It works fine unless the table is empty, in which case the query returns NULL and the insert fails, so I need to test the result of the query first.

And my other suggestion of checking for Nothing did not work?

If scalarquery is nothing then ...

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

And my other suggestion of checking for Nothing did not work?

If scalarquery is nothing then ...

---

www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Nope, that didn't work either.

I just found a different way round it, instead of the query, i'm filling the table adapter and doing a bindingsource.count.
If it's zero then the table's empty... and I use '1' as the ID, if it's not empty then I'm using the scalarquery to get the value & adding 1 to it

Not quite the way I wanted to do it, but it's working...

Thanks for the help though!