Issues with SQL Client Library and Connection in C#

Question

I am having too many issues regarding how to connect using the right data provider:

Here is the code below:
using System;
using System.Data.SqlClient;

namespace CSharpConsole003
{
    class Program
    {
        static void Main(string[] args)
        {
            //This line of code generates a data runtime error.


            string ConnectionString = "C:/Users/POS/[DataFileName].mdb";



            string SQL = "SELECT * FROM Glossaries";

            SqlConnection conn = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand(SQL, conn);
            conn.Open();


            SqlDataReader reader = cmd.ExecuteReader();

            Console.WriteLine("GlossaryNumber", "GlossaryCategory");
            Console.WriteLine("=============================");

            while (reader.Read())
            {
                Console.Write(reader["GlossaryCategory"].ToString());

            }

            reader.Close();
            conn.Close();

        }
    }
}

Users are willing to help me at their convenience.

Regards,

JohnCTX

Answer 1

Since I am more familiar with Microsoft Access Programming interface, I prefer the DAO Data Access Object library.

1. Access the DBEngine.
2. Access the Database.
3. Access the Database's Recordset.
4. Did a While Loop structure control block and displayed the records from that corresponding table.
5. Closed the Recordset and Database. using DAO; void main()
   {
   DBEngine DBE=new DBEngine();
   Database DTX = DBE.OpenDatabase("C:/Users/POS/[DataBaseFileName].mdb");
   Recordset RECSET = DTX.OpenRecordset("Glossaries");

           Console.WriteLine("GlossaryCategory");
           Console.WriteLine("=============================");
   
           while (!RECSET.EOF)
           {
               Console.WriteLine(RECSET.Fields[1].Value);
               RECSET.MoveNext();
           }
   
   
           RECSET.Close();
           DTX.Close();
   

   }

Regards,
JohnCTX

Answer 2

using System.Data.SqlClient;

In addition to changing the connection string as in @Deependra Khangarot answer, you will need to use objects in the System.Data.Odbc namespace (e.g. OdbcConnection, OdbcCommand, OdbcDataReader) instead of System.Data.SqlClient. SqlClient is for Microsoft SQL Server whereas System.Data.Odbc uses ODBC drivers and System.Data.OleDb uses OleDb providers. Connection strings vary for each but, since all implement the ADO.NET IDb interfaces, the properties and methods are similar.

Answer 3

Hi John,

Please check the connection string, it should be something like below one:

myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" +
"Dbq=C:/Users/POS/[DataFileName].mdb;Uid=Admin;Pwd=;

Answer 4

Hello,

I have a GitHub code sample were you can clone it or simply copy the code below. In this case the database resides in the same folder as the application executable using a Windows Form project and will work also in a Control project by altering the code a little.

Important

The table name and column name is not my choice was done for another forum question.

Class for data operations

Note the connection string as a public variable, could also be private or protected.

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Access_mdb
{
    public class DataOperations
    {
        public static string ConnectionString = 
            "Provider=Microsoft.Jet.OLEDB.4.0;" + 
            $"Data Source={Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db1.mdb")};";

        public static List<string> Read()
        {

            var list = new List<string>();

            using (var cn = new OleDbConnection() {ConnectionString = ConnectionString})
            {
                using (var cmd = new OleDbCommand() {Connection = cn})
                {
                    cmd.CommandText = "SELECT [Consonant Determiner Required] FROM DeterminersWithA;";

                    cn.Open();

                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        list.Add(reader.GetString(0));
                    }
                }
            }

            return list;
        }
    }
}

Form code

namespace Access_mdb
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void LoadDataButton_Click(object sender, EventArgs e)
        {
            var cb = new ComboBox()
            {
                Left = 10, 
                Top = 10, 
                DataSource = DataOperations.Read()
            };

            Controls.Add(cb);
        }
    }
}

Data class modified

Here instead of doing a list the data is displayed to the console.

public static void Read()
{

    using (var cn = new OleDbConnection() { ConnectionString = ConnectionString })
    {
        using (var cmd = new OleDbCommand() { Connection = cn })
        {
            cmd.CommandText = "SELECT [Consonant Determiner Required] FROM DeterminersWithA;";

            cn.Open();

            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            }
        }
    }

}

Note

Unless there is a really good reason for using .mdb move up to .accdb or better yet use SQL-Server LocalDb or SQL-Server Express edition.