hello!
how to resolve this error and what does that means ?
The 'System.Data.SqlClient' provider is not registered on the local machine
Line 186: using (OleDbConnection excel_con = new OleDbConnection(conString))
Line 187: {
Line 188: excel_con.Open();
Line 189: String sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["ENGLISH"].ToString();
Line 190: DataTable dtExcelData = new DataTable();
.
Ramesh MaruthiPosted Jul 23, 2014, 1:48 PM
string excelpath =string.Concat(Server.MapPath(" ~ "+ Path.GetFileName(Filecom.PostedFile.FileName)));
conString = string.Format(conString, excelpath);
can you make these small changes and give a try
Abhsihek look at this link it has a neat explanation
http://www.cprogramdevelop.com/3531415/
Abhishek JaiswalPosted Jul 22, 2014, 11:30 PM
click event:
protected void Btncom_Click(object sender, EventArgs e)
{
// Providing Path
string excelpath = Server.MapPath(" ~ ") + Path.GetFileName(Filecom.PostedFile.FileName);
Filecom.SaveAs(excelpath);
string conString = string.Empty;
string extension = Path.GetExtension(Filecom.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelpath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
String sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["ENGLISH"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[7]
{
new DataColumn("Eng_Id", typeof(int)),
new DataColumn("Eng_Ques",typeof(string)),
new DataColumn("a",typeof(string)),
new DataColumn("b",typeof(string)),
new DataColumn("c",typeof(string)),
new DataColumn("d",typeof(string)),
new DataColumn("ECorrect_answer",typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.ENGLISH";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Eng_Id", "Eng_Id");
sqlBulkCopy.ColumnMappings.Add("Eng_Ques", "Eng_Ques");
sqlBulkCopy.ColumnMappings.Add("a", "a");
sqlBulkCopy.ColumnMappings.Add("b", "b");
sqlBulkCopy.ColumnMappings.Add("c", "c");
sqlBulkCopy.ColumnMappings.Add("d", "d");
sqlBulkCopy.ColumnMappings.Add("ECorrect_answer", "ECorrect_answer");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
Now please let me know what is the cause of this error:
The 'System.Data.SqlClient' provider is not registered on the local machine
Ramesh MaruthiPosted Jul 22, 2014, 2:00 PM
what is conString Abhisek ?
you gave as mycon right ?
And instead of this Data Source=Microsoft.Jet.OLEDB.12.0 can you try it with Microsoft.ACE.OLEDB.12.0
And make sure you have already installed Microsoft Access Database Engine
Abhishek JaiswalPosted Jul 22, 2014, 1:57 PM
Ramesh MaruthiPosted Jul 22, 2014, 1:10 PM
I think here comes the problem Abhisek your giving the provider name as sql.client and asking for the connection for oledb
can you give the provider as Provider=Microsoft.Jet.OLEDB.4.0;
or
can you change this line
Abhishek JaiswalPosted Jul 22, 2014, 1:01 PM
Ramesh MaruthiPosted Jul 22, 2014, 12:51 PM
if not check with this, is it same like this ?
OleDbConnection constring= new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\parodeghero\\Documents\\Visual Studio 2010\\Projects\\Project3\\Project3\\App_Data\\QA.mdb;Persist Security Info=True");
Abhishek JaiswalPosted Jul 22, 2014, 12:40 PM
these references are already in my namespace. what now ??
Ramesh MaruthiPosted Jul 22, 2014, 12:38 PM
Its an assembly reference dude you can add the assembly reference to your project try adding with the both assemblies System.Data.OleDb. and System.Data.SqlClient;
Abhishek JaiswalPosted Jul 22, 2014, 12:33 PM
where i need to use that class ??
pls let me know!
(bcuz i didn't used System.Data.OleDb.OleDbConnection class in my web)
Ramesh MaruthiPosted Jul 22, 2014, 9:35 AM
you can give the connection string as in below mentioned link
http://www.connectionstrings.com/sql-server-2008/#p1