asp.net / excel upload

Webboy2008
Webboy2008 used Ask the Experts™
on
protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }

Open in new window

Dear Experts,

I have attached code working fine in asp.net/c#.
However, I have the following line
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + "; Extended Properties=Excel 8.0");
       
Now capture the particular excel file <<example.xls>>
And now I hope to use file upload feature to get a xls file from my users.
Assume they will always put same file name and file type.

Is this possible to convert existing codes to file upload ?
If yes, can you show me how to do that? Or rewrite the codes and show me? Thanks.

For completed working codes, I will give out 500 pts.

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
you want something like add a fileupload control and on click event of a button use the following:
if (FileUpload1.HasFile == true)
{
  string filename = Path.GetFileName(FileUpload1.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/example.xls") + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }


}

Open in new window

Commented:
and this if u want the filename to be the same as the one they upload:
if (FileUpload1.HasFile == true)
{
  string filename = Path.GetFileName(FileUpload1.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/" + filename) + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }


}

Open in new window

Author

Commented:
can you get me a completed code included the front end like the textbox and browse button?
Thanks
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Commented:
sure:
FRONT END:

<form id="form1" runat="server">
    <asp:FileUpload id="FileUploadControl" runat="server" />
    <asp:Button runat="server" id="UploadButton" text="Upload" onclick="UploadButton_Click" />
    <br /><br />
    <asp:Label runat="server" id="StatusLabel" text="Upload status: " />
</form>

CODEBEHIND:

protected void UploadButton_Click(object sender, EventArgs e)
{
if (FileUploadControl.HasFile == true)
{
  string filename = Path.GetFileName(FileUploadControl.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/" + filename) + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
			
			  StatusLabel.Text = "Upload status: File uploaded!";
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
			StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
}
}

Open in new window

Author

Commented:
appreciate your helps. I will try tonight...

Author

Commented:
studentFileUpload? What it is ? Your code does not work.
Commented:
Change that to fileuploadcontrol

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial