cmd.ExecuteScalar() Object reference not set to an instance of an object vb.net

Question

I have error "Object reference not set to an instance of an object" on this code. I've tried to run this code with SQL (PHP myadmin) and I can receive my data but this code doesn't not work.

For i = 0 To arr_pdetail.Length
    Dim cmdText = "Select price from tb_pdetail where category = @car"
    Using cmd = New MySqlCommand(cmdText, conn)
        cmd.Parameters.Add("@cat", MySqlDbType.VarChar).Value = arr_pdetail(i)
        Dim result = cmd.ExecuteScalar()
        If result Is Nothing Then
            MessageBox.Show("No category found")
        Else
            MessageBox.Show("Found category with price " & result.ToString())
        End If
    End Using
    'sum_cost = sum_cost + price
Next

Duplicate of [What does "Object reference not set to an instance of an object" mean?](http://stackoverflow.com/questions/779091/what-does-object-reference-not-set-to-an-instance-of-an-object-mean) — Bjørn-Roger Kringsjå, Jul 25 '15 at 13:53
`For i = 0 To arr_pdetail.Length` should be `For i = 0 To (arr_pdetail.Length - 1)`. Still a dupe though. — Bjørn-Roger Kringsjå, Jul 25 '15 at 14:19

score 0 · Accepted Answer · edited May 23 '17 at 12:06

ExecuteScalar could return null (Nothing in VB) if there is no category matching your condition, you should add some defensive action against the result of ExecuteScalar

Dim cmdText = "Select price from tb_pdetail where category = @cat"
Using conn = new MySqlConnection(...........)
Using cmd = New MySqlCommand(cmdText, conn)
    conn.Open()
    cmd.Parameters.Add("@cat", MySqlDbType.VarChar).Value =  arr_pdetail(i)
    Dim result = cmd.ExecuteScalar()
    if result Is Nothing Then
        MessageBox.Show("No category found")
    else
        MessageBox.Show("Found the category with price " & result.ToString())
    End If
End Using

In this reworking of your code, the return of ExecuteScalar is checked against Nothing before trying to use it. If the result is Nothing this could only mean that your table doesn't contain a row with the Category column having the requested value.

Notice also that I have used a parameterized query instead of a string concatenation to avoid possible Sql Injections and parsing problems and finally I have put the disposable objects like the MySqlCommand and the MySqlConnection inside a Using Statement to avoid memory leaks and unnecessary usage of costly resources.

The error is still on the cmd.ExecuteScalar line? Are you sure that you write exactly as above? Do not define the _Dim result as String = cmd.ExecuteScalar()_ — Steve, Jul 25 '15 at 13:10

cmd.ExecuteScalar() Object reference not set to an instance of an object vb.net

1 Answers1