Question About Gridview

Hi Hamid,

Try this...


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._Default" %>





   


   

   

   
           
               
                                        Text="Upload Excel Files" OnClick="ButtonUpload_Click" />
               
               
                                        Text="View Excel Data"  OnClick="ButtonView_Click" />
               
               
           
       
       
           
           

            Please select an Excel file to import:

                            Text="Upload File" OnClick="ButtonUploadFile_Click" />

           
       
       
           
                            BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
               
               
               
               
               
           
       
       
           
       
       
   

            Text="Insert to Db" />
       
       
   






using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Show_Excel_gridview_insert_to_db
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand com;

        protected void ButtonUploadFile_Click(object sender, EventArgs e)
        {
            if (FileUploadExcel.HasFile)
            {
                try
                {
                    FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
                    LabelUpload.Text = "Upload File Name: " +
                        FileUploadExcel.PostedFile.FileName + "
" +
                        "Type: " + FileUploadExcel.PostedFile.ContentType +
                        " File Size: " + FileUploadExcel.PostedFile.ContentLength +
                        " kb
";
                }
                catch (System.NullReferenceException ex)
                {
                    LabelUpload.Text = "Error: " + ex.Message;
                }
            }
            else
            {
                LabelUpload.Text = "Please select a file to upload.";
            }
        }
        protected OleDbCommand ExcelConnection()
        {

            string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection Con = new OleDbConnection(ConnStr);
            Con.Open();
            OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);
            return com;

        }
        protected void ButtonView_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            PanelImport.Visible = false;

            OleDbDataAdapter oledbda = new OleDbDataAdapter();
            oledbda.SelectCommand = ExcelConnection();
            DataSet ds = new DataSet();
            oledbda.Fill(ds);
            GridViewExcel.DataSource = ds.Tables[0].DefaultView;
            GridViewExcel.DataBind();
        }
        protected void ButtonUpload_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;
        }

        protected void btn_insert_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow g1 in GridViewExcel.Rows)
            {
                SqlConnection con = new SqlConnection(connStr);
                com = new SqlCommand("insert into student(sid,sname,smarks,saddress) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con);
                con.Open();
                com.ExecuteNonQuery();
                con.Close();

            }
            Label1.Text = "Records inserted successfully";
        }
    }
}




Thanks
If this post helps you mark it as answer

Hi,

You no need to save the grid view.
The data is stored in the data table and which binded with the gridview control.

You need to iterate the datatable and insert the record into sql server.