如何使用存储过程将输出参数返回给VB [英] How to used stored procedure to return an output parameter to VB

问题描述

嗨朋友们，我想用SQL存储过程将值返回给VB。下面的代码返回零到VB，但在SQL级别工作正常。任何帮助将不胜感激。



SQL代码

Hi friends, I want to use SQL stored procedure to return value to VB. Below codes returns zero to the VB but works fine at the SQL level. Any help will be appreciated.

SQL Codes

CREATE PROCEDURE prcStockTotalVal
(
     @TotalStockValue money OUTPUT
)
AS
BEGIN
    Select  @TotalStockValue = Sum (QauntityInStock * SellingPrice)
    From STOCK_DETAILS

    Select (@TotalStockValue)
END

VB代码

VB Codes

Try
            With cmd

                net_55.ConnectionString = netDB
                net_55.Open()
                .Connection = net_55
                .CommandText = "prcStockTotalVal"
                .CommandType = CommandType.StoredProcedure

                prmt(0) =New OleDb.OleDbParameter("@TotalStockValue",OleDb.OleDbType.Integer, 8)
              
                prmt(0).Direction = ParameterDirection.Output

                .Parameters.AddWithValue("@TotalStockValue", 0)
               
                .ExecuteNonQuery()
                lblTotalStockValue.Text = .Parameters("@TotalStockValue").Value

             
            End With

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        End Try

谢谢。

Thanks.

推荐答案

请参阅：

http://msdn.microsoft.com/en-us/library/37hwc7kt.aspx [ ^ ]。



注意代码示例使用 ExecuteScalar ，返回一些东西：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar。 aspx [ ^ ]。



如果您使用 ExecuteNonQuery ，则返回值仅为数量受影响的行：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.endexecutenonquery%28v=vs.110%29.aspx [ ^ ]。



如果您的存储过程没有更新数据库中的任何数据，它应该返回零;这正是你的情况。

-SA

Please see:
http://msdn.microsoft.com/en-us/library/37hwc7kt.aspx[^].

Pay attention the code sample uses ExecuteScalar, to return something:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx[^].

If you use ExecuteNonQuery, the return value is merely the number of affected rows:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.endexecutenonquery%28v=vs.110%29.aspx[^].

If your stored procedure does not update any data in the database, it should return zero; this is exactly what happened in your case.

—SA

Plase，请参考： 如何：执行返回行的存储过程 [ ^ ]并阅读所有相关文章。



正如谢尔盖提到的那样，你的查询没有返回任何内容的原因是 ExecuteNonQuery（）的错误用法，用于修改数据 [ ^ ]。



此处 [ ^ ]是如何将数据加载到datatable对象的另一个示例。之后，您将能够将它们用于进一步使用。

Plase, refer this: How to: Execute a Stored Procedure that Returns Rows[^] and read all related articles.

As Sergey had mentioned, the reason your query returns nothing is bad usage of ExecuteNonQuery(), which is used to modify data[^].

Here[^] is another example how to load data into datatable object. After that you'll be able to use them for further usage.

这篇关于如何使用存储过程将输出参数返回给VB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！