[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

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

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
WillyCornbread
Asked:
WillyCornbread
  • 4
  • 2
1 Solution
 
raterusCommented:
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
 
WillyCornbreadAuthor Commented:
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
 
mAjKoLCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
WillyCornbreadAuthor Commented:
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
 
mAjKoLCommented:
Have you doublechecked your connection string ?
0
 
WillyCornbreadAuthor Commented:
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
 
WillyCornbreadAuthor Commented:
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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now