Can't read data from MS Access Query in C#

I am writing a C# Windows Forms program that is connecting to a MS Access database frontend (with a SQL server backend).  In Access, I have a form that creates a query based on user input, which I then want to read from the C# program.  I have been able to successfully run queries against the linked tables in Access; however, I cannot get any data from the query - it always executes, but returns no data.

Here is my preliminary connection code:

OleDbConnection dbConnection = new OleDbConnection();
dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPathText.Text;
dbConnection.Open();

Open in new window


I have tried executing it as a stored procedure:

OleDbCommand callCmd = new OleDbCommand("MassEmailQuery", dbConnection);
callCmd.CommandType = CommandType.StoredProcedure;
OleDbDataReader reader = callCmd.ExecuteReader();
while (reader.Read())
{
    emailListBox.Items.Add(reader[0].ToString());
}

Open in new window


And I have tried running a SQL statement against the query:

DataSet dataSet = new DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
string sqlQuery = "SELECT " + dbFieldCombo.SelectedItem.ToString() + " FROM " + dbQueryCombo.SelectedItem.ToString();
dataAdapter.SelectCommand = new OleDbCommand(sqlQuery, dbConnection);
dataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
foreach (DataRow row in dataTable.Rows)
{
    emailListBox.Items.Add(row[0]);
}

Open in new window


But neither method works - what am I doing wrong?
aesdttOwnerAsked:
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.

BuggyCoderCommented:
debug and share the query in strquery string or share query written in stored procedure
aesdttOwnerAuthor Commented:
strquery is: "SELECT Email from MassEmailQuery"

and the access stored query is:

SELECT DISTINCT Contact.Email, Contact.MailingList, Customer.Type, Customer.State, Customer.Country, Customer.Status, Customer.Description
FROM Customer INNER JOIN Contact ON Customer.ID = Contact.CustomerID
WHERE (((Contact.Email) Like "*@*") AND ((Contact.MailingList)=True) AND ((Customer.Type)="Advocate") AND ((Customer.Country)="USA" Or (Customer.Country)="United States" Or (Customer.Country)="US") AND ((Customer.Status)="Active"));
BuggyCoderCommented:
looks ok, just see if they run on access by opening MS-Access and running these queries there and see if they return any data there.
Also please check that your connection string is correct, i mean make sure that you are not pointing to a wrong database or some testing database which might be empty...

also i have made few changes to your query, just try this one as well:-

SELECT DISTINCT Contact.Email, Contact.MailingList, Customer.Type, Customer.State, Customer.Country, Customer.Status, Customer.Description
FROM Customer INNER JOIN Contact ON Customer.ID = Contact.CustomerID
WHERE (((Contact.Email) Like "*@*") AND ((Contact.MailingList)=True) AND ((Customer.Type)="Advocate") AND (((Customer.Country)="USA" Or (Customer.Country)="United States" Or (Customer.Country)="US")) AND ((Customer.Status)="Active")));
just see if i have messed up with braces...
basically have taken your country OR Condition into separate braces.
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.

HainKurtSr. System AnalystCommented:
oh my god, why so many (..)
you don't need any, try this:

SELECT DISTINCT Contact.Email, Contact.MailingList, Customer.Type, Customer.State, Customer.Country, Customer.Status, Customer.Description
FROM Customer INNER JOIN Contact ON Customer.ID = Contact.CustomerID
WHERE Contact.Email Like "*@*" AND Contact.MailingList=True AND Customer.Type="Advocate" AND Customer.Country in ("USA","United States","US") AND Customer.Status="Active";
HainKurtSr. System AnalystCommented:
also, after saving the query like above,

OleDbCommand callCmd = new OleDbCommand("MassEmailQuery", dbConnection);
callCmd.CommandType = CommandType.TableDirect;
OleDbDataReader reader = callCmd.ExecuteReader();
while (reader.Read())
{
    emailListBox.Items.Add(reader[0].ToString());
}

or

OleDbCommand callCmd = new OleDbCommand("select * from MassEmailQuery", dbConnection);
callCmd.CommandType = CommandType.Text;
OleDbDataReader reader = callCmd.ExecuteReader();
while (reader.Read())
{
    emailListBox.Items.Add(reader[0].ToString());
}

is MassEmailQuery a sp or view? is this sql server or access?
aesdttOwnerAuthor Commented:
The stored query (long SQL SELECT statement) runs and does give data in Access - I can view the information I'm trying to bring over to the application in Access' datasheet view (and all the parentheses were just from copying and pasting from Access' SQL view...that's what MS gave us to play with).

I don't think the problem is with SQL syntax, since I can replace "MassEmailQuery" with a table name in the basic SQL statement and, using the same code, retrieve data (so connection string is good)...the problem appears to be just in retrieving data from MassEmailQuery because it is a query and not a standard table.

HainKurt: I tried changing CommandType.StoredProcedure to CommandType.TableDirect, but that did not seem to make a difference.  I also tried your code selecting all data from the query, but that did not work either.  To answer your question, MassEmailQuery is just a  query in the Access frontend...my understanding was that they are equivalent to stored procedures.
HainKurtSr. System AnalystCommented:
i don't see any issue here

string mySelectQuery = "SELECT Email from MassEmailQuery";
OleDbCommand myCommand = new OleDbCommand(mySelectQuery,dbConnection);
dbConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read()) {
	mailListBox.Items.Add(myCommand[0].ToString());
}

myReader.Close();
myConnection.Close();

Open in new window


if you dont get any data from here, try to use any other table ("select somecolumn from someTable") instead of MassEmailQuery, just to see it works...
aesdttOwnerAuthor Commented:
I'm not sure exactly what it is, but for some reason the code does not like that query.  If I change it to another (simple) query or a table, it works fine.  My suspicion is that it doesn't like the INNER JOIN.

As a workaround (and probably my permanent solution), I just turned the query into a make-table query (added "INTO MassEmailQueryResults" after the SELECT portion of the statement, if anyone else ever needs to do the same) and pointed my C# program at the table, which now contains the query data.  After doing that, everything seems to be working fine.
HainKurtSr. System AnalystCommented:
ok, write that query without inner join and save as MassEmailQuery2 and use one of the solutions above to see if it makes any difference...

SELECT DISTINCT Contact.Email, Contact.MailingList, Customer.Type, Customer.State, Customer.Country, Customer.Status, Customer.Description
FROM Customer, Contact
WHERE Customer.ID = Contact.CustomerID and Contact.Email Like "*@*" AND Contact.MailingList=True AND Customer.Type="Advocate" AND Customer.Country in ("USA","United States","US") AND Customer.Status="Active";
aesdttOwnerAuthor Commented:
Sorry it's been a while, this is a side project that I haven't been able to work on in a week or so...HK, I tried your suggestion, but that wasn't it.

However, it put me in the mood to try a few other changes, and I figured out what it was.  I changed each asterisk to a percent sign, and the c# program was able to pull the data from the database.

Thanks for all your help, and I hope this thread helps someone else one day!

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
aesdttOwnerAuthor Commented:
This is the correct answer.
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
C#

From novice to tech pro — start learning today.