Link to home
Start Free TrialLog in
Avatar of Webboy2008
Webboy2008

asked on

asp.net / excel upload

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,
Avatar of disrupt
disrupt
Flag of United States of America image

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

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

Avatar of Webboy2008
Webboy2008

ASKER

can you get me a completed code included the front end like the textbox and browse button?
Thanks
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

appreciate your helps. I will try tonight...
studentFileUpload? What it is ? Your code does not work.
ASKER CERTIFIED SOLUTION
Avatar of disrupt
disrupt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial