C# MS Access Database Connection System.Data.SqlClient.SqlException' occurred in System.Data.dll

Question

I am not sure if this problem has anything to do with my code or simply the way that my database is set up. Any pointers would be awesome!

This is the error message that I get:

enter image description here

I have gone to "Modify Connection" and used the 'Test Connection' tool and that says that it connects fine but when the actual program runs nothing happens and I get the error.

Here is my code:

    private void btnAddCustomer_Click(object sender, EventArgs e)
    {
    SqlConnection CustomerInfo = new SqlConnection("Data Source=C:\\Users\\Cory\\Desktop\\DeluxWrapsWindows\\DeluxWrapsWindows\\DeluxWraps_DB.mdb");
    { 
    SqlCommand xp = new SqlCommand("Insert into CustomerInfo(LastName, FirstName, Email, PhoneNumber, Address, Instagram, CarMake, CarModel, AdditionalNotes) Values(@LastName, @Firstname, @Email, @PhoneNumber, @Address, @Instagram, @CarMake, @CarModel, @AdditionalNotes)", CustomerInfo);

     xp.Parameters.AddWithValue("@LastName", txtLastName.Text);
     xp.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
     xp.Parameters.AddWithValue("@Email", txtEmail.Text);
     xp.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text);
     xp.Parameters.AddWithValue("@Address", txtAddress.Text);
     xp.Parameters.AddWithValue("@Instagram", txtInstagram.Text);
     xp.Parameters.AddWithValue("@Carmake", txtCarMake.Text);
     xp.Parameters.AddWithValue("@CarModel", txtCarModel.Text);
     xp.Parameters.AddWithValue("@AdditionalNotes", txtAdditionalNotes.Text);

     CustomerInfo.Open();
     xp.ExecuteNonQuery();
     CustomerInfo.Close();

    }
}

Sqlconnection should only be used when trying to connect to a SQL server database. See this link to see more information about this kind of connection. msdn.microsoft.com/en-us/library/…. To connect to a MS Access database take a look at this SO post stackoverflow.com/questions/17023861/…. — WorkSmarter
– WorkSmarter, Commented Mar 2, 2015 at 23:51

Renatto Machado · Accepted Answer · 2015-03-03 00:13:47Z

3

You should try create the SqlCommand with:

SqlCommand xp = CustomerInfo.CreateCommand();

See this example:

https://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlconnection.createcommand(v=vs.110).aspx

Update:

Try use OleDbConnection. See:

public DataSet GetDataSetFromAdapter(
    DataSet dataSet, string connectionString, string queryString)
{
    using (OleDbConnection connection =
               new OleDbConnection(connectionString))
    {
        OleDbDataAdapter adapter =
            new OleDbDataAdapter(queryString, connection);

        // Set the parameters.
        adapter.SelectCommand.Parameters.Add(
            "@CategoryName", OleDbType.VarChar, 80).Value = "toasters";
        adapter.SelectCommand.Parameters.Add(
            "@SerialNum", OleDbType.Integer).Value = 239;

        // Open the connection and fill the DataSet. 
        try
        {
            connection.Open();
            adapter.Fill(dataSet);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the 
        // code exits the using block.
    }
    return dataSet;
}

See more details in: https://msdn.microsoft.com/en-us/library/System.Data.OleDb.OleDbParameterCollection(v=vs.110).aspx

edited Mar 3, 2015 at 0:13

answered Mar 2, 2015 at 23:47

Renatto Machado

1,5842 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cory Hull Over a year ago

Thanks for the advice Renatto Machado! I am still getting the same error though. For some reason it will not connect to the database.

Eric Bowser · Accepted Answer · 2015-03-02 23:56:37Z

0

It sounds like your trying to use a local database or auto-named database: Here is the syntax for your connection string:

Data Source=(LocalDB)\v11.0;
AttachDbFilename=Your local location here;
Integrated Security=True

Hope that helps.

answered Mar 2, 2015 at 23:56

Eric Bowser

627 bronze badges

4 Comments

Eric Bowser Over a year ago

Also, you will need to specify the local db verson of sql server. Mine is version v11.0.

Cory Hull Over a year ago

I am using MS Access 2010. This is the first time that I have tried to connect to a database like this so thank you for your help. The Database is on my local machine.

Eric Bowser Over a year ago

In that case your connection string with need to use a different driver because you will be using Microsoft.Jet.OLEDB.4.0. Here is a connection string example: connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb;" msdn.microsoft.com/en-us/library/hktw939c%28v=vs.85%29.aspx. Just make sure that your .mdb file goes in your data directory and replace the name with your own .mdb file name.

Cory Hull Over a year ago

Thanks Eric! I will try this as soon as possible.

Fandango68 · Accepted Answer · 2018-01-20 03:39:08Z

0

The problem is you're using System.Data.Sql as the provider, when it should all be coming from System.Data.OleDb;

However, you can use <asp:SqlDataSource> in ASPX, to connect to an Access Database, but that is not the same thing as System.Data.Sql.

answered Jan 20, 2018 at 3:39

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

Collectives™ on Stack Overflow

C# MS Access Database Connection System.Data.SqlClient.SqlException' occurred in System.Data.dll

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related