My application trims the database table and populates it with rows from excel.
Using an excel file (2000 lines), the download code below inserts all the lines into the database completely on my local computer (my development environment), and it was also on the server, so I thought I successfully completed the task,
But then the user added 10 new rows to my excel and tried to load the 2010 rows, but in addition to the recently added 10 rows, the 2000 row was inserted. Therefore, using this excel file with the 2010 rows, if I load it from the server database table filled 2000 rows, and if I download it using my workspace, the resulting table is 2010 rows.
The server and the local application are exactly the same. I also tried formatting and
Edit example of execution: MyRecords.xlsx file is on my desktop, now I connect to my application using
http:
and download MyRecords.xlsx, then mark the account in the database, it contains 2361 entries, then open the visual studio and run my application ( http://localhost:58029/ ) and download MyRecords.xlsx and check the database again, in which 2,362 records are indicated.
Change run example 2: My excel has 2160 lines. If I download it, it inserts 2160 lines from the local and to the server. If I delete 1000 thousand lines and unload it, now it inserts 1160 lines from the local and from the server, as expected. Now, if I re-add 1000 lines and unload excel, now if I made this process using the local environment, it works fine with 2160 lines if 1160 lines are installed on the server. Therefore, any modification on excel is not considered on the server, so are you sure this is the reason? -
private void UploadData(string path, string dbTableName) { //Create connection string to Excel work book string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";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 excelConnection.Open(); DataTable dbSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get First Sheet Name OleDbCommand cmd = new OleDbCommand("Select * from [" + dbSchema.Rows[0]["TABLE_NAME"].ToString() + "]", excelConnection); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString); //Give your Destination table name sqlBulk.DestinationTableName = dbTableName; try { sqlBulk.WriteToServer(dReader); if(dbTableName == "TempTP") { SDatabaseManagerData.DatabaseManagerData.UpdateTP(); } lbl_Error.Visible = true; lbl_Error.Text = "Database updated!"; } catch (SqlException ex) { lbl_Error.Visible = true; lbl_Error.Text = "Database updated edilemedi! Hata: " + ex.Message; } excelConnection.Close(); }