ReturnValue not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All!
I'm calling a store proc in my vb.net code, but the value returned is
always 0, which is wrong. But when I runt the query in SQL, it's working
fine. My SP is
CREATE PROCEDURE [dbo].[max tableid] AS
SELECT MAX(TableID) AS returnvalue
FROM TableID
WHERE (Inuse =1)
GO
and my code is
cmdLog = New SqlCommand("Max TableID", conLog)
cmdLog.CommandType = CommandType.StoredProcedure
TableIDReturn = cmdLog.Parameters.Add("ReturnValue", SqlDbType.Int)
TableIDReturn.Direction = ParameterDirection.ReturnValue

conLog.Open()
cmdLog.ExecuteNonQuery()

intTableID = cmdLog.Parameters("ReturnValue").Value
txtTableid.Text = intTableID.ToString()
If intTableID <= 5 Then
myMainfrm.Show()
Else : intTableID = 0

MessageBox.Show("Sorry")
conLog.Close()
End If
I have the string in there to see what the value is returned.
Any thoughts what I'm doing wrong?

Thanks!!

Rudy
 
the ADO.NET Cookbook claims you should use a DataReader in such
situations, even if you aren't reading any records - google for it
 
Uri Dor said:
the ADO.NET Cookbook claims you should use a DataReader in such
situations, even if you aren't reading any records - google for it

Rudy wrote:
Click to expand...


What you should really do is rewrite your stored procedure to use an output
parameter instead of a one-row, one-column result set.

CREATE PROCEDURE max_tableid @id int output
AS
SELECT @id = MAX(TableID)
FROM TableID
WHERE Inuse =1


Then bind an output parameter, run ExecuteNonQuery and examine the value of
the output parameter. This is much more efficient than returning a result
set.

David
 
Hello all!
I tried all your sugesstions, but the same results. Here is more of the code
if this helps.

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLogin.Click
Dim conLog As SqlConnection
Dim cmdLog As SqlCommand
Dim TableIDReturn As SqlParameter
Dim intTableID As Integer
Dim myMainfrm As New Mainfrm
Dim myloginfrm As Loginfrm



conLog = New
SqlConnection("Server=localhost;uid=****;pwd=******;database=mydb")
cmdLog = New SqlCommand("Max TableID", conLog)
cmdLog.CommandType = CommandType.StoredProcedure
TableIDReturn = cmdLog.Parameters.Add("ReturnValue", SqlDbType.Int)
TableIDReturn.Direction = ParameterDirection.ReturnValue

conLog.Open()
cmdLog.ExecuteScalar()

intTableID = cmdLog.Parameters("ReturnValue").Value
txtTableid.Text = intTableID.ToString()
' If intTableID <= 5 Then
' myMainfrm.Show()
' Else : intTableID = 0

' MessageBox.Show("Sorry")
conLog.Close()
' End If
I guess I'm confused on a couple of things. I would think calling a SP, I
should get the same results when I call it in a program. I guess I don't
understand what the "return value" refers to? God knows what book or books I
took a snippet of code to do this. I usually have a good understanding what
the line of code does, but the "return value", I'm not sure what part that
is. A name of a couln, or just a reference name.
I think I'm goin to just try makeing the SQL statement in the code, and see
if it works that way.
Any other ideas would be great!

Thank you all for your time!

Rudy
 
And the code for the stored proc is ?

For now the code you show us assumes that you return the value using the
RETURN T-SQL statement and AFAIK the paramter should likely be named
@RETURN_VALUE

Patrice




--
 
Back
Top