ASP.Net C# OleDBCommand not Returning Data

I have a query string that works in my database and returns data.  When I feed it into my OleDBCommand to read the data and populate a chart, it comes back with no data - the program never enters the while loop but if I run the query (strSelect) in the database directly, I get 101 records.  I have this exact code in another application and it works fine.   Am I missing something??

private void SetGwChartTest(string strSelect)
    {
        OpenDbConnection();
        var dCmd = new OleDbCommand(strSelect, _objConnection);
        var dRead = dCmd.ExecuteReader();
        while (dRead != null && dRead.Read())
        {
            DateTime dt = Convert.ToDateTime(dRead["sample_date"].ToString());
            double dblDate = ConvertToUnixTimestamp(dt);
            string strMw1 = dRead["MW-1"].ToString(),
                strMw4 = dRead["MW-4"].ToString(),
                strMw5 = dRead["MW-5"].ToString(),
                strMw19 = dRead["MW-19"].ToString();//,

            if (strMw1 != "") { chGwChart1.Series[0].Data.Add(new ChartPoint(dblDate, Convert.ToDouble(strMw1))); }
            if (strMw4 != "") { chGwChart1.Series[1].Data.Add(new ChartPoint(dblDate, Convert.ToDouble(strMw4))); }
            if (strMw5 != "") { chGwChart1.Series[2].Data.Add(new ChartPoint(dblDate, Convert.ToDouble(strMw5))); }
            if (strMw19 != "") { chGwChart1.Series[3].Data.Add(new ChartPoint(dblDate, Convert.ToDouble(strMw19))); }
        }
    }
LVL 1
sj_rodriguezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Has the object _objConnection been initialize?
sj_rodriguezAuthor Commented:
Yes.  The OpenDbConnection does that.

private void OpenDbConnection()
    {  
        string strDbConString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Server.MapPath("~\\App_Data\\") + "mydata.mdb;";
        _objConnection = new OleDbConnection(strDbConString);
        _objConnection.Open();
    }
Fernando SotoRetiredCommented:
Hi sj_rodriguez;

Check what the status of the connection is after opening it. Try inserting the if statement after opening the connection and displaying the state to the IDE Console Window.
OpenDbConnection();
if( _objConnection.State != ConnectionState.Open )
{
    Console.WriteLine( "Connection State is : " +  _objConnection.State.ToString() );
    return;
}
var dCmd = new OleDbCommand(strSelect, _objConnection);

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sj_rodriguezAuthor Commented:
If statement is false. Connection is open.
Fernando SotoRetiredCommented:
Place a breakpoint on this line of code.

 while (dRead != null && dRead.Read())

Then hover the mouse cursor over dRead and see if it has a non null value. Also what is the SQL statement being executed.
sj_rodriguezAuthor Commented:
Field count is 5 (which is correct), but has rows = false.

Statement being executed is:

TRANSFORM Avg(qry_chart_GW_trend.no3n) AS AvgOfno3n
SELECT qry_chart_GW_trend.sample_date FROM qry_chart_GW_trend
WHERE (((qry_chart_GW_trend.sample_date)>#1/1/2014#) AND ((qry_chart_GW_trend.well_name)='MW-1' Or (qry_chart_GW_trend.well_name)='MW-4' Or (qry_chart_GW_trend.well_name)='MW-5' Or (qry_chart_GW_trend.well_name)='MW-19'))
GROUP BY qry_chart_GW_trend.sample_date
PIVOT qry_chart_GW_trend.well_name In ('MW-1', 'MW-4', 'MW-5', 'MW-19')
sj_rodriguezAuthor Commented:
In a different program I have the same exact thing with the following statement and it results in data populating into the chart.

TRANSFORM Avg(qry_Charts_GW.no3) AS AvgOfno3
SELECT qry_Charts_GW.sample_date
FROM qry_Charts_GW
GROUP BY qry_Charts_GW.sample_date
ORDER BY qry_Charts_GW.sample_date
PIVOT qry_Charts_GW.well_name

I just noticed that the one difference is the where clause.  Let me try moving the where clause so that it is executed before the statement I am passing to the OleDbCommand.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
So when you did this, "but if I run the query (strSelect) in the database directly, I get 101 records", did this have the Where clause?
sj_rodriguezAuthor Commented:
Yes.

Now when I run the program with the Where clause in the "qry_Charts_GW" query (residing in the database), I get data to populate in dRead and the chart gets data.
sj_rodriguezAuthor Commented:
It appears that I cannot have any additional data filtering in a where clause of a Transform query.  It has to reside in a query that the Transform query pulls information from.
Fernando SotoRetiredCommented:
Yes I figured it was in the query seeming after the test we went through it was the only thing left and why I had ask for you to post it.

Glad it was resolved.
sj_rodriguezAuthor Commented:
Thank you for "talking" this through with me.
sj_rodriguezAuthor Commented:
The expert helped walk me through some debugging steps which helped me locate the issue.  I accepted both of our discussion items as the solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.