0

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"   />
      &nbsp;&nbsp;&nbsp;&nbsp;
      <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

Edited by rpv_sen: update some information

4
Contributors
5
Replies
25
Views
3 Years
Discussion Span
Last Post by geniusvishal
0

Hi

Thanks for your reply. i have enclosed screen shot for error c51c6e2ae9111d2f32f6be69e1e159ea

Attachments Untitled.jpg 401.41 KB
0

I don't see anything wrong with your connection string, but are you sure the path to the file is correct?

0

Yes, first path is corrent. When i am testing it debugging option i am getting the below error. i have enclosed screen shot for reference.
a97026f72e9232d88c4b9f0cb21e8f1e

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.