locked
Uploading Excel data using ASP.Net to SQLServer. RRS feed

  • Question

  • User45730163 posted

    Hi I am completely new in ASP.net, I tried to use one code that suresh has posted in this forum earlier with little change but having bellow errors.

    Code I have used:

    using System;

    using System.Collections.Generic;

    using System.Data.OleDb;

    using System.Data.SqlClient;

    using System.Linq;

    using System.Web;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.IO;

     

    public partial class _Default : System.Web.UI.Page

    {

    private String strConnection ="Data Source=SENPWM10519387\\SQLEXPRESS;Initial Catalog=QATest;Integrated Security=True";

    protected void Page_Load(object sender, EventArgs e)

        {

        }

     protected void btnSend_Click(object sender, EventArgs e)

        {

           //file upload path

         string path = fileuploadExcel.PostedFile.FileName;

         /Import Data Type check

    string ddlValue = ddlImportType.SelectedValue; 

    //Create connection string to Excel work book 

    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

    //Create Connection to Excel work book

     OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

     //Create OleDbCommand to fetch data from Excel

     if (ddlValue ='DUComp')

            {

     OleDbCommand cmd = new OleDbCommand("Select [Deployment Unit],[Deployment Scenario],[Wave Plan], [User ID], [Country], [Computer Name (Old)], [New Computer Type], [New Tag], [Application Readiness] from [Deployment Unit Computer$]", excelConnection);

      excelConnection.Open();

     OleDbDataReader dReader;

     dReader = cmd.ExecuteReader();

     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

     //Give your Destination table name

         sqlBulk.DestinationTableName ="DUComputer";

         sqlBulk.WriteToServer(dReader);

          excelConnection.Close();

         }

        else if (ddlValue = 'DUApp')

            {

        OleDbCommand cmd = new OleDbCommand("Select  [User ID], [Application], [Status], [Computer Name (Old)], [Package-ID] from [Deployment Unit Apps$]", excelConnection);

        excelConnection.Open();

        OleDbDataReader dReader;

        dReader = cmd.ExecuteReader();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

        //Give your Destination table name

         sqlBulk.DestinationTableName ="DUApplications";

                sqlBulk.WriteToServer(dReader);

                excelConnection.Close();

            }

    else

            {

            }

        }

    }

     

    Design Page:--------------------------------------------------------------------------------------------------------------------------------------

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head id="Head1" runat="server">

    <title></title>

    </head>

    <body>

     <form id="form1" runat="server"> 

     <div>

     <table>

    <tr>

    <td>

    <span style="color: Red">*</span>Select Import Data Type

    </td>

    <td>

    <asp:DropDownList ID="ddlImportType" runat="server" AutoPostBack="true">

        <asp:ListItem Text="--Select Import Type--" Selected="True"></asp:ListItem>

        <asp:ListItem Text="Deployment Unit Computers" Value="DUComp"></asp:ListItem>

       <asp:ListItem Text="Deployment Unit Applications" Value="DUApp"></asp:ListItem>

      </asp:DropDownList>

    </td>

    </tr>

    <tr>

    <td>

    <span style="color: Red">*</span>Attach Excel file

    </td>

    <td>

    <asp:FileUpload ID="fileuploadExcel" runat="server" />

    </td>

    </tr>

    <tr>

    <td></td>

    <td>

    <asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click"  />

    </td>

    </tr>

    </table>

    <asp:GridView ID="GridView1" runat="server">

    </asp:GridView>

    </div></form>

    </body>

    </html>

    ----------------------------------------------------------------------------------------------------------------------------------------------------------

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
    Compiler Error Message: CS1012: Too many characters in character literal
    Source Error:

    Line 32:         //Create OleDbCommand to fetch data from Excel
    Line 33: 
    Line 34: if (ddlValue ='DUComp')
    Thursday, July 18, 2013 3:58 AM

Answers

  • User2019981500 posted

    replace your if statement with

    if (ddlValue =="DUComp")



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 5:33 AM
  • User2019981500 posted

    Hi,

    Just drag one lable on design page where ever you want to dispaly message and write below code.

     public partial class _Default : System.Web.UI.Page
        {
            private String strConnection = "Data Source=SENPWM10519387\\SQLEXPRESS;Initial Catalog=QATest;Integrated Security=True";
    
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void btnSend_Click(object sender, EventArgs e)
            {
    
                //file upload path
    
                string path = fileuploadExcel.PostedFile.FileName;
    
                //Import Data Type check
    
                string ddlValue = ddlImportType.SelectedValue;
    
                //Create connection string to Excel work book 
    
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
    
                //Create Connection to Excel work book
    
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    
                //Create OleDbCommand to fetch data from Excel
    
                if (ddlValue == "DUComp")
                {
    
                    OleDbCommand cmd = new OleDbCommand("Select [Deployment Unit],[Deployment Scenario],[Wave Plan], [User ID], [Country], [Computer Name (Old)], [New Computer Type], [New Tag], [Application Readiness] from [Deployment Unit Computer$]", excelConnection);
    
                    excelConnection.Open();
    
                    OleDbDataReader dReader;
    
                    dReader = cmd.ExecuteReader();
    
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    
                    //Give your Destination table name
    
                    sqlBulk.DestinationTableName = "DUComputer";
    
                    sqlBulk.WriteToServer(dReader);
    
                    excelConnection.Close();
                    Label1.Text = "Uploaded Successfully";
    
                }
    
                else if (ddlValue == "DUApp")
                {
    
                    OleDbCommand cmd = new OleDbCommand("Select  [User ID], [Application], [Status], [Computer Name (Old)], [Package-ID] from [Deployment Unit Apps$]", excelConnection);
    
                    excelConnection.Open();
    
                    OleDbDataReader dReader;
    
                    dReader = cmd.ExecuteReader();
    
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    
                    //Give your Destination table name
    
                    sqlBulk.DestinationTableName = "DUApplications";
    
                    sqlBulk.WriteToServer(dReader);
    
                    excelConnection.Close();
                    Label1.Text = "Uploaded Successfully";
                }
    
                else
                {
                    Label1.Text = "Failed to Upload";
                }
    
            }
        }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 8:23 AM

All replies

  • User2019981500 posted

    replace your if statement with

    if (ddlValue =="DUComp")



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 5:33 AM
  • User45730163 posted

    Thanks a lot Shabir. It's working now. But can you please help me with code that will show data is uploaded successfully or not and refresh the page with previous stage. Right now it remains with the selected dropdown value.

    Many thanks in advance!

    Thursday, July 18, 2013 7:05 AM
  • User2019981500 posted

    Hi,

    Just drag one lable on design page where ever you want to dispaly message and write below code.

     public partial class _Default : System.Web.UI.Page
        {
            private String strConnection = "Data Source=SENPWM10519387\\SQLEXPRESS;Initial Catalog=QATest;Integrated Security=True";
    
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void btnSend_Click(object sender, EventArgs e)
            {
    
                //file upload path
    
                string path = fileuploadExcel.PostedFile.FileName;
    
                //Import Data Type check
    
                string ddlValue = ddlImportType.SelectedValue;
    
                //Create connection string to Excel work book 
    
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
    
                //Create Connection to Excel work book
    
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    
                //Create OleDbCommand to fetch data from Excel
    
                if (ddlValue == "DUComp")
                {
    
                    OleDbCommand cmd = new OleDbCommand("Select [Deployment Unit],[Deployment Scenario],[Wave Plan], [User ID], [Country], [Computer Name (Old)], [New Computer Type], [New Tag], [Application Readiness] from [Deployment Unit Computer$]", excelConnection);
    
                    excelConnection.Open();
    
                    OleDbDataReader dReader;
    
                    dReader = cmd.ExecuteReader();
    
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    
                    //Give your Destination table name
    
                    sqlBulk.DestinationTableName = "DUComputer";
    
                    sqlBulk.WriteToServer(dReader);
    
                    excelConnection.Close();
                    Label1.Text = "Uploaded Successfully";
    
                }
    
                else if (ddlValue == "DUApp")
                {
    
                    OleDbCommand cmd = new OleDbCommand("Select  [User ID], [Application], [Status], [Computer Name (Old)], [Package-ID] from [Deployment Unit Apps$]", excelConnection);
    
                    excelConnection.Open();
    
                    OleDbDataReader dReader;
    
                    dReader = cmd.ExecuteReader();
    
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    
                    //Give your Destination table name
    
                    sqlBulk.DestinationTableName = "DUApplications";
    
                    sqlBulk.WriteToServer(dReader);
    
                    excelConnection.Close();
                    Label1.Text = "Uploaded Successfully";
                }
    
                else
                {
                    Label1.Text = "Failed to Upload";
                }
    
            }
        }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 8:23 AM
  • User45730163 posted

    Thanks Shabir

    I need a favor to develop an application, where there will be a drop-down list and when user will select the Item from the drop-down, it will display available data from the database and in the output there should be a remove button in each row, if user will press the remove button data of the corresponding row will be deleted from the database. Also there would be an Add button if user click on add button two txt box will display and will need to insert the data in the database by submitting the form. 

    I am looking forward to your help with sample code.

    Thank You.

    BR/ IQBAL

    Monday, August 12, 2013 11:48 AM
  • User45730163 posted

    Hi Again,

    My Application is working fine with the excel file where first row is the column name.

    However I need some help with excel file where 3rd row is the column name and first two rows contains some header information, right now I am facing error while uploading such excel file.

    Please help me how can I solve such error.  

    Many thanks in advance.

    Tuesday, August 13, 2013 8:48 AM