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.Valu e),
new SqlParameter("@ Title", ddl_title.Selec tedValue),
new SqlParameter("@ Name", txt_Name.Text),
new SqlParameter("@ Address", txt_Address.Tex t),
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.Sel ectedValue),
new SqlParameter("@ Email", txt_Email.Text) ,
new SqlParameter("@ UserName", txt_UserName.Te xt),
new SqlParameter("@ Password", txt_Password.Te xt) };
try
{
int x =
SqlHelper.Execu teNonQuery(conn String,CommandT ype.StoredProce dure,"wsi_Users _Insert",collec tion);
if (x != 0)
{
userid = int.Parse(colle ction[0].Value.ToString ());
return true;
}
else
return false;
}
catch (SqlException) { return false; }

and here is the procedure
------------------------------
CREATE PROCEDURE dbo.wsi_Users_I nsert
(

@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.Directio n = ParameterDirect ion.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("ReturnValu e", SqlDbType.Int)
parmReturn.Dire ction = ParameterDirect ion.ReturnValue
Chip

"Islamegy®" <Is******@Priva te.4me> wrote in message
news:et******** ******@TK2MSFTN GP02.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.Valu e),
new SqlParameter("@ Title", ddl_title.Selec tedValue),
new SqlParameter("@ Name", txt_Name.Text),
new SqlParameter("@ Address", txt_Address.Tex t),
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.Sel ectedValue),
new SqlParameter("@ Email", txt_Email.Text) ,
new SqlParameter("@ UserName", txt_UserName.Te xt),
new SqlParameter("@ Password", txt_Password.Te xt) };
try
{
int x =
SqlHelper.Execu teNonQuery(conn String,CommandT ype.StoredProce dure,"wsi_Users _Insert",collec tion);
if (x != 0)
{
userid = int.Parse(colle ction[0].Value.ToString ());
return true;
}
else
return false;
}
catch (SqlException) { return false; }

and here is the procedure
------------------------------
CREATE PROCEDURE dbo.wsi_Users_I nsert
(

@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