Get Return value from SQL Stored Procedure.

[Hardik Shah]

Hi,

I have created a sql stored procedure which returns two values. it runs successfully from query analyser. I want to run it from my vb.net code , it runs without any error but it don't store the value of return parameter (i.e. dbid,tbid) . Here I paste my code. Please tell me where I am wrong.

Thanks in advance.

Hardik shah.

==========

Dim dbid As Integer

Dim tbid As Integer

Dim myReader As SqlDataReader

Dim CONN As New SqlConnection

CONN.ConnectionString = "workstation id=SBGBHO;packet size=4096;user id=sa;data source=SBGBHO;persist security info=True;initial catalog=hsData;password=hardik"

Dim mycmd As New SqlCommand

mycmd.Connection = CONN

mycmd.CommandText = "strproc"

mycmd.CommandType = CommandType.StoredProcedure

Dim Param1 As New SqlClient.SqlParameter("@dbasename", SqlDbType.Char, DbName)

Dim Param2 As New SqlClient.SqlParameter("@tblname", SqlDbType.Char, tblName)

Dim Param3 As New SqlClient.SqlParameter("@dbase_id", dbid)

Dim Param4 As New SqlClient.SqlParameter("@table_id", tbid)

Param1.Direction = ParameterDirection.Input

Param2.Direction = ParameterDirection.Input

Param3.Direction = ParameterDirection.Output

Param4.Direction = ParameterDirection.Output

mycmd.Parameters.Add(Param1)

mycmd.Parameters.Add(Param2)

mycmd.Parameters.Add(Param3)

mycmd.Parameters.Add(Param4)

CONN.Open()

mycmd.ExecuteNonQuery()

CONN.Close()

CONN = Nothing

 

[Ken Tucker [MVP]]

Hi,

Here is a link on how to get a return value. You really should not
post your passwords in the newsgroup.

http://msdn.microsoft.com/library/d...ml/cpconinputoutputparametersreturnvalues.asp

Ken
--------------------------

Hi,
I have created a sql stored procedure which returns two values. it runs
successfully from query analyser. I want to run it from my vb.net code , it
runs without any error but it don't store the value of return parameter
(i.e. dbid,tbid) . Here I paste my code. Please tell me where I am wrong.
Thanks in advance.
Hardik shah.
==========
Dim dbid As Integer
Dim tbid As Integer
Dim myReader As SqlDataReader
Dim CONN As New SqlConnection
CONN.ConnectionString = "workstation id=SBGBHO;packet size=4096;user
id=sa;data source=SBGBHO;persist security info=True;initial
catalog=hsData;password=hardik"
Dim mycmd As New SqlCommand
mycmd.Connection = CONN
mycmd.CommandText = "strproc"
mycmd.CommandType = CommandType.StoredProcedure
Dim Param1 As New SqlClient.SqlParameter("@dbasename", SqlDbType.Char,
DbName)
Dim Param2 As New SqlClient.SqlParameter("@tblname", SqlDbType.Char,
tblName)
Dim Param3 As New SqlClient.SqlParameter("@dbase_id", dbid)
Dim Param4 As New SqlClient.SqlParameter("@table_id", tbid)
Param1.Direction = ParameterDirection.Input
Param2.Direction = ParameterDirection.Input
Param3.Direction = ParameterDirection.Output
Param4.Direction = ParameterDirection.Output
mycmd.Parameters.Add(Param1)
mycmd.Parameters.Add(Param2)
mycmd.Parameters.Add(Param3)
mycmd.Parameters.Add(Param4)
CONN.Open()
mycmd.ExecuteNonQuery()
CONN.Close()
CONN = Nothing

 

[ECathell]

dbid=param3.value
tbid=param4.value

also you can really lighten your coding load either by using the DAAB 2.0 or

With cmd

..Connection = con

..CommandTimeout = 120

..CommandType = CommandType.StoredProcedure

..CommandText = "br_updateboxweights"

..Parameters.Add("@loadno", SqlDbType.Int)

..Parameters("@loadno").Value = loadnumber

End With


--
--Eric Cathell, MCSA
Hi,

I have created a sql stored procedure which returns two values. it runs successfully from query analyser. I want to run it from my vb.net code , it runs without any error but it don't store the value of return parameter (i.e. dbid,tbid) . Here I paste my code. Please tell me where I am wrong.

Thanks in advance.

Hardik shah.

==========

Dim dbid As Integer

Dim tbid As Integer

Dim myReader As SqlDataReader

Dim CONN As New SqlConnection

CONN.ConnectionString = "workstation id=SBGBHO;packet size=4096;user id=sa;data source=SBGBHO;persist security info=True;initial catalog=hsData;password=hardik"

Dim mycmd As New SqlCommand

mycmd.Connection = CONN

mycmd.CommandText = "strproc"

mycmd.CommandType = CommandType.StoredProcedure

Dim Param1 As New SqlClient.SqlParameter("@dbasename", SqlDbType.Char, DbName)

Dim Param2 As New SqlClient.SqlParameter("@tblname", SqlDbType.Char, tblName)

Dim Param3 As New SqlClient.SqlParameter("@dbase_id", dbid)

Dim Param4 As New SqlClient.SqlParameter("@table_id", tbid)

Param1.Direction = ParameterDirection.Input

Param2.Direction = ParameterDirection.Input

Param3.Direction = ParameterDirection.Output

Param4.Direction = ParameterDirection.Output

mycmd.Parameters.Add(Param1)

mycmd.Parameters.Add(Param2)

mycmd.Parameters.Add(Param3)

mycmd.Parameters.Add(Param4)

CONN.Open()

mycmd.ExecuteNonQuery()

CONN.Close()

CONN = Nothing