Hi,

I have a stored procedure that returns a single value. Example:
[...]
SELECT [at] RowCount = COUNT(*) FROM t WHERE [...]
RETURN [at] 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

Have you set the SqlCommand.CommandType to StoredProcedure?

"bogdan" <bogdan [at] company.com> wrote in message
news:%23vIBPKYmIHA.1208 [at] TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I have a stored procedure that returns a single value. Example:
> [...]
> SELECT [at] RowCount = COUNT(*) FROM t WHERE [...]
> RETURN [at] 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
>
>

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

"Waldy" <someone [at] microsoft.com> wrote in message
news:OL5XhSYmIHA.3780 [at] TK2MSFTNGP06.phx.gbl...
> Have you set the SqlCommand.CommandType to StoredProcedure?
>
> "bogdan" <bogdan [at] company.com> wrote in message
> news:%23vIBPKYmIHA.1208 [at] TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> I have a stored procedure that returns a single value. Example:
>> [...]
>> SELECT [at] RowCount = COUNT(*) FROM t WHERE [...]
>> RETURN [at] 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
>>
>>
>
>

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(" [at] returnValue",SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
int rows = (int) cmd.Parameters(" [at] returnValue").Value;


-- bruce (sqlwork.com)


"bogdan" wrote:

> Hi,
>
> I have a stored procedure that returns a single value. Example:
> [...]
> SELECT [at] RowCount = COUNT(*) FROM t WHERE [...]
> RETURN [at] 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
>
>
>