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

Re: Upload an data in excel file using ASP.NET into MSSQL database 80 80

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

Re: Upload an data in excel file using ASP.NET into MSSQL database 80 80

Hi

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

Re: Upload an data in excel file using ASP.NET into MSSQL database 80 80

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

Re: Upload an data in excel file using ASP.NET into MSSQL database 80 80

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

Re: Upload an data in excel file using ASP.NET into MSSQL database 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.