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_CheckForPatien tExist]
@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?
ALTER PROCEDURE [INFC].[usp_CheckForPatien
@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
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
ParameterValue.jpg
if p <> vbNull ?
If Not IsDBNull(p) AndAlso not convert.isdbnull(p.Value) Then
if p <> vbNull AndAlso Not IsDBNull(P) other conditions here
ASKER
That throws this error:
Operator '<>' is not defined for type 'DBNull' and type 'VariantType
Operator '<>' is not defined for type 'DBNull' and type 'VariantType
Try:
If p IsNot Nothing Then AndAlso p.Value.ToString.ToLower <> "null" Then
Ooops! Remove the extra Then in mine = )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
This is the only one that worked. Thanks
If Not IsDBNull(p.SqlValue)
If Not IsDBNull(p.SqlValue)
You're welcome, happy coding :)