TableAdapter returns null for stored proc returning a single value

  • Thread starter Thread starter bogdan
  • Start date Start date
B

bogdan

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
 
I used TableAdapter Qeury Configuration Wizard and selected "Use existing
stored procedure".

Waldy said:
Have you set the SqlCommand.CommandType to StoredProcedure?

bogdan said:
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
Click to expand...
Click to expand...
 
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)
 
Back
Top