Kevin
Fri Nov 21 01:50:25 CST 2003
Maybe it is the way I am setting up the parameters:
public SqlCommand InitializeCommand
(SqlCommand cmd, string type, long startPage, long
rowsOnPage, ref SqlParameter returnValue, ref
SqlParameter rowCount)
{
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Parameters.Add
("@User_Transaction_Type", SqlDbType.VarChar, 10);
cmd.Parameters
["@User_Transaction_Type"].Value = type;
cmd.Parameters.Add("@Alias_ID",
SqlDbType.BigInt);
cmd.Parameters["@Alias_ID"].Value
= this.AliasId;
cmd.Parameters.Add
("@Tran_DateTime", SqlDbType.VarChar, 20);
cmd.Parameters
["@Tran_DateTime"].Value = this.TransactionDate.ToString
("yyyy-MM-dd hh:mm:ss");
cmd.Parameters.Add
("@Rows_On_Page", SqlDbType.SmallInt);
cmd.Parameters
["@Rows_On_Page"].Value = rowsOnPage;
cmd.Parameters.Add("@Page",
SqlDbType.Int);
cmd.Parameters["@Page"].Value =
startPage;
rowCount = cmd.Parameters.Add
("@Row_Count", SqlDbType.Int);
cmd.Parameters
["@Row_Count"].Direction = ParameterDirection.InputOutput;
if(startPage == 0)
{
cmd.Parameters
["@Row_Count"].Value = 0;
}
else
{
cmd.Parameters
["@Row_Count"].Value = 1;
}
returnValue = cmd.Parameters.Add
("@ReturnValue", SqlDbType.Int);
cmd.Parameters
["@ReturnValue"].Direction =
ParameterDirection.ReturnValue;
cmd.CommandText
= "pphtran_get_trans";
return cmd;
}
I have tried this stored procedure with the Query
Analyzer and it works just fine. I have included a simple
test script to give you an idea of the calling procedure.
declare @RowCount int,
@alias_id int,
@tran_datetime varchar(20),
@Row_On_Page int,
@Page int
SET @RowCount = 0
SET @Row_On_Page = 10
SET @Page = 1
SET @tran_datetime = convert(varchar
(20),current_timestamp,120)
SET @alias_id = 307
SET @RowCount = 0
EXEC dbo.pphtran_get_trans 'Posted', @alias_id,
@tran_datetime, @Row_On_Page, @Page, @RowCount OUTPUT
print @RowCount
Thanks again.
Kevin
>-----Original Message-----
>See my article on this subject at www.betav.com\articles
(it's one of the
>MSDN articles).
>
>--
>____________________________________
>William (Bill) Vaughn
>Author, Mentor, Consultant
>MVP, hRD
>www.betav.com
>Please reply only to the newsgroup so that others can
benefit.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>__________________________________
>
>"Kevin Burton" <anonymous@discussions.microsoft.com>
wrote in message
>news:2b8101c3afb7$14697f40$a601280a@phx.gbl...
>> I have a stored procedure and I set up the command type
>> to be StoredProcedure with the return value and the
>> output parameter set to InputOutput. This
StoredProcedure
>> also returns a set of rows. From the best that I can
tell
>> if I use ExecuteReader() the first time it returns the
>> OUTPUT parameter and it takes a second time to actually
>> get the data. Is this the default behavior or is there
>> some special setup to get this to return the data AND
the
>> OUTPUT parameter in one call to ExecuteReader()?
>>
>> Thank you for your suggestions.
>>
>> Kevin
>> rkevinburtn@charter.net
>
>
>.
>