SQL connection string for microsoft access 2010 .accdb

Question

I am doing a simple login form using winforms and access 2010 database (.accdb) in C#.

I have the following code and it seems that the connection string is wrong. I have tried searching and found that .Jet is for access 07?? but this doesnt seem to work too. i am an amateur at databases (code referred from msdn). I am having trouble understand which should i use for this example too.

access table name: haha

ID (PK)  |   password
-----------------------
   1     |   testing

        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BC207\test.accdb");
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

        comm.CommandText = "SELECT HAHA(*) FROM password";
        comm.CommandType = CommandType.Text;
        comm.Connection = conn;

        conn.Open();

        Object returnValue = comm.ExecuteScalar();
        conn.Close();

        MessageBox.Show((string)returnValue);

edited: the table's name is password, and the field that i want to get the value is ID.

SQL statement i wrote it as : SELECT ID FROM password

and yes, only one record in only one field in the table as the primary key.

anyway the problem is that the program hangs upon execution on the first line
-> Keyword not supported: 'provider'.

so i figured that I have a wrong connection string..

Simon Mourier · Accepted Answer · 2011-10-14 08:23:36Z

9

For Acces databases (.mdb, .accdb, etc...), you want to use OleDbConnection, not SqlConnection (SQL Server), like this:

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

answered Oct 14, 2011 at 8:23

Simon Mourier

141k22 gold badges269 silver badges320 bronze badges

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

7 Comments

MoonKnight Over a year ago

I don't think the connection type in this case matters. It is SQL Server that the command will be parsed to reguardless...

Christian Specht Over a year ago

This is the only correct answer so far. @Killercam: SqlConnection works with SQL Server only, and here SQL Server is not involved at all, so your comment is wrong. SqlConnection doesn't work with MS Access!

Fionnuala Over a year ago

@Killercam Where do you get that idea?

Simon Mourier Over a year ago

@Killercam - OleDbConnection opens ... an OLE DB Connection using an OLE DB provider. It does not presume anything in terms of network, or the underlying system. It is not in any way related to SQL Server (there is in fact an OLE DB provider for SQL Server but we prefer ADO.NET SqlConnection when coding in .NET). Access files (.mdb and alike) are accessible through the JET driver which is also an OLE DB Provider. That's why the connection string requires the Provider key. There is no ADO.NET Access provider. OleDbConnection is not a sub class of SqlConnection (OleDbProvider does not exists).

MoonKnight Over a year ago

@Simon Mourier +1. Cheers for the explanation.

|

Davide Piras · Accepted Answer · 2011-10-14 11:48:21Z

2

Edit: as pointed out, for access OleDbConnection should be used, not SqlConnection...

you can use a much more compact way and also be sure connection is closed and disposed in any possible case even when exceptions are thrown, by using the using statements:

your query text was also, probably wrong as others have suggested...

using (var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BC207\test.accdb"))
using (var comm = conn.CreateCommand())
{
    comm.CommandText = "SELECT password FROM HAHA";
    comm.CommandType = CommandType.Text;

    conn.Open();

    var returnValue = comm.ExecuteScalar();

    MessageBox.Show(returnValue.ToString());
}

Edit: are you sure the table HAHA only contains one row? Because the ExecuteScalar returns only one value, if you want to get 1 column but from many records you could use a DataReader or a DataSet...

edited Oct 14, 2011 at 11:48

answered Oct 14, 2011 at 8:10

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

1 Comment

BOOnZ Over a year ago

ah yes, my entire table only consists of one record in a single field, to store a general password for my program to refer to. I have since changed the table name to "password" and field name to "ID" which is also the primary key.

mggSoft · Accepted Answer · 2011-10-14 08:06:52Z

0

comm.CommandText = "SELECT HAHA(*) FROM password";

It´s wrong.

"SELECT password FROM HAHA"

answered Oct 14, 2011 at 8:06

mggSoft

1,0522 gold badges24 silver badges37 bronze badges

1 Comment

BOOnZ Over a year ago

ah yes, my SQL statement was wrong and I have since changed it. but the problem is on the first line of the code and not the sql statement(did not execute till that far yet haha)

KV Prajapati · Accepted Answer · 2011-10-14 08:40:14Z

0

Your SQL statement should be,

SELECT * from HAHA

OR

 SELECT [Password] From HAHA

EDIT:

You should change the ConnectionString.

edited Oct 14, 2011 at 8:40

answered Oct 14, 2011 at 8:07

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

2 Comments

BOOnZ Over a year ago

ah yes, my SQL statement was wrong and I have since changed it. but the problem is on the first line of the code and not the sql statement(did not execute till that far yet haha)

KV Prajapati Over a year ago

@user776914 - You have to set appropriate connection string. I've edit my post.

Collectives™ on Stack Overflow

SQL connection string for microsoft access 2010 .accdb

4 Answers 4

7 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related