Invalid Connection String - XLS IO

[T]

Version: 19.4450.0.52

MVC 5

I am trying to established a connection to sql server from XLS IO but I keep getting invalid connection string with IConnection, I try same connection string with SqlDataReader and its working.

Debugging Result (see attached image):

- DBConnectionString is null

- SourceFile=localhost;

Did I construct the connection string wrongly? or it does not accept direct connection to sql server

Appreciate if someone can give guidance on how can I connect directly to sql server with XLS IO.

This is my code

var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

string query = "SELECT * FROM People";

IConnection connection = workbook.Connections.Add("DefaultConnection", "Sample connection with SQL Server", connectionString, query, ExcelCommandType.Sql);

Connection Strings

<add name="DefaultConnection" connectionString="Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

[Ramya Sivakumar]

Hi T, 

Greetings from Syncfusion. 

The User Id and Passwords seem to be wrong. In the blog post, we mentioned the UserId and Password as an example. So, you need to replace that with your User Id and Password. 

Kindly provide your Database User Id and Password similar to the following code snippet and let us know if this is helpful. 

Code snippet: 

//Connection string for DataSource 

string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + dataPath; 

//Adding a connection to the workbook 

IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql); 

Kindly refer to the following link to import the data from the database and let us know if this is working properly. And please refer the different data sources and their connection string formats supported in XlsIO. 

https://help.syncfusion.com/file-formats/xlsio/working-with-excel-tables#create-a-table-from-external-connection 

Regards, 

Ramya.