Click here to Skip to main content
15,892,674 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys

As the title suggests, I am using ASP to upload an Excel file, whose data needs to be bulk copied to my database sitting in SQL server.
I have the following code to upload:

XML
<asp:Panel ID= "PanelUpload" runat="server"
                Please select an Excel file to import: <br/>;
                asp:FileUpload ID="FileUploadExcel"; runat="server";
                <br\>;
                <asp:Button ID="ButtonUploadFile"; runat="server" Text="Upload File" onclick="ButtonUploadFile_Click />
<br />;
                <asp:Label ID="LabelUpload"; runat="server" Text=""></asp:Label>;
            </asp:Panel>



C#
protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
       if (FileUploadExcel.HasFile)
       {
           LabelUpload.Text = "File uploaded is "; + FileUploadExcel.PostedFile.FileName;   
       }
}


//I then have a button where the adding to the database is done. Here i used the OleDB class.

string path = Path.GetDirectoryName(FileUploadExcel.FileName);
            string selectedServer = cmbServer.Text;
            serverConnectionString = BC_Users.SelectServer(selectedServer);
            string selectedTable = cmbTable.SelectedValue;
            //Load Excel file into DataTable          
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source= " + path + ";Extended Properties=Excel 8.0");
            con.Open(); 
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", con);
            OleDbDataAdapter adapt = new OleDbDataAdapter(cmd);
            //Create DataTable
            DataTable dtable = new DataTable();
            //Fill table with Excel file's data
            adapt.Fill(dtable);


I keep getting errors where i have "string path = ..." saying that the path is not of a legal form, and if its not that, i get an error at con.Open() that the argument is invalid. My BulkCopy is after this, but i dont think that bit will be a problem. It doesnt even reach it when i run anyway.

This is the first time i am using excel with ASP, as well as the OleDB class. So please can you tell me what to do in the simplest way!
Thanks!
Posted
Updated 12-May-10 23:15pm
v2

1 solution

 
Share this answer
 
Comments
Aradhna Krishna 13-May-10 6:02am    
Thanks. I checked for dead references, but i dont have any. I am looking into using something to do with InputStream or something like that. I also just noticed that I should have an INSERT statement in my OleDBCommand, rather than a SELECT, since I'm not displaying the data in a gridview anymore.

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