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..

Solution 1

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")

Solution 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...

Comments

[BOOnZ]

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.

[BOOnZ]

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)

[BOOnZ]

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)

[MoonKnight]

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

[KV Prajapati]

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

[nedhenry]

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]

@Killercam Where do you get that idea?

[MoonKnight]

OK. But the OleDbConnection object represents a unique connection to a data source - fine, but in this case this is equivalent to a network connection to the server that is the native OLE DB provider (in this case SQL Server). This is what I thought was the case? That is, when using the OleDbProvider, this is a sub-class of the SqlConnection?

[Simon Mourier]

@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]

@Simon Mourier +1. Cheers for the explanation.

[Saeid]

@SimonMourier I ask a related Question here: stackoverflow.com/questions/14475968/… , would you please check it