Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-20
11
Medium Priority
?
308 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 60

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 60

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 60

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 60

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

610 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