Reuse OleDbDataReader object

F

fizzled

Well-known member
Joined
Aug 9, 2004
Messages
52
Location
Atlanta, GA, USA
My current code is intended to retrieve the number of entries in a database table, then retrieve these entries if the count is greater than 0. I assume I can simply reuse the same OleDbDataReader object for both queries, but Im getting a "Type mismatch in expression" error. Im not sure if this is because Im reusing the OleDbDataReader object, an OleDbCommand object, or if there is a problem with my boolean parameter. The code is as follows:

Code: 
public void Page_Load (object Sender, EventArgs e) {
  string qryActiveCount = "SELECT COUNT(BlogID) AS ActiveCount FROM Blogs WHERE BlogActive = @BlogActive";
  string qryActiveBlogs = "SELECT B.BlogID, B.BlogTitle, B.BlogPostDate, B.BlogText, U.PublicName AS AuthorName FROM Blogs AS B INNER JOIN BlogUsers AS U ON B.BlogAuthor = U.UserID WHERE B.BlogActive = @BlogActive";
  OleDbConnection cnBlog = new OleDbConnection(Application.Contents["FBConnStr"].ToString());
  OleDbCommand cmdBlog = new OleDbCommand(qryActiveCount, cnBlog);
  OleDbDataReader rdrBlog;

  cmdBlog.Parameters.Clear();
  cmdBlog.Parameters.Add(new OleDbParameter("@BlogActive", OleDbType.Boolean));
  cmdBlog.Parameters["@BlogActive"].Value = true;

  cnBlog.Open();
  rdrBlog = cmdBlog.ExecuteReader();

  if (rdrBlog.HasRows) {
    rdrBlog.Read();
    if (Convert.ToInt32(rdrBlog["ActiveCount"]) > 0) {
      rptrActiveBlogs.Visible = true;
    }
  }
  rdrBlog.Close();

  if (rptrActiveBlogs.Visible) {
    cmdBlog.CommandText = qryActiveBlogs;

    cmdBlog.Parameters.Clear();
    cmdBlog.Parameters.Add(new OleDbParameter("@BlogActive", OleDbType.Boolean));
    cmdBlog.Parameters["@BlogActive"].Value = true;

    [color=red]rdrBlog = cmdBlog.ExecuteReader();[/color]

    rptrActiveBlogs.DataSource = rdrBlog;
    rptrActiveBlogs.DataBind();

    rdrBlog.Close();
  } else {
    litBlogEmpty.Visible = true;
  }

  cnBlog.Close();

  rdrBlog = null;
  cmdBlog = null;
  cnBlog = null;
}

The error occurs on the red line.

PS - My host (Brinkster.com) sets the following ASP Rule:

"Set all objects you use equal to Nothing when you are done with them. (ie. "Set Conn = Nothing")"

So I tacked on the "rdrBlog = null; cmdBlog = null; cnBlog = null;" there at the end of my code. I assume in C# this produces the same result?
 
Try replacing:

cmdBlog.CommandText = qryActiveBlogs;

with:

cmdBlog = New OleDbCommand(qryActiveBlogs, cnBlog);

You also shouldnt need the line:

cmdBlog.Parameters.Clear();

since its a new object the parameters should be empty.
 
Well, I dont think type mismatch in expression would have to do with the reader itself. But try setting the reader to null after the initial close. Sometimes the reader doesnt close when you call close on it (suprisingly).

Also, I dont see your logic in checking the number of rows before you perform your query. When you perform your query, if there are no rows, no error will occur, and you can check hasRows on that reader.
 
Diesel said:
Well, I dont think type mismatch in expression would have to do with the reader itself. But try setting the reader to null after the initial close. Sometimes the reader doesnt close when you call close on it (suprisingly).

Also, I dont see your logic in checking the number of rows before you perform your query. When you perform your query, if there are no rows, no error will occur, and you can check hasRows on that reader.
Click to expand...

Thats true, I guess I hadnt thought the logic through well. Anyways, I streamlined it a bit, but the same error still occurs. The new code is as follows:

Code: 
public void Page_Load (object Sender, EventArgs e) {
  string qryActiveBlogs = "SELECT B.BlogID, B.BlogTitle, B.BlogPostDate, B.BlogText, U.PublicName AS AuthorName FROM Blogs AS B INNER JOIN BlogUsers AS U ON B.BlogAuthor = U.UserID WHERE B.BlogActive = @BlogActive";
  OleDbConnection cnBlog = new OleDbConnection(Application.Contents["FBConnStr"].ToString());
  OleDbCommand cmdBlog = new OleDbCommand(qryActiveBlogs, cnBlog);
  OleDbDataReader rdrBlog;

  cmdBlog.Parameters.Add(new OleDbParameter("@BlogActive", OleDbType.Boolean));
  cmdBlog.Parameters["@BlogActive"].Value = true;

  cnBlog.Open();
  [color=red]rdrBlog = cmdBlog.ExecuteReader();[/color]

  if (rdrBlog.HasRows) {
    rptrActiveBlogs.DataSource = rdrBlog;
    rptrActiveBlogs.DataBind();
    rptrActiveBlogs.Visible = true;
  } else {
    litBlogEmpty.Visible = true;
  }

  rdrBlog.Close();
  cnBlog.Close();

  rdrBlog = null;
  cmdBlog = null;
  cnBlog = null;
}

The error still occurs on the red line "rdrBlog = cmdBlog.ExecuteReader();". Im guessing the type mismatch is because cmdBlog.ExecuteReader() is not returning an OleDbDataReader object (?), but Im not sure why that would happen. The only other thing I can think of is that Im setting the boolean parameter incorrectly, but if so, I dont know how else to set it.
 
Just my 2 cents:

I have stopped useing the DataReader in ASP.NET because there are many things that
can/cant happen and you are not able to debug.

Start useing the DataAdapter with a DataSet.
Your code would be:

Code: 
public void Page_Load (object Sender, EventArgs e) {
  string qryActiveBlogs = "SELECT B.BlogID, B.BlogTitle, B.BlogPostDate, B.BlogText, U.PublicName AS AuthorName FROM Blogs AS B INNER JOIN BlogUsers AS U ON B.BlogAuthor = U.UserID WHERE B.BlogActive = @BlogActive";

  OleDbDataAdapter adBlog = new OleDbDataAdapter(qryActiveBlogs,Application.Contents["FBConnStr"].ToString());
  adBlog.SelectCommand.Parameters.Add("@BlogActive", OleDbType.Boolean).Value = true;

  DataSet dsBlog = new DataSet();
  cnBlog.Fill( dsBlog, "dsBlog");
  if( dsBlog.Tables["dsBlog"].Rows.Count > 0 )
  {    
    rptrActiveBlogs.DataSource = dsBlog.Tables["dsBlog"];
    rptrActiveBlogs.DataBind();
    rptrActiveBlogs.Visible = true;
  } 
  else 
  {
    litBlogEmpty.Visible = true;
  }
}
 
Using a DataSet would probably be fine in my current project, since it is very small-scale, but I would rather do it the correct way. Everything I need to do with the resulting rows at the moment can be accomplished without the extra overhead of a DataSet.
 
A user on another forum was able to resolve the problem. I had to open the database in Access, open the table in Design view, and change the Format of the Yes/No column to True/False.
 
Back
Top