Why connecting to an OLEDB is giving me an connection error

Question

I have the following code which is connecting to my database and retrieving some data from a table:

    string connectionString = "Data Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=Db;Integrated Security=FALSE;user=zh;pwd=zh12;";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();

    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "SELECT [Location], [URL], [TAGS] FROM [Db].[dbo].[BOOKINGTABLE]";
    command.CommandType = CommandType.Text;

    using (OleDbDataReader reader = command.ExecuteReader())
    {
        menu_ul_1.DataSource = reader;
        menu_ul_1.DataBind();
    }
}

I get the following error:

Exception Details: System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

When I change the connectionstring line to:

string connectionString = "Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=Db;Integrated Security=FALSE;user=zh;pwd=zh12;";

I get the following error:

Exception Details: System.Data.OleDb.OleDbException: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

Source Error: 


Line 23: using (OleDbConnection connection = new OleDbConnection(connectionString))
Line 24: {
Line 25:     connection.Open();
Line 26: 
Line 27:     OleDbCommand command = new OleDbCommand(); 

How can I resolve the issue?

My Web.config file has the following line:

<add key="ConnStringTEST" value="Data Source=myserver;Initial Catalog=Db;Integrated Security=FALSE;user=zh;pwd=zh12;" />

How, If, I can use the above line in my C# code?

Answer 1

After much troubleshooting, I was able to figure out why it wasn't working. I rewrote the string like this:

string cString = "Provider=sqloledb;Data Source=myserver;Initial Catalog=mydatabase;User Id=myid;Password=mypassword;";

That worked like a charm, in case someone else is having the same issue.