Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Getting Return Value From Stored Proceedure.

Reply
Thread Tools

Getting Return Value From Stored Proceedure.

 
 
Mick Walker
Guest
Posts: n/a
 
      06-20-2007
Hi Everyone,

I am stumped here. I have the following stored proceedure

CREATE PROCEDURE [dbo].[ImportLinesProductExists]
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where [SupplierSKUCode] = @SupplierSKU
if @Retval > 0
BEGIN
Return 0
END
-- It wasn't found so we can now return -1
Return -1

Which works perfectly when I execute it with SQL Server Management Studio.

I call the stored proceedure with the following code:

Public Sub CheckProduct(ByVal _ConnString As String, ByVal
supplierSKUCode as Integer)
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"

With cmd.Parameters
.AddWithValue("@SupplierSKU", supplierSKUCode)
.AddWithValue("@RetVal", System.DBNull.Value)
End With
Try
conn.Open()
ReturnValue = cmd.ExecuteScalar()
Catch ex As SqlException
Throw ex
Finally
conn.Close()
cmd.Parameters.Clear()
End Try
If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
End Sub

But no matter what, the sub always returns 0, even when I manually add a
entry which should conflict, into the database.

Does anyone know whay this is happening?

Kind Regards
Mick Walker
 
Reply With Quote
 
 
 
 
=?Utf-8?B?U2l2YSBN?=
Guest
Posts: n/a
 
      06-20-2007
You have to set the parameter direction to ParameterDirection.ReturnValue
explicitly for the return parameter..

"Mick Walker" wrote:

> Hi Everyone,
>
> I am stumped here. I have the following stored proceedure
>
> CREATE PROCEDURE [dbo].[ImportLinesProductExists]
> @SupplierSKU varchar(50),
> @RetVal int
> AS
> Select @Retval = count(*) from dbo.ImportLines
> Where [SupplierSKUCode] = @SupplierSKU
> if @Retval > 0
> BEGIN
> Return 0
> END
> -- It wasn't found so we can now return -1
> Return -1
>
> Which works perfectly when I execute it with SQL Server Management Studio.
>
> I call the stored proceedure with the following code:
>
> Public Sub CheckProduct(ByVal _ConnString As String, ByVal
> supplierSKUCode as Integer)
> 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"
>
> With cmd.Parameters
> .AddWithValue("@SupplierSKU", supplierSKUCode)
> .AddWithValue("@RetVal", System.DBNull.Value)
> End With
> Try
> conn.Open()
> ReturnValue = cmd.ExecuteScalar()
> Catch ex As SqlException
> Throw ex
> Finally
> conn.Close()
> cmd.Parameters.Clear()
> End Try
> If ReturnValue = 0 Then
> InsertTempProducts(_ConnString, Item)
> End If
> End Sub
>
> But no matter what, the sub always returns 0, even when I manually add a
> entry which should conflict, into the database.
>
> Does anyone know whay this is happening?
>
> Kind Regards
> Mick Walker
>

 
Reply With Quote
 
 
 
 
Hans Kesting
Guest
Posts: n/a
 
      06-20-2007
> Hi Everyone,
>
> I am stumped here. I have the following stored proceedure
>
> CREATE PROCEDURE [dbo].[ImportLinesProductExists]
> @SupplierSKU varchar(50),
> @RetVal int
> AS
> Select @Retval = count(*) from dbo.ImportLines
> Where [SupplierSKUCode] = @SupplierSKU
> if @Retval > 0
> BEGIN
> Return 0
> END
> Which works perfectly when I execute it with SQL Server Management
> Studio.
>
> I call the stored proceedure with the following code:
>
> Public Sub CheckProduct(ByVal _ConnString As String, ByVal
> supplierSKUCode as Integer)
> 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"
> With cmd.Parameters
> .AddWithValue("@SupplierSKU", supplierSKUCode)
> .AddWithValue("@RetVal", System.DBNull.Value)


as mentioned before, you need to set the Direction to ParameterDirection.ReturnValue

> End With
> Try
> conn.Open()
> ReturnValue = cmd.ExecuteScalar()


you don't need ExecuteScalar here: that will return the first value in the
first column
in the first table returned, and you don't return any tables at all.
An ExecuteNonQuery should do.

> Catch ex As SqlException
> Throw ex
> Finally
> conn.Close()
> cmd.Parameters.Clear()


before you clear the parameters, read the value of @RetVal - this is the
value you want.

> End Try
> If ReturnValue = 0 Then
> InsertTempProducts(_ConnString, Item)
> End If
> End Sub
> But no matter what, the sub always returns 0, even when I manually add
> a entry which should conflict, into the database.
>
> Does anyone know whay this is happening?
>
> Kind Regards
> Mick Walke



 
Reply With Quote
 
bruce barker
Guest
Posts: n/a
 
      06-20-2007
you are confused.

executescaler returns the first column value of the first row of the
first resultset. your proc does not return a resultset, so it should be
throwing an error. you should be calling ExecuteNonquery().

after processing the resultsets (or call ExecuteNonquery which does
this) you can access output parameters (though you need to set the
parameter direction to output before making the query).

to get the actual return value of a proc, you add an int parameter with
the direction of ReturnValue. after all resultsets have been returned,
you can accesses this parameter's value to get the return value.


-- bruce (sqlwork.com)





Mick Walker wrote:
> Hi Everyone,
>
> I am stumped here. I have the following stored proceedure
>
> CREATE PROCEDURE [dbo].[ImportLinesProductExists]
> @SupplierSKU varchar(50),
> @RetVal int
> AS
> Select @Retval = count(*) from dbo.ImportLines
> Where [SupplierSKUCode] = @SupplierSKU
> if @Retval > 0
> BEGIN
> Return 0
> END
> -- It wasn't found so we can now return -1
> Return -1
>
> Which works perfectly when I execute it with SQL Server Management Studio.
>
> I call the stored proceedure with the following code:
>
> Public Sub CheckProduct(ByVal _ConnString As String, ByVal
> supplierSKUCode as Integer)
> 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"
>
> With cmd.Parameters
> .AddWithValue("@SupplierSKU", supplierSKUCode)
> .AddWithValue("@RetVal", System.DBNull.Value)
> End With
> Try
> conn.Open()
> ReturnValue = cmd.ExecuteScalar()
> Catch ex As SqlException
> Throw ex
> Finally
> conn.Close()
> cmd.Parameters.Clear()
> End Try
> If ReturnValue = 0 Then
> InsertTempProducts(_ConnString, Item)
> End If
> End Sub
>
> But no matter what, the sub always returns 0, even when I manually add a
> entry which should conflict, into the database.
>
> Does anyone know whay this is happening?
>
> Kind Regards
> Mick Walker

 
Reply With Quote
 
Lloyd Sheen
Guest
Posts: n/a
 
      06-20-2007

"Mick Walker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Everyone,
>
> I am stumped here. I have the following stored proceedure
>
> CREATE PROCEDURE [dbo].[ImportLinesProductExists]
> @SupplierSKU varchar(50),
> @RetVal int
> AS
> Select @Retval = count(*) from dbo.ImportLines
> Where [SupplierSKUCode] = @SupplierSKU
> if @Retval > 0
> BEGIN
> Return 0
> END
> -- It wasn't found so we can now return -1
> Return -1
>
> Which works perfectly when I execute it with SQL Server Management Studio.
>
> I call the stored proceedure with the following code:
>
> Public Sub CheckProduct(ByVal _ConnString As String, ByVal
> supplierSKUCode as Integer)
> 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"
>
> With cmd.Parameters
> .AddWithValue("@SupplierSKU", supplierSKUCode)
> .AddWithValue("@RetVal", System.DBNull.Value)
> End With
> Try
> conn.Open()
> ReturnValue = cmd.ExecuteScalar()
> Catch ex As SqlException
> Throw ex
> Finally
> conn.Close()
> cmd.Parameters.Clear()
> End Try
> If ReturnValue = 0 Then
> InsertTempProducts(_ConnString, Item)
> End If
> End Sub
>
> But no matter what, the sub always returns 0, even when I manually add a
> entry which should conflict, into the database.
>
> Does anyone know whay this is happening?
>
> Kind Regards
> Mick Walker


This looks like a function rather than a procedure. Make this a Stored
Function and return the count as the return value for the function.

LS

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Return Value From Stored Proccedure (Part 2) Mick Walker ASP .Net 2 06-20-2007 11:05 PM
Getting ID, calling url, search for value, return value Tim Fröglich ASP .Net Web Services 1 01-10-2006 09:18 PM
what value does lack of return or empty "return;" return Greenhorn C Programming 15 03-06-2005 08:19 PM
getting return value from function without return statement. Seong-Kook Shin C Programming 1 06-18-2004 08:19 AM
Getting Return Value of Stored Procedure Vipul Pathak ASP General 3 07-30-2003 01:51 PM



Advertisments