Post
Ask Question

Insert Data into Excel or Access as a Database

//"To run this code errorfree please change/set component name what you have given at design time." 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.OleDb;                //Add this namepace to get object for OLEDB  
  9. public partial class addtoexcel : System.Web.UI.Page  
  10. {  
  11.       protected void Page_Load(object sender, EventArgs e)  
  12.       {  
  13.       }  
  14.       public void clearall()  
  15.       {  
  16.             txtnm.Text = "";  
  17.             txtadd.Text = "";  
  18.             txtcity.Text = "";  
  19.             txtphone.Text = "";  
  20.             txtemail.Text = "";  
  21.       }  
  22.       protected void btnsubmit_Click(object sender, EventArgs e)  
  23.       {  
  24.             //Excel Connection string  
  25.             //string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\\Users\\Administrator\\Desktop\            \Excel data.xlsx;Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";  
  26.   
  27.             //Access Connection string  
  28.             string constr = "Provider=Microsoft.ACE.OLEDB.12.0;; Data Source = C:\\Users\\Administrator\\Desktop\            \Acces sdata.accdb; Persist Security Info=False;";  
  29.             //Excel Querystring  
  30.             //string query = "Insert into [sheetname$] (Name, Address, City, Phone, EmailID) VALUES ('" + txtnm.T            ext +             "','" + txtadd.Text + "','" + txtcity.Text + "','" + txtphone.Text + "','" + txtemail.Text + "')";  
  31.             //Access Querystring  
  32.             string query = "Insert into youraccesstablename (Name, Address, City, Phone, EmailID) VALUES ('" + tx            tnm.Text + "','" + txtadd.Text + "','" + txtcity.Text + "','" + txtphone.Text + "','" + txtemail.Text             + "')";  
  33.             OleDbConnection con = new OleDbConnection(constr);  
  34.             if (con.State == ConnectionState.Closed)  
  35.             {  
  36.                   con.Open();  
  37.             }  
  38.             OleDbCommand cmd = new OleDbCommand(query,con);  
  39.             int result = cmd.ExecuteNonQuery();  
  40.             if (result > 0)  
  41.             {  
  42.                   Response.Write("<script>alert('Data Inserted Successfully.')</script>");  
  43.             }  
  44.             else  
  45.             {  
  46.                   Response.Write("<script>alert('Daata not Inserted successfully.')</script>");  
  47.             }  
  48.             con.Close();  
  49.             clearall();  
  50.       }  
  51. }