got me puzzled - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

got me puzzled

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-27 : 10:08:44

hi,

please don't shoot me as this question likely belongs in the asp.net section.

i have just created a sproc which returns an id integer value. i have fully tested it in the query analyser and it works fine. however, when i call the sproc from within my asp.net page and try to store the id value in an asp.net integer variable, the value is different to what i am getting when i run it in the sql native environment. i keep getting a zero. i thought it might have been a conversion issue but it is defined as an integer in the sproc and in the .net page.

any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-27 : 10:10:40

There is a .NET forum on SQL Team.

Post your code. Did you know you can't get a return value (or OUTPUT parameter) unless you close the returned recordset? (sounds backward to me but that's the deal.)

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-27 : 10:17:15

I am a little unsure what you mean but here is my PROC, and below is the call I used:

CREATE PROCEDURE uspAddFreeMember1

@rank tinyint ,
@business_name varchar(50) ,
@address varchar(75) ,
@city varchar(10) = null,
@state varchar(3) ,
@suburb_town varchar(30) ,
@postcode char(4) ,
@phone varchar(10) ,
@fax varchar(10) = null,
@tot_emps varchar(6) ,
@internet varchar(10) ,
@email_address varchar(55) ,
@pword varchar(25)

AS

DECLARE
@Result int

-- Check that email doesn't already exist

IF EXISTS(select * from Login where email_address = @email_address)
BEGIN
SET @Result = 0
RETURN @Result
END

ELSE

BEGIN TRANSACTION TransPage1

BEGIN

-- Insert basic Business details

INSERT INTO Business(
rank,
business_name,
address,
city,
state,
suburb_town,
postcode,
phone,
fax,
tot_emps,
internet
)
VALUES
(
@rank,
@business_name,
@address,
@city,
@state,
@suburb_town,
@postcode,
@phone,
@fax,
@tot_emps,
@internet
)

-- Insert Login Information

INSERT INTO Login(
email_address,
pword
)
VALUES
(
@email_address,
@pword
)

-- Insert LoginBusiness IDs into Lookup Table

INSERT INTO LoginBusiness(
login_id,
business_id
)

VALUES(IDENT_CURRENT('Business'), IDENT_CURRENT('Login'))
SET @Result = IDENT_CURRENT('Business')

IF @@error != 0
BEGIN
ROLLBACK TRANSACTION TransPage1
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION TransPage1
END
Return @Result
END

GO

-------------------------------------

Declare @return_result As int
EXEC @return_result = uspAddFreeMember1 'value1', 'value2', 'value3'...etc etc
PRINT @return_result

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-27 : 10:51:58

ok this is definitely an asp.net issue. it is a conundrum because my proc inserts data and also returns a value. the problem is that if i use asp.net's executenonquery method it only allows the number of rows affected to be returned, not the result value that i want, i will continue this post in the asp.net section if anybody has a work-around

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-27 : 12:09:21

Yes. I was hoping for the .NET code, not the sproc.

See you in the .NET forum.

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-28 : 10:18:59

You don't want to be use ExecuteNonQuery as this will only return the number of rows affected. You want to use ExecuteScalar which returns the single value.

i.e.

Sql = "SELECT COUNT(*) FROM MyTable WHERE Name='Tim'";

Command.CommandText = Sql;
int NumberOfTims = (int)Command.ExecuteScalar();

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-29 : 07:19:06

quote:
Originally posted by ts_abbott@hotmail.com

You don't want to be use ExecuteNonQuery as this will only return the number of rows affected.

This is not the issue...

You need to define a return value parameter, something like:

SqlParameter paramRet = cmd.Parameters.Add("@status", SqlDbType.Int );
					paramRet.Direction = ParameterDirection.ReturnValue;

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-29 : 23:48:59

thanks Ehorn, I've solved the problem, and the method you suggested was correct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources