Delete data from dbo table in ASP.NET

Garishma G

10y1.3k.NET

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.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Praveen Sreeram10y ago
Accepted answer
Hi Garishma,
Basically, your problem is to avoid the execution of the same code even after f5. So, you shouldn't delete the existing records.
You should avoid re-execution of the code. Please go through the following article.
http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.4
Thanks,
Prawin
Praveen Sreeram10y ago
that's really good to hear Garishma
Thanks,
Prawin
Garishma G10y ago
Hello Praveen,
Thank you so much for the response. The link really helped me in solving the issue. I am able to retrieve details from the excel file and display in graph without any error.
Thank you.
Garishma G10y ago
Hello Praveen,
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.
:(
Vasanth Natarajan10y ago
Better using SQL Transactions...
Praveen Sreeram10y ago
Hi Garishma,
Are you sure that there are no duplicates in the main column (which maps to Primary Key)of the Excel sheet? If the data is huge and not possible to manually check then Sort all the columns of the excel sheet. In excel, you can sort by multiple columns.
If you are sure that there are no duplicates, then please debug the button click function and see how many times it is getting executed.
If it is getting executed more than once then we need to find a mechanism of avoiding that.
(Deleting the data before inserting in not the right solution. we need to make sure that the data gets inserted )
Please let me know if you still face any problem,
Thanks,
Prawin
Abhilash J A10y ago
Sir,
Please check if the primary key references to other table.
Garishma G10y ago
Hello Praveen,
Thank you for your prompt response. The code is present on the button click itself.As mentioned by you the last line of the code is Response.Redirect("ExcelData.aspx"); but it still gives me the error :
Violation of PRIMARY KEY constraint 'PK_ExcelTable'. Cannot insert duplicate key in object 'dbo.ExcelTable'.
The statement has been terminated.
Hence to get hold of the above error i thought of working with a code which will erase the data from the dbo table on each page load (refresh - F5) and will insert the data again from the excel table.
Please help.
Praveen Sreeram10y ago
Hi,
First of all you shouldn't write the code in the Page_Load. Becuase, as you might already know that Page_Load will execute everytime you referesh the page.
You should write the code of inserting the data into the table in some button click. And, at the end of the button click do a Response.Redirect to the same to avoid insertion of the duplicates of the records becuase browsers will re-post to the same page when F5 is pressed.
Thanks,
Prawin

Carats	Value	Month
1694.931	1528046.74	Jan
1418.289	1255673.58	Feb
1573.352	1754248.22	Mar

Delete data from dbo table in ASP.NET

Violation of PRIMARY KEY constraint 'PK_ExcelTable'. Cannot insert duplicate key in object 'dbo.ExcelTable'.The statement has been terminated.

Violation of PRIMARY KEY constraint 'PK_ExcelTable'. Cannot insert duplicate key in object 'dbo.ExcelTable'.
The statement has been terminated.