Cannot set Connection String to LocalDb using OLEDB

Question

Good morning guys,

I feel quite silly to ask this question, but I have looked everywhere and possibly at all questions in this matter and could not find a solution that would work for me.

Long story short. I am using a local database called TestDB.mdf in windows form application. The application is designed to do (as per current) two simple things.

1. Import data from excel document into the database - which I don't have any issues with.

and...

2. Clear all data stored in that database - this is where I am struggling

For the import data into the database (point 1) I am using two connection strings. One for excelConnectionString

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " +
                    "C:\\Users\\User.AR\\Desktop\\export.xls; " +
                    "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

and second for sqlConnectionString

string sqlConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\arkadiusz.rzepka\source\repos\Database_application\Database_application\TestDB.mdf;Integrated Security=True";

Then I use SqlBulkCopy to import all data and all is working like a charm.

Now the issue I can see is that I cannot open a connection to clear all data from the same database. I have navigated to properties of my database to find connection string and this has been presented in the below format:

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User.AR\source\repos\Database_application\Database_application\TestDB.mdf;Integrated Security=True

I have had to amend the above connection string as I was getting errors such as missing provider, should be like Provider=SQLOLEDB, after adding a provider, I have had to change Integrated Security = SSPI, and now I am getting error such as SQL Server does not exist or access denied

My code just to check if the connection was opened is presented below and I would be grateful if you could advise of what I am doing wrong.

    private void DeleteAllRecords()
    {
        string connectionString = @"Provider=Sqloledb;Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User.AR\source\repos\Database_application\Database_application\TestDB.mdf;Integrated Security=SSPI";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            try
            {
                connection.Open();
                MessageBox.Show("Connection openned successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

why not just use `SqlConnection` in this case? `OleDbConnection` is a useful backup for general sources such as Excel (your example), but you don't need to use it when you're talking to something more specific. Your code can use `DbConnection` (the abstract base class) so you can use the same code for both — Marc Gravell, Jun 29 '18 at 09:12
@MarcGravell Could you clarify please of how I set this connection up then as I haven't used it before? Do I need to set Entity Framework as well? — A.Rosso, Jun 29 '18 at 09:23
well, you haven't mentioned or shown anything EF-related, so I can't comment on that; but to answer the question: `using (DbConnection connection = new SqlConnection(connectionString))` - that's it — Marc Gravell, Jun 29 '18 at 09:43

score 2 · Answer 1 · answered Mar 18 '21 at 10:48

If you run this in the command prompt.

SqlLocalDB.exe i "MSSQLLocalDB"

You will get the instance pipe name. You should be able to use that in your OLEDB connection. This has worked for me when connecting Excel using OLEDB connection to my MSSQLLocalDB.

Name:               MSSQLLocalDB
Version:            13.1.4001.0
Shared name:
Owner:              Foo\Foo.Bar
Auto-create:        Yes
State:              Running
Last start time:    18/03/2021 09:00:34
Instance pipe name: np:\\.\pipe\LOCALDB#DA1FAFF6\tsql\query

So my final connection string in excel looked like this.

provider=SQLOLEDB;initial catalog=IpsosDC;data source=np:\\.\pipe\LOCALDB#DA1FAFF6\tsql\query

This information is gold! – Alielson Piffer Apr 08 '22 at 21:53 — Alielson Piffer, Apr 08 '22 at 21:53

score 0 · Answer 2 · answered Jun 29 '18 at 09:12

0

Try using double bar on the paths of your connection strings, instead of one. So it would be like this:

string connectionString = @"Provider=Sqloledb;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\User.AR\\source\\repos\\Database_application\\Database_application\\TestDB.mdf;Integrated Security=SSPI";

answered Jun 29 '18 at 09:12

Mário Leitão-Teixeira

86
1
1
5

Same error has accrued. DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied – A.Rosso Jun 29 '18 at 09:17
Do you have 'Sql Server Configuration Manager' installed on your computer? If so, open it and check if the server service is running or stopped, on the tab 'SQL Server Services'. If it's not running you can manually start it by right clicking on the desired icon. But like @MarcGravell said, try using [SqlConnection](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=vs.110).aspx) – Mário Leitão-Teixeira Jun 29 '18 at 09:21
1

Thanks for your suggestions, but SqlConnection will require connectionString as well... and this is where I am struggling as I am not sure that connectionString that I am using is 100% correct. Could someone clarify of how should I set the code for SqlConnection, please? – A.Rosso Jun 29 '18 at 09:31
Try doing: `SqlConnection connection = new SqlConnection(connection string);` Then, instead of the 'using': `if (connection.State != ConnectionState.Open) { try { connection.Open(); MessageBox.Show("Connection openned successfully!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }` – Mário Leitão-Teixeira Jun 29 '18 at 10:17

Cannot set Connection String to LocalDb using OLEDB

2 Answers2