Data Reader Now Rows

[Jim Heavey]

I am new to SqlServer, have been using Oracle in a prior life.

I have written a very simple stored procedure in SQL Server and it (the
query) looks like the following...

Select AD_ID, First_Name, Last_Name, User_Type, Phone_Nbr, Email_Addr
From dbo.User_Tbl
Where UPPER(AD_ID) = UPPER(@AD_ID)
and UPPER(User_Type) = UPPER(@User_Type)

I have placed (not shown) the @@ROWCOUNT into the procedure and when I debug
it in Visual Studio 2005, it shows a rowcount of 1, and this is how many rows
I was expecting. A Datareader is returned, but when I perform the While
(dr.Read()) it immedialtely falls out and does not read any records.

Any idea as to what I might be doing wrong such that no records are returned?

Thanks in advance for your assistance!!

[Leon Mayne]

I have placed (not shown) the @@ROWCOUNT into the procedure and when I debug

it in Visual Studio 2005, it shows a rowcount of 1, and this is how many rows
I was expecting. A Datareader is returned, but when I perform the While
(dr.Read()) it immedialtely falls out and does not read any records.

If executing the query in query analyser works fine then it's probably
something in your .NET code. Could you post the snippet which is failing?

[Jim Heavey]

Well I use a lot of general routines to prepare the sql statements and create
the data reader that it becomes a bit complex to show snipits...but here
goes...

Here is the code to create my parameters....
parms[0] = TblUtilites.GetParameter(TblUtilites.DataBaseType. SqlServer,
"@AD_ID", ParameterDirection.Input, parmValues[0],
TblUtilites.DataType.SqlDbTypeChar, 8);
parms[1] = TblUtilites.GetParameter(TblUtilites.DataBaseType. SqlServer,
"@User_Type", ParameterDirection.Input, parmValues[1],
TblUtilites.DataType.SqlDbTypeVarChar, 50);
parms[2] = TblUtilites.GetParameter(TblUtilites.DataBaseType. SqlServer,
"@Active_Inactive_All", ParameterDirection.Input, parmValues[2],
TblUtilites.DataType.SqlDbTypeChar, 1);

Here is the code to create my Command.. (conMgr is a class I have created
which hold my connection, my command, the Data reader...)
try
{conMgr.Sql_Cmd = new SqlCommand(qryNameOrQryString, conMgr.Sql_Con ); }
catch (Exception e)
{ conMgr.Error_Message = e.Message; }
Here is the code to create the data reader (Again, conMgr holds the command
and data reader)

conMgr.Data_Reader = (IDataReader)conMgr.Sql_Cmd.ExecuteReader();

Here is the code to read the data reader... (My count is always zero)

IDataReader dr = cm.Data_Reader;
int cnt = 0;

if (dr != null)
{
while (dr.Read())
{ cnt++; }
dr.Close();
}
if (cnt 0)
{ return true; }
else
{ return false; } // always returns false

[Leon Mayne]

Well I use a lot of general routines to prepare the sql statements and
create

the data reader that it becomes a bit complex to show snipits...but here
goes...

I can't really tell what's going on because of your helper class. I suspect
the parameters arn't being sent across correctly. Have you tried running SQL
Profiler while executing the code to see what is actually being sent to SQL
Server?

[Jim Heavey]

Have never run the profiler...Is there such a thing with Sql Express with the
Server Management Studio installed?

[Leon Mayne]

Have never run the profiler...Is there such a thing with Sql Express with
the

Server Management Studio installed?

Hmm, I don't think it comes with SQL Express. Do you have SQL Server
Management Studio Express installed or the full SQL Server Management Studio?

[Jim Heavey]

I do not have the full SQL server Management studio installed although I have
a MSDN subscription which I believe provides me that. I read that it does not
come with the version which is freely downloaded from the web.

I changed my stored procedure to write the values passed into the stored
procedure to a temporary database and I discoved what the problem was. I had
changed the code from passing an in-stream SQL statement to a stored
procedure and forgot that I no longer needed to enclose my string variables
in quotes. Silly mistake, but I just could not see it...so obvious that it
could knock you over but...

Thanks for your assistance!!