使用 vb.net oledbcommand 从存储过程中检索输出参数（标识列）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24462957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieve output parameter (identity column) from stored procedure with vb.net oledbcommand
提问by user2965499
I am trying to retrieve an output parameter value (identity field) from a stored procedure in SQL Server 2008 R2.
我正在尝试从 SQL Server 2008 R2 中的存储过程中检索输出参数值（标识字段）。
I looked at the following stackoverflow links. I am doing the same but still facing issues: Retrieving output parameter from stored procedure with oledb command vb.netStored procedure - return identity as output parameter or scalar
我查看了以下 stackoverflow 链接。我正在做同样的事情，但仍然面临问题： 使用 oledb 命令从存储过程中检索输出参数 vb.net存储过程 - 将身份作为输出参数或标量返回
My stored procedure:
我的存储过程：
ALTER proc [dbo].[sp_Custom_InsertxRef]
@DocumentID int,
@RevNr int,
@xRefDocument int,
@xRefRevNr int,
@xRefProjectId int,
@RefCount int,
@xRef int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @HasFullPath bit=1;
DECLARE @RelativePath nvarchar(300)='';
DECLARE @RefCountEdit float=NULL;
DECLARE @RefTimeStamp as datetime=GETDATE();
DECLARE @xrType as int = 1;
INSERT INTO [EpiGrid].[dbo].[XRefs]
([DocumentID]
,[RevNr]
,[XRefDocument]
,[XRefProjectID]
,[XRefRevNr]
,[HasFullPath]
,[RelativePath]
,[RefCount]
,[RefCountEdit]
,[RefTimeStamp]
,[XrType])
VALUES
(
@DocumentID,
@RevNr,
@xRefDocument,
@xRefProjectId,
@xRefRevNr,
@HasFullPath,
@RelativePath,
@RefCount,
@RefCountEdit,
@RefTimeStamp,
@xrType
)
SET @xRef=(SELECT SCOPE_IDENTITY());
--select @xRef=(SELECT SCOPE_IDENTITY());
return @xRef;
END
My vb.net code:
我的 vb.net 代码：
Using connection As New System.Data.OleDb.OleDbConnection(Connectionstring)
connection.Open()
Using command As New OleDbCommand("sp_Custom_InsertxRef", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@DocumentID", OleDbType.Integer, 4, ParameterDirection.Input).Value = epdmParDoc.ID
command.Parameters.Add("@RevNr", OleDbType.Integer, 4, ParameterDirection.Input).Value = epdmParDoc.GetLocalVersionNo(parFolderId)
command.Parameters.Add("@xRefDocument", OleDbType.Integer, 4, ParameterDirection.Input).Value = targetReplaceDoc.ID
command.Parameters.Add("@xRefRevNr", OleDbType.Integer, 4, ParameterDirection.Input).Value = targetReplaceDoc.CurrentVersion
command.Parameters.Add("@xRefProjectId", OleDbType.Integer, 4, ParameterDirection.Input).Value = parFolderId
command.Parameters.Add("@RefCount", OleDbType.Integer, 4, ParameterDirection.Input).Value = count
'command.Parameters.Add("@xRef", OleDbType.Integer, 4, ParameterDirection.InputOutput).Value = -1
command.Parameters.Add("@xRef", OleDbType.Integer)
command.Parameters("@xRef").Direction = ParameterDirection.Output
command.ExecuteReader()
xRefId = command.Parameters("@xRef").Value
End Using
connection.Close()
End Using
I tried direction as output and return value and I see the same result. The record is created in the database (no errors) but my code gets nothing for the output parameter. Any idea why?
我尝试将方向作为输出和返回值，我看到了相同的结果。该记录是在数据库中创建的（没有错误），但我的代码没有为输出参数获取任何信息。知道为什么吗？
When I debug the stored procedure in SQL Server Management Studio it creates the record and I see the return or output (tried both) with correct value.
当我在 SQL Server Management Studio 中调试存储过程时，它会创建记录，并且我看到返回或输出（都尝试过）具有正确的值。
Exec [dbo].[sp_Custom_InsertxRef]
@DocumentID =12,
@RevNr =1,
@xRefDocument = 15,
@xRefRevNr =1,
@xRefProjectId =1,
@RefCount =2,
@xRef=-1;
Please advise.
请指教。
回答by Shell
First of all why are you using execute reader function? if you don't want to return any value from stored then just use ExecuteNonQuery()method instead of ExecuteReader(). If you want to read value from stored procedure then you can use ExecuteReader()but, you must have assigned DataReaderobject.
首先，你为什么使用执行阅读器功能？如果您不想从存储中返回任何值，则只需使用ExecuteNonQuery()method 而不是ExecuteReader(). 如果你想从存储过程中读取值，那么你可以使用，ExecuteReader()但是，你必须已经分配了DataReader对象。
For example
例如
Dim dr As OleDBDataReader = Nothing
Try
dr = command.ExecuteReader()
IF dr.HasRows Then
While (dr.Read())
var value = dr.getValue(0)
End While
End IF
dr.Close()
Catch
'OleDB Exception will be thrown here
Finally
IF dr IsNot Nothing Then
IF dr.IsClosed = false Then dr.Close()
End If
End Try
but, to use above method you should make some changes in your stored procedure.
但是，要使用上述方法，您应该对存储过程进行一些更改。
Replace following line:
替换以下行：
SET @xRef=(SELECT SCOPE_IDENTITY());
--select @xRef=(SELECT SCOPE_IDENTITY());
return @xRef;
To
到
SELECT SCOPE_IDENTITY();
after these output parameter will not be needed in your stored procedure.
在您的存储过程中将不需要这些输出参数之后。
回答by user2965499
Using the same SP (no changes) with sqlclient instead of the oledb did the job. Probably a bug.
使用相同的 SP（无变化）与 sqlclient 而不是 oledb 完成了这项工作。大概是个bug。
Using connection As New System.Data.SqlClient.SqlConnection(connectionstrng)
connection.Open()
Using command As New System.Data.SqlClient.SqlCommand("sp_Custom_InsertxRef", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@DocumentID", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.ID
command.Parameters.Add("@RevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.GetLocalVersionNo(parFolderId)
command.Parameters.Add("@xRefDocument", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.ID
command.Parameters.Add("@xRefRevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.CurrentVersion
command.Parameters.Add("@xRefProjectId", SqlDbType.Int, 4, ParameterDirection.Input).Value = parFolderId
command.Parameters.Add("@RefCount", SqlDbType.Int, 4, ParameterDirection.Input).Value = count
'command.Parameters.Add("@xRef", OleDbType.Integer, 4, ParameterDirection.InputOutput).Value = -1
command.Parameters.Add("@xRef", SqlDbType.Int)
command.Parameters("@xRef").Direction = ParameterDirection.Output
command.ExecuteReader()
xRefId = command.Parameters("@xRef").Value
End Using
connection.Close()
End Using
