locked
insert the records into excel useing C# RRS feed

  • Question

  • User87195929 posted

    Hi All

    i am doing insert the records into excel .

    query is working fine, but records are  not inserting into Excel

     code is       like below

    try
    {
    	string fileLocation = Server.MapPath("~/Upload/Test_Web.xlsx");
    	System.Data.OleDb.OleDbConnection MyConnection;
    	System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    	strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;\"";
    	MyConnection = new System.Data.OleDb.OleDbConnection(strConn);
    	if (MyConnection.State.ToString() == "Closed")
    	{
    		MyConnection.Open();
    	}
    	myCommand.Connection = MyConnection;
    	string sql = "INSERT INTO [Sheet1$] ([username],[password]) VALUES('siddu', '4646')";
    	// string sql =""Insert into[Sheet1$] (username, password) values('siddu', '344')";
    	myCommand.CommandText = sql;
    	myCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    	// MessageBox.Show(ex.ToString());
    	throw ex;
    }

    please suggest me 

    thanks and regards

    siddu

    Thursday, January 4, 2018 2:07 PM

All replies

  • User632428103 posted

    Hello siddangoud,

    i've copy paste your code and in fact something is wrong : 

    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 xml;ReadOnly=False;HDR=Yes;\"";
                    string query = string.Format("INSERT INTO [sheet1$] ([username],[password]) VALUES('{0}', '{1}')", "data 1", "data 2");

    I have added readOnly=false into the connexion string and i have modified the file name of the sheet => delete the S in UPPER add a s in LOWER 

    On my desktop that's work fine

    Thursday, January 4, 2018 2:30 PM