Custom embed the data source variable in the connection string

how do I custom call the data source of a connection string, the existing connection string is stored in the web.config file. I am using Oledb to connect to an Excel sheet uploaded to the server.

I have got my connection strings in the web.config file. Earlier, I was hard-coding the entire string including the data source location in the web.config file.

//this one works
<add name="testexcel1" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\usethis\WebApplication1\Uploaded Files\weekly output target.xlsx;Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;MAXSCANROWS=0'"/>
//I would like to specify the data source parameter in my code behind file.

<add name=“testexcel” connectionString=“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=‘Excel 8.0;HDR=No’”/>
//this is what I am trying to do, but it just does not work, the string gets concatenated wrongly.

string fileLocation = Server.MapPath(“~/Uploaded Files/” + filename);

builder.Provider = ConfigurationManager.ConnectionStrings[“testexcel1”].ConnectionString;

builder.DataSource = accept_filelocation;
//This is finally working

string connectionStringProvider =

ConfigurationManager.ConnectionStrings[“testexcel1”].ConnectionString;
    string dataSource = accept_filelocation;

    string connectionString = String.Format(connectionStringProvider, dataSource);



    OleDbConnection oledbConn = new OleDbConnection(connectionString);

#c-sharp #asp.net