如何从存储过程中检索输出参数并在vb.net中获取值? [英] How to retrieve output parameter from Store procedure and get value in vb.net?

问题描述

我创建了一个返回一个值的sql存储过程.它运行
从查询分析器成功.

我已经创建了下面给出的存储过程

I have created a sql stored procedure which returns one value. It runs
successfully from query analyser.

I have created Store procedure given below

CREATE PROCEDURE Realto
@MM		varchar(50),
@YY		varchar(50),
@amt     int  output
  	
AS
Declare  @alto int
Declare  @realto  int

if(select   sum(intAltoAmt) from tblJoiningPayment where strMonth<=  @MM  and strYear<=  @YY)=0
  Begin
    Set @alto=0
  end
else
  Begin             
    select   @alto=  sum(intAltoAmt) from tblJoiningPayment where strMonth<= @MM  and strYear<=  @YY
  end

if(select sum(intPayAmount) from tblAltoPayment where strMonth<=  @MM  and strYear<=  @YY)=0
  Begin
    Set @realto=0
  end
else                
  Begin		
    select   @realto=  sum(intPayAmount) from tblAltoPayment where strMonth<= @MM  and strYear<=  @YY
  End
Set @amt =@alto- @realto
GO

VB.net代码

VB.net code

Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "''" & MM & "''"
cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "''" & YY & "''"
cmd1.Parameters.Add("@amt", SqlDbType.Int)
cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
Con.Open()
cmd1.ExecuteNonQuery()
Con.Close()

Label38.Text = cmd1.Parameters("@amt").Value.ToString()

我说的对吗?因为这将返回零值.
但是我在查询分析器中运行，它返回一些值.有什么问题吗?
请帮帮我.

谢谢
Mahendra Kumar Das

我想从我的vb.net代码中运行它.

Am I right or not? Because this is returning zero value.
But I run in query analyzer it returns some value. What''s the problem?
Please help me.

Thanks
Mahendra Kumar Das

I want to run it from my vb.net code

推荐答案

谢谢您的提问.

Thank you for your question.

Try
Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & MM & "'"
            cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & YY & "'"
            cmd1.Parameters.Add("@amt", SqlDbType.Int)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output
            Con.Open()
            cmd1.ExecuteNonQuery()
Label38.Text = cmd1.Parameters("@amt").Value.ToString()

    Catch ex As Exception
           // your code here
            Exit Sub
        Finally
            Con.Close()
        End Try

谢谢，
Mamun

Thanks,
Mamun

您需要在获得输出值后关闭连接.
尝试这个.我认为这会对您有所帮助.

You need to close the connection after getting the output value.
Try this. I think this will help you.

Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & MM & "'"
            cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & YY & "'"
            cmd1.Parameters.Add("@amt", SqlDbType.Int)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
            Con.Open()
            cmd1.ExecuteNonQuery()
            
Label38.Text = cmd1.Parameters("@amt").Value.ToString()

Con.Close()

尝试在获取输出参数后关闭连接.

Try closing the connection AFTER retrieving the output parameter.

这篇关于如何从存储过程中检索输出参数并在vb.net中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！