Link to home
Start Free TrialLog in
Avatar of rutledgj
rutledgj

asked on

Problem checking for dbnull

I'm making a simple call to a tsql stored proc that does the following:
ALTER PROCEDURE [INFC].[usp_CheckForPatientExist]
                         @Org varchar(50),
                         @Chartnum varchar(50),
                         @PtId int OUTPUT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      
            BEGIN TRY
                  --See if patient exists first
                  SET @PtId = (SELECT ptid FROM INSTANCE1.MedscribeSQL.dbo.Master
                                     WHERE org = @Org AND Chartnum = @Chartnum)
                  print @PtId                   
          END TRY
            BEGIN CATCH
                        IF @@TRANCOUNT > 0
                        BEGIN
                              ROLLBACK TRAN
                              DECLARE  @ErrMsg1 nvarchar ( 4000 ),  @ErrSeverity1 int
                              SELECT  @ErrMsg1 =   ERROR_MESSAGE (),  @ErrSeverity1 =   ERROR_SEVERITY ()
                              RAISERROR ( @ErrMsg1 ,  @ErrSeverity1 ,  16 )
                              
                              RETURN
                        END
            END CATCH      
            RETURN
END




In my vb code I'm doing an executescalar and expecting to be able to read the value in the ptid param (see attached code).

The problem I have is in the if statement. It passes the first If check even though the contents of p is null. See attached image.

How can I check for a null or no return value here?






Dim param(2) As SqlClient.SqlParameter
        Dim result As Boolean = False
        Try
            Dim pOrg As New SqlClient.SqlParameter
            pOrg.ParameterName = "@Org"
            pOrg.SqlDbType = SqlDbType.VarChar
            pOrg.Direction = ParameterDirection.Input
            pOrg.Value = cboOrg.Text.Trim
            param(0) = pOrg

            Dim pChartnum As New SqlClient.SqlParameter
            pChartnum.ParameterName = "@Chartnum"
            pChartnum.SqlDbType = SqlDbType.VarChar
            pChartnum.Direction = ParameterDirection.Input
            pChartnum.Value = txtChartNum.Text.Trim
            param(1) = pChartnum

            Dim pPtId As New SqlClient.SqlParameter
            pPtId.ParameterName = "@PtId"
            pPtId.SqlDbType = SqlDbType.Int
            pPtId.Direction = ParameterDirection.Output
            pPtId.Value = -1
            param(2) = pPtId

            Using Sqlops As New DataAccess.DatabaseOperations()
                Sqlops.SqlServer_ExecuteScalar(CS.CS_SQLServer_Robot, "INFC.usp_CheckForPatientExist", CommandType.StoredProcedure, param)
                Dim p As SqlClient.SqlParameter
                p = param(2)
                If Not IsDBNull(p) AndAlso p.Value.ToString.ToLower <> "null" Then
                    If CInt(p.Value) > -1 Then
                        result = True
                    End If
                End If
            End Using
        Catch ex As Exception
            MessageBox.Show("Error looking up chartnumber. Error: " & ex.ToString)
        End Try

Open in new window

ParameterValue.jpg
Avatar of rushtoshankar
rushtoshankar
Flag of United States of America image

if p <> vbNull ?
Avatar of Éric Moreau
If Not IsDBNull(p) AndAlso not convert.isdbnull(p.Value) Then
if p <> vbNull AndAlso Not IsDBNull(P) other conditions here
Avatar of rutledgj
rutledgj

ASKER

That throws this error:

Operator '<>' is not defined for type 'DBNull' and type 'VariantType
Try:

If p IsNot Nothing Then AndAlso p.Value.ToString.ToLower <> "null" Then

Open in new window

Ooops! Remove the extra Then in mine  = )
ASKER CERTIFIED SOLUTION
Avatar of tlayton
tlayton
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Further to above post, you do not need the 2nd condition as it will be returned as an int and the .value will never be accessible if it is null.
This is the only one that worked. Thanks

If Not IsDBNull(p.SqlValue)
You're welcome, happy coding :)