Hi
I am trying to upload excel file data thorugh .net uploading code. But i am getting an error message. can any one please help me to resolve this.
Upload.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="add_residentupload.aspx.cs" Inherits="add_residentupload" %>
<table class="table-list">
<tr>
<td width="30%">Upload File</td>
<td><asp:FileUpload ID="fupUpload" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ErrorMessage="Select the File" ControlToValidate="fupUpload" CssClass ="warning"
Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td width="30%"><b>Note *</b> :<br />
(Fields should be in order)</td>
<td><br />
Association Name, Community Name, Lot number, Dwelling Number, Street,State, City, Zip, First Name1, Last Name1, First Name2, Last Name2, Email Address, Home Telephone, Cell Telephone1 , Cell Telephone2 <br />
<br />
<b>Mandatory Fields are : </b> Community, Lot number, Street, State, City, Zip, First Name1, Last Name1 , Email Address </td>
</tr>
<tr>
<td width="30%"></td>
<td><asp:Button ID="Butsubmit" runat="server" CssClass="save_but" Text="Save" OnClick="but_submit"
Height="30px" Width="65px" />
<asp:Button ID="Butexit"
runat="server" Text="Exit" CausesValidation="False" CssClass="send_but" OnClick="but_exit"
Height="30px" Width="65px" /></td>
</tr>
</table>
add_residentupload.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using System.Drawing;
using System.Data;
using System.Web.Security;
using System.Data.SqlClient;
public partial class add_residentupload : System.Web.UI.Page
{
OleDbDataReader dr;
public string ascn_name, com_name, name;
protected void Page_Load(object sender, EventArgs e)
{
if (Session["username"] == null || Session["ascn_name"] == null || Session["comname"] == null)
{
Response.Redirect("~/index.aspx");
}
name = Session["username"].ToString();
com_name = Session["comname"].ToString();
ascn_name = Session["ascn_name"].ToString();
//ascn_name=Request .QueryString ["ascn_name"];
}
//------------- FILE UPLOAD ------------ //
protected void upload()
{
if (fupUpload.HasFile)
{
FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
string ext = fi.Extension;
if (ext == ".xls" || ext == ".xlsx")
{
string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
string strFilepPath = DirectoryPath + fupUpload.FileName;
Directory.CreateDirectory(DirectoryPath);
fupUpload.SaveAs(strFilepPath);
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilepPath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
dr = cmd.ExecuteReader();
String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ToString();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "ascn_resident_list";
//sqlBulk.ColumnMappings.Add("AUTO_ID", "AUTO_ID");
sqlBulk.ColumnMappings.Add("association_name","association_name");
sqlBulk.ColumnMappings.Add("community_name", "community_name");
sqlBulk.ColumnMappings.Add("lot_number", "lot_number");
sqlBulk.ColumnMappings.Add("dwelling_number", "dwelling_number");
sqlBulk.ColumnMappings.Add("street", "street");
sqlBulk.ColumnMappings.Add("state", "state");
sqlBulk.ColumnMappings.Add("city", "city");
sqlBulk.ColumnMappings.Add("zip", "zip");
sqlBulk.ColumnMappings.Add("first_name1", "first_name1");
sqlBulk.ColumnMappings.Add("last_name1", "last_name1");
sqlBulk.ColumnMappings.Add("first_name2", "first_name2");
sqlBulk.ColumnMappings.Add("last_name2", "last_name2");
sqlBulk.ColumnMappings.Add("email_address", "email_address");
sqlBulk.ColumnMappings.Add("home_telephone", "home_telephone");
sqlBulk.ColumnMappings.Add("cell_telephone1", "cell_telephone1");
sqlBulk.ColumnMappings.Add("cell_telephone2", "cell_telephone2");
sqlBulk.WriteToServer(dr);
excelConnection.Close();
}
else if (ext == ".csv")
{
string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
string strFilepPath = DirectoryPath + fupUpload.FileName;
Directory.CreateDirectory(DirectoryPath);
fupUpload.SaveAs(strFilepPath);
StreamReader sr = new StreamReader(strFilepPath);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
}
}
String strConnection1 = ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ToString();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection1);
sqlBulk.DestinationTableName = "ascn_resident_list";
sqlBulk.BatchSize = dt.Rows.Count;
sqlBulk.WriteToServer(dt);
sqlBulk.Close();
}
}
}
//------------- INSERT FILE COMMAND ------------ //
protected void but_submit(object sender, EventArgs e)
{
upload();
Response.Redirect("resident_list.aspx");
}
//------------- EXIT COMMAND ------------ //
protected void but_exit(object sender, EventArgs e)
{
Response.Redirect("resident_list.aspx");
}
//------------- LOG OUT ------------ //
protected void DisableBufferingOnPage()
{
Response.Buffer = true;
Response.ExpiresAbsolute = DateTime.Now.AddDays(-1);
// set expiry date in the past
Response.Expires = -1;
Response.CacheControl = "no-cache";
Response.Cache.SetNoStore();
Response.AppendHeader("Pragma", "no-cache");
Response.AppendHeader("Cache-Control", "no-cache");
Response.CacheControl = "no-cache";
Response.Expires = -1;
Response.ExpiresAbsolute = new DateTime(1900, 1, 1);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Redirect("~/index.aspx");
}
protected void logout_Click(object sender, EventArgs e)
{
Session.Clear();
FormsAuthentication.SignOut();
DisableBufferingOnPage();
}
}
I am getting a error message on sqlBulk.WriteToServer(dr); in add_residentupload.aspx.cs line 87
