Scalar returns null

[Miro]

I have a scaller query that can return a null value.

Here is how I get around it:

(start pseudocode)
if ( scalarquery return count > 0 )
dim bla as integer = scalarquery top 1 of columnA order by columnA

(End Pseudocode)

This way I will never get a null being assigned to the integer which creates
an exception.

Is there another way around this? - other than a try catch statement i
guess? <- or is that another proper way.

I just was trying to figuer out if there was a way that I didnt have to hit
the database twice.
Once get get the count of what my result will return,
and the other is the result - so incase there are no records i dont crash
cause of a dbnull.

Thanks,

Miro

 

[Cor Ligthert[MVP]]

Miro,

(I assume that you use Command.ExecuteScalar)

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

I assume that most people test the returned value to DBNull.Value

Cor

 

[Michel Posseth [MCP]]

Hello

You could also just wrap your call in a table adapter wich will return you a
nullable datatype

HTH

Michel

 

[Göran Andersson]

I have a scaller query that can return a null value.

Here is how I get around it:

(start pseudocode)
if ( scalarquery return count > 0 )
dim bla as integer = scalarquery top 1 of columnA order by columnA

(End Pseudocode)

This way I will never get a null being assigned to the integer which
creates an exception.

Is there another way around this? - other than a try catch statement i
guess? <- or is that another proper way.

I just was trying to figuer out if there was a way that I didnt have to
hit the database twice.
Once get get the count of what my result will return,
and the other is the result - so incase there are no records i dont
crash cause of a dbnull.

Thanks,

Miro

You can get the result from the call in a variable of the type object.
Then you can check if the result is DbNull before you cast it to an integer.

Another alternative is to handle it as a regular result, and use the
IsDbNull method of the data reader. The ExecuteScalar method uses a data
reader itself, so there is no performance penalty in doing the same
yourself.

 

[Miro]

Actually I created it in the dataset as a table adapter.
Like this

Dim TopWinnerTableadapter As New
MyDataDataSetTableAdapters.WinnersTableAdapter

'GameID is a paramter of an int - key of file.
If CInt(TopWinnerTableadapter.ReturnTopStanding(_GameID)) <> 1 Then

i have even tried

DIM intBla as Integer = TopWinnerTableadapter.ReturnTopStanding(_GameID)

but i could not figure out how to get do something like this:

dim hello as 'something?' = TopWinnerTableadapter.ReturnTopStanding(_GameID)
DIM intBla as Integer = IIF( IsNull( hello ), 0, hello )

Hope that makes sense.

Miro

 

[Miro]

After re-reading all your posts I see what I think I needed to do...i did
this:

Dim testStanding As Object =
TopWinnerTableadapter.ReturnTopStanding(_GameID)
If IsDBNull(testStanding) Then
...so-on

I had to create the variable as an object and then test it and then go on.

My problem was that i was defining it as an integer right away.

Thanks for your help.

Miro

 

[Cowboy \(Gregory A Beamer\)]

The top two that come to mind are:

Return to an object and test for a null
Return to a nullable type and test for a null

Try .. Catch is possible, but expensive, especially since this appears to
NOT be an EXCEPTIONAL case.

Your method is the other possibility, although it does take two hits. Note,
however, that the top two suggestions would require two hits if you MUST
have a value, so it is a wash. You are still testing and then hitting again.

Question: Can the second hit fail ever?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************

 

[Miro]

No Never.

There are records or there are not.
If there are not I set my variable to 0. Basically in this case null is
zero.

I created a variable as an object, test it for null, and then cint(
variable ) to an integer and push it through the rest of the code.

Basically upon close of a form i was checking to see how many 'children'
records are there, and if zero - pull up a screen for the option to add
some.

Thanks,

Miro