StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs there a way to check for Duplicates when Importing Excel Sheet into MySQL table using vb.net
text
Body
copied!<p>I have a upload excel file on my web application. When the file is uploaded it is supposed to be openned and then import the data into a mysql table. It works fine in that it adds the information just fine. However there is a problem the code I cannot get the duplicate checking to work correctly.. Part of this is because the excel sheet that will be used does not have any column that I can use as a unique identifier. Several times in the excel sheet the information is completely identical across the entire row with only differences in one or two columns... I was thinking I could do something like: Select * FROM table_name WHERE table_col_1 = variable and table_col_2 = variable 2 and etc...</p> <p>I am thinking by doing this I could compare the values of every column in the row to the import value and if its the same it would skip it... However I cant quite get my head around it... Maybe I am thinking into it to deep and there is a simple way to do this. Below is the function that i am using:</p> <pre><code> Private Function PerFormUpdate(ByVal customer As String, ByVal bill_to As String, ByVal Contact As String, ByVal Company As String, ByVal firstName As String, ByVal mi As String, ByVal lastname As String, ByVal phone As String, ByVal altPhone As String, ByVal fax As String) Dim _db As New schoolEntities Dim command As MySqlCommand = _dbconn.CreateCommand() command.Parameters.AddWithValue("@Customer", customer) command.Parameters.AddWithValue("@Bill_to", bill_to) command.Parameters.AddWithValue("@Contact", Contact) command.Parameters.AddWithValue("@Company", Company) command.Parameters.AddWithValue("@First_Name", firstName) command.Parameters.AddWithValue("@M_I", mi) command.Parameters.AddWithValue("@Last_Name", lastname) command.Parameters.AddWithValue("@Phone", phone) command.Parameters.AddWithValue("@Alt_Phone", altPhone) command.Parameters.AddWithValue("@Fax", fax) command.CommandText = "SELECT * FROM quickbooks_imports WHERE Customer=@Customer, Bill_to=@Bill_to, Contact=@Contact, Company =@Company, First_Name=@First_Name, M_I=@M_I, Last_Name=@Last_Name, Phone =@Phone, Alt_Phone=@Alt_Phone, Fax=@Fax" _dbconn.Open() Dim _mysqlReader As MySqlDataReader = command.ExecuteReader() _dbconn.Close() If Not _mysqlReader.HasRows Then Dim _UpdateItem As New quickbooks_imports Dim updateCommand As MySqlCommand = _dbconn.CreateCommand() _UpdateItem.Customer = customer _UpdateItem.Bill_to = bill_to _UpdateItem.Contact = Contact _UpdateItem.Company = Company _UpdateItem.First_Name = firstName _UpdateItem.M_I = mi _UpdateItem.Last_Name = lastname _UpdateItem.Phone = phone _UpdateItem.Alt_Phone = altPhone _UpdateItem.Fax = fax updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES (@Customer, @Bill_to, @Contact, @Company, @First_Name, @M_I, @Last_Name, @Phone, @Alt_Phone, @Fax)" updateCommand.Parameters.AddWithValue("@Customer", _UpdateItem.Customer) updateCommand.Parameters.AddWithValue("@Bill_to", _UpdateItem.Bill_to) updateCommand.Parameters.AddWithValue("@Contact", _UpdateItem.Contact) updateCommand.Parameters.AddWithValue("@Company", _UpdateItem.Company) updateCommand.Parameters.AddWithValue("@First_Name", _UpdateItem.First_Name) updateCommand.Parameters.AddWithValue("@M_I", _UpdateItem.M_I) updateCommand.Parameters.AddWithValue("@Last_Name", _UpdateItem.Last_Name) updateCommand.Parameters.AddWithValue("@Phone", _UpdateItem.Phone) updateCommand.Parameters.AddWithValue("@Alt_Phone", _UpdateItem.Alt_Phone) updateCommand.Parameters.AddWithValue("@Fax", _UpdateItem.Fax) 'updateCommand.CommandText = "INSERT INTO EXCEL (id,Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.id & "','" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') ON DUPLICATE KEY UPDATE Customer= '" & _UpdateItem.Customer & "' Bill_to= '" & _UpdateItem.Bill_to & "' Contact= '" & _UpdateItem.Contact & "' Company= '" & _UpdateItem.Company & "' First_Name= '" & _UpdateItem.First_Name & "' M_I= '" & _UpdateItem.M_I & "' Last_Name= '" & _UpdateItem.Last_Name & "' Phone= '" & _UpdateItem.Phone & "' Alt_Phone= '" & _UpdateItem.Alt_Phone & "' Fax= '" & _UpdateItem.Fax & "'" 'updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') " _dbconn.Open() Try updateCommand.ExecuteNonQuery() Catch ex As Exception Dim _error As String = Nothing MsgBox(ex.Message) End Try _db.SaveChanges() Else Dim _NewItem As New quickbooks_imports _NewItem.Customer = customer _NewItem.Bill_to = bill_to _NewItem.Contact = Contact _NewItem.Company = Company _NewItem.First_Name = firstName _NewItem.M_I = mi _NewItem.Last_Name = lastname _NewItem.Phone = phone _NewItem.Alt_Phone = altPhone _NewItem.Fax = fax _db.quickbooks_imports.AddObject(_NewItem) _db.SaveChanges() End If _dbconn.Close() Return View() End Function </code></pre> <p>Any help would be greatly appreciated...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload