Click here to Skip to main content
14,240,397 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello Friends,
I have created a project ExcelChart.aspx where in the data from an excel file Sample.xls has been copied to a dbo table - ExcelTable.dbo. The details of the table is been displayed on the webpage via a gridview control.
Also the same dbo table is been used for a graphical representation via Chart.

I have used the below code to copy the data from Excel file to dbo table and to display in gridview control:
String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ExcelChart.mdf;Integrated Security=True;User Instance=True";
//file upload path
string path = FileUploadexcel.PostedFile.FileName;
string fileBasePath = Server.MapPath("~/Excel/");
string fileName = Path.GetFileName(this.FileUploadexcel.FileName);
string fullFilePath = fileBasePath + fileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";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
OleDbCommand cmd = new OleDbCommand("Select [Carats],[Value],[Month] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "ExcelTable";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridViewexcel.DataSource = ds.Tables[0];
GridViewexcel.DataBind();
da.Dispose();



The above code works fine for the first time execution. However on page load or on refresh the data is again been copied from excel file to dbo table. But as the Month column in dbo table is set to primary key the line sqlBulk.WriteToServer(dReader); gives me error for copying duplicate values.
I have tried a million other codes to set the same but failed to do so.
Now i was thinking if it is possible to delete records from dbo table on page load and let the details from excel file be copied again in the dbo table.
Please help. If the above concept is possible i will be really glad to gain some help.
Thanks a lot.

What I have tried:

sqlBulk.WriteToServer(dReader); gives error of duplicate records cannot be copied in the dbo table.
Posted
Comments
CHill60 6-Jun-16 11:30am
   
Where is this code? (what event is it?)
Will the data in the Excel file ever change? (In which case why have this step as part of your site?)
Garishma 7-Jun-16 5:04am
   
The code is in the aspx.cs page. the code runs on click of a button.
The data in the excel file wont change. It is static data.
CHill60 7-Jun-16 9:54am
   
Try putting it in the page load (remember to check for post-back) and precede it with a check to see if the data has already been loaded. That way it won't be reloaded.
Or, take the loading of the data from the spreadsheet entirely out of this page and have it as a separate setup process.
Garishma 8-Jun-16 14:36pm
   
I might not be clear in my words but the actul issue is not been clear till now. The below details are in my excel file:
Carats Value Month
1694.931 1528046.74 Jan
1418.289 1255673.58 Feb
1573.352 1754248.22 Mar
On page load the code is been run and the data same data is been inserted in the dbo table. But when i refresh the webpage it again try to insert the same data in the dbo table. As the Month column in dbo table is been set to primary key it gives error.
Now the data is already present in the dbo table no matter how many times i debug the code. so i want to erase the data from dbo table and then again insert the code on debugging.
I hope now the issue is clear.
CHill60 9-Jun-16 6:26am
   
That's what you said earlier. I can't see the point of deleting the data if it never changes - just load it once. OR, check to see if there is any data in the table before loading. OR, truncate or empty the table before loading the data "Delete from ExcelTable"

1 solution

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

Solution 2

the solution is by using Sessions and Viewstate which is described in the below link: Preventing Duplicate Record Insertion on Page Refresh: ASP Alliance[^]
   

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