john kanyora

john kanyora

  • 1.7k
  • 242
  • 33.3k

importing data from excel to a atble in the database

Aug 25 2017 5:50 AM
Hi. am writing a code to import data from excel to a table in the database but am getting the following errror;
"Format of the initialization string does not conform to specification starting at index 89"
below is my code behind and the webconfig respectively;
using System;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;



public partial class Smarttable : System.Web.UI.Page
{
OleDbConnection Econ;
SqlConnection con;

string constr, Query, sqlconn;
protected void Page_Load(object sender, EventArgs e)
{

}


private void ExcelConn(string FilePath)
{

constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\DAKTARI\\Desktop\\smarttable.xls;Extended Properties=''Excel 12.0 Xml;HDR=YES;''", FilePath);
Econ = new OleDbConnection(constr);

}
private void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;
con = new SqlConnection(sqlconn);

}


private void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);

Query = "SELECT [InvoiceNumber],[AmountPaid],[Remarks] FROM [Orders$]";
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();

DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "smarttable";
objbulk.ColumnMappings.Add("InvoiceNumber", "InvoiceNumber");
objbulk.ColumnMappings.Add("AmountPaid", "AmountPaid");
objbulk.ColumnMappings.Add("Remarks", "Remarks");
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();

}
protected void Button1_Click(object sender, EventArgs e)
{
string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
InsertExcelRecords(CurrentFilePath);
}
}
webconfig
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0"/>
</system.web>
<connectionStrings>
<add name="SqlCom" connectionString ="Data Source=SQLEXPRESS;Initial Catalog=SmartCare;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>

</connectionStrings>
</configuration>

......kindly assist 
 
 
 
 
 
 
 
 
 

Answers (2)