[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?
Re: Dataset and Sql Stored Proc return value
How are you calling them?
-tg
1 Attachment(s)
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.
Attachment 83937
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.
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.
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
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.
Re: Dataset and Sql Stored Proc return value
Quote:
Originally Posted by
GaryMazzone
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
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.