How to get a single record via OleDB from MS Access in C#

Hakan
Hakan used Ask the Experts™
on
Hello,

I'm trying to get a value from a MS Access which has several tabular datas. I'd like to get single value with UniqueID (string) and Column Name.

Do you suggest any methods for getting single values? Any help would be grateful.

Regards.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AndyAinscowFreelance programmer / Consultant

Commented:
Use ExecuteScalar.  An example is here:
http://csharp.net-informations.com/data-providers/csharp-sqlcommand-executescalar.htm

basically
x = cmd.ExecuteScalar()  where cmd is an OleDBCommand object and x is the single value you want returning.

Author

Commented:
Hi Andy i only would like to read a data also is there any thing for only read purpose maybe it has better performance?
AndyAinscowFreelance programmer / Consultant

Commented:
That is read only.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

AndyAinscowFreelance programmer / Consultant

Commented:
It returns one record with one value - better performance than data reader (read only, multiple records)  which has better performance than dataset (read/write, multiple records).

Author

Commented:
Hi AndyAinscow,

Thanks for your information, i'd like to know it can be a problem if multiple users try to connect the same database?
AndyAinscowFreelance programmer / Consultant

Commented:
Only if they are writing.  Reading shouldn't be a problem

Author

Commented:
Ok got it. Maybe it's a very basic question but i coudln't connect to my database with that;

            connetionString = @"Data Source=E:\SampleData\ASampleDatabase.accdb";
AndyAinscowFreelance programmer / Consultant

Commented:
You need to supply the provider information.
Have a look at:
https://www.connectionstrings.com/access/

Author

Commented:
I tried provider but it says it doesnt support provide keyword.
AndyAinscowFreelance programmer / Consultant

Commented:
Please post what you tried

Author

Commented:
Here is the last code i tried and on attachment error which i got.

        private void AccessReader_Click(object sender, RoutedEventArgs e)
        {
            string connetionString = null;
            SqlConnection cnn;
            SqlCommand cmd;
            string sql = null;

            connetionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\SampleData\ASampleDatabase.accdb";
            sql = "Select Count(*) from Asset Items";

            cnn = new SqlConnection(connetionString);
            try
            {
                cnn.Open();
                cmd = new SqlCommand(sql, cnn);
                Int32 count = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                cnn.Close();
                MessageBox.Show(" No. of Rows " + count);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }

        }

Open in new window

error_1.png
Freelance programmer / Consultant
Commented:
SqlConnection (and the others) I think are specific for SQL Server, not for using with Access databases.
Try with OleDb based classes:
https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection?view=netframework-4.8

Author

Commented:
Ath that time do you know how can i tie access database at the backend side to the SQL Server Express?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial