Wednesday, March 7, 2012

returning value from sql to c#

hello,
I have a small problem. i'm adding records into the DB. the primary keyis the company name which is abviously unique. before saving the recordi check in the stored procedure if the company code is unique or not.if unique then the record is added & an output parameter is set to2 & should b returned to the data access layer. if not unique then3 should be returned. but everytime it seems to be returning 2 whetherit is unique or not. can u plz help me? here is the code of the dataaccess layer:
cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Value = ParameterDirection.ReturnValue;

//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters;
cmd.ExecuteNonQuery();
status = (int)cmd.Parameters["@.Status"].Value;

here is the stored procedure:
CREATE PROCEDURE spOrganizationAdd(
@.OrgCode varchar(10),
@.OrgName varchar(50),
@.AddressLine1 varchar(30),
@.AddressLine2 varchar(30),
@.City varchar(15),
@.State varchar(15),
@.Country varchar(15),
@.PinCode varchar(7),
@.Phone varchar(20),
@.Fax varchar(20),
@.Website varchar(30),
@.Email varchar(50),
@.CreatedBy int,
@.LastModifiedBy int,
@.Status INTEGER OUTPUT) AS
BEGIN TRAN
IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @.OrgCode)
BEGIN
SET @.Status = 3

END
ELSE
BEGIN
INSERT INTO tblOrganizationMaster VALUES(
@.OrgCode,
@.OrgName,
@.AddressLine1 ,
@.AddressLine2 ,
@.City ,
@.State,
@.Country,
@.PinCode,
@.Phone,
@.Fax ,
@.Website,
@.Email,
@.CreatedBy ,
GETDATE(),
@.LastModifiedBy ,
GETDATE())
SET @.Status = 2
END
IF @.@.ERROR = 0 COMMIT TRAN
ELSE ROLLBACK TRAN

plz reply as soon as possible.

Change this:

cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Value = ParameterDirection.ReturnValue;

To this:

cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Direction = ParameterDirection.Output;

and then it should work.

Bill

No comments:

Post a Comment