Saturday, February 18

How to insert data in excel using asp.net?


My Excel sheet:-



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class ExcelInsert : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
//if you are using excel 2010 then this connectionstring
            string concn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/shibashish/Desktop/All Download/DemoData/SaveFilesInDatabase/SaveFilesInDatabase/Docs/Book3.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
//if you are not using excel 2010 then change  this connectionstring "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""
            OleDbConnection con = new OleDbConnection(concn);
            string cmdTExt = "Insert into [Sheet1$] (name,id) values('shibashish','kendrapara')";
            //DataSet ds = new DataSet();
            //OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]",con);
            //da.Fill(ds);
            //txtName.Text = ds.Tables[0].Rows[0][0].ToString();

            OleDbCommand cmd = new OleDbCommand(cmdTExt,con);
            cmd.CommandType = CommandType.Text;
            con.Open();
        int result=cmd.ExecuteNonQuery();
        lblError.Text = "Inserted succesfully";

        }
         catch
        {
            lblError.Text = "Error occured";
        }
    }
}

If it is giving error as 

Operation must use an updateable query error


Then just change your configuration as
Follow these steps to resolve this issue:
1. Use Windows Explorer to find the folder on the disk, which contains the database file (.mdb). This is usually your project folder.
2. Right-click on the project folder and select Properties.
3. Uncheck the Read-only property.
4. Click on the Security tab in the properties window.
(Attention: If you don't see such tab (Windows XP), close the properties window, open any folder, select Tools -> Folder Options -> View, and uncheck the option Use simple file sharing.)
5. Click Add and find, then select the user IUSR_<COMPUTERNAME>.
6. Select the Write checkbox in the Allow; column to assign Write permissions to the IUSR account.



More Info

After being fixed, this error may reoccur when compacting the MS Access file since the database file being compacted is being erased and a new one is created in its place.

Status

This issue doesn't require further action.
Thanks shibashish mohanty

2 comments:

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty