C# - unable to connect to a local ACCDB file with new SqlConnection()

Question

Doing a university project that requires an application that stores and outputs data from a local access DB file.

So far I have very little C# knowledge but I have a basic understanding of how to write code and what functions and methods are.

I have looked up a lot of different questions on StackOverflow and other sites about this kind of issue, yet I could not find a solution to my problem.

Here is the code -

private void search_db(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection())
    {
        string search_query = search_input.Text;
        conn.ConnectionString = "Data Source=D:/Projects/WIP/8515/Academy/Travel Agency C#/Hotel_Agency/Hotel_Database.accdb;";

        conn.Open();

        SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE (name LIKE @query) OR (EGN LIKE @query)", conn);
        command.Parameters.Add(new SqlParameter("query", search_query));

        // Create new SqlDataReader object and read data from the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // while there is another record present
            while (reader.Read())
            {
                // write the data on to the screen
                Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3}",
                    // call the objects from their index
                reader[0], reader[1], reader[2], reader[3]));
            }
        }
    }
}

on line 6 of the given code, if my ConnectionString which provides the data souce which I have triple checked and confirmed is proper, the only thing I'm not sure is what type of slash should I use, since the default \ slash from windows directories gets mistaken for an escape by Visual Studio, while with the / slash its ok.

The problem is the connection to the DB always fails with this error

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL

Here is the full list of the namespaces we are using for this application

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

I have tried adding provider keyword to the connection string but that leads to another error about an unsupported keyword, since the System.Data.SqlClient namespace is allegedly automatically setting a provider.

Any help as to what might be causing this is appreciated!

PS: I apologize for any further lack of knowledge I present, I really am new to C# programming, but I find it quite interesting and exciting, and would like to learn more.

Answer 1

You are making a common mistake here. The classes defined in the namespace System.Data.SqlClient (SqlConnection, SqlCommand etc.) are able to talk only with a Sql Server database system (Full, Express or LocalDb). They cannot work with an Access database.

For this database you should use the classes in the System.Data.OleDb namespace (OleDbConnection, OleDbCommand etc.) These classes understand the connectionstring to reach an Access database and can open and work with it.

So your code should be:

....
using System.Data.OleDb;

private void search_db(object sender, EventArgs e)
{
    using (OleDbConnection conn = new OleDbConnection())
    {
        conn.ConnectionString = ......
        conn.Open();
        string cmdText = @"SELECT * 
                           FROM Customers 
                           WHERE ([name] LIKE @q1) OR (EGN LIKE @q2)";
        using(OleDbCommand command = new OleDbCommand(cmdText, conn))
        {
            command.Parameters.Add("@q1", OleDbType.VarWChar).Value = search_query;
            command.Parameters.Add("@q2", OleDbType.VarWChar).Value = search_query;
            using (OleDbDataReader reader = command.ExecuteReader())
            {
               ....
            }
        }
    }
}

An importat thing to remember with OleDb is that you have positional parameters. The parameters are not recognized by their name but by their position in the query text. So if you have two parameters placeholder, even if they are for the same value, you need to put two parameters in the collection. One for the first placeholder and one for the second placeholder. This is even more important when you have placeholders for different values. You need to add the values in the exact order in which they are expected in the query text.