executescalar() method

  • Thread starter Thread starter buran
  • Start date Start date
B

buran

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 Höppner
 
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!
****************************************************************************
****
 
buran said:
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
Click to expand...


- 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
 
Thanks David, this solved the problem :)

David Browne said:
- 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
Click to expand...
 
Back
Top