TableAdapter returns null for stored proc returning a single value

Hi,

I have a stored procedure that returns a single value. Example:
[...]
SELECT @RowCount = COUNT(*) FROM t WHERE [...]
RETURN @RowCount

I created a data set, table adapter, and adapter's method configured for the
stored proc and as returning a single value. The wizard created an adapter
method that calls SqlCommand.ExecuteScalar(). The problem is that
ExecuteScalar() always returns a null object. I have no problem executing
the stored procedure from outside of asp.net.

Could someone please let me give me some idea what could be wrong here?

Thanks,
Bogdan

Re: TableAdapter returns null for stored proc returning a single value

Have you set the SqlCommand.CommandType to StoredProcedure?

"bogdan" wrote in message
news:%23vIBPKYmIHA.1208@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I have a stored procedure that returns a single value. Example:
> [...]
> SELECT @RowCount = COUNT(*) FROM t WHERE [...]
> RETURN @RowCount
>
> I created a data set, table adapter, and adapter's method configured for
> the stored proc and as returning a single value. The wizard created an
> adapter method that calls SqlCommand.ExecuteScalar(). The problem is that
> ExecuteScalar() always returns a null object. I have no problem executing
> the stored procedure from outside of asp.net.
>
> Could someone please let me give me some idea what could be wrong here?
>
> Thanks,
> Bogdan
>
>

Re: TableAdapter returns null for stored proc returning a single value

I used TableAdapter Qeury Configuration Wizard and selected "Use existing
stored procedure".

"Waldy" wrote in message
news:OL5XhSYmIHA.3780@TK2MSFTNGP06.phx.gbl...
> Have you set the SqlCommand.CommandType to StoredProcedure?
>
> "bogdan" wrote in message
> news:%23vIBPKYmIHA.1208@TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> I have a stored procedure that returns a single value. Example:
>> [...]
>> SELECT @RowCount = COUNT(*) FROM t WHERE [...]
>> RETURN @RowCount
>>
>> I created a data set, table adapter, and adapter's method configured for
>> the stored proc and as returning a single value. The wizard created an
>> adapter method that calls SqlCommand.ExecuteScalar(). The problem is
>> that ExecuteScalar() always returns a null object. I have no problem
>> executing the stored procedure from outside of asp.net.
>>
>> Could someone please let me give me some idea what could be wrong here?
>>
>> Thanks,
>> Bogdan
>>
>>
>
>

RE: TableAdapter returns null for stored proc returning a single value

executescaler returns the first column, of the first row, of the first
resultset. your stored proc does not return any rows, only a return value. to
get the return value, use ExecuteNonQuery with a returnvalue parameter

cmd.CommandText = "myproc";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@returnValue",SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
int rows = (int) cmd.Parameters("@returnValue").Value;


-- bruce (sqlwork.com)


"bogdan" wrote:

> Hi,
>
> I have a stored procedure that returns a single value. Example:
> [...]
> SELECT @RowCount = COUNT(*) FROM t WHERE [...]
> RETURN @RowCount
>
> I created a data set, table adapter, and adapter's method configured for the
> stored proc and as returning a single value. The wizard created an adapter
> method that calls SqlCommand.ExecuteScalar(). The problem is that
> ExecuteScalar() always returns a null object. I have no problem executing
> the stored procedure from outside of asp.net.
>
> Could someone please let me give me some idea what could be wrong here?
>
> Thanks,
> Bogdan
>
>
>