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."
//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);
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Robot";
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.