CODEWITHSUNDEEP
vb.net
user6156963
user6156963

Reputation:

SQL parameter either integer value or dbnull pass to query

Can't get my query to work. I want to pass either integer value or dbnull to my query but in that form is not working. Below my code.

Query:

Using cmd As New SqlCommand("SELECT COUNT(*) FROM tbSuSubSections_Sentences WHERE FK_Sentence_Id = @FK_Sentence_Id And FK_SubSection_Id = @FK_SubSection_Id And FK_SubSubKategorie_Id=@FK_SubSubKategorie_Id", con)

Parameter set up based on which one i need:

If _FK_SubSubKategorie_Id = 0 Then
                    cmd.Parameters.AddWithValue("@FK_SubSubKategorie_Id", DBNull.Value)
                Else
                    cmd.Parameters.AddWithValue("@FK_SubSubKategorie_Id", _FK_SubSubKategorie_Id)
                End If

if _FK_SubSubKategorie_Id is set to DBNull as shown my query should change a bit instead this part:

And FK_SubSubKategorie_Id=@FK_SubSubKategorie_Id", con)

to

And FK_SubSubKategorie_Id IS NULL", con)

What is the right way to do?

that's entire SQL function:

#Region "Check if connection already exist"
    Public Function CheckIfConnectionAlreadyExist(_FK_Sentence_Id As Integer, _FK_SubSection_Id As Integer, _FK_SubSubKategorie_Id As Integer) As Boolean
        Dim result As Boolean
        Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(String)).ToString()
        Using con As New SqlConnection(strcon)
            Using cmd As New SqlCommand("SELECT COUNT(*) FROM tbSuSubSections_Sentences WHERE FK_Sentence_Id = @FK_Sentence_Id And FK_SubSection_Id = @FK_SubSection_Id And FK_SubSubKategorie_Id=@FK_SubSubKategorie_Id", con)
                cmd.CommandType = CommandType.Text

                cmd.Parameters.AddWithValue("@FK_Sentence_Id", _FK_Sentence_Id)
                cmd.Parameters.AddWithValue("@FK_SubSection_Id", _FK_SubSection_Id)
                If _FK_SubSubKategorie_Id = 0 Then
                    cmd.Parameters.AddWithValue("@FK_SubSubKategorie_Id", DBNull.Value)
                Else
                    cmd.Parameters.AddWithValue("@FK_SubSubKategorie_Id", _FK_SubSubKategorie_Id)
                End If
                con.Open()
                Dim o As Integer = cmd.ExecuteScalar()
                If o > 0 Then
                    result = True
                Else
                    result = False
                End If
                con.Close()
            End Using
        End Using
        Return result
    End Function
#End Region

Upvotes: 1

Views: 458

Answers (1)

Alex B.
Alex B.

Reputation: 2167

Just create one query string which both conditions have in common and change it regarding _FK_SubSubKategorie_Id afterwards. Then create the command:

Dim qry as String = "SELECT COUNT(*) FROM tbSuSubSections_Sentences WHERE FK_Sentence_Id = @FK_Sentence_Id And FK_SubSection_Id = @FK_SubSection_Id And FK_SubSubKategorie_Id {0}"

If _FK_SubSubKategorie_Id = 0 Then
  qry = string.Format(qry," IS NULL")
Else
  qry = string.Format(qry, "=@FK_SubSubKategorie_Id"
End if

Using cmd As New SqlCommand(qry, con)
  ...

Upvotes: 1

Related Questions