======= start Error======
Server Error in '/' Application.
The 'Microsoft.ACE.OLEDB.4.0' provider is not registered on the local machine.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.4.0' provider is not registered on the local machine.
Source Error:
Line 50: if (con.State == ConnectionState.Closed) Line 51: { Line 52: con.Open(); Line 53: } Line 54: |
Source File: f:\IIS Applications\Dot Net\AuditBilling2010\frmexcelfile.aspx.cs Line: 52
Stack Trace:
[InvalidOperationException: The 'Microsoft.ACE.OLEDB.4.0' provider is not registered on the local machine.] System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) +1761342 System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1777302 System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +100 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +43 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +6296143 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6296606 System.Data.OleDb.OleDbConnection.Open() +48 frmexcelfile.Button1_Click(Object sender, EventArgs e) in f:\IIS Applications\Dot Net\AuditBilling2010\frmexcelfile.aspx.cs:52 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691 |
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1 =========End==========
MY Code Is
======== Start=======
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
public partial class frmexcelfile : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
string forthddate;
protected void Button1_Click(object sender, EventArgs e)
{
string strConnection1 = Convert.ToString(ConfigurationManager.ConnectionStrings["AuditBilling"]);
SqlConnection con1 = new SqlConnection(strConnection1);
con1.Open();
String strConnection = "ConnectionString";
string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Exel_Updata_Fiele/" + fileName);
FileUpload1.SaveAs(fileLocation);
if (fileExtension == ".xls")
{
//connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=Excel 8.0";
connectionString = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=Excel 8.0;Persist Security Info=False";
}
else if (fileExtension == ".xlsx")
{
connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
SqlCommand cmdtr = new SqlCommand("truncate table dbo.Temp_Insert_Exel_TBL", con1);
cmdtr.ExecuteNonQuery();
// ------Data Table Used Update Data---------------------
try
{
foreach (DataRow row in dtExcelRecords.Rows) // Loop over the rows.
{
string str1 = row["Bill No"].ToString();
string str2 = row["Associate Sign Date"].ToString();
//DateTime firstdate = DateTime.Parse(str2);
string firstdate = Convert.ToDateTime(str2).ToString("yyyy-MM-dd h:mm tt");
string str3 = row["Signed By Manager Date"].ToString();
string seconddate = Convert.ToDateTime(str3).ToString("yyyy-MM-dd h:mm tt");
//DateTime seconddate = DateTime.Parse(str3);
string str4 = row["Submitted to AC Date"].ToString();
string thirdddate = Convert.ToDateTime(str4).ToString("yyyy-MM-dd h:mm tt");
//DateTime thirdddate = DateTime.Parse(str3);
string str5 = row["Payment Release Date"].ToString();
if (str5 == "")
{
str5 = "NULL";
//string forthddate = Convert.ToDateTime(str5).ToString("yyyy-MM-dd h:mm tt");
}
else
{
forthddate = Convert.ToDateTime(str5).ToString("yyyy-MM-dd h:mm tt");
}
//DateTime forthddate = DateTime.Parse(str3);
string str6 = row["Status"].ToString();
string str7 = row["Cheque Date"].ToString();
string str8 = row["Cheque No"].ToString();
string str9 = row["Cheque Amount"].ToString();
string str10 = row["TDS Amount"].ToString();
int str6cn = 1;
//str6 = 1;DateTime date = DateTime.ParseExact(strDate, "dd/mm/YYYY", null)
//convert(varchar(20),GETDATE(),101)+ ' '+convert(varchar(20),GETDATE(),108)+ ' ' +right(convert(varchar(30),GETDATE(),109),0)
//SqlCommand update = new SqlCommand("UPDATE dbo.Temp_Insert_Exel_TBL set AssociateSigndate='" + str2 + "',manager_sign_dt='" + str3 + "',submitted_to_acct_dt='" + str4 + "',payment_release_dt='" + str5 + "',bill_status='" + str6 + "',Chq_dt='" + str7 + "',Chq_no='" + str8 + "',Chq_amt='" + str9 + "',TDS_Amt='" + str10 + "' WHERE BillNo='" + str1 + "'", con1);
//SqlCommand update = new SqlCommand("UPDATE dbo.Temp_Insert_Exel_TBL_update set AssociateSigndate='" + firstdate + "',manager_sign_dt='" + seconddate + "',submitted_to_acct_dt='" + thirdddate + "',payment_release_dt='" + forthddate + "',bill_status='" + str6cn + "',Chq_dt=" + str7 + ",Chq_no='" + str8 + "',Chq_amt='" + str9 + "',TDS_Amt='" + str10 + "' WHERE BillNo='" + str1 + "'", con1);
SqlCommand update1 = new SqlCommand("IF NOT EXISTS(SELECT BillNo from dbo.Temp_Insert_Exel_TBL_update where BillNo='" + str1 + "') " + "insert into dbo.Temp_Insert_Exel_TBL(BillNo,AssociateSigndate,manager_sign_dt,submitted_to_acct_dt,payment_release_dt,bill_status,Chq_dt,Chq_no,Chq_amt,TDS_Amt) VALUES('" + str1 + "','" + firstdate + "','" + seconddate + "','" + thirdddate + "','" + str5 + "','" + str6 + "','" + str7 + "','" + str8 + "','" + str9 + "','" + str10 + "')" + "else " + "UPDATE dbo.Temp_Insert_Exel_TBL_update set AssociateSigndate='" + firstdate + "',manager_sign_dt='" + seconddate + "',submitted_to_acct_dt='" + thirdddate + "',payment_release_dt='" + forthddate + "',bill_status='" + str6cn + "',Chq_dt=" + str7 + ",Chq_no='" + str8 + "',Chq_amt='" + str9 + "',TDS_Amt='" + str10 + "' WHERE BillNo='" + str1 + "'", con1);
update1.ExecuteNonQuery();
}
//MSGLABLE.Text = "Record Upload Success Fully";
gridviesbind();
}
catch (Exception ex)
{
MSGLABLE.Text = ex.Message;
}
// gridviesbind();
con.Close();
}
// string strConnection1 = Convert.ToString(ConfigurationManager.ConnectionStrings["AuditBilling"]);
//SqlConnection con1 = new SqlConnection(strConnection1);
//con1.Open();
//String strConnection = "ConnectionString";
//string connectionString = "";
//if (FileUpload1.HasFile)
//{
// string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
// string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
// string fileLocation = Server.MapPath("~/App_Exel_Updata_Fiele/" + fileName);
// FileUpload1.SaveAs(fileLocation);
// if (fileExtension == ".xls")
// {
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
// }
// else if (fileExtension == ".xlsx")
// {
// connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
// }
// OleDbConnection con = new OleDbConnection(connectionString);
// OleDbCommand cmd = new OleDbCommand();
// cmd.CommandType = System.Data.CommandType.Text;
// cmd.Connection = con;
// OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
// DataTable dtExcelRecords = new DataTable();
// con.Open();
// DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
// cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
// dAdapter.SelectCommand = cmd;
// dAdapter.Fill(dtExcelRecords);
// // ------Data Table Used Update Data---------------------
// try
// {
// foreach (DataRow row in dtExcelRecords.Rows) // Loop over the rows.
// {
// string str1 = row["Bill No"].ToString();
// string str2 = row["Associate Sign Date"].ToString();
// //DateTime firstdate = DateTime.Parse(str2);
// string firstdate = Convert.ToDateTime(str2).ToString("yyyy-MM-dd h:mm tt");
// string str3 = row["Signed By Manager Date"].ToString();
// string seconddate = Convert.ToDateTime(str3).ToString("yyyy-MM-dd h:mm tt");
// //DateTime seconddate = DateTime.Parse(str3);
// string str4 = row["Submitted to AC Date"].ToString();
// string thirdddate = Convert.ToDateTime(str4).ToString("yyyy-MM-dd h:mm tt");
// //DateTime thirdddate = DateTime.Parse(str3);
// string str5 = row["Payment Release Date"].ToString();
// string forthddate = Convert.ToDateTime(str5).ToString("yyyy-MM-dd h:mm tt");
// //DateTime forthddate = DateTime.Parse(str3);
// string str6 = row["Status"].ToString();
// string str7 = row["Cheque Date"].ToString();
// string str8 = row["Cheque No"].ToString();
// string str9 = row["Cheque Amount"].ToString();
// string str10 = row["TDS Amount"].ToString();
// int str6cn = 1;
// //str6 = 1;DateTime date = DateTime.ParseExact(strDate, "dd/mm/YYYY", null)
// //convert(varchar(20),GETDATE(),101)+ ' '+convert(varchar(20),GETDATE(),108)+ ' ' +right(convert(varchar(30),GETDATE(),109),0)
// //SqlCommand update = new SqlCommand("UPDATE dbo.Temp_Insert_Exel_TBL set AssociateSigndate='" + str2 + "',manager_sign_dt='" + str3 + "',submitted_to_acct_dt='" + str4 + "',payment_release_dt='" + str5 + "',bill_status='" + str6 + "',Chq_dt='" + str7 + "',Chq_no='" + str8 + "',Chq_amt='" + str9 + "',TDS_Amt='" + str10 + "' WHERE BillNo='" + str1 + "'", con1);
// SqlCommand update = new SqlCommand("UPDATE dbo.Temp_Insert_Exel_TBL_update set AssociateSigndate='" + firstdate + "',manager_sign_dt='" + seconddate + "',submitted_to_acct_dt='" + thirdddate + "',payment_release_dt='" + forthddate + "',bill_status='" + str6cn + "',Chq_dt=" + str7 + ",Chq_no='" + str8 + "',Chq_amt='" + str9 + "',TDS_Amt='" + str10 + "' WHERE BillNo='" + str1 + "'", con1);
// //SqlCommand update1 = new SqlCommand("IF NOT EXISTS(SELECT id_intrebare from Raspunsuri where id_intrebare=2) " + "Insert INTO Raspunsuri VALUES(@raspuns,@cnp,@data,2,@ip,@idsesiune) " + "else " + "UPDATE Raspunsuri SET raspuns=@raspuns,cod_numeric_personal=@cnp,data_raspuns=@data,id_intrebare=2,ip_user=@ip,id_sesiune=@idsesiune WHERE id_intrebare=2",con1);
// update.ExecuteNonQuery();
// }
// MSGLABLE.Text = "Record Upload Success Fully";
// }
// catch (Exception ex)
// {
// MSGLABLE.Text = ex.Message;
// }
//}
}
public void gridviesbind()
{
//MSGLABLE.Text = "These Recoed Are not uploded";
string strConnection0 = Convert.ToString(ConfigurationManager.ConnectionStrings["AuditBilling"]);
SqlConnection con0 = new SqlConnection(strConnection0);
con0.Open();
SqlCommand cmd0 = new SqlCommand("select * from dbo.Temp_Insert_Exel_TBL", con0);
SqlDataAdapter da = new SqlDataAdapter(cmd0);
DataSet ds0 = new DataSet();
da.Fill(ds0);
GridView1.DataSource = ds0;
GridView1.DataBind();
if (GridView1.Rows.Count == 0)
{
MSGLABLE.Text = "Record upload successfully";
}
else
{
MSGLABLE.Text = "These records Not Uploaded";
}
}
}
==== End===========
Please Help me.......