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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

RecordCount = (int)reccount.V alue;
//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.Command Type = CommandType.Sto redProcedure;

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.Direct ion = ParameterDirect ion.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

--
MY ASP.Net tutorials
http://www.openmymind.net/

"Patreek" <do**@spam.me > wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[Elton W]

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.V alue;
HTH

Elton Wang
"Patreek" wrote:

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

RecordCount = (int)reccount.V alue;
//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" <do**@spam.me > wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[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

"Elton W" <El****@discuss ions.microsoft. com> wrote in message
news:AC******** *************** ***********@mic rosoft.com...

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.V alue;
HTH

Elton Wang
"Patreek" wrote:

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[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(objec t sender, System.EventArg s e)
{
SqlDataReader ret;
SqlConnection objConn = new SqlConnection(c lsData.GetConnS tr());
SqlCommand objCmd = new SqlCommand("Tes tProc", objConn);
objCmd.CommandT ype = CommandType.Sto redProcedure;
SqlParameter testParam = objCmd.Paramete rs.Add("@TestOu tput",
SqlDbType.VarCh ar, 50);
testParam.Direc tion = ParameterDirect ion.Output;
objConn.Open();
ret = objCmd.ExecuteR eader(CommandBe havior.CloseCon nection);

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

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:O4******** ******@TK2MSFTN GP14.phx.gbl...

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.Command Type = CommandType.Sto redProcedure;

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.Direct ion = ParameterDirect ion.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

--
MY ASP.Net tutorials
http://www.openmymind.net/

"Patreek" <do**@spam.me > wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[Patreek]

"Bruce Barker" <br************ ******@safeco.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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.

--
Pat

[Elton W]

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" wrote:

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

"Elton W" <El****@discuss ions.microsoft. com> wrote in message
news:AC******** *************** ***********@mic rosoft.com...

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.V alue;
HTH

Elton Wang
"Patreek" wrote:

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(c lsData.GetConnS tr()))
{
SqlCommand objCmd = new SqlCommand("sea rch", objConn);
objCmd .CommandType = CommandType.Sto redProcedure;
//some input parameters defined here

SqlParameter reccount= objCmd.Paramete rs.Add("@reccou nt",
SqlDbType.Int);
reccount.Direct ion = ParameterDirect ion.Output;

objConn.Open();
ret = oCmd.ExecuteRea der(CommandBeha vior.CloseConne ction);

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

[Patreek]

"Elton W" <El****@discuss ions.microsoft. com> wrote in message
news:FC******** *************** ***********@mic rosoft.com...

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.