Why does MysqlCommand.ExecuteScalar return System.NullReferenceException

Question

I am using MySQL in a VB 2013 project. I am trying to get the number of records in a table running

Dim SQLstr As String = "SELECT COUNT(*) FROM lieferantenartikel WHERE LiefID=1 AND LfAIDLief=1"
Dim CheckExist As New MySqlCommand(SQLstr, New MySqlConnection(strConn))
Try
    Dim recEx As Integer = CheckExist.ExecuteScalar()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

By definition COUNT(*) is supposed to return a scalar number, i.e. 0 if no records exist or otherwise any other positive number.

But I am getting a System.NullReferenceException. What am I doing wrong?

The error is likely a result of one of the connection or command objects being `Nothing`. Store the connection as an object variable so you can check them all with the debugger. Also, creating a commection does not open it. — Ňɏssa Pøngjǣrdenlarp, Jun 17 '15 at 15:43
Plutonix is right. If this doesn't help try to give us the stacktrace of ex.toString — etalon11, Jun 17 '15 at 16:17
possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Ňɏssa Pøngjǣrdenlarp, Jun 17 '15 at 16:36

score 0 · Answer 1 · answered Jun 17 '15 at 16:05

0

I had similar issues. This happened when a table had no entries. You can Do a workaround with "ISNULL". But maybe there is a better way.

answered Jun 17 '15 at 16:05

etalon11

771
1
9
32

`DBNull` is not the same thing as `Nothing`. The OP is getting a NRE which means an object being used is invalid, `DBNull` means the data is missing. – Ňɏssa Pøngjǣrdenlarp Jun 17 '15 at 16:14
Sorry etalon: when a table has no entries then the count is 0 which is different from DBNull, That's why using count is so sexy, because you get exact the answer you are asking, i.e. how many rows are there. It will never return DBNull. – pb_SKAT Jun 18 '15 at 07:39

score 0 · Answer 2 · answered Jun 18 '15 at 07:08

In fact the problem has gon when I used an open connection instead of the connection object ! Thank you, plutonix. I have obviously overseen in the documentation that the connection has to be open.

So the corrent code is now:

Dim SQLstr As String = "SELECT COUNT(*) FROM lieferantenartikel WHERE LiefID=1 AND LfAIDLief=1"
Dim cConn as New MySqlConnection(strConn)
cConn.Open()
Dim CheckExist As New MySqlCommand(SQLstr, cConn)
Try
    Dim recEx As Integer = CheckExist.ExecuteScalar()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Thank you very much for your quick help!e

Why does MysqlCommand.ExecuteScalar return System.NullReferenceException

2 Answers2