Trying to connect to a Access database table in order to retrieve data, but encounter difficulties

Question

First time doing database programming, so I just made a database in Access to try to do something with it. The database I created called "TestDatabase" on my desktop, and the table I created inside this database called "TestTable". And here is my code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseTest
{
    class Test
    {
        static void Main(string[] args)
        {
            // I don't know if my connection is correct or not. My access database is on my local desktop though
            string connectionString = "Data Source = (local); Initial Catalog = TestDatabase; Integrated Security = SSPI";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataReader reader = null;
                SqlCommand command = new SqlCommand("SELECT * from TestTable", connection);

                connection.Open();
                try
                {
                    reader = command.ExecuteReader();
                }
                catch (InvalidOperationException e)
                {
                    Console.WriteLine(e.ToString());
                }

                // print all the data in the table
                while (reader.Read())
                {
                    Console.Write(reader[0].ToString() + ", ");
                    Console.Write(reader[1].ToString() + ", ");
                    Console.Write(reader[2].ToString() + ", ");
                    Console.Write(reader[3].ToString() + ", ");
                    Console.WriteLine(reader[4].ToString());
                }
            }
            Console.ReadLine();
        }
    }
}

And here is my table looks like, if you wonder: (just a toy example)

ID   First Name  Last Name   Age     Friend
1    Leon        Ma          18      Yes
2    Amy         Jane        16      No
3    David       Zhang       20      No
4    Alan        Yue         19      Yes

However, it doesn't work, because nothing appear on my console. What did I do wrong. Really need some help. Thanks.

Do you have a SQL Server (or other DB) instance running on localhost? That's usually what "Data Source = (local)" is used for. Access is usually loaded with something like "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\filename.accdb". — Michael Todd, Jan 11 '16 at 16:49
Is there a reason why you're using Access instead of something like mysql? — devlin carnate, Jan 11 '16 at 16:53
@devlincarnate your question is opinion based in your comment not everyone these days uses the same `DBMS` — MethodMan, Jan 11 '16 at 16:59
[ConnectionStrings.com](http://www.connectionstrings.com/access-2010/) is a great website for just this sort of problem. Here's the [Access page](http://www.connectionstrings.com/access/). There A LOT of different options here. But if you scroll to the bottom of the page there links for each of the different versions of Access. — David Rushton, Jan 11 '16 at 17:17
Can you add `if(!reader.hasRow) { Console.WriteLine("empty"); }` and tell us what it is doing ? And please remove spaces in you connectionString and put the `connection.Open()` in the try-catch — Guillaume Beauvois, Jan 11 '16 at 17:23
@MethodMan - it's not opinion based. there are specific reasons why you would NOT choose Access and it has nothing to do with who likes what flavor of db best. — devlin carnate, Jan 11 '16 at 17:31
@devlincarnate I think you are missing the point I am well aware of the short comings with Access however what if that's the only option the OP has.. you can't dictate what a person should use for example I think that Sql Server should be used vs mySql.. does that make my opinion incorrect or correct..? Cheers...! — MethodMan, Jan 11 '16 at 18:17
@MethodMan - I suppose you might have missed why I asked the question. It's a common mistake for new devs to choose Access because they have no experience with RDBMS. My question was a question and not a statement -- because I thought it was worthwhile for the OP to consider the implications of the choice. I wasn't dictating anything. — devlin carnate, Jan 11 '16 at 18:25
no problem @devlincarnate I am not going to put too much thought into it.. it's all good — MethodMan, Jan 11 '16 at 18:27

MethodMan · Accepted Answer · 2016-01-11T16:58:01.020

you need something like the following to connect to Access DB

conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DbPath\SomeAccessFileName.accdb")

Config file would be setup like this for Standard security

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Persist Security Info=False;

Reference Link Access connection strings

Usage for creating the connection objects in Code Behind

using System.Data.Odbc;

using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("SELECT * from TestTable", connection) 
    using(OleDbDataReader reader = command.ExecuteReader())
    {
         while(reader.Read())
         {
            Console.Write(reader[0].ToString() + ", ");
            Console.Write(reader[1].ToString() + ", ");
            Console.Write(reader[2].ToString() + ", ");
            Console.Write(reader[3].ToString() + ", ");
            Console.WriteLine(reader[4].ToString());
         }
    }
}

Trying to connect to a Access database table in order to retrieve data, but encounter difficulties

1 Answers1