返回存储过程值

Question

我正在尝试获取具有搜索ID的行数。我正在使用它来检查重复项。

我不确定如何将计数作为变量返回，以便进行比较。我有检查If cmd.ExecuteNonQuery() = 0 Then但它总是 -1。我试过做一个Int row as Integer = cmd.ExecuteNonQuery()，总是-1。

所以我的问题是...在我执行cmd.ExecuteNonQuery()后，如何获取计数量的数据集值并将其分配给类似Dim count as Integer = ....

存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE UserCheck
-- Add the parameters for the stored procedure here
@ID bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
   SELECT COUNT(*) FROM tbl WHERE ID= @ID;
END
GO

Vb.net

   Dim CS1 As String = ModuleGlobals.connectionString
    Using con As New SqlConnection(CS1)
        Dim cmd As New SqlCommand("dbo.UserCheck", con)
        con.Open()
        'specify that it is a stored procedure and not a normal proc
        cmd.CommandType = System.Data.CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ID", TextBox3.Text)
        'Dim count As Integer = cmd.ExecuteNonQuery()
        If cmd.ExecuteNonQuery() = 0 Then
            Dim CS As String = ModuleGlobals.connectionString
            Using con2 As New SqlConnection(CS)
                Dim cmd2 As New SqlCommand("dbo.Create", con2)
                con2.Open()
                ...add parameters here
                cmd2.ExecuteNonQuery()
                con2.Close()
            End Using
        Else
            MsgBox("No user was created")
        End If
        con.Close()
    End Using

Answer 1

根据 ExecuteNonQuery() 方法的文档：

对于 UPDATE、INSERT 和 DELETE 语句，返回值是受命令影响的行数。

[...]

对于所有其他类型的语句，返回值为 -1。如果发生回滚，则返回值也是 -1。

由于您的存储过程仅调用 SELECT ，它将始终返回 -1。

如果我没记错的话，您必须改用ExecuteScalar()方法。

Dim count As Integer = CType(cmd.ExecuteScalar(), Integer)