Abhishek Abhishek - 1 year ago 168
ASP.NET (C#) Question

Import Excel 2016 data to local DB using ASP.net & C# in VS 2015

I'm trying to import the data from Excel 2016 to a localDB created in VS2015 using ASP.net & C#.

Below is my code but when I run it, it gives me this error:

"A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 50 - Local Database Runtime error occurred.
Cannot create an automatic instance. See the Windows Application event
log for error details."

I'm able to connect to my localDB using Server Explorer.

Can anyone tell me what's wrong here?

if (File.Exists(savePath))
//string strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";
string strConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\inetpub\wwwroot\WebSite\App_Data\Database.mdf;Integrated Security=True;Context Connection=False";
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;Persist Security Info=False;";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Robot";

Answer Source

The error for sure is from the Connection String of SQL.

use (LocalDB)\\MSSQLLocalDB instead of (LocalDB)\MSSQLLocalDB

Notice the two back slashes required because \v and \t means special characters.

Also no need to use full DB Path when your mdf file is in AppData folder just use the fileName.mdf instead.

if you are unsure about your instance MSSQLLocalDB then

use Server=(localdb)\\v11.0;Integrated Security=true;Database=filename.mdf;

hope it helps.