Please help.....

[M. Ali Qureshi]

Hi,

I have following stored procedure that should return @UserID.

=======================
CREATE PROCEDURE [dbo].[CreateUserProfile]
@UserType int,
@BusinessStatus int,
@BusinessBranch int,
@BusinessForm int,
@BusinessType int,
@TotalEmployees nvarchar(50),
@TurnOver nvarchar(50)

AS
BEGIN
declare @UserID as int

SET NOCOUNT ON;

INSERT into Users (UserTypeID, BusinessStatusID, BusinessBranchID,
BusinessFormID, BusinessTypeID, TotalEmployees, TurnOver, Status)
values (@UserType, @BusinessStatus, @BusinessBranch, @BusinessForm,
@BusinessType, @TotalEmployees, @TurnOver, 0)
SELECT @UserID = scope_identity()
END
========================

I added the stored procedure in the table adapter, and called it through a
function in BLL class file.

When i run the stored procedure through an aspx page, i get only "0" value
back. Here is the code from aspx page.

======================
If Not HasError Then
Dim UserAdapter As New DBAccessBLL
Dim UserID As Integer
UserID = UserAdapter.InsertUserProfile(UserType.SelectedValue,
BusinessStatus.SelectedValue, BusinessBranch.SelectedValue,
BusinessForm.SelectedValue, BusinessType.SelectedValue, _Employees,
_TurnOver)
Response.Redirect("Start.aspx?u=" & UserID.ToString())
End If
=======================

What am i doing wrong? Could anyone please help me please?

Thanks alot.

 

[Mick Walker]

Hi,

I have following stored procedure that should return @UserID.

=======================
CREATE PROCEDURE [dbo].[CreateUserProfile]
@UserType int,
@BusinessStatus int,
@BusinessBranch int,
@BusinessForm int,
@BusinessType int,
@TotalEmployees nvarchar(50),
@TurnOver nvarchar(50)

AS
BEGIN
declare @UserID as int

SET NOCOUNT ON;

INSERT into Users (UserTypeID, BusinessStatusID, BusinessBranchID,
BusinessFormID, BusinessTypeID, TotalEmployees, TurnOver, Status)
values (@UserType, @BusinessStatus, @BusinessBranch, @BusinessForm,
@BusinessType, @TotalEmployees, @TurnOver, 0)
SELECT @UserID = scope_identity()
END
========================

I added the stored procedure in the table adapter, and called it through
a function in BLL class file.

When i run the stored procedure through an aspx page, i get only "0"
value back. Here is the code from aspx page.

======================
If Not HasError Then
Dim UserAdapter As New DBAccessBLL
Dim UserID As Integer
UserID =
UserAdapter.InsertUserProfile(UserType.SelectedValue,
BusinessStatus.SelectedValue, BusinessBranch.SelectedValue,
BusinessForm.SelectedValue, BusinessType.SelectedValue, _Employees,
_TurnOver)
Response.Redirect("Start.aspx?u=" & UserID.ToString())
End If
=======================

What am i doing wrong? Could anyone please help me please?

Thanks alot.

Ok,

I have no idea of your database layout, so I will create a imaginary
table (People) to show my point.

The table is as follows

ID int
FirstName varchar(100)
Surname varchar(100)

I create a stored proceedure called GetUserCount which consists of the
following:

BEGIN
Declare @Count int
Select @Count = count(*) from dbo.People
Return @Count

END

Thats my stored proceedure done, now for my code:

C#

int ReturnValue;

SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlParameter Param;

Param = cmd.Parameters.Add("@Count", SqlDbType.Int);
Param.Direction = ParameterDirection.ReturnValue;

conn.ConnectionString = _ConnString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.GetUserCount";
try {
conn.Open();
cmd.ExecuteNonQuery();
ReturnValue =
Convert.ToInt32(cmd.Parameters["@Count"].Value);
}
catch (Exception ex) {
throw new InvalidDataException(ex.Message);
}
finally {
conn.Close();
cmd.Dispose();
}

VB.NET

Dim ReturnValue As Integer

Dim conn As New SqlConnection()
Dim cmd As New SqlCommand()
Dim Param As SqlParameter

Param = cmd.Parameters.Add("@Count", SqlDbType.Int)
Param.Direction = ParameterDirection.ReturnValue

conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.GetUserCount"
Try
conn.Open()
cmd.ExecuteNonQuery()
ReturnValue = Convert.ToInt32(cmd.Parameters("@Count").Value)
Catch ex As Exception
Throw New InvalidDataException(ex.Message)
Finally
conn.Close()
cmd.Dispose()
End Try

In both cases, the variable ReturnValue will contain the return value
from the stored proceedure.

I hope this helps,

Regards
Mick Walker

 

[Jeff Dillon]

You are only putting scope_identity() into a variable, but not returning the
variable.

Either use an OUT param for the variable, or simply

SELECT @UserID

Sample code to demonstrate:


declare @test int

select @test = 5

select @test