[RESOLVED] Dataset and Sql Stored Proc return value-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] Dataset and Sql Stored Proc return value

  1. #1

    Thread Starter
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    3,817

    Resolved [RESOLVED] Dataset and Sql Stored Proc return value

    I'm working on an app that will use a few stored procedures in the Sql Server 2008 database. I've created the stored procedures and they all return either a 1 if they succeed or a -1 if they fail & Management studio displays the values returned just fine when I run them. On the vb.net side, I've added the Dataset to the project and the several tables I've added work fine, but when it comes to the stored procedures (yes, I've selected the one that says there's a return value) when I call it in code it's working but I'm not getting the return value. Any ideas?

  2. #2
    PowerPoster
    Join Date
    May 2002
    Posts
    25,753

    Re: Dataset and Sql Stored Proc return value

    How are you calling them?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    3,817

    Re: Dataset and Sql Stored Proc return value

    Well, here's the Stored Proc code:
    Code:
    ALTER PROCEDURE [dbo].[CreateNewUser] @UserName varchar(16),
    	@Password VarChar(50),	@FirstName varchar(15), @LastName varchar(15),
    	@PhoneNumber varchar(20), @PrimaryEmail varchar(110)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @Return int
    	DECLARE @PWD Varbinary(50) = CONVERT(varbinary(50), @Password)
    
        -- Insert statements for procedure here
    	IF NOT Exists (SELECT 1 FROM EventUsers WHERE UserName = @UserName AND UserPass = @PWD)
    		BEGIN
    			INSERT INTO EventUsers (UserName, UserPass, FirstName, LastName, PhoneNumber, PrimaryEmail, Active)
    			VALUES (@UserName, @PWD, @FirstName, @LastName, @PhoneNumber, @PrimaryEmail, 1)
    			SET @Return = 1
    		END
    	ELSE
    		BEGIN
    			SET @Return = -1
    		END
    	
    	RETURN @Return
    END
    And here's a screenshot of the DataSet designer, adding the stored proc was easy, it found all of the param's and their data types, then it asked me if it was a stored proc that returns a value, so I selected yes.
    Name:  StoredProcs.jpg
Views: 566
Size:  46.8 KB

    Then I added the needed controls to a page and added the code to the RegisterButton and here's the code I have, it will successfully run the stored procedure & the method does return an object, which I know is an integer:
    Code:
        Private Sub RegisterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegisterButton.Click
            If Password1TextBox.Text.Trim = Password2TextBox.Text.Trim Then
                Dim DA As New DataLayerTableAdapters.StoredProcsTableAdapter
                Try
                    'ToDo: Fix this!!
                    Dim ReturnInt As Integer = CInt(DA.CreateNewUser(UserNameTextBox.Text.Trim, Password1TextBox.Text.Trim, FirstNameTextBox.Text.Trim, LastNameTextBox.Text.Trim, _
                                              PhoneTextBox.Text.Trim, EmailTextBox.Text.Trim))
                    If CType(ReturnInt, StoredProcReturn) = StoredProcReturn.Success Then
                        RegisterLabel.Text = "Successful!"
                    Else
                        RegisterLabel.Text = "Couldn't create user!"
                    End If
                Catch ex As Exception
                    RegisterLabel.Text = ex.ToString
                Finally
                    DA.Dispose()
                    RegisterLabel.Visible = True
                End Try
            Else
                RegisterLabel.Text = "Passwords don't match!"
                RegisterLabel.Visible = True
            End If
        End Sub
    I just don't get why the object, when converted to an integer is 0 (zero), it should be either 1 or -1.

  4. #4
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    6,900

    Re: Dataset and Sql Stored Proc return value

    For giggles try and change the RETURN @Return in the stored proc to Select @Return and see what you get.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    PowerPoster
    Join Date
    May 2002
    Posts
    25,753

    Re: Dataset and Sql Stored Proc return value

    What does intellisence tell you that CreateNewUser returns... Also normally, the convention is 1=success 0=failure ... or 0=success and !0 is failure... but that's probably immaterial at the moment.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,902

    Re: Dataset and Sql Stored Proc return value

    You need to create a parameter to get the return value, similar to an output parameter:
    Code:
    Using myConnection As New SqlClient.SqlConnection("server=...;database=...;trusted_connection=true")
        myConnection.Open()
        Using myCommand As New SqlClient.SqlCommand("TestReturn", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            'configure return parm
            Dim returnParm As New SqlClient.SqlParameter("@returnValue", SqlDbType.Int)
            returnParm.Direction = ParameterDirection.ReturnValue
            myCommand.Parameters.Add(returnParm)
            'execute proc
            myCommand.ExecuteNonQuery()
            'get return value
            Dim returnValue = CInt(myCommand.Parameters("@returnValue").Value)
        End Using
    End Using
    Edit: The number you are getting is probably the number of rows updated. Since you have nocount set on, I assume this would always be zero for you.
    Last edited by wild_bill; May 18th, 2011 at 04:31 PM.
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  7. #7

    Thread Starter
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    3,817

    Re: Dataset and Sql Stored Proc return value

    Quote Originally Posted by GaryMazzone View Post
    For giggles try and change the RETURN @Return in the stored proc to Select @Return and see what you get.
    That actually did the trick, I was reading How to retrieve stored procedure return values from TableAdapter - Smart Client Data - Site Home - MSDN Blogs and it was suggesting I add a new Partial Class and adding a new function to call after running the stored proc, but if simply selecting the return value in the stored proc that's a whole lot easier to maintain.

    Quote Originally Posted by techgnome View Post
    What does intellisence tell you that CreateNewUser returns... Also normally, the convention is 1=success 0=failure ... or 0=success and !0 is failure... but that's probably immaterial at the moment.

    -tg
    I might not have caught something like this if I'd used zero as a return value so I was wanting to avoid that & I figure a positive number would be good for a success value and a negative for a failure value, but in the vb code I have an enum that I cast the object to (after converting to an int) and I just check it against the 'Success' or 'Fail' of the enum anyways.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.