Please help.....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M. Ali Qureshi - 11 Dec 2007 10:32 GMT

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.

Reply to this Message

Mick Walker - 11 Dec 2007 11:19 GMT

> Hi,
>
[quoted text clipped - 46 lines]
>
> 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

Reply to this Message

Jeff Dillon - 11 Dec 2007 18:32 GMT

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

> Hi,
>
[quoted text clipped - 45 lines]
>
> Thanks alot.

Reply to this Message

Please help.....

Free Magazines