Error in SQL Server

Question

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 Maruthi · Answer

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 If you haven't, please download and install it. http://www.microsoft.com/en-us/download/details.aspx?id=13255

Ramesh Maruthi · Answer

for xls for xlsx " Provider =Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0 " ;HDR=YES'"/> 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 Jaiswal · Answer

THIS IS COMPLETE modified SCENARIO 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

Abhishek Jaiswal · Answer

i already tried that, but getting same error!

Ramesh Maruthi · Answer

providerName="system.data.sqlclient" 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 using (SqlConnection excel_con = new SqlConnection (conString)) {

Abhishek Jaiswal · Answer

yeah sure , it is Microsoft.Jet.OLEDB.12.0 ;Initial Catalog=Campus;Integrated Security=True" providerName="system.data.sqlclient"/>

Ramesh Maruthi · Answer

Abhishek can u paster constring here if its possible ? 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 Jaiswal · Answer

heyy! these references are already in my namespace. what now ??

Ramesh Maruthi · Answer

Hey Abhishek, 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 Jaiswal · Answer

heyy ! 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 Maruthi · Answer

The Microsoft Jet Engine is the database engine used in Microsoft Access. The error simply means that the required Access version is not installed and thus, your application cannot open the OLEDB driver. Generally, you should probably just use the System.Data.SqlClient.SqlConnection class instead of the System.Data.OleDb.OleDbConnection class to connect to your SQL Server 2008 database. you can give the connection string as in below mentioned link http://www.connectionstrings.com/sql-server-2008/#p1

Error in SQL Server

The 'System.Data.SqlClient' provider is not registered on the local machine

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (11)