Solved

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

Posted on 2012-03-20
11
278 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
  • 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
 
LVL 51

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 51

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 51

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 51

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now