Click here to Skip to main content
15,911,711 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This code is running properly in local machine
As in local machine i can upload and save that file in a path.

but how do that in server .
changing the
Data Source=.\SQLEXPRESS
to
Data Source=10.129.10.1

but it doesnot work to upload the file in server machine
SO i want to read the excel file ...
and get the data into SQL server(table)in server

webconfig:


XML
<appSettings>
   <add key="FolderPath" value="Files/"/>
 </appSettings>
 <connectionStrings>
   <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
   <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
   <add name="myConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=rdba; User ID=rw;pwd=rw@123; " providerName="System.Data.SqlClient"/>
 </connectionStrings>





code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;
using System.IO;
using System.Drawing;
using System.Configuration;

public partial class ImportExcel : System.Web.UI.Page
{

    string con_str = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
   
    
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
    }


    protected void Button1_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, rbHDR.SelectedItem.Text);


        }
    }
         

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                         .ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                          .ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();

        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        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);


        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();


        //insert to database 
        OleDbDataReader dr = cmdExcel.ExecuteReader();
        // DbDataReader dr = cmd.ExecuteReader();

        SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
        bulkInsert.DestinationTableName = "DBtable";
        bulkInsert.WriteToServer(dr);
        //Array.ForEach(Directory.GetFiles((Server.MapPath("FolderPath"))), File.Delete);
        connExcel.Close();
    }

}
Posted
Comments
Prasad Avunoori 25-Sep-14 4:29am    
Got any errror/
10923679 25-Sep-14 5:28am    
Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customerrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customerrors> tag should then have its "mode" attribute set to "Off".


<!-- Web.Config Configuration File -->


<system.web>
<customerrors mode="Off">


Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customerrors> configuration tag to point to a custom error page URL.


<!-- Web.Config Configuration File -->

<c>
<system.web>
<customerrors mode="RemoteOnly" defaultredirect="mycustompage.htm">



this is the error i am getting
[no name] 25-Sep-14 6:24am    
Did you read the error message and do what the error tells you to do so that you can see the actual error message that you are getting?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900