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.Connection String = "workstatio n id=SBGBHO;packe t size=4096;user id=sa;data source=SBGBHO;p ersist security info=True;initi al catalog=hsData; password=hardik "

Dim mycmd As New SqlCommand

mycmd.Connectio n = CONN

mycmd.CommandTe xt = "strproc"

mycmd.CommandTy pe = CommandType.Sto redProcedure

Dim Param1 As New SqlClient.SqlPa rameter("@dbase name", SqlDbType.Char, DbName)

Dim Param2 As New SqlClient.SqlPa rameter("@tblna me", SqlDbType.Char, tblName)

Dim Param3 As New SqlClient.SqlPa rameter("@dbase _id", dbid)

Dim Param4 As New SqlClient.SqlPa rameter("@table _id", tbid)

Param1.Directio n = ParameterDirect ion.Input

Param2.Directio n = ParameterDirect ion.Input

Param3.Directio n = ParameterDirect ion.Output

Param4.Directio n = ParameterDirect ion.Output

mycmd.Parameter s.Add(Param1)

mycmd.Parameter s.Add(Param2)

mycmd.Parameter s.Add(Param3)

mycmd.Parameter s.Add(Param4)

CONN.Open()

mycmd.ExecuteNo nQuery()

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/de...turnvalues.asp

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

"Hardik Shah" <ha********@hot mail.com> wrote in message
news:e%******** ********@TK2MSF TNGP09.phx.gbl. ..
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.Connection String = "workstatio n id=SBGBHO;packe t size=4096;user
id=sa;data source=SBGBHO;p ersist security info=True;initi al
catalog=hsData; password=hardik "
Dim mycmd As New SqlCommand
mycmd.Connectio n = CONN
mycmd.CommandTe xt = "strproc"
mycmd.CommandTy pe = CommandType.Sto redProcedure
Dim Param1 As New SqlClient.SqlPa rameter("@dbase name", SqlDbType.Char,
DbName)
Dim Param2 As New SqlClient.SqlPa rameter("@tblna me", SqlDbType.Char,
tblName)
Dim Param3 As New SqlClient.SqlPa rameter("@dbase _id", dbid)
Dim Param4 As New SqlClient.SqlPa rameter("@table _id", tbid)
Param1.Directio n = ParameterDirect ion.Input
Param2.Directio n = ParameterDirect ion.Input
Param3.Directio n = ParameterDirect ion.Output
Param4.Directio n = ParameterDirect ion.Output
mycmd.Parameter s.Add(Param1)
mycmd.Parameter s.Add(Param2)
mycmd.Parameter s.Add(Param3)
mycmd.Parameter s.Add(Param4)
CONN.Open()
mycmd.ExecuteNo nQuery()
CONN.Close()
CONN = Nothing

[ECathell]

dbid=param3.val ue
tbid=param4.val ue

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

With cmd

..Connection = con

..CommandTimeou t = 120

..CommandType = CommandType.Sto redProcedure

..CommandText = "br_updateboxwe ights"

..Parameters.Ad d("@loadno", SqlDbType.Int)

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

End With
--
--Eric Cathell, MCSA
"Hardik Shah" <ha********@hot mail.com> wrote in message news:e%******** ********@TK2MSF TNGP09.phx.gbl. ..
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.Connection String = "workstatio n id=SBGBHO;packe t size=4096;user id=sa;data source=SBGBHO;p ersist security info=True;initi al catalog=hsData; password=hardik "

Dim mycmd As New SqlCommand

mycmd.Connectio n = CONN

mycmd.CommandTe xt = "strproc"

mycmd.CommandTy pe = CommandType.Sto redProcedure

Dim Param1 As New SqlClient.SqlPa rameter("@dbase name", SqlDbType.Char, DbName)

Dim Param2 As New SqlClient.SqlPa rameter("@tblna me", SqlDbType.Char, tblName)

Dim Param3 As New SqlClient.SqlPa rameter("@dbase _id", dbid)

Dim Param4 As New SqlClient.SqlPa rameter("@table _id", tbid)

Param1.Directio n = ParameterDirect ion.Input

Param2.Directio n = ParameterDirect ion.Input

Param3.Directio n = ParameterDirect ion.Output

Param4.Directio n = ParameterDirect ion.Output

mycmd.Parameter s.Add(Param1)

mycmd.Parameter s.Add(Param2)

mycmd.Parameter s.Add(Param3)

mycmd.Parameter s.Add(Param4)

CONN.Open()

mycmd.ExecuteNo nQuery()

CONN.Close()

CONN = Nothing