Solved

Sql query is fine, but the dataReader is EMPTY??!?!?

Posted on 2004-10-07
9
517 Views
Last Modified: 2012-08-14
HI all,

I have function that use to Bind a repeater to a dataReader. I pass in the sql string, and the repeater, the code for this function is below:

/* ======================================================================
            BindRepeater helper function takes a repeater reference and a sql string
            and binds the two using a simple dataReader, disposing of the reader and conn at the end
            Also takes a boolean of whether or not to bind the data if the reader has no rows
            ====================================================================== */
            public void BindRepeater (Repeater r, string strSql, Boolean blnBindEmpty ){
                  
                  //set a  a connection reference
                  OleDbConnection conBindRepeater      = new OleDbConnection(ConfigurationSettings.AppSettings["strConnection"]);
                  
                  //try to run the sql insert
                  try{
                        //open the connection
                        conBindRepeater.Open();
                                          
                        //create the command object for the articles
                        OleDbCommand cmdBindRepeater      = new OleDbCommand(strSql,conBindRepeater);
                        
                        //execute the articles command
                        OleDbDataReader rdrBindRepeater      = cmdBindRepeater.ExecuteReader(CommandBehavior.CloseConnection);
                        
                        HttpContext.Current.Trace.Warn(strSql);
                        HttpContext.Current.Trace.Warn("HasRows="+rdrBindRepeater.HasRows.ToString());
                        
                        //only bind if it hasRows or the bindEmpty is true
                        //if(rdrBindRepeater.HasRows || blnBindEmpty == true){      
                              
                              //bind the articles data to the rptrArticles control
                              r.DataSource = rdrBindRepeater;
                              r.DataBind();
                        //}//end if HasRows
                        
                        //explicitly close the reader
                        rdrBindRepeater.Close();      
                        
                        //catch any exceptions
                  }catch(Exception ex){
                        //write the exception to the trace log for debugging
                        HttpContext.Current.Trace.Warn("DataBinding Exception",Convert.ToString(ex));
                        //and finally, close the connection object if it is open                  
                  }finally{
                        // Close the connection
                        conBindRepeater.Dispose();
                  }//end try catch finally
            }//end BindRepeater method


Now, this works fine most of the time, but I have one query I am passing it that returns no rows. The HasRows check returns false, and if I bind the date there are no rows... BUT THE QUERY LOOKS FINE... I trace it out to the log and cut and past it directly in my access database and it does what's expected. It's a very simple query, here it is:

SELECT TOP 20 r.recordID,r.recordTitle, r.dtg_created, c.categoryName FROM content r, categories c WHERE r.categoryID = c.categoryID  AND r.active = 1  ORDER BY dtg_created

Has anyone experienced this? I don't even know where to look for a solution since everything seems fine except the results....

Thanks for your time and energy.

b
0
Comment
Question by:WillyCornbread
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 33

Expert Comment

by:raterus
ID: 12254670
actually your query is quite inefficient.  When your From clause just lists tables, but doesn't join them, the database has to perform a cross join.  Every row of one table is matched up to every row of the other table.  makes for quite a bit of results.  My guess is nothing passes your WHERE clause (check and doublecheck this)

0
 

Author Comment

by:WillyCornbread
ID: 12255265
Fair enough, despite the inneficiency of the it, there are indeed records that are returned from this query via the query analyzer, but the dataReader is empty. I have checked this numerous times...

Thanks again.

b
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12256722
If you used query analyzer - I suspect you are using MS SQL Server. If I'm right - why are you using OleDb* instead of Sql* ?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:WillyCornbread
ID: 12258271
I say query analyzer, but I actually mean the query pane in both Visual Studio .NET, and the Query pane in access itself.

Again, I can without a DOUBT assure you that there is nothing wrong with the results of the query.

Any other thoughts?

b
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12259183
Have you doublechecked your connection string ?
0
 

Author Comment

by:WillyCornbread
ID: 12259359
Yes, again, I am using the same function to return dataReaders in several places uses the same connection string and it all works fine.

This is very very strange. all the symptoms point to a query that is not returning any rows.

Is there some difference in the results a query would return based on whether it is handled via Ole or directly queried in the database itself?

Thanks again,

b
0
 

Accepted Solution

by:
WillyCornbread earned 0 total points
ID: 12261101
Okay, I figured it out.

Apparently when query'ing the database directly via Visual Studio or the Access query pane you can use 'WHERE [booleanField] = 1' and it works fine. BUT if you try to send that through the OleDB adapter it will not work. It MUST be 'WHERE [booleanField] = True'...

Hope this helps someone else, too.

b
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question