Click here to Skip to main content
15,897,187 members

Delete data from dbo table in ASP.NET

Garishma asked:

Open original thread
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:
C#
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.
Tags: ASP.NET

Plain Text
ASM
ASP
ASP.NET
BASIC
BAT
C#
C++
COBOL
CoffeeScript
CSS
Dart
dbase
F#
FORTRAN
HTML
Java
Javascript
Kotlin
Lua
MIDL
MSIL
ObjectiveC
Pascal
PERL
PHP
PowerShell
Python
Razor
Ruby
Scala
Shell
SLN
SQL
Swift
T4
Terminal
TypeScript
VB
VBScript
XML
YAML

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900