如何从SQL Server存储过程返回值和Utilise他们在Access VBA [英] How to return values from a SQL Server Stored Procedure and Utilise them in Access VBA

问题描述

我已经成立了一个存储过程在SQL Server中正常工作。我现在可以从VBA调用它，但要返回一个值来知道是否有任何错误等。在我的SP的最后一个参数被设置为OUTPUT：

  @DataSetID INT = 0，
@Destination CHAR（1）=' - '，
@errStatusOK位输出
 

我的VBA调用SP低于，但它不会现在的工作，加入了新的参数后，我不知道我要去哪里错了，我不断收到 3708 - 参数对象定义不正确。提供了不一致或不完整信息：

 设置CNN =新adodb.Connection
cnn.ConnectionString =
   DRIVER = SQL Server的; SERVER = Server \服务器; DATABASE = a_db; Trusted_Connection =是

cnn.Open cnn.ConnectionString

设置CMD =新adodb.Command
cmd.ActiveConnection = CNN
cmd.CommandType = adCmdStoredProc
cmd.CommandText =stprMoveDataSet

设置参数= cmd.CreateParameter
               （@DataSetID，adInteger，adParamInput，stDataSet）
cmd.Parameters.Append参数
设置参数= cmd.CreateParameter
               （@Destination，adChar，adParamInput，1，stDestination）
cmd.Parameters.Append参数
设置参数= cmd.CreateParameter
               （@errStatusOK，adBit，adParamReturnValue）
cmd.Parameters.Append参数

rs.CursorType = ADOPENSTATIC
rs.CursorLocation = adUseClient的
rs.LockType = ADLOCKOPTIMISTIC
rs打开CMD
 

我怎样才能获得VBA由VBA使用输出参数工作，使返回值可读。

编辑 - 我已经改变了的问题是关于返回值，而不是只是使用输出参数更具体

解决方案

 设置CNN =新adodb.Connection
cnn.ConnectionString =
   DRIVER = SQL Server的; SERVER = Server \服务器; DATABASE = a_db; Trusted_Connection =是

cnn.Open cnn.ConnectionString

设置CMD =新adodb.Command
cmd.ActiveConnection = CNN
cmd.CommandType = adCmdStoredProc
cmd.CommandText =stprMoveDataSet

设置参数1 = cmd.CreateParameter
               （@DataSetID，adInteger，adParamInput，stDataSet）
cmd.Parameters.Append参数
设置参数2 = cmd.CreateParameter
               （@Destination，adChar，adParamInput，1，stDestination）
cmd.Parameters.Append参数
设置参数3 = cmd.CreateParameter
               （@errStatusOK，adBit，adParamOutput，adParamReturnValue）
cmd.Parameters.Append参数

rs.CursorType = ADOPENSTATIC
rs.CursorLocation = adUseClient的
rs.LockType = ADLOCKOPTIMISTIC
rs打开CMD
 

I've set up a Stored Procedure in SQL Server that works fine. I can now call it from VBA, but want to return a value to know if there were any errors etc. The last parameter in my SP is set up as OUTPUT:

@DataSetID int = 0,
@Destination char(1)='-',
@errStatusOK bit OUTPUT

My VBA to call the SP is below, but it won't work now, after adding the new parameter and I'm not sure where I'm going wrong, I keep getting 3708 - Parameter object is improperly defined. Inconsistent or incomplete information was provided.:

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

How can I get the vba to work with the OUTPUT parameter and make the return value 'readable' by the vba.

EDIT - I've changed the question to be more specifically about returning values and not just about using OUTPUT Parameters.

解决方案

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param1 = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param2 = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param3 = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamOutput, , adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

这篇关于如何从SQL Server存储过程返回值和Utilise他们在Access VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！