Cannot set Connection String to LocalDb using OLEDB

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.

TestDB.mdf

and...

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

excelConnectionString

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " +
"C:UsersUser.ARDesktopexport.xls; " +
"Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";


and second for sqlConnectionString

sqlConnectionString

string sqlConnectionString = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=c:usersarkadiusz.rzepkasourcereposDatabase_applicationDatabase_applicationTestDB.mdf;Integrated Security=True";


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

SqlBulkCopy

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:

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

missing provider, should be like Provider=SQLOLEDB

Integrated Security = SSPI

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:UsersUser.ARsourcereposDatabase_applicationDatabase_applicationTestDB.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 at 9:12

SqlConnection

OleDbConnection

DbConnection

@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 at 9: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 at 9:43

using (DbConnection connection = new SqlConnection(connectionString))

1 Answer
1

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:UsersUser.ARsourcereposDatabase_applicationDatabase_applicationTestDB.mdf;Integrated Security=SSPI";

string connectionString = @"Provider=Sqloledb;Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersUser.ARsourcereposDatabase_applicationDatabase_applicationTestDB.mdf;Integrated Security=SSPI";

Same error has accrued. DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied
– A.Rosso
Jun 29 at 9: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
– Mário Teixeira
Jun 29 at 9:21

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 at 9: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 Teixeira
Jun 29 at 10:17

SqlConnection connection = new SqlConnection(connection string);

if (connection.State != ConnectionState.Open) { try { connection.Open(); MessageBox.Show("Connection openned successfully!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.