User-1647172364 posted
Hlo
In my code i want to import data from excel to gridview now i want to save gridview data to database but an error occur
"Object reference is not set to an instance of an object"
Please execute it
here is my code
Aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="Dept Desg.aspx.cs" Inherits="WebApplication14.Dept_Desg" %>
<asp:Content ID="Content1" ContentPlaceHolderID="title" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="body" runat="server">
<div style="margin-top:30px; margin-left:20px">
<table>
<tr><td>
<asp:FileUpload ID="FileUpload1" runat="server" /></td>
<td>
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" BackColor="#3366CC" BorderColor="#3366CC" ForeColor="White" />
</td>
<br />
<br />
</tr>
<asp:GridView ID="Gv8" runat="server" OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true" CellPadding="4" ForeColor="#333333" GridLines="Both">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Upload" OnClick="btnUpload1_Click" BackColor="#3366CC" BorderColor="#3366CC" ForeColor="White" />
</table>
</div>
</asp:Content>
C#
using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
namespace WebApplication14
{
public partial class Dept_Desg : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension);
}
}
private void Import_To_Grid(string FilePath, string Extension)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, 1);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
Gv8.DataSource = dt;
Gv8.DataBind();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = Gv8.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension);
Gv8.PageIndex = e.NewPageIndex;
Gv8.DataBind();
}
protected void btnUpload1_Click(object sender, EventArgs e)
{
// create some string variables and assign null values
string temp1 = "";
string temp2 = "";
string temp3 = "";
string temp4 = "";
string temp5 = "";
string temp6 = "";
string temp7 = "";
string temp8 = "";
//string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
// assign session object data to myfile_name variable
string myfile_name = Session["temp1"].ToString();
// get complete path of excel sheet and assing it Excel_path variable
string Excel_path = Server.MapPath(myfile_name);
// create connection with excel database
OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
my_con.Open();
try
{
// get the excel file data and assign it in OleDbcoomad object(o_cmd)
OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);
// read the excel file data and assing it o_dr object
OleDbDataReader o_dr = o_cmd.ExecuteReader();
while (o_dr.Read())
{
//get first row data and assign it ex_id variable
temp1 = o_dr[0].ToString();
//get second row data and assign it ex_name variable
temp2 = o_dr[1].ToString();
//get thirdt row data and assign it ex_name variable
temp3 = o_dr[2].ToString();
//get first row data and assign it ex_location variable
temp4 = o_dr[3].ToString();
temp5 = o_dr[4].ToString();
temp6 = o_dr[5].ToString();
temp7 = o_dr[6].ToString();
temp8 = o_dr[7].ToString();
// create a connection string with your sql database
SqlConnection con = new SqlConnection("server=SANAMDEEP;uid=sa;pwd=sa123;database=School");
con.Open();
//insert excel data in student table
SqlCommand cmd = new SqlCommand("insert into tbl_dept_desg values(@a,@b,@c,@d,@e,@f,@g,@h)", con);
cmd.Parameters.AddWithValue("a", temp1);
cmd.Parameters.AddWithValue("b", temp2);
cmd.Parameters.AddWithValue("c", temp3);
cmd.Parameters.AddWithValue("d", temp4);
cmd.Parameters.AddWithValue("e", temp5);
cmd.Parameters.AddWithValue("f", temp6);
cmd.Parameters.AddWithValue("g", temp7);
cmd.Parameters.AddWithValue("h", temp8);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
// Label1.Text = "Data inserted successfully";
}
con.Close();
}
}
catch (Exception )
{
//Label1.Text = ex.Message;
}
}
}
}
