JimmyJimm JimmyJimm - 7 months ago 31
Vb.net Question

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

Answer

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)
  ...