Can't get my output parameter since it's not an object

[Patreek]

Hi,

On the line where I'm assigning RecordCount to be the value of my output
parameter, I'm getting the generic "Object reference not set to an instance
of an object" error. I've isolated it down to this line, as the line of
code commented out just beneath it runs fine. Can anyone see why my
parameter isn't an object? When I grab the command from SQL profiler and
run it in Query Analyzer, I get my output parameter returned with a non-null
value.

Thank you

using (SqlConnection objConn = new SqlConnection(clsData.GetConnStr()))
{
SqlCommand objCmd = new SqlCommand("search", objConn);
objCmd .CommandType = CommandType.StoredProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Parameters.Add("@reccount",
SqlDbType.Int);
reccount.Direction = ParameterDirection.Output;

objConn.Open();
ret = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

RecordCount = (int)reccount.Value;
//RecordCount = 5; //no error with that
//etc.
}

 

[Karl Seguin]

Patreek:
normally null reference error are pretty easy to spot, but this one has me a
little baffled (though I'm still sure it's something basic).

Normally it would mean that if you do something like:

SqlCommand command;
command.CommandType = CommandType.StoredProcedure;

you would get an error because you are never creating an instance (via new
in this case) of SqlCommand such as SqlCommand command = new SqlCommand();

However, it seems unlikely that reccount is null on the line that you
suspect, because a couple lines up from it, you are successfully accessing
it via reccount.Direction = ParameterDirection.Output;


If you've typed thsis out exactly, I'd point out that at one place you use
objCmd and at the other you've used oCmd

I can tell you that other than this, I've taken your code and made it work
perfectly. Perhaps you could debug and step through your code to double
check the exact location of the error.

Karl

 

[Guest]

Hi Patreek,

Since your oCmd to execute a datareasder, only after the datareader closing,
you can get output paramter value. Try

ret.Close();
RecordCount = (int)reccount.Value;


HTH

Elton Wang

 

[Patreek]

I don't really understand why, but that worked. Thank you Elton. Of
course, this creates a new problem as I'm trying to return a SqlDataReader
from my function, but if I have to close it to get the output parameter
value, that renders the datareader pretty useless.

Is the purpose of .Net to make it impossible to actually separate out your
data class? It sure seems that way.

Thanks,

Patreek

 

[Bruce Barker]

sql returns output parameters and the return value after all result sets
have been sent back to the client. as you are using a reader, you need to
read all rows and results sets before you can access them.

try the following:

using (SqlConnection objConn = new SqlConnection(clsData.GetConnStr()))
{
SqlCommand objCmd = new SqlCommand("search", objConn);
objCmd .CommandType = CommandType.StoredProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Parameters.Add("@reccount",
SqlDbType.Int);
reccount.Direction = ParameterDirection.Output;

objConn.Open();
ret = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

do
{
while (ret.Read())
;
} while (ret.NextResult())

RecordCount = (int)reccount.Value;
//RecordCount = 5; //no error with that
//etc.
}

i realize you expected to get the count before processing the rows, but if
you think about it, it makes sense. sqlserver does not know the count until
its sent all the rows back to the client. if you really need the count
first, then you need two result sets in your search proc, the first one
should return the count, the second one the search rows.

-- bruce (sqlwork.com)

 

[Patreek]

Hi Karl,

I tried to trim up the code before posting it. Here is some test code that
I'm using that does work, however.

private void Page_Load(object sender, System.EventArgs e)
{
SqlDataReader ret;
SqlConnection objConn = new SqlConnection(clsData.GetConnStr());
SqlCommand objCmd = new SqlCommand("TestProc", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
SqlParameter testParam = objCmd.Parameters.Add("@TestOutput",
SqlDbType.VarChar, 50);
testParam.Direction = ParameterDirection.Output;
objConn.Open();
ret = objCmd.ExecuteReader(CommandBehavior.CloseConnection);

ret.Close();

Response.Write(testParam.Value.ToString());
objConn.Close();
}


And the proc:

CREATE PROC TestProc (@TestOutput varchar(50) output) AS
SELECT 'whatever'
select @TestOutput = 'the output value'
GO

The problem with this is that I have to CLOSE the datareader to get the
output parameter. That's not helpful since I was trying to return the
datareader from my function so that I could bind it to a dropdown in a box.
Why am I having such a hard time trying to do this "create a separate data
layer" thing? If I just stuck the database code in my codebehind files
instead of trying this data class stuff, all this would work fine. But I'm
trying to do things the "right" way. Trying.

Thanks

 

[Patreek]

sql returns output parameters and the return value after all result sets

i realize you expected to get the count before processing the rows, but if
you think about it, it makes sense. sqlserver does not know the count
until its sent all the rows back to the client. if you really need the
count first, then you need two result sets in your search proc, the first
one should return the count, the second one the search rows.

Hi Bruce,

If I were using a recordcount property of the datareader object (I'm
assuming there is one), I'd expect not to be able to get the recordcount
until after the rows are read, perhaps. But that's not really what I'm
doing. I'm just trying to select an output parameter value. That value
just so happens to be called "reccount" in this case, but it could be
something totally unrelated to the data. Example:

CREATE PROC TestProc (@TestOutput varchar(50) output) AS
SELECT 'whatever'
select @TestOutput = 'the output value'
GO

I can't get the value of the output parameter there until after I close the
reader. I don't really understand why that is.

 

[Guest]

Hi Patreek,

While the SqlDataReader is in use, the associated SqlConnection is busy
serving the SqlDataReader, and no other operations can be performed on the
SqlConnection other than closing it. This is the case until the Close method
of the SqlDataReader is called. For example, you cannot retrieve output
parameters until after you call Close.

In order to get count before getting actual records, you can either execute
query Select Count(*) From table_name, first then run actual query to a
datareader, or you can use dataadapter to fill query result to a datatable.
So datatable.Rows.Count gives you the count of records.

HTH

Elton

 

[Patreek]

Hi Patreek,

While the SqlDataReader is in use, the associated SqlConnection is busy
serving the SqlDataReader, and no other operations can be performed on the
SqlConnection other than closing it. This is the case until the Close
method
of the SqlDataReader is called. For example, you cannot retrieve output
parameters until after you call Close.

That explains it. Thanks! I don't like that I can't get to the output
parameters, but it is what it is.

In order to get count before getting actual records, you can either
execute
query Select Count(*) From table_name, first then run actual query to a
datareader, or you can use dataadapter to fill query result to a
datatable.
So datatable.Rows.Count gives you the count of records.

Getting the count isn't the issue, but thank you anyway.