Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem checking for dbnull

Posted on 2011-10-25
10
Medium Priority
?
364 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:rutledgj
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 6

Expert Comment

by:rushtoshankar
ID: 37026776
if p <> vbNull ?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37026783
If Not IsDBNull(p) AndAlso not convert.isdbnull(p.Value) Then
0
 
LVL 6

Expert Comment

by:rushtoshankar
ID: 37026786
if p <> vbNull AndAlso Not IsDBNull(P) other conditions here
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rutledgj
ID: 37026793
That throws this error:

Operator '<>' is not defined for type 'DBNull' and type 'VariantType
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37026816
Try:

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

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37026824
Ooops! Remove the extra Then in mine  = )
0
 
LVL 5

Accepted Solution

by:
tlayton earned 1000 total points
ID: 37026860
If Not IsDBNull(p.SqlValue)
0
 
LVL 5

Expert Comment

by:tlayton
ID: 37026872
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.
0
 

Author Closing Comment

by:rutledgj
ID: 37026936
This is the only one that worked. Thanks

If Not IsDBNull(p.SqlValue)
0
 
LVL 5

Expert Comment

by:tlayton
ID: 37026958
You're welcome, happy coding :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question