By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

Getting Return Value From Stored Proccedure (Part 2)

P: n/a
Public Sub CheckProduct(ByVal _ConnString As String, ByVal ProductList
As List(Of Import_ImportLines.Lines))
Dim ReturnValue As Integer = 0 ' Our Return Value
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.ImportLinesProductExists"

Dim Item As New Import_ImportLines.Lines
For Each Item In ProductList
Dim param As New SqlParameter

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.Output
param = cmd.Parameters.Add("@SupplierSKU",
SqlDbType.VarChar, 50)
param.Value = Item.supplierSKUCode
param.Direction = ParameterDirection.Input

Try
conn.Open()
cmd.ExecuteNonQuery()
ReturnValue =
Convert.ToInt32(cmd.Parameters("@RetVal").Value)
Catch ex As SqlException
Throw ex
Finally
conn.Close()
End Try

If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
Next
conn.Close()
End Sub

Could anyone please tell me where I am going wrong with trying to get a
return value from a stored proccedure.

Regards
Jun 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
you are fetching the value of a parameter, not the return value of a
proc. for a proc parameter to return a value to the caller, the proc
must declare it as a output parameter as sql defaults to pass by value.

create procdure test1 @i1 int, i2 int
as
set @i1 = @i2;
return @i1;
create procdure test2 @i1 int out, i2 int
as
set @i1 = @i2;
return @i1;

.....

declare @r int, @i1 int;
exec @r = test1 @i,2; -- @r = 2 @i = null
exec @r = test2 @i,2; -- @r = 2 @i = null
exec @r = test2 @i out,2; -- @r = 2 @i = 2
-- bruce (sqlwork.com)

Mick Walker wrote:
Public Sub CheckProduct(ByVal _ConnString As String, ByVal ProductList
As List(Of Import_ImportLines.Lines))
Dim ReturnValue As Integer = 0 ' Our Return Value
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.ImportLinesProductExists"

Dim Item As New Import_ImportLines.Lines
For Each Item In ProductList
Dim param As New SqlParameter

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.Output
param = cmd.Parameters.Add("@SupplierSKU",
SqlDbType.VarChar, 50)
param.Value = Item.supplierSKUCode
param.Direction = ParameterDirection.Input

Try
conn.Open()
cmd.ExecuteNonQuery()
ReturnValue =
Convert.ToInt32(cmd.Parameters("@RetVal").Value)
Catch ex As SqlException
Throw ex
Finally
conn.Close()
End Try

If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
Next
conn.Close()
End Sub

Could anyone please tell me where I am going wrong with trying to get a
return value from a stored proccedure.

Regards
Jun 20 '07 #2

P: n/a
Hi Mick,

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.ReturnValue

Hope this helps
--
Milosz
"Mick Walker" wrote:
Public Sub CheckProduct(ByVal _ConnString As String, ByVal ProductList
As List(Of Import_ImportLines.Lines))
Dim ReturnValue As Integer = 0 ' Our Return Value
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.ImportLinesProductExists"

Dim Item As New Import_ImportLines.Lines
For Each Item In ProductList
Dim param As New SqlParameter

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.Output
param = cmd.Parameters.Add("@SupplierSKU",
SqlDbType.VarChar, 50)
param.Value = Item.supplierSKUCode
param.Direction = ParameterDirection.Input

Try
conn.Open()
cmd.ExecuteNonQuery()
ReturnValue =
Convert.ToInt32(cmd.Parameters("@RetVal").Value)
Catch ex As SqlException
Throw ex
Finally
conn.Close()
End Try

If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
Next
conn.Close()
End Sub

Could anyone please tell me where I am going wrong with trying to get a
return value from a stored proccedure.

Regards
Jun 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.