从 VB.NET 应用程序调用 SQL Server 存储过程。输出参数返回空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34276309/
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
Calling SQL Server stored procedure from VB.NET application. Output parameter is coming back blank
提问by Tom O.
I am trying to call a simple stored procedure with that returns an OUTPUT parameter from a VB.NET application, but when i try to display the result, it appears blank. The code is not throwing any exceptions and when i execute the SP in SQL Server Mgmt studio, it returns as I expect. I am probably just overlooking something. Appreciate any suggestions. SP and VB code are below:
我试图调用一个简单的存储过程，它从 VB.NET 应用程序返回一个 OUTPUT 参数，但是当我尝试显示结果时，它显示为空白。代码没有抛出任何异常，当我在 SQL Server Mgmt Studio 中执行 SP 时，它会按我的预期返回。我可能只是忽略了一些东西。感谢任何建议。SP和VB代码如下：
SP Code :
SP 代码 :
create procedure [dbo].[get_number_potential_matches] @deductnRecId int, @numberPotentialMatches int output as
begin
select numberPotentialMatches = count(*)
from potential_match pm
where pm.deductn_rec_id = @deductnRecId;
return
end
VB.Net Procedure:
VB.Net程序：
Sub getPotentialMatchCount(ByVal deductnRecId As Integer)
Try
SqlCmd = New SqlCommand("get_number_potential_matches", SqlConn)
SqlCmd.CommandType = CommandType.StoredProcedure
SqlCmd.Parameters.Add("@deductnRecId", SqlDbType.Int).Value = deductnRecId
SqlCmd.Parameters("@deductnRecId").Direction = ParameterDirection.Input
SqlCmd.Parameters.Add("@numberPotentialMatches", SqlDbType.Int)
SqlCmd.Parameters("@numberPotentialMatches").Direction = ParameterDirection.Output
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
Catch ex As Exception
lbl_pm.Text = ex.Message
Finally
SqlCmd.Dispose()
SqlConn.Close()
End Try
lbl_pm.Text = "deductnRecId: " & deductnRecId.ToString & " has " & SqlCmd.Parameters("@numberPotentialMatches").Value.ToString() & " matches"
End Sub
回答by Jakub Lortz
You missed '@' in the parameter name in the SP. It should be
您在 SP 的参数名称中遗漏了“@”。它应该是
select @numberPotentialMatches = count(*) ...
What you did was selecting count(*)and setting numberPotentialMatchesas column alias.
您所做的是选择count(*)并设置numberPotentialMatches为列别名。
Also, don't access the SqlCmdafter it's disposed.
另外，不要在处理SqlCmd后访问它。
