[RESOLVED] Can anybody see what is wrong with this sql code???

[Kimmy4]

Hi guys,
I am using .net cf 2.0.

I am trying to get the maximum value from a column in a table in my database and then call a function using the value returned.

Here's the code i have:

sqlCmd = Database.sqlConn.CreateCommand
            With sqlCmd
                .CommandText = "SELECT MAX(seqOrder) FROM questionnaire_results WHERE visitID = @visitID AND qID = @qID"
                .Parameters.Add(New SqlCeParameter("@visitID", GetCurrentVisit()))
                .Parameters.Add(New SqlCeParameter("@qID", qID))
                maxSeqOrder = .ExecuteScalar.ToString
                If IsDBNull(maxSeqOrder) OrElse IsNothing(maxSeqOrder) Then
                    maxSeqOrder = 0
                End If
                Select Case maxSeqOrder
                    Case "0"
                        DisplayQuestion(0)
                    Case Else
                        DisplayQuestion((CInt(maxSeqOrder) / 5) - 1)
                End Select
            End With

Now everytime i run this code i keep getting an invalidcast exception. maxSeqOrder is declared (currently) as a string and DisplayQuestion needs to be passed an integer. I have tried using maxseqorder as an integer but i get the same exception. I have put debug lines in and found that the exception is thrown at the line

maxSeqOrder = .ExecuteScalar.ToString

when maxseqorder is a string and a integer. I have tried to get help from a colleaugue but neither of us can see why the exception is being thrown.

Can anybody else see where the problem is coming from?

Thanks in advance for any help

[Kimmy4]

I've managed to work around it by storing all the values in the seqOrder column of my table and ordering them in descending order and then using the first element in the array, but surely there has to be a way to do it straight from the SELECT statement???
Is there???

[jmcilhinney]

Firstly, you should debug your code. ExecuteScalar returns an Object reference, so assign it to an Object variable, place a break point on that line and examine the value.

Secondly, this code is dodgy:

If IsDBNull(maxSeqOrder) OrElse IsNothing(maxSeqOrder) Then
                    maxSeqOrder = 0
                End If

You've said that maxSeqOrder is a String so testing it for DBNull is pointless. A String can never be DBNull. Also, if ExecuteScalar was to return Nothing then calling ToString on the result would throw an exception, so testing maxSeqOrder for Nothing is also pointless. Finally, you're assigning an Integer value to a String variable. Not the end of the world but poor practice. If your column contains numbers then why not declare maxSeqOrder as a numeric type, otherwise at least assign a string value to it.

[Kimmy4]

Ok thanks for the advice. I'll try it out and let you know what i get.
Thanks again

[Kimmy4]

A quick question i have....if i declare maxSeqOrder as an object and assign it to .executescalar, when i come to use maxseqorder what check will i need to make to make sure that nothing is being returned. Shall i use IsNothing or is it here where i should use IsDBNull? If not IsDBNull then could you explain when IsDBNull should be used?
Thanks

[jmcilhinney]

ExecuteScalar can return three things: a value, a DBNull object or a null reference. If you execute a query that returns the MAX value of a column then if and only if the table has no rows will ExecuteScalar return a null reference. If and only if the table contains null in that column in every row will it return a DBNull object. Now, you need to ask yourself whether those scenarios are possible. If they are not then there is no point to testing for a null reference or a DBNull value.

Having said that, if you take the return value of ExecuteScalar and call ToString on it then it's already too late to bother about testing for a null reference or a DBNull value. If ExecuteScalar did return a null value then calling ToString on it would throw an exception, while if it returned a DBNull value then calling ToString would return an empty string. If you want to test the return value then test the return value itself, before you (try to) do anything else to it.

Also, don't use IsDBNull or IsNothing at all. Compare values directly to DBNull.Value and Nothing:

Dim result As Object = myCommand.ExecuteScalar()
 
If result Is Nothing Then
    'The query returned a null reference, i.e. Nothing.
Else If result Is DBNull.Value Then
    'The query returned a null value, i.e. DBNull.Value.
Else
    'The query returned a value that you can cast as the appropriate type and use.
End If

[Kimmy4]

Ok thanks for clearing it up for me. I'll use your suggestion.
Thanks again