?
Solved

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

Posted on 2012-03-20
11
Medium Priority
?
299 Views
Last Modified: 2012-04-03
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?
0
Comment
Question by:aesdtt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37744017
debug and share the query in strquery string or share query written in stored procedure
0
 

Author Comment

by:aesdtt
ID: 37744066
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"));
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37744158
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.
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 57

Expert Comment

by:HainKurt
ID: 37744396
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";
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 37744411
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?
0
 

Author Comment

by:aesdtt
ID: 37744668
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.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 37744817
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...
0
 

Author Comment

by:aesdtt
ID: 37744992
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.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 37745206
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";
0
 

Accepted Solution

by:
aesdtt earned 0 total points
ID: 37774742
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!
0
 

Author Closing Comment

by:aesdtt
ID: 37799994
This is the correct answer.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

765 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