How to query a MS Access database

Hello

I have a singleton class that I'm calling DataManager and I want to use a thread to run a query every couple of minutes so that the user always has access to data that's fairly new without having to wait for the query to execute... problem is that I can't get the query to work.

Here's what I have...

   private void UserThreadProc()
        string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb;PWD=password";
        string SelectText = "SELECT * FROM Users";

        OleDbConnection conn = new OleDbConnection(ConnStr);
        OleDbDataAdapter ssDA = new OleDbDataAdapter(SelectText, conn);
        OleDbCommandBuilder cb = new OleDbCommandBuilder(ssDA);

        try
        {
            ssDA.SelectCommand = new OleDbCommand(SelectText,conn);
            ssDA.Fill(dsUsers);
            ssDA.Update(dsUsers.Tables["Users"]);            
        }
        finally
        {
            conn.Close();
            ssDA.Dispose();
            cb.Dispose();
        }
   }

I get "Could not find installable ISAM" error on line "ssDA.Fill(dsUsers)".

All the above code runs inside a thread (called "UserThread") that I want to continuously update the dataset dsUsers.  dsUsers is declared inside my DataManager class as:   public static DataSet dsUsers = new DataSet();.  I also want to run another thread at the same time(Called "ReportThread") to do similar work on different data.  The dataset that's updated by each thread will be used by a winform that may or may not be visible.  I only need to read data, no updating is required.

1) I'm confused about the roles of different data components (DataSets, DataSources. Connections, Adapters, CommandBuilder, Commands, etc.).
2) I don't know if I should have seperate data connections for each of my threads or share one between them
3) I would like to know how to trap any errors that come out of this connection/query

As always, source code and any help you can give this newbie is appreciated. :)
bussdAsked:
Who is Participating?
 
HeidarVConnect With a Mentor Commented:
update the structure of datatable in the dataset by the table in access. this means you allowed a column of an access table to contain null value and your column in datatable in c# doesn't allow null
0
 
HeidarVCommented:
Hello,

- DataTable is the container of data in your app and always contains a copy of the main data which may be on ms access or sql server or ...
- DataSet contains multiple datatables and connections between them.
- DataConnection is the connection to data source (access, sql server, ...)
- DataCommand executes sql query on datasource and may or may not return value (single value or table)
- DataAdapter contains 4 DataCommands related to one table: select, insert, update, delete. Each of thease commands executes the related command on datasource. DataAdapter fills and updates DataTable.
- CommandBuilder builds 4 commands in DataAdapter in code. If you use visual studio wizards thease commands are created automatically.

You can use "Microsoft patterns & practices - Enterprise Library" for updating data in your application. http://msdn.microsoft.com/practices/

To trap exceptions you can write your code like this:
        try
        {
            ssDA.SelectCommand = new OleDbCommand(SelectText,conn);
            ssDA.Fill(dsUsers);
            ssDA.Update(dsUsers.Tables["Users"]);            
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            conn.Close();
            ssDA.Dispose();
            cb.Dispose();
        }
0
 
bussdAuthor Commented:
Thanks for the info.  I'm looking into the MS patterns and practices, but in the mean time can you see anything wrong with the code that I posted?  I still get "Could not find installable ISAM" error on line "ssDA.Fill(dsUsers)".
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HeidarVCommented:
Use this connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb;Persist Security Info=True;Jet OLEDB:Database Password=password"
0
 
bussdAuthor Commented:
Now I get a different error: "Value cannot be null" on line "ssDA.Update(dsUsers.Tables["Users"]);"

Shouldn't the previous line ("ssDA.Fill(dsUsers);") prevent this?
0
 
bussdAuthor Commented:
Thanks HeidarV.

Unfortunately I couldn't get it working this way.  I used an example on a web page that was given to me in another post and that seems to be going better.  I'm going to close off this question and continue in another thread.  Feel free to contribute.

http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21878995.html 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.