Solved

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

Posted on 2004-10-07
9
507 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now