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

And what is the error message? I don't see it in your post...

Hi

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

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

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

From the Images you have posted It seems like you are using some provider other than DATA.SQLCLIENT.. YOu are using provider as SQLOLEDB which is not supported by SQLBULKCOPY.

The constructor you are trying to call, SqlBulkCopy(string) expects a valid connection string for SQL Server.

http://msdn.microsoft.com/en-us/library/8x2hdfta.aspx