MySQL Forums :: MySQL for Visual Studio :: Inserted data gets duplicated in MySql


Advanced Search

Inserted data gets duplicated in MySql
Posted by: Daniel Márquez ()
Date: November 27, 2015 11:17PM

Well, I continued my project and it finally inserts the data from an excel file to a MySql table, the thing is that it gets duplicated 4 times (1 with a default Date format and the other 3 with a different dat format, which is weird because I made the "fecha" to get strings values not dates).

Here is the code, maybe you can help me:


---------------------------------

Imports System.Data
Imports System.Data.OleDb
Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Core.MsoAutomationSecurity
Imports System.ComponentModel
Imports Spire.Xls

Public Class Import

Dim xlApp As New Application
Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet


Private Sub Import_Load(sender As Object, e As EventArgs) Handles MyBase.Load

radio_med.Checked = True

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

OpenInput.Title = "Select a File"
OpenInput.InitialDirectory = "C:\"
OpenInput.Filter = ("All Files (*.*)|*.*" & "|Excel 97-2003 (*.xls)|*.xls")
OpenInput.FilterIndex = 2
OpenInput.FileName = ""
OpenInput.ShowDialog()

End Sub

Private Sub OpenInput_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenInput.FileOk


If radio_med.Checked = True Then

' Defining MySql Connection.
Dim InputConnectionMySql As New MySqlConnection
InputConnectionMySql.ConnectionString = "Server = localhost; User id = root; Password = 1234; Database = ats_db"

' Disabling all securities from Excel files, including macros.
Dim oldSecurity
oldSecurity = xlApp.AutomationSecurity
xlApp.AutomationSecurity = msoAutomationSecurityForceDisable

' Opening Excel file.
xlWorkbook = xlApp.Workbooks.Open(OpenInput.FileName)
xlApp.Application.AutomationSecurity = oldSecurity
xlWorksheet = xlWorkbook.ActiveSheet

' Deleting unnecesary header rows to prevent errors while reading.
For row As Integer = 1 To 6
xlWorksheet.Rows(1).Delete
Next

' Inserting a new row for "clean" columns names
xlWorksheet.Rows(2).Insert()

' Replacing names
For col As Integer = 1 To 52
Dim col2 As Integer = col + 1

If xlWorksheet.Cells(1, col).Value = "MÉTODO" = True Then
xlWorksheet.Cells(2, col).Value = "METODO"

ElseIf xlWorksheet.Cells(1, col).Value = "AREA" = True Then
xlWorksheet.Cells(2, col).Value = "ESTACION"

ElseIf xlWorksheet.Cells(1, col).Value = "POZO" = True Then
xlWorksheet.Cells(2, col).Value = "POZO"

ElseIf xlWorksheet.Cells(1, col).Value = "CABEZAL ROTATORIO UNIDAD DE BOMBEO" = True Then
xlWorksheet.Cells(2, col).Value = "UNIDAD"

ElseIf xlWorksheet.Cells(1, col).Value = "FECHA CHEQUEO" = True Then
xlWorksheet.Cells(2, col).Value = "FECHA"

ElseIf xlWorksheet.Cells(1, col).Value = "DATOS DE SUPERFICIE" = True Then
xlWorksheet.Cells(2, col).Value = "VARIADOR"

ElseIf xlWorksheet.Cells(1, col).Value = "CABEZAL ROTATORIO UNIDAD DE BOMBEO" = True Then
xlWorksheet.Cells(2, col).Value = "UNIDAD"

ElseIf xlWorksheet.Cells(1, col).Value = "SUME. (Ft)" = True Then
xlWorksheet.Cells(2, col).Value = "SUMERGENCIA"

ElseIf xlWorksheet.Cells(1, col).Value = "PIP (PSI)" = True Then
xlWorksheet.Cells(2, col).Value = "PIP"

ElseIf xlWorksheet.Cells(1, col).Value = "PBHP (PSI)" = True Then
xlWorksheet.Cells(2, col).Value = "PBHP"

ElseIf xlWorksheet.Cells(1, col).Value = "TORQUE" & vbLf & "(%) (Lb/Ft) " = True Then
xlWorksheet.Cells(2, col).Value = "TORQUE"

ElseIf xlWorksheet.Cells(1, col).Value = "P. INICIAL" = True Then
xlWorksheet.Cells(2, col).Value = "PINICIAL"

ElseIf xlWorksheet.Cells(1, col).Value = "P. FINAL" = True Then
xlWorksheet.Cells(2, col).Value = "PFINAL"

ElseIf xlWorksheet.Cells(1, col).Value = "PCSG (LPC)" = True Then
xlWorksheet.Cells(2, col).Value = "PCASING"

ElseIf xlWorksheet.Cells(1, col).Value = "TRABAJO REALIZADO" = True Then
xlWorksheet.Cells(2, col).Value = "TRABAJO"

ElseIf xlWorksheet.Cells(1, col).Value = "DIAGNOSTICO" = True Then
xlWorksheet.Cells(2, col).Value = "DIAGNOSTICO"

ElseIf xlWorksheet.Cells(1, col).Value = "CUADRILLA " = True Then
xlWorksheet.Cells(2, col).Value = "TECNICOS"

ElseIf xlWorksheet.Cells(1, col).Value = "COMENTARIOS / RECOMENDACIONES" = True Then
Dim Rng As Range = xlWorksheet.Range(xlWorksheet.Cells(2, col), xlWorksheet.Cells(2, col + 2))
Rng.Merge()
xlWorksheet.Cells(2, col).Value = "OBSERVACION"
End If

Next

xlWorksheet.Rows(1).delete

' Saving the file to not modify the original
xlWorkbook.SaveAs(Filename:="C:\Users\" & Environment.UserName & "\temp.xls")

' Excel connection
Dim ExcelInputConnection As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\" & Environment.UserName & "\temp.xls; Extended Properties = ""Excel 8.0; HDR = YES"""

Using InputConnectionExcel As New OleDb.OleDbConnection(ExcelInputConnection)
InputConnectionExcel.Open()

' Getting the required columns
Dim CommandInput As New OleDbCommand("SELECT FECHA, ESTACION, POZO, METODO, VARIADOR, UNIDAD, PCASING, TORQUE, PINICIAL, PFINAL, SUMERGENCIA, PIP, PBHP, DIAGNOSTICO, OBSERVACION, TECNICOS, TRABAJO FROM [" & xlWorkbook.ActiveSheet.Name & "$" & "]", InputConnectionExcel)

' Crear DataReader para la Hoja de datos
Using DReader As OleDbDataReader = CommandInput.ExecuteReader()

' Reading the data
If DReader.HasRows() Then
While DReader.Read()
Dim InputCommandMySql As New MySqlCommand
InputCommandMySql.Connection = InputConnectionMySql
InputCommandMySql.CommandType = CommandType.StoredProcedure
InputCommandMySql.CommandText = "getDataLivianoMediano"
InputCommandMySql.Parameters.AddWithValue("fecha", Convert.ToString(DReader.Item("FECHA")))
InputCommandMySql.Parameters.AddWithValue("estacion", Convert.ToString(DReader.Item("ESTACION")))
InputCommandMySql.Parameters.AddWithValue("pozo", Convert.ToString(DReader.Item("POZO")))
InputCommandMySql.Parameters.AddWithValue("metodo", Convert.ToString(DReader.Item("METODO")))
InputCommandMySql.Parameters.AddWithValue("variador", Convert.ToString(DReader.Item("VARIADOR")))
InputCommandMySql.Parameters.AddWithValue("unidad", Convert.ToString(DReader.Item("UNIDAD")))
InputCommandMySql.Parameters.AddWithValue("pcasing", Convert.ToString(DReader.Item("PCASING")))
InputCommandMySql.Parameters.AddWithValue("torque", Convert.ToString(DReader.Item("TORQUE")))
InputCommandMySql.Parameters.AddWithValue("pinicial", Convert.ToString(DReader.Item("PINICIAL")))
InputCommandMySql.Parameters.AddWithValue("pfinal", Convert.ToString(DReader.Item("PFINAL")))
InputCommandMySql.Parameters.AddWithValue("sumergencia", Convert.ToString(DReader.Item("SUMERGENCIA")))
InputCommandMySql.Parameters.AddWithValue("pip", Convert.ToString(DReader.Item("PIP")))
InputCommandMySql.Parameters.AddWithValue("pbhp", Convert.ToString(DReader.Item("PBHP")))
InputCommandMySql.Parameters.AddWithValue("diagnostico", Convert.ToString(DReader.Item("DIAGNOSTICO")))
InputCommandMySql.Parameters.AddWithValue("observacion", Convert.ToString(DReader.Item("OBSERVACION")))
InputCommandMySql.Parameters.AddWithValue("tecnicos", Convert.ToString(DReader.Item("TECNICOS")))
InputCommandMySql.Parameters.AddWithValue("trabajo", Convert.ToString(DReader.Item("TRABAJO")))

' Inserting into database
InputConnectionMySql.Open()
InputCommandMySql.ExecuteScalar()
InputConnectionMySql.Close()

End While
End If

End Using
InputConnectionExcel.Close()

End Using

End If

End Sub


Private Sub Import_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing

' Closes Microsoft Excel process when closing the form
If xlApp IsNot Nothing Then

xlApp.Quit()
xlApp = Nothing

End If

End Sub
End Class

Options: ReplyQuote


Subject Views Written By Posted
Inserted data gets duplicated in MySql 929 Daniel Márquez 11/27/2015 11:17PM
Re: Inserted data gets duplicated in MySql 490 Daniel Márquez 11/27/2015 11:24PM
Re: Inserted data gets duplicated in MySql 419 Javier Treviño 12/11/2015 01:42PM
Re: Inserted data gets duplicated in MySql 419 Daniel Márquez 12/11/2015 06:36PM
Re: Inserted data gets duplicated in MySql 393 Shay _Fox 01/11/2016 04:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.