从存储过程返回值时出现问题 [英] Problem returning value from stored procedure

问题描述

我从存储过程中获取返回值时遇到问题，它只是返回dbnull，我检查了SP并且工作正常



存储过程： - < br $>

I have a problem getting a returned value from a stored procedure, it's just returning dbnull, I checked the SP and that's working okay

Stored Procedure :-

alter PROCEDURE UPDATE_SiteVisitors2
	
@VPCode nvarchar(10),


AS

BEGIN

declare @Exists INT 

SET NOCOUNT ON;

IF EXISTS (select vpcode FROM SiteVisitors WHERE vpcode=@vpcode)

BEGIN

update sitevisitors

set

VSignOut = getdate()

where VPCode=@VPCode

		SET @Exists = 1

END

ELSE

BEGIN

		SET @Exists = 0
END

RETURN @Exists

END

VB： -

VB :-

<pre>    Public Function updateVisitors(ByVal VPCode As String)

        Dim conn As New System.Data.SqlClient.SqlConnection(access.SQLstrconn)
        Dim sql As String = "UPDATE_SiteVisitors2 '" & VPCode & "'"
        Dim Cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
        Dim Par As New System.Data.SqlClient.SqlParameter
        Cmd.Parameters.Add("@Exists", SqlDbType.Int)
        Cmd.Parameters("@Exists").Direction = ParameterDirection.Output

        conn.Open()
        Cmd.ExecuteNonQuery()

        SignIn.exists = Cmd.Parameters("@Exists").Value

        conn.Close()

'signin.exists is a public variable int

        MsgBox(SignIn.exists)

        Return SignIn.exists

    End Function

我尝试了什么：



尝试了一些事情，但只是得到了DBNULL

What I have tried:

Tried a few things but just getting DBNULL

推荐答案

@Exists不是输出参数，它是返回值

请尝试以下操作：

@Exists is not an output parameter, it's a return value
Try the following:

Dim sql As String = "UPDATE_SiteVisitors2"
        Dim Cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
        Cmd.Parameters.Add("@Exists", SqlDbType.Int)
        Cmd.Parameters("@Exists").Direction = ParameterDirection.ReturnValue
        Dim par As SqlParameter = Cmd.Parameters.Add("@VP", SqlDbType.NVarChar)
        par.Direction = ParameterDirection.Input
        par.Value = VPCode
...

您需要在SQL中将您的变量声明为OUTPUT。这里有一个很好的例子，使用OUTPUT参数返回数据 [ ^ ] 。



我的偏好是不使用输出参数。只需在你的SQL中使用RETURN而不是SELECT @Exists AS Exists



然后在C＃

You need to declare your variable in SQL as OUTPUT. A good example is here, Returning Data by Using OUTPUT Parameters[^].

My preference is to NOT use output parameters. Instead of RETURN in your SQL just do SELECT @Exists AS Exists

Then in C#

String returnValue;
Object temp = cmd.ExecuteScalar();
if (temp!= null){
  returnValue = temp.ToString();
}

只要你做得对，你去的方式都没问题。 ;）

Either way you go is fine as long as you do it right. ;)

Nailed它。感谢

Nailed it. was thanks to the

Cmd.CommandType = CommandType.StoredProcedure

指针



感谢非常感谢帮助和指示



新密码

pointer

Thanks for the help and pointers much appreciated

New code

Public Function updateVisitors(ByVal VPCode As String)



       Dim conn As New System.Data.SqlClient.SqlConnection(access.SQLstrconn)


       'Dim sql As String = "UPDATE_SiteVisitors2 '" & VPCode & "'"
       Dim sql As String = "UPDATE_SiteVisitors2"
       Dim Cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
       Cmd.CommandType = CommandType.StoredProcedure
       Cmd.Parameters.Add("@VPCode", SqlDbType.VarChar, 10, ParameterDirection.Input).Value = VPCode
       Cmd.Parameters.Add("@Exists", SqlDbType.Int)
       Cmd.Parameters("@Exists").Direction = ParameterDirection.Output
       conn.Open()

       Cmd.ExecuteNonQuery()

       SignIn.exists = Cmd.Parameters("@Exists").Value

       conn.Close()


       MsgBox(SignIn.exists)

       Return SignIn.exists

   End Function

SQL略有变化

SQL slightly changed

alter PROCEDURE UPDATE_SiteVisitors2
	
@VPCode nvarchar(10),
@Exists INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

--declare @Exists INT 

IF EXISTS (select vpcode FROM SiteVisitors WHERE vpcode=@vpcode)


BEGIN

update sitevisitors

set

VSignOut = getdate()

where VPCode=@VPCode


		set @Exists = 1
END
ELSE
BEGIN
		set @Exists = 0
END

RETURN @Exists
END

这篇关于从存储过程返回值时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！