executescalar() method

Dear .NET Programmers,

I use the following code snippet but can't figure out what is going wrong
since the result object equals to nothing although it shouldn't be :)

Function CheckMFileToBeClosed() As Boolean
    Dim result As Object
    myCommand.Parameters.Clear()
    myCommand.CommandText = "SELECT Result = CASE WHEN DischargeDate IS NULL
THEN 1 " & _
    "ELSE 0 END " & _
    "FROM dbo.MedicalFollowUpInHospital " & _
    "WHERE OurFileNo = @medicalFileNo"
    myCommand.CommandType = CommandType.Text
    myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber")
+ "M")
    result = myCommand.ExecuteScalar()
    If CInt(result) = 1 Then
        Return False
    Else
        Return True
    End If
End Function

Thanks in advance,

Buran

Hi,

my guess is that there is no row returned, therefore the result set is empty
and the scalar result is set to NULL. The reason for not returning a row
could be due to the WHERE OurFileNo = @medicalFileNo clause. Debug into the
code and take a look at Session("selectedFileNumber") especially the type.

Best regards,

Marc Hpner






NULL
Session("selectedFileNumber")

Only thing I see strange is the following:
myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber") +
"M")

Corrected:
myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber") &
"M")

-- 
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think Outside the Box!
****************************************************************************
****




NULL
Session("selectedFileNumber")

NULL
Session("selectedFileNumber")


 - ExecuteScalar is slow and sloppy.
 - don't reuse command objects.
 - VB now has unlimited line lengths. Use them for static SQL queries.
     SQL queries should no longer be broken over lines.
 - Test it in Query Analyzer.  With the static query it's easy to paste it
into QA
   just add declare's for the parameters

in QA:

declare @Result int
declare @medicalFileNo varchar(50)
set @medicalFileNo = '123546M'

SELECT @Result = CASE WHEN DischargeDate IS NULL THEN 1 ELSE 0 END FROM
MedicalFollowUpInHospital WHERE OurFileNo = @medicalFileNo

select @Result

Updated Function:

Function CheckMFileToBeClosed() As Boolean

    dim myCommand as new SQLCommand("SELECT @Result = CASE WHEN
DischargeDate IS NULL THEN 1 ELSE 0 END FROM MedicalFollowUpInHospital WHERE
OurFileNo = @medicalFileNo",con)
    Dim pResult As New SqlClient.SqlParameter("@Result", SqlDbType.Int)
    pResult.Direction = ParameterDirection.Output
    myCommand.Parameters.Add(pResult)
    myCommand.Parameters.Add("@medicalFileNo",
CStr(Session("selectedFileNumber")
& "M")) 'always cast explicitly for this constructor.

    myCommand.ExecuteNonQuery

    If CInt(pResresult) = 1 Then
        Return False
    Else
        Return True
    End If
End Function


David

Dear David, thanks for help but following code says "Invalid cast from
sqlparameter to integer" ??

 If CInt(pResresult) = 1 Then
         Return False
Else
         Return True
End If



"David Browne" <davidbaxterbrowne no potted  XXXX@XXXXX.COM > wrote in





wrong
WHERE

Thanks David, this solved the problem :)

"David Browne" <davidbaxterbrowne no potted  XXXX@XXXXX.COM > wrote in





wrong
WHERE