问题描述
使用VB 2012和SQL Server 2012.书面和测试的存储过程根据过程的成功返回值。存储过程工作得很好，但我很难正确编写代码来检索返回值，所以我可以处理它。
什么不断返回我的变量是一个-1。无论NOCOUNT设置为ON还是OFF，都会发生这种情况。
我尝试过的事情：
SQL Server存储过程：
USE [BEClientData]
GO
/ ******对象：StoredProcedure [dbo]。[sprocAuthorizeLogin]脚本日期：9/20/2016 9:35:48 AM ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ============= ================================
- 作者：Stan Bibbs
- 创建日期：2016年9月18日
- 描述：检查登录提交
- ============= ================================
ALTER PROCEDURE [dbo]。[sprocAuthorizeLogin]
- 在这里添加存储过程的参数
@Username nvarchar（20）= 0，
@Password nvarchar（20）= 0
AS
BEGIN
- 添加SET NOCOUNT ON以防止来自
- 干扰SELECT语句。
SET NOCOUNT ON;
- 在此处插入程序声明
如果EXISTS（选择用户名，密码
来自dbo.SignInTable
WHERE用户名= @Username和密码= @Password）
返回2
ELSE
返回1
结束
VB函数调用程序：
公共函数ValidateLogin（ByVal strUsername As String，ByVal strPassword As String）As Integer
Dim intExists As Integer
Dim cn As New SqlConnection（My.Settings.BEClientDataConnectionString）
Dim procName As String =sprocAuthorizeLogin
Dim cmd As New SqlCommand（procName，cn）
with cmd
.CommandType = CommandType.StoredProc edure
.CommandText = procName
.Parameters.Add（@ Username，SqlDbType.NVarChar，20）.Value = strUsername
。 Parameters.Add（@ Password，SqlDbType.NVarChar，20）.Value = strPassword
结束
cn.Open（）
intExists = cmd.ExecuteNonQuery（）
返回intExists
Using VB 2012 and SQL Server 2012. Written and tested stored procedure to return a value depending upon success of procedure. Stored procedure works perfectly, but I am having difficulty writing the code correctly to retrieve the return value so I can process it.
What keeps getting returned to my variable is a -1. This happens regardless of whether NOCOUNT is set to ON or OFF.
What I have tried:
SQL Server stored procedure:
USE [BEClientData]
GO
/****** Object: StoredProcedure [dbo].[sprocAuthorizeLogin] Script Date: 9/20/2016 9:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Stan Bibbs
-- Create date: 18 Sep 2016
-- Description:Check login submission
-- =============================================
ALTER PROCEDURE [dbo].[sprocAuthorizeLogin]
-- Add the parameters for the stored procedure here
@Username nvarchar(20) = 0,
@Password nvarchar(20) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(SELECT Username, Password
FROM dbo.SignInTable
WHERE Username = @Username AND Password = @Password)
RETURN 2
ELSE
RETURN 1
END
VB function which calls procedure:
Public Function ValidateLogin(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim intExists As Integer
Dim cn As New SqlConnection(My.Settings.BEClientDataConnectionString)
Dim procName As String = "sprocAuthorizeLogin"
Dim cmd As New SqlCommand(procName, cn)
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = procName
.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = strUsername
.Parameters.Add("@Password", SqlDbType.NVarChar, 20).Value = strPassword
End With
cn.Open()
intExists = cmd.ExecuteNonQuery()
Return intExists
推荐答案
ALTER PROCEDURE [dbo].[sprocAuthorizeLogin]
@Username nvarchar(20) = 0,
@Password nvarchar(20) = 0
AS
BEGIN
IF EXISTS(SELECT Username, Password FROM dbo.SignInTable WHERE Username = @Username AND Password = @Password)
select 2
ELSE
select 1
END
intExists = Convert.ToInt32(cmd.ExecuteScalar())
参考 []
CREATE PROCEDURE spGetTheRETURNValue
AS
BEGIN
RETURN 666
END
CREATE PROCEDURE spGetTheActualValue
AS
BEGIN
DECLARE @ret INT
EXEC @ret = spGetTheReturnValue
SELECT @ret
END然后你就可以了使用ExecuteScalar创建新过程并以该方式检索值。
You can then call the new procedure using ExecuteScalar and retrieve the value that way.
这篇关于如何使用VB作为调用代码从存储过程中检索返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！