[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to query a MS Access database

Posted on 2006-06-06
6
Medium Priority
?
292 Views
Last Modified: 2012-06-27
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. :)
0
Comment
Question by:bussd
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:HeidarV
ID: 16849106
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
 

Author Comment

by:bussd
ID: 16853846
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
 
LVL 2

Expert Comment

by:HeidarV
ID: 16858555
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:bussd
ID: 16859053
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
 
LVL 2

Accepted Solution

by:
HeidarV earned 800 total points
ID: 16859084
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
 

Author Comment

by:bussd
ID: 16867915
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

Featured Post

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.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

834 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