3

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?

Si8
  • 9,141
  • 22
  • 109
  • 221
  • 1
    Don't use OleDb for SQL Server connections. Use SqlClient, and refer to http://www.connectionstrings.com/sql-server/ for connection strings – Yuriy Galanter May 09 '14 at 17:45
  • @YuriyGalanter - Why do you say so ? I used to use `Provider=SQLNCLI10` and Provider=SQLNCLI.1 or something like that without knowing why. It just worked. – Erran Morad May 09 '14 at 17:47
  • 1
    @BoratSagdiyev How do I know which is mine? – Si8 May 09 '14 at 17:51
  • @SiKni8 - I'd like to know the answer of your question too. You can try to use this website to generate your strings - http://www.developerfusion.com/tools/sql-connection-string/. Better yet, try some of the strings given in yuriy's link. – Erran Morad May 09 '14 at 17:55
  • @SiKni8 - If you have visual studio, then you can try this video to create the string - http://www.youtube.com/watch?v=OEcd5oRRPmM More here - http://stackoverflow.com/questions/10479763/how-to-get-the-connection-string-from-a-database – Erran Morad May 09 '14 at 17:59
  • It shows how to create a SQL connection string. I would like to get the OLEDB connection string. – Si8 May 09 '14 at 18:01
  • Standard connection string seems to work fine for me... but I need the OLEDB string... – Si8 May 09 '14 at 18:02
  • Did you try `Username=xyz;Password=klm`? And check http://connectionstrings.net for examples of almost every type of connection string imaginable. – Lasse V. Karlsen May 09 '14 at 18:06
  • Think it's the connection issue because that didn't help either. Will check out the website. Thanks. – Si8 May 09 '14 at 18:16
  • @SiKni8 - Here is a sample oledb connection string from ssis application. Edit is as per your environment and see. `Data Source=MyPc;User ID=Maxim;Initial Catalog=AdventureWorks2008;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS-Package-{blah}MyPc.AdventureWorks2008.Maxim;Auto Translate=False;` In some connections, provider can also be `Provider=SQLNCLI10` – Erran Morad May 09 '14 at 18:47
  • I added an answer to my question on how I solved it :) Hope it helps other users. – Si8 May 09 '14 at 19:36

3 Answers3

2

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.

Si8
  • 9,141
  • 22
  • 109
  • 221
2

Don't use "Integrated Security" when you are supplying the user ID and password.

Ujjwal Vaish
  • 373
  • 1
  • 7
  • 21
0

Using this video mentioned by Borat in the comments I was able to reference differences in the connection string to adjust mine. The video demonstrates windows authentication, so if that's not what you want be sure to add your own user id and password.

My issue was my provider attribute was referencing: "Provider=IBMDASQL.DataSource.1" when connection to DB/2 but the connection string when viewed as shown in the video was referencing, "IBMDA400.DataSource.1"

Funny, after watching the video, I already knew this, and have used this method but have forgotten. How quickly we forget things.

eaglei22
  • 2,589
  • 1
  • 38
  • 53