Reuse OleDbDataReader object

[fizzled]

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 I'm getting a "Type mismatch in expression" error. I'm not sure if this is because I'm reusing the OleDbDataReader object, an OleDbCommand object, or if there is a problem with my boolean parameter. The code is as follows:

 

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?

[Machaira]

Try replacing:

 

cmdBlog.CommandText = qryActiveBlogs;

 

with:

 

cmdBlog = New OleDbCommand(qryActiveBlogs, cnBlog);

 

You also shouldn't need the line:

 

cmdBlog.Parameters.Clear();

 

since it's a new object the parameters should be empty.

[fizzled]

Thanks for the suggestions, Machaira, but they didn't work. Still encountering the same error.

[Diesel]

Well, I don't 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 doesn't close when you call close on it (suprisingly).

 

Also, I don't 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.

[fizzled]

Well, I don't 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 doesn't close when you call close on it (suprisingly).

 

Also, I don't 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.

 

That's true, I guess I hadn't thought the logic through well. Anyways, I streamlined it a bit, but the same error still occurs. The new code is as follows:

 

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();". I'm guessing the type mismatch is because cmdBlog.ExecuteReader() is not returning an OleDbDataReader object (?), but I'm not sure why that would happen. The only other thing I can think of is that I'm setting the boolean parameter incorrectly, but if so, I don't know how else to set it.

[FZelle]

Just my 2 cents:

 

I have stopped useing the DataReader in ASP.NET because there are many things that

can/can't happen and you are not able to debug.

 

Start useing the DataAdapter with a DataSet.

Your code would be:

 

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;
 }
}

[fizzled]

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.

[fizzled]

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.