-
May 17th, 2011, 03:56 PM
#1
[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?
-
May 17th, 2011, 04:10 PM
#2
Re: Dataset and Sql Stored Proc return value
How are you calling them?
-tg
-
May 18th, 2011, 08:58 AM
#3
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.
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.
-
May 18th, 2011, 09:15 AM
#4
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
-
May 18th, 2011, 11:24 AM
#5
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
-
May 18th, 2011, 03:27 PM
#6
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 03: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
-
May 18th, 2011, 04:03 PM
#7
Re: Dataset and Sql Stored Proc return value
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.
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|