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
Solved

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

Posted on 2004-10-07
9
513 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
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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