Click here to Skip to main content
14,668,312 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am created a project.
I used a label, fileupload, gridview and button in it.

This is the code in button click event :

SqlConnection con = new SqlConnection(/*connection string*/);
       FileUpload1.SaveAs(Server.MapPath("~//App_Data//") + FileUpload1.FileName);
       string path = Server.MapPath("~//App_Data//") + FileUpload1.FileName;
       try
       {
           //for .xls file
           System.Data.OleDb.OleDbConnection MyConnection;
           DataSet DtSet;
           System.Data.OleDb.OleDbDataAdapter MyCommand;
           MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
           MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
           DtSet = new System.Data.DataSet();
           MyCommand.Fill(DtSet, "[Sheet1$]");
           MyConnection.Close();
           GridView1.DataSource = DtSet.Tables[0];
           GridView1.DataBind();

           SqlDataAdapter adpter = new SqlDataAdapter("Select * from StudentList", con);
           adpter.Update(DtSet.Tables[0]);
           Response.Write("successfully Imported values to SQL Server");

       }
       catch (Exception ex)
       {
           Response.Write(ex.Message);
       }


The above code executes successfully. It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server.

I want to use disconnected mode.

[Edit - Henry]
Changed backslashes to slashes in the MapPath lines so that the code colourizer could cope.
[/Edit]
Posted
Updated 12-Apr-11 23:27pm
v4
Comments
Sandeep Mewara 13-Apr-11 4:55am
   
It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server.
Not clear. Please explain.
Tarun.K.S 13-Apr-11 4:57am
   
The rows of the table in the DataSet is not getting saved in the table that's there in Sql Server.
Sandeep Mewara 13-Apr-11 5:02am
   
Then why is the retrieval code shared here?
Rakesh From Patna 13-Apr-11 5:07am
   
yes,
I retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server.
Sandeep Mewara 13-Apr-11 5:14am
   
Instead of retrieve code, you should share the save code then.
Tarun.K.S 13-Apr-11 4:56am
   
You don't have to close the connection explicitly as this is done by the SqlDataAdapter.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think it would work better if you would store it into the database first and select it from there. Have a look here for more more info and an example how to use SqlBulkCopy for that.

http://stackoverflow.com/questions/3664067/import-data-from-excel-into-multiple-tables[^]

As a side note: In your code example it would be somewhat better to create the connection (con) as late as possible so you don't use unnecessary resources and keep a connection occupied even if it isn't used until later.

Good luck!
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100