sql stored proc returns integer, how do I get access to that in ASP.NET

[Aptiva Dave]

Here is a snippet of my stored proc:

            Insert into users
	(
		firstName,
		middleName,
		lastName,
		sex,
		entryDate,
		modifyDate,
		modifyUser,
		locID,
		deptID,
		contactID,
		authenticationID,
		userLevelID
	)
	Values
	(
		@firstName,
		@middleName,
		@lastName,
		@sex,
		getDate(),
		getDate(),
		@modifyUser,
		@locID,
		@deptID,
		@contactID,
		@authenticationID,
		@userLevelID
	);
set @userID = Scope\_Identity();

return @userID;	

This runs fine and inserts my users, but in my code I have this:

        conn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@firstName", Server.HtmlEncode(txtFirstName.Text.Trim))
        cmd.Parameters.AddWithValue("@middleName", Server.HtmlEncode(txtMiddleName.Text.Trim))
        cmd.Parameters.AddWithValue("@lastName", Server.HtmlEncode(txtLastName.Text.Trim))
        cmd.Parameters.AddWithValue("@sex", dropSex.SelectedValue.ToString)
        cmd.Parameters.AddWithValue("@locID", "1")
        cmd.Parameters.AddWithValue("@userLevelID", "10")
        cmd.Parameters.AddWithValue("@deptID", "1")
        cmd.Parameters.AddWithValue("@hash", strHash)
        cmd.Parameters.AddWithValue("@salt", strSalt)
        cmd.Parameters.AddWithValue("@ssn", txtSSN.Text.Trim)
        cmd.Parameters.AddWithValue("@street", Server.HtmlEncode(txtStreet.Text.Trim))
        cmd.Parameters.AddWithValue("@city", Server.HtmlEncode(txtCity.Text.Trim))
        cmd.Parameters.AddWithValue("@stateID", dropStates.SelectedValue.ToString)
        cmd.Parameters.AddWithValue("@zip", txtZip.Text.Trim)
        If txtPhone.Text.Length > 0 Then
            cmd.Parameters.AddWithValue("@phone", txtPhone.Text.Trim)
        Else
            cmd.Parameters.AddWithValue("@phone", DBNull.Value)
        End If
        If txtCell.Text.Trim.Length > 0 Then
            cmd.Parameters.AddWithValue("@cell", txtCell.Text.Trim)
        Else
            cmd.Parameters.AddWithValue("@cell", DBNull.Value)
        End If
        If txtPhone2.Text.Trim.Length > 0 Then
            cmd.Parameters.AddWithValue("@phone2", txtPhone2.Text.Trim)
        Else
            cmd.Parameters.AddWithValue("@phone2", DBNull.Value)
        End If
        cmd.Parameters.AddWithValue("@userName", txtUserName.Text.Trim)
        c

[Wendelius]

Hi, ExecuteScalar returns the first column from the first row in the result set. The userID isn't a result set but a return value. So you have to define one more parameter for the return value. For this parameter define the direction as ParameterDirection.Input. For examples see: http://support.microsoft.com/default.aspx?scid=kb;en-us;308049[^] Side note: If your procedure isn't actually returning a result set you could use ExecuteNonQuery instead of ExecuteScalar for performance reasons.

The need to optimize rises from a bad design.My articles[^]

[Mycroft Holmes]

Change this line

set @userID = Scope_Identity();

to

set @userID = Scope_Identity();
Select @UserID as UserID

As Mika suggested you are not returning a result dataset and @UserID is not an out parameter so the value is being ethered.

Never underestimate the power of human stupidity RAH