SQL Output Paramter problem

[Islamegy®]

When i call stored procedure which have an output paramter, t'm unable to
get the value
How could i fix it??

here is the method....
----------------------------
userid = 0;
SqlParameter[] collection = new SqlParameter[]
{
new SqlParameter("@ID",DBNull.Value),
new SqlParameter("@Title", ddl_title.SelectedValue),
new SqlParameter("@Name", txt_Name.Text),
new SqlParameter("@Address", txt_Address.Text),
new SqlParameter("@Tel1", txt_Tel.Text),
new SqlParameter("@Tel2", txt_AltTel.Text),
new SqlParameter("@Fax", txt_Fax.Text),
new SqlParameter("@Mobile", txt_Mobile.Text),
new SqlParameter("@City", txt_City.Text),
new SqlParameter("@Country", ddl_Country.SelectedValue),
new SqlParameter("@Email", txt_Email.Text),
new SqlParameter("@UserName", txt_UserName.Text),
new SqlParameter("@Password", txt_Password.Text) };
try
{
int x =
SqlHelper.ExecuteNonQuery(connString,CommandType.S toredProcedure,"wsi_Users_Insert",collection);
if (x != 0)
{
userid = int.Parse(collection[0].Value.ToString());
return true;
}
else
return false;
}
catch (SqlException) { return false; }

and here is the procedure
------------------------------
CREATE PROCEDURE dbo.wsi_Users_Insert
(

@ID int OUTPUT,

@Title varchar (10) ,

@Name varchar (100) ,

@Address varchar (500) ,

@Tel1 varchar (15) ,

@Tel2 varchar (15) ,

@Fax varchar (15) ,

@Mobile varchar (15) ,

@City varchar (50) ,

@Country int ,

@Email varchar (50) ,

@UserName varchar (50) ,

@Password varchar (50)
)
AS

INSERT INTO dbo.[Users]
(
[Title]
,[Name]
,[Address]
,[Tel1]
,[Tel2]
,[Fax]
,[Mobile]
,[City]
,[Country]
,[email]
,[UserName]
,[Password]
)
VALUES
(
@Title
,@Name
,@Address
,@Tel1
,@Tel2
,@Fax
,@Mobile
,@City
,@Country
,@Email
,@UserName
,@Password
)

-- Get the identity value
SET @ID = SCOPE_IDENTITY()
GO

[Chip]

You need to set the sqlparameter to output as well. Not sure how to do it
using your syntax, but this will work

dim parmID as sqlparameter
parmID = cmd.Parameters.Add("@ID", SqlDbType.Int)
parmID.Direction = ParameterDirection.Output

Then retrieve the value with parmID.value.

Your could also use the SQL return value to pass an integet back. In your
proc:

RETURN @@identity -- as the exit line in your proc

then in .NET
parmReturn = cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
parmReturn.Direction = ParameterDirection.ReturnValue
Chip

"Islamegy®" <Is******@Private.4me> wrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...

When i call stored procedure which have an output paramter, t'm unable to
get the value
How could i fix it??

here is the method....
----------------------------
userid = 0;
SqlParameter[] collection = new SqlParameter[]
{
new SqlParameter("@ID",DBNull.Value),
new SqlParameter("@Title", ddl_title.SelectedValue),
new SqlParameter("@Name", txt_Name.Text),
new SqlParameter("@Address", txt_Address.Text),
new SqlParameter("@Tel1", txt_Tel.Text),
new SqlParameter("@Tel2", txt_AltTel.Text),
new SqlParameter("@Fax", txt_Fax.Text),
new SqlParameter("@Mobile", txt_Mobile.Text),
new SqlParameter("@City", txt_City.Text),
new SqlParameter("@Country", ddl_Country.SelectedValue),
new SqlParameter("@Email", txt_Email.Text),
new SqlParameter("@UserName", txt_UserName.Text),
new SqlParameter("@Password", txt_Password.Text) };
try
{
int x =
SqlHelper.ExecuteNonQuery(connString,CommandType.S toredProcedure,"wsi_Users_Insert",collection);
if (x != 0)
{
userid = int.Parse(collection[0].Value.ToString());
return true;
}
else
return false;
}
catch (SqlException) { return false; }

and here is the procedure
------------------------------
CREATE PROCEDURE dbo.wsi_Users_Insert
(

@ID int OUTPUT,

@Title varchar (10) ,

@Name varchar (100) ,

@Address varchar (500) ,

@Tel1 varchar (15) ,

@Tel2 varchar (15) ,

@Fax varchar (15) ,

@Mobile varchar (15) ,

@City varchar (50) ,

@Country int ,

@Email varchar (50) ,

@UserName varchar (50) ,

@Password varchar (50)
)
AS

INSERT INTO dbo.[Users]
(
[Title]
,[Name]
,[Address]
,[Tel1]
,[Tel2]
,[Fax]
,[Mobile]
,[City]
,[Country]
,[email]
,[UserName]
,[Password]
)
VALUES
(
@Title
,@Name
,@Address
,@Tel1
,@Tel2
,@Fax
,@Mobile
,@City
,@Country
,@Email
,@UserName
,@Password
)

-- Get the identity value
SET @ID = SCOPE_IDENTITY()
GO