SQL参数整数值或dbnull传递给查询

user6156963 发表于 Dev

用户名

无法使我的查询正常工作。我想将整数值或dbnull传递给我的查询，但是这种形式不起作用。在我的代码下面。

询问：

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)

根据我需要的参数设置：

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

如果_FK_SubSubKategorie_Id设置DBNull为如图所示，我的查询应该更改一下，而不是这部分：

And FK_SubSubKategorie_Id=@FK_SubSubKategorie_Id", con)

到

And FK_SubSubKategorie_Id IS NULL", con)

正确的做法是什么？

这就是整个SQL函数：

#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

亚历克斯·B。

只需创建两个条件都相同的查询字符串，然后再进行更改即可_FK_SubSubKategorie_Id。然后创建命令：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。