How to pass dataReader object

13 Apr 2005 8:52 PM

Mori

I am trying to pass a datareader object and then iterate through the reader
in a client application. I get the following exception "Invalid attempt to
read when no data is present."

Question: What is the correct way to pass a dataReader? Any Good examples
please. thanks

public SqlDataReader GetCust(string CustId)
{
    try
    {
        if (sqlConn.State == ConnectionState.Closed)
            sqlConn.Open();
        SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
(@CustId)", this.sqlConn);
        sqlCmd.CommandType = CommandType.Text;
        SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
        p0.Value = CustId;

        SqlDataReader sqlRead =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
        return sqlRead;
    }
    catch(Exception exep)
    {
        Console.WriteLine("An error occurred " + exep.Message.ToString());
        return null;
    }
}

//client
static void Main(string[] args)
{
    UserDefinedFunction.UDFSampler udf = new UDFSampler();
    SqlDataReader sRead =   udf.GetCust("xyz");
    while (sRead.Read())
    {
        //do something with record
    }
    sRead.Close();

}

14 Apr 2005 12:06 AM

Val Mazur (MVP)

Hi,

You have an incorrect SQL statement. What are you trying to achieve with
this SQL statement? Following syntax is not correct, because @CustId
parameter does not belong to any condition

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

Show quoteHide quote

"Mori" <M***@discussions.microsoft.com> wrote in message
news:0682863C-87EF-4B79-A9E4-F046B16092A7@microsoft.com...
>I am trying to pass a datareader object and then iterate through the reader
> in a client application. I get the following exception "Invalid attempt
> to
> read when no data is present."
>
> Question: What is the correct way to pass a dataReader? Any Good examples
> please. thanks
>
> public SqlDataReader GetCust(string CustId)
> {
> try
> {
> if (sqlConn.State == ConnectionState.Closed)
> sqlConn.Open();
> SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
> (@CustId)", this.sqlConn);
> sqlCmd.CommandType = CommandType.Text;
> SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
> p0.Value = CustId;
>
> SqlDataReader sqlRead =
> sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
> return sqlRead;
> }
> catch(Exception exep)
> {
> Console.WriteLine("An error occurred " + exep.Message.ToString());
> return null;
> }
> }
>
>
> //client
> static void Main(string[] args)
> {
> UserDefinedFunction.UDFSampler udf = new UDFSampler();
> SqlDataReader sRead = udf.GetCust("xyz");
> while (sRead.Read())
> {
> //do something with record
> }
> sRead.Close();
>
> }
>
>

14 Apr 2005 1:35 AM

John Papa

I am assuming that dbo.ListCustomers is a SQL Server user defined function
.... if so, have you tried running the SQL statement inside of query analyzer
to make sure it returns rows when you pass it a parameter?

// John Papa
// http://codebetter.com/blogs/john.papa

Show quoteHide quote

"Mori" wrote:

> I am trying to pass a datareader object and then iterate through the reader
> in a client application. I get the following exception "Invalid attempt to
> read when no data is present."
>
> Question: What is the correct way to pass a dataReader? Any Good examples
> please. thanks
>
> public SqlDataReader GetCust(string CustId)
> {
>     try
>     {
>         if (sqlConn.State == ConnectionState.Closed)
>             sqlConn.Open();
>         SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
> (@CustId)", this.sqlConn);
>         sqlCmd.CommandType = CommandType.Text;
>         SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
>         p0.Value = CustId;
>
>         SqlDataReader sqlRead =
> sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
>         return sqlRead;
>     }
>     catch(Exception exep)
>     {
>         Console.WriteLine("An error occurred " + exep.Message.ToString());
>         return null;
>     }
> }
>
>
> //client
> static void Main(string[] args)
> {
>     UserDefinedFunction.UDFSampler udf = new UDFSampler();
>     SqlDataReader sRead =   udf.GetCust("xyz");
>     while (sRead.Read())
>     {
>         //do something with record
>     }
>     sRead.Close();
>
> }
>
>

14 Apr 2005 11:29 AM

Mori

The sql string works in query analyzer. It also works when I use a
sqlAdapter and data table to return the data, but not when I use a data
deader. Why? Please help.

Show quoteHide quote

"John Papa" wrote:

> I am assuming that dbo.ListCustomers is a SQL Server user defined function
> ... if so, have you tried running the SQL statement inside of query analyzer
> to make sure it returns rows when you pass it a parameter?
>
> // John Papa
> // http://codebetter.com/blogs/john.papa
>
> "Mori" wrote:
>
> > I am trying to pass a datareader object and then iterate through the reader
> > in a client application. I get the following exception "Invalid attempt to
> > read when no data is present."
> >
> > Question: What is the correct way to pass a dataReader? Any Good examples
> > please. thanks
> >
> > public SqlDataReader GetCust(string CustId)
> > {
> >     try
> >     {
> >         if (sqlConn.State == ConnectionState.Closed)
> >             sqlConn.Open();
> >         SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
> > (@CustId)", this.sqlConn);
> >         sqlCmd.CommandType = CommandType.Text;
> >         SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
> >         p0.Value = CustId;
> >
> >         SqlDataReader sqlRead =
> > sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
> >         return sqlRead;
> >     }
> >     catch(Exception exep)
> >     {
> >         Console.WriteLine("An error occurred " + exep.Message.ToString());
> >         return null;
> >     }
> > }
> >
> >
> > //client
> > static void Main(string[] args)
> > {
> >     UserDefinedFunction.UDFSampler udf = new UDFSampler();
> >     SqlDataReader sRead =   udf.GetCust("xyz");
> >     while (sRead.Read())
> >     {
> >         //do something with record
> >     }
> >     sRead.Close();
> >
> > }
> >
> >

14 Apr 2005 6:48 PM

Sahil Malik [MVP]

My 2 cents - don't be passin' around open data readers. That encourages bad
design - open connections, left open too long or just never closed.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

Show quoteHide quote

"Mori" <M***@discussions.microsoft.com> wrote in message
news:0682863C-87EF-4B79-A9E4-F046B16092A7@microsoft.com...
> I am trying to pass a datareader object and then iterate through the
reader
> in a client application. I get the following exception "Invalid attempt
to
> read when no data is present."
>
> Question: What is the correct way to pass a dataReader? Any Good examples
> please. thanks
>
> public SqlDataReader GetCust(string CustId)
> {
> try
> {
> if (sqlConn.State == ConnectionState.Closed)
> sqlConn.Open();
> SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
> (@CustId)", this.sqlConn);
> sqlCmd.CommandType = CommandType.Text;
> SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
> p0.Value = CustId;
>
> SqlDataReader sqlRead =
> sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
> return sqlRead;
> }
> catch(Exception exep)
> {
> Console.WriteLine("An error occurred " + exep.Message.ToString());
> return null;
> }
> }
>
>
> //client
> static void Main(string[] args)
> {
> UserDefinedFunction.UDFSampler udf = new UDFSampler();
> SqlDataReader sRead = udf.GetCust("xyz");
> while (sRead.Read())
> {
> //do something with record
> }
> sRead.Close();
>
> }
>
>

15 Apr 2005 5:56 PM

William (Bill) Vaughn

1) I agree with Mr. Malik. Don't pass a DataReader around...
2) Turn on the profiler to see what's getting executed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quoteHide quote

"Mori" <M***@discussions.microsoft.com> wrote in message
news:0682863C-87EF-4B79-A9E4-F046B16092A7@microsoft.com...
>I am trying to pass a datareader object and then iterate through the reader
> in a client application. I get the following exception "Invalid attempt
> to
> read when no data is present."
>
> Question: What is the correct way to pass a dataReader? Any Good examples
> please. thanks
>
> public SqlDataReader GetCust(string CustId)
> {
> try
> {
> if (sqlConn.State == ConnectionState.Closed)
> sqlConn.Open();
> SqlCommand sqlCmd = new SqlCommand("SELECT * FROM dbo.ListCustomers
> (@CustId)", this.sqlConn);
> sqlCmd.CommandType = CommandType.Text;
> SqlParameter p0 = sqlCmd.Parameters.Add("@CustId", SqlDbType.VarChar, 3);
> p0.Value = CustId;
>
> SqlDataReader sqlRead =
> sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
> return sqlRead;
> }
> catch(Exception exep)
> {
> Console.WriteLine("An error occurred " + exep.Message.ToString());
> return null;
> }
> }
>
>
> //client
> static void Main(string[] args)
> {
> UserDefinedFunction.UDFSampler udf = new UDFSampler();
> SqlDataReader sRead = udf.GetCust("xyz");
> while (sRead.Read())
> {
> //do something with record
> }
> sRead.Close();
>
> }
>
>