ExecuteScalar null reference

[paulthepaddy]

hey guys, i understand what is happening here im, just not sure of how to work around it.

Public Function CarExists(reg As String) As Boolean
        Using constring As New OleDb.OleDbConnection(My.Settings.DBConnectionString)
            Dim DA As New OleDb.OleDbDataAdapter
            DA.SelectCommand = New OleDb.OleDbCommand("SELECT Reg FROM Cars WHERE Reg = @Reg", constring)
            DA.SelectCommand.Parameters.AddWithValue("@Reg", reg)
            constring.Open()
            Return If(DA.SelectCommand.ExecuteScalar.ToString = reg, True, False)
            constring.Close()
        End Using
    End Function

If i pass a Reg that does exist in the Database then everything works fine, BUT when i pass a Reg that ISN'T in the DB it is giving a null reference error. which i understand the select Statement should only return a row so if it returns nothing then the scalar is nothing, does anyone of a fix or a better way to get the job done.

Thanks

 

[jmcilhinney]

First of all, why are you creating a data adapter? You're calling ExecuteNonQuery on the command so just create the command. The data adapter is completely useless.

Secondly, this statement is a bit silly:

Return If(DA.SelectCommand.ExecuteScalar.ToString = reg, True, False)

You do an equality comparison and if that evaluates to True then you return the literal value True and if it evaluates to False then you return the the literal value False. Why not just use the result of the comparison in the first place? That code is functionally equivalent to this:

Return DA.SelectCommand.ExecuteScalar.ToString = reg

Thirdly, you have the line to Close the connection after your Return statement, which means that that line will never be hit. Your Close call is useless for that reason but not needed in the first place, given that the connection is disposed, and therefore closed, at the End Using line.

Finally, your method of determining whether the value exists is just not the way it should be done. You should get a COUNT of matching records and then that will be either zero or not zero and that is the condition you test.

If you really were going to do it that way and ExecuteScalar might return Nothing then you simply assign the result of ExecuteScalar to a variable and check whether that variable is Nothing.

 

[paulthepaddy]

Hi jmcilhinney, thanks for you reply, and a few lessons here as well it would seem.

First of all, why are you creating a data adapter? You're calling ExecuteNonQuery on the command so just create the command. The data adapter is completely useless.

I had a question and used google to find answers, that was code that just worked for me. So am i right to think if im doing ANYTHING that is executeNonQuery i can just use a command component instead of linking it to a data adapter.
if im honest i have loads of code that uses data adapters that is only doing nonQuery's. some one had said to use a DA,command and a command builder to get the statements generated, but im writing my own statements anyway, so would youu advise just get rid of them and use the command?

Secondly, this statement is a bit silly: Code:


Return If(DA.SelectCommand.ExecuteScalar.ToString = reg, True, False)
You do an equality comparison and if that evaluates to True then you return the literal value True and if it evaluates to False then you return the the literal value False. Why not just use the result of the comparison in the first place? That code is functionally equivalent to this: Code:

Return DA.SelectCommand.ExecuteScalar.ToString = reg

i am re writing my app in a new project, everything from scratch, i have options Explicit, strict and infer on, so i am getting LOADS of errors when copying small chunks of code over. This code was giving me errors i just fiddled with it until i got it to half work.
hense the closing of the connection, i know USING disposes of it after and it wont get fired after the return value, but the app was hanging when calling this so, it was just part of my process of elimination.

thanks for the advice, il change the method to a count test instead, i just thought because their will only ever be 1 reg in my DB that ExecuteScalar would be the better way to go. thanks for correcting that tho.

 

[paulthepaddy]

hi again

i changed the code to this and im getting a false returned instead of a true as i know the reg is in the database.

as far as i know counts generally arn't zero based, so if its returning a integer of the rows surely its should be 1 or above

Public Function CarExists(reg As String) As Boolean
        Using constring As New OleDb.OleDbConnection(My.Settings.DBConnectionString)
            Dim Command As New OleDb.OleDbCommand("SELECT * FROM Cars WHERE Reg = @Reg", constring)
            Command.Parameters.AddWithValue("@Reg", reg)
            constring.Open()
            If Command.ExecuteNonQuery() >= 1 Then
                Return True
            Else
                Return False
            End If
        End Using
    End Function

 

[paulthepaddy]

Hey i got it, i think this is what you where hinting at me to use

Public Function CarExists(reg As String) As Boolean
        Using constring As New OleDb.OleDbConnection(My.Settings.DBConnectionString)
            Dim Command As New OleDb.OleDbCommand("SELECT Count(*) FROM Cars WHERE Reg = @Reg", constring)
            Command.Parameters.AddWithValue("@Reg", reg)
            constring.Open()
            Dim result As Integer
            result = CInt(Command.ExecuteScalar)
            If result = 1 Then Return True
            Return False
        End Using
    End Function

 

[jmcilhinney]

You're still testing a Boolean to get a Boolean. Just return the original Boolean. This:

result = 1

is a Boolean expression. It evaluates to True or False. Just return that result.

Also, is 'constring' really a sensible name in that context? Does the variable represent a connection string? No, it doesn't. It represents a connection, so the sensible name would be 'connection'.

connection.Open()

Return CInt(command.ExecuteScalar()) > 0

That's all you need.

 

[paulthepaddy]

Yea ok, i never thought i could do that, but again smaller neater win win

believe it or not, already beat you to it,

Dim Command As New OleDb.OleDbCommand("SELECT Count(*) FROM Cars WHERE Reg = @Reg", Connection)
        Command.Parameters.AddWithValue("@Reg", reg)
        Connection.Open()
        Dim result As Integer = CInt(Command.ExecuteScalar)
        Connection.Close()
        If result = 1 Then Return True
        Return False

I was only using the 'Using' to help encapsulate it and debug, it was just to make sure, the hanging wasn't being caused by the connection

i have a module level OleDBConnection for the chunks of small database code.

Thanks For Your Help!!!!
Its good to see bits of code get tidy up

 

[paulthepaddy]

HA HA, im going to have to use 'Using 'anyway as my 'Close' statement wont get fired

EDIT : I might be alright, their is always another DB function to come after checking for car, invoice, would you say id be alright to leave it as it is, or just the using?