RowCount is returning null

i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.

SP Code

ALTER

PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]

@.assignmentPrimaryKey

int,

@.rowCount

intOUTPUT

AS

BEGIN

SETNOCOUNTON;SELECT employeePrimaryKeyFROM assignmentTableWHERE primaryKey= @.assignmentPrimaryKey;SET @.rowCount=@.@.RowCount;

END

c# code

SqlConnection

conn = GetOpenSqlConnection();if (conn ==null) returntrue;SqlDataReader reader =null;SqlParameter p1 =newSqlParameter(); SqlParameter p2 =newSqlParameter();try{SqlCommand command =newSqlCommand();

command.CommandText =

"RetrieveEmployeeKeyFromAssignmentTable";

command.CommandType =

CommandType.StoredProcedure;

command.Connection = conn;

p1.ParameterName =

"@.assignmentPrimaryKey";

p1.Value = assignmentPrimaryKey;

p2.ParameterName =

"@.rowCount";

p2.Direction =

ParameterDirection.Output;

p2.Value = 0;

command.Parameters.Add(p1); command.Parameters.Add(p2);

reader = command.ExecuteReader();

if (p2.Value ==null)//always true

any suggestions would be appreciated.

thanks. matt

also, reader.HasRows is true.

matt

|||

Hello,

first, I was suspicious of your code that you assigned the value of output parameter p2.value=0.

but it was O.K., and I was wrong , I tested with query analyzer.

I looked carefully in your code, find that you called with ExecuteReader ==> it is connected data object.

you have to close the Reader object before you try to get Output or else paremeter values.

verified with internet search...

|||

EXCELLENT.

thank you very much.

matt

Round() function doesn't seem to work properly in SQL 2k5

Hi,
I have been using the round function in SQL 2000 for quite a while, and when doing sums of percentages have used the Round(99.99,0) function to great avail to get a value of 100.00.

But, when I migrated to SQL 2K5, i used the same function and it gave me an arithmetic overflow error; more specifically:
"An error occurred while executing batch. Error message is: Arithmetic Overflow."

Is anyone aware of any bugs / changes to this function in 2k5 that I am not aware of?

Thanks,
Saurabh

You might also try using CEILING() in conjuction with ROUND(). But be careful if you do...

|||

Yeah, ceiling will work for 99.99, but for the likes of 100.01, then floor is better.

Anyway, does anyone know why the round doesnt work properly in sql 2k5?

|||

I'm not aware of any changes in the underlaying functionality of ROUND().

Could it be that something about the data is causing the overflow?

|||

Hmm...not sure

It was just a simple select round(99.99,-2) that yielded 100.00 in sql 2k and arithmetic overflow error in 2k5

|||

You got me on that one. I definitely see that the results are the same for SLQ 2000 and SQL 2005.

Hopefully, someone from MS will chime in and let us know what is happening...

|||That seems to be a problem with implicit casting.

This works:
select round(CAST(99.99 AS DECIMAL(10,2)),0)

This errors with "An error occurred while executing batch. Error message is: Arithmetic Overflow":

select round(99.99,0)

It looks like a bug to me.

Running 2005 9.0.2153. Anyone running SP2 to see if this fails under it also?|||

Cool, mate!

That works.

I am running 2005 9.0.2047 at the moment, and have that error.