Solved

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

Posted on 2004-10-07
9
511 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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