[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Get a value for webpage, returned from stored procedure

Hi all, I have a web page and I am trying to call a stored proc and have it return a value of 0 or 1 to me.  I am having a problem getting this to work, can someone please tell me what I am doing wrong on this...  Please see attached.

Thanks for any and all help
Here is my code-behind that is calling the storedproc, but not sure what needs to be modified to receive a value back...
 
		Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("WebConnectionString").ConnectionString)
                    Dim cmd As New SqlCommand("Approved_User", conn)
 
                    cmd.Parameters.Add(New SqlParameter("@Login", SqlDbType.VarChar, 20))
                    cmd.Parameters("@Login").Value = hdnUser.Value
 
                    'questionable area....
                    Dim paramReturnValue As New SqlParameter()
                    paramReturnValue.ParameterName = "@return_value"
                    paramReturnValue.SqlDbType = SqlDbType.Int
                    paramReturnValue.Direction = ParameterDirection.ReturnValue
 
                    conn.Open()
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandTimeout = 300
                    cmd.ExecuteNonQuery()
 
 
                    cmd.Dispose()
                    conn.Close()
                    conn.Dispose()
                    'Return
                End Using
 
Here is my SQL, again not sure what I need to change to get it to send a value back once it has been called, I need something like the following, I am trying to see if a person is on a list of approved users and return a 1 if they are or a 0 if they are not.
ALTER PROCEDURE [dbo].[ApprovedUser]
(
	@Login varchar(50)
)
AS
DECLARE @rc Int   
    
SELECT distinct Security_User.USERID               
FROM Security_User 
	inner join UserBranch on Security_User.UserID = UserBranch.UserID 
	inner join UserInfo on Security_User.UserID = UserInfo.UserID 
	inner join Security_User_Role on Security_User.UserID = Security_User_Role.UserID
WHERE Login = @Login and IsActive = 1 and (BranchRoleCode in (Select distinct BranchRoleCode from BranchRole)
    or IsGlobalOverrider = 1 or RoleID = 1)
        
IF @@RowCount > 0 
 BEGIN
   SELECT @rc = 1
   RETURN @rc
 END
ELSE
 BEGIN
   SELECT @rc = 0
   RETURN @rc
 END

Open in new window

0
ratkinso666
Asked:
ratkinso666
  • 5
  • 4
1 Solution
 
justin-clarkeCommented:
Instead of.....

                    Dim paramReturnValue As New SqlParameter()
                    paramReturnValue.ParameterName = "@return_value"
                    paramReturnValue.SqlDbType = SqlDbType.Int
                    paramReturnValue.Direction = ParameterDirection.ReturnValue


Do....

cmd.Parameters.Add("@returnValue", SqlDbType.Int)
cmd.Parameters("@ReturnValue").Direction = ParameterDirection.Output



Then in your SQL parameter syntax do...

@Login varchar(50),
@ReturnValue int OUTPUT


To set the return value in the main SQL do...

SET @ReturnValue = 1
0
 
justin-clarkeCommented:
Don't know if the case of this line in my example makes any difference...

cmd.Parameters.Add("@returnValue", SqlDbType.Int)

The @returnValue can be replaced with @Returnvalue

Thanks.
0
 
justin-clarkeCommented:
The @returnValue can be replaced with @ReturnValue

!!!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ratkinso666Author Commented:
Great Justin, that works great, can you tell me one more thing, if I needed it to return the UserID value in the select statement instead of the 1 or 0, like I thought, what would I need to change to get that value instead??
Set @ReturnValue = ???

Thank you VERY much!!
0
 
ratkinso666Author Commented:
Is this the best way to call the value in the code-behind??  It works, just wanted to make sure it was the best way to call it...

Dim returnValue2 As Integer = CInt(cmd.Parameters("@ReturnValue").Value)

0
 
justin-clarkeCommented:
Hi,

Sorry for the late reply...

If you want a different data type passed back you simply change the type...

cmd.Parameters.Add("@ReturnedUserID", SqlDbType.NVarChar)
cmd.Parameters("@ReturnedUserID").Direction = ParameterDirection.Output

Then in SQL just do...
@ReturnedUserID NVARCHAR(10) OUTPUT


And for your second question that is the best way to call the value.

Thanks.
0
 
ratkinso666Author Commented:
Justin, so if I don't do the set statement, then it will get the value from the select statement??
0
 
justin-clarkeCommented:
In your SQL instead of...

IF @@RowCount > 0
 BEGIN
   SELECT @rc = 1
   RETURN @rc
 END
ELSE
 BEGIN
   SELECT @rc = 0
   RETURN @rc
 END

You should be doing...

IF @@RowCount > 0
 BEGIN
   SET @ReturnedValue = 1
 END
ELSE
 BEGIN
   SET @ReturnedValue = 0
 END

You don't need the "SELECT" bit unless you are running the SP from Management Studio and want to view the Result. But in that instance I would use the "PRINT" command instead.
0
 
ratkinso666Author Commented:
Great!!! Thank you very much Justin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now