Empty dataset from Access DB?

Question

Hi,

I have an existing Access database (2010) that I'm buildig a WPF/MVVM application to manage. In VS i can connect to the db and reteieve data using Tools > Connect to database ..., and when I open the connection from my c# code i can see that a ".laccdb" file i created so I'm pretty sure the connection is working. For starters I'm trying to retreive all rows from a table named PROJECTS. My code looks like this:

static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                 @"Data source=C:\Database\" +
                                 @"MyDB.accdb;";

DataSet dataSet = new DataSet();
string strSql = "SELECT * FROM PROJECTS";
OleDbConnection dbConnection = new OleDbConnection(conString);
dbConnection.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter .SelectCommand = new OleDbCommand(strSql, dbConnection);
dataAdapter .SelectCommand.ExecuteNonQuery();
dataAdapter .Fill(dataSet);
dbConnection.Close();

No errrors are reported, but the dataSet is empty (Count == 0). When i examin the dataSet in the debugger after the 'Fill' call, i see that the property SchemaSerializationMode is set to IncludeSchema, but i have no xsd files for the Access tables - is that an issue?

I've also tried to use a DataTable instead of DataSet, but the result is stille empty (not really sure what the difference is between DataTable and DatSet, but from verious examples I've seen they seem to be interchangeable)

Any help is greatly appreciated.

Thanks

Erik

 

Answer 1

Hi Erik,

You don't need both the .ExecuteNonQuery() and the .Fill() ... since you trying to fill a DataSet/DataTable, you only need the .Fill(). Notice the word "Non" in .ExecuteNonQuery() ... it is used for doing things like Updating and/or Deleting ... not for Querying. 

And, you can also make that code a bit more concise, like this:

static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                 @"Data source=C:\Database\" +
                                 @"MyDB.accdb;";

DataSet dataSet = new DataSet();
string strSql = "SELECT * FROM PROJECTS";
using (OleDbConnection dbConnection = new OleDbConnection(conString))
{
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, dbConnection);
    dataAdapter.Fill(dataSet); 
}

The .Fill() command will open a connection if it's not already open. It will also leave the connection in the state it found it in. So, if it's already open, it leaves it open when it's done. If it wasn't already open, it closes it when it's done.

No errrors are reported, but the dataSet is empty (Count == 0). When i examin the dataSet in the debugger after the 'Fill' call, i see that the property SchemaSerializationMode is set to IncludeSchema, but i have no xsd files for the Access tables - is that an issue?

None of what I just wrote above explains why your DataSet/DataTable isn't getting filled, because what you already have should work just fine. And not having an .xsd schema doesn't matter either. I'm assuming that you meant DataSet.Tables.Count was 0 (and when you tried it with a DataTable instead, you meant DataTable.Rows.Count was 0)?


So perhaps your connection string needs work, even though you got no errors. Take a look at this link for proper connection strings:
http://www.connectionstrings.com/access/

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 2

Hi Bonnie,

Thanks for your reply and explaining about the connections and the Fill method. I've consulteted the link you supplied regarding the connection string, but as far as i can tell, my connection string is correct.

I'm using Visual Studio 2013 RC - i don't know if that has any influence on the problem?

Erik

Answer 3

ahhh ... the Access db is made with 2010 32 bit, and I'm now using Windows 7 & VS 64 bit. I recall and issue in that respect, but alas i forget how to fix it :(

Erik

Answer 4

nope ... upgrading to office 64 bit didn't help. I guess Access Db and VS 2013 is a no go. To bad :(

Erik

Answer 5

>>the Access db is made with 2010 32 bit, and I'm now using Windows 7 & VS 64 bit

Have a try to leave access db to be 32 bit and complie program to X86 plat like below:

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 6

Hi Fred,

What a brilliant idea - why didn't i think of that. And it works too :) THANKS.

Erik

Answer 7

Sorry I couldn't help you, Erik ... I was out of town all week. Looks like Fred hit the nail on the head though! Yay! (You should mark Fred's post as the answer).

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com