trouble with stored procedure retun values 1

[jordanking]

hello,

I have a asp.net 3.5 website that has a sql server 2005 backend. I have a stored procedure that accepts a couple of parameters and returns an integer value. The procedure runs fine when executed from SQL management studio, but I can not capture the return values on the asp side. The following is mey procedure with asp code.

SQL Stored Procedure

ALTER PROCEDURE [dbo].[EFFU_Validate_Membership] 
    @FirstName		nvarchar(50),
    @LastName		nvarchar(50),
    @IAFF			int,
	@Validated		int		OUTPUT
AS
BEGIN

    -- Insert statements for procedure here
	SELECT     intIAFF, chrFirst, chrLast, blnActive
	FROM         Member
	WHERE     (intIAFF = @IAFF) AND (chrFirst = @FirstName) AND (chrLast = @LastName) 
	IF @@ROWCOUNT = 1 SELECT @Validated = 1
	ELSE SELECT @Validated = 0

	RETURN @Validated
	
END

Asp Code that runs the procedure

    Protected Sub btn_SignUp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_SignUp.Click
        Dim intIAFF As Integer
        Dim chrFirst As String
        Dim chrLast As String
        Dim intValidated As Integer
        Dim iRet As Integer

        ''VALIDATE USER INPUT
        If IsNumeric(txtIAFF.Text) = True Then
            intIAFF = Convert.ToInt32(txtIAFF.Text)
        End If

        If txtFirst.Text = String.Empty Then
            lblError.Text = "Please Enter a First Name"
            Exit Sub
        Else
            chrFirst = txtFirst.ToString.ToLower
        End If

        If txtLast.Text = String.Empty Then
            lblError.Text = "Please Enter a Last Name"
            Exit Sub
        Else
            chrLast = txtLast.ToString.ToLower
        End If

        ''CHECK SQL FOR MEMBERSHIP VALIDATION AND SET SESSION VARIABLES
        ''define and open connection referencing web.config for connection string
        Dim cs As String = ConfigurationManager.ConnectionStrings("EFFU_Main").ConnectionString
        Using con As New System.Data.SqlClient.SqlConnection(cs)

            con.Open()

            ''define the command object
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = con
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "EFFU_Validate_Membership"

            ''define and populate the parameters
            cmd.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar, 50)
            cmd.Parameters("@FirstName").Value = chrFirst
            cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar, 50)
            cmd.Parameters("@LastName").Value = chrLast
            cmd.Parameters.Add("@IAFF", Data.SqlDbType.Int)
            cmd.Parameters("@IAFF").Value = intIAFF
            cmd.Parameters.Add("@Validated", Data.SqlDbType.Int)
            cmd.Parameters("@Validated").Direction = Data.ParameterDirection.Output

            ''read results of stored procedure;
            cmd.ExecuteNonQuery()
            intValidated = CInt(cmd.Parameters("@Validated").Value)

            ''close connection
            con.Close()

        End Using

        ''REDIRECT TO APPRORIATE PAGE 
        If intValidated = 1 Then
            lblError.Text = "User Validated"
        Else
            lblError.Text = "User Not Validated"
        End If

    End Sub

I can not get the procedure's return values. Can anyone help me debug this?

thanks in advance

.....
I'd rather be surfing

 

[jmeckley]

ExecuteReader will return a result set (even if you are returning 1 row
ExecuteNonQuery is used for CUD commands (create, update, delete)
ExecuteScalar is used when the result returned is a single row and a single column.


it's the return keyword in the proc that is throwing off the executenonquery method. I would avoid this keyword in your procs and stick with common sql commands. with little refactoring of the procs and your code you can do this

ALTER PROCEDURE [dbo].[EFFU_Validate_Membership]
    @FirstName        nvarchar(50),
    @LastName        nvarchar(50),
    @IAFF            int
AS
BEGIN
SELECT     count(1)
FROM       Member
WHERE      (intIAFF = @IAFF)
 AND       (chrFirst = @FirstName)
 AND       (chrLast = @LastName)

var count = (int)cmd.ExecuteScalar();
if(count == 0)
{
}
else
{
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jordanking]

thanks for the quick reply jason,

I adjusted your code as follows:

ALTER PROCEDURE [dbo].[EFFU_Validate_Membership]    
	@FirstName       nvarchar(50),    
	@LastName        nvarchar(50),    
	@IAFF            int
AS

BEGIN
	SELECT     Count(1)
	FROM       dbo.Member
	WHERE     (dbo.Member.intIAFF = @IAFF) 
	AND       (dbo.Member.chrFirst = @FirstName) 
	AND       (dbo.Member.chrLast = @LastName)	
END

... user validation ...
        Dim cs As String = ConfigurationManager.ConnectionStrings("EFFU_Main").ConnectionString
        Using con As New System.Data.SqlClient.SqlConnection(cs)

            con.Open()

            ''define the command object
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = con
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "EFFU_Validate_Membership"

            ''define and populate the parameters
            cmd.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar, 50)
            cmd.Parameters("@FirstName").Value = chrFirst
            cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar, 50)
            cmd.Parameters("@LastName").Value = chrLast
            cmd.Parameters.Add("@IAFF", Data.SqlDbType.Int)
            cmd.Parameters("@IAFF").Value = intIAFF

            ''read results of stored procedure;
            iRet = CInt(cmd.ExecuteScalar())

            ''close connection
            con.Close()

        End Using

        ''REDIRECT TO APPRORIATE PAGE 
        If iRet = 1 Then
            lblError.Text = "User Validated"
        Else
            lblError.Text = "User Not Validated"
        End If

but i am still not getting any value other than zero from the procedure regardless of input values. Again, the procedure works are you supplied from SQL managment studio

.....
I'd rather be surfing

 

[jmeckley]

try removing the @ symbol when defining the parameters in code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jbenson001]

Remove this line from your SP and try again:

RETURN @Validated

If you specify a return paramter, .NET will handle it differently. What confuses most people is that OUTPUT parameters and RETURN values are 2 different things.

 

[jordanking]

i tried removing the "@" but that did not work.

Also, i tried running the page from the remote server instead of from my local testing server (through visual studio) and the problem is the same.

This is a bit strange. I assume the procedure is running because no errors are thrown, but its like the procedure is not getting the right values.

.....
I'd rather be surfing

 

[jordanking]

okay,

I found the problem, jason helped me to confirm the stored procedure was working and recieving values. This made me think that maybe I needed to rexamine the user input verification and that is where I found the problem.

I had to change this assignment of a variable:
from

chrFirst = txtFirst.ToString.ToLower

to this

chrFirst = txtFirst.Text.ToString.ToLower

so everything works,

thanks

.....
I'd rather be surfing

 

[jmeckley]

yup, that would do it. txtFirst.ToString would return the full name of the type. in this case TextBox.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190