vb.net Update datatable to MySQL

Question

vb.net Update datatable to MySQL

Navigation

#1 by (0 votes)

0

Good morning:

At this moment I am developing a small app in VS.2015 (VB), I have two databases, the first in MySQL and the second in Ms-SQL, both have the same structure, first I make a select and bring the data from a table of each database to a dataset and a datatable, then I make a brief comparison between the datatables, and finally I return the final tab to both databases, when I do it to MS-SQL I have no problem and update , but when I do it to MySQL it does not update, nor does it throw me an error or exception. The code I am using is:

Dim condicion As String = ""
Dim Tabla As String = "Horarios"
'Objects MySql'
Dim conMySql As MySqlConnection
Dim cmdMySql As MySqlCommand
Dim comBMySql As MySqlCommandBuilder
Dim daMySql As MySqlDataAdapter

'Objects Sql'
Dim conSql As SqlConnection
Dim cmdSql As SqlCommand
Dim comSql As SqlCommandBuilder
Dim daSql As SqlDataAdapter

conSql = New SqlConnection(My.Settings.MsSql.ToString)
cmdSql = New SqlCommand("SELECT * FROM " & Tabla & IIf(Condicion <> "", " WHERE " & Condicion, ""), conSql)
daSql = New SqlDataAdapter(cmdSql)
conSql.Open()
cmdSql.ExecuteNonQuery()
daSql.Fill(dsg, Tabla & "1")
conSql.Close()

conMySql = New MySqlConnection(My.Settings.MySqlLocal.ToString)
cmdMySql = New MySqlCommand("SELECT * FROM " & Tabla & IIf(Condicion <> "", " WHERE " & Condicion.Replace("GETDATE", "CURDATE"), ""), conMySql)
daMySql = New MySqlDataAdapter(cmdMySql)
conMySql.Open()
cmdMySql.ExecuteNonQuery()
daMySql.Fill(dsg, Tabla)
conMySql.Close()

Form1.DataGridView1.DataSource = dsg.Tables(Tabla)
Form1.DataGridView2.DataSource = dsg.Tables(Tabla & "1")

dsg.Tables(Tabla).Merge(dsg.Tables(Tabla & "1"))

comBMySql = New MySqlCommandBuilder(daMySql)

Dim upcmdmysql As MySqlCommand
upcmdmysql = New MySqlCommand("UPDATE horarios SET idhorario = ?idhorario, NombreHora = ?NombreHora, Entrada1 = ?Entrada1, Salida1 = ?Salida1, Entrada2 = ?Entrada2, Salida2 = ?Salida2, Observacion = ?Observacion", conMySql)

For Each columna In dsg.Tables(Tabla).Columns
    upcmdmysql.Parameters.Add(New MySqlParameter("?" & columna.ToString, columna.GetType))
    upcmdmysql.Parameters("?" & columna.ToString).SourceVersion = DataRowVersion.Original
    upcmdmysql.Parameters("?" & columna.ToString).SourceColumn = columna.ToString
Next

daMySql.UpdateCommand = upcmdmysql
daMySql.Update(dsg.Tables(Tabla))

mysql vb.net

asked by Miguel Avendaño 30.06.2016 в 20:13

source

1 answer

How can I change the structure of a column in oracle report builder Display select edit mode in Angular handsontable

score 0 · Answer 1

Maybe my answer is short and not deep but I think the mistake you are making in this part:

comBMySql = New MySqlCommandBuilder(daMySql)

Dim upcmdmysql As MySqlCommand
upcmdmysql = New MySqlCommand("UPDATE horarios SET idhorario = ?idhorario, NombreHora = ?NombreHora, Entrada1 = ?Entrada1, Salida1 = ?Salida1, Entrada2 = ?Entrada2, Salida2 = ?Salida2, Observacion = ?Observacion", conMySql)

For Each columna In dsg.Tables(Tabla).Columns
    upcmdmysql.Parameters.Add(New MySqlParameter("?" & columna.ToString, columna.GetType))
    upcmdmysql.Parameters("?" & columna.ToString).SourceVersion = DataRowVersion.Original
    upcmdmysql.Parameters("?" & columna.ToString).SourceColumn = columna.ToString
Next

daMySql.UpdateCommand = upcmdmysql
daMySql.Update(dsg.Tables(Tabla))

The same thing happened to me too, and I ended up, after a lot of searching, using the UPDATE command just passing it the Table parameter. That means that when you modify the table in memory that you filled with your previous Fill, then you only do Update and that automatically executes the Update in the Base.

In summary: modify the DataTable you fill with daMySql.Fill (dsg, Table) directly and then run daMySql.Update (dsg.Tables (Table)).

Try it like that.

EDIT:

I leave the part of code where I filled the DataTable. This is also very important because at first it did not work for me either, and with this I managed to make it work.

Try
            If Not cEJECUTAR.State = ConnectionState.Open Then cEJECUTAR.Open()
            dgABMSist.Columns.Clear()
            DT = New DataTable
            Dim cmd As MySqlCommand = New MySqlCommand
            stm = "SELECT * FROM " & Tabla & ";"
            cmd.CommandType = CommandType.Text
            cmd.CommandText = stm
            cmd.Connection = cEJECUTAR
            Adapter = New MySqlDataAdapter(cmd)
            cb = New MySqlCommandBuilder(Adapter)
            Adapter.Fill(DT)
            With dgABMSist
                .AutoGenerateColumns = True
                .DataSource = DT
            End With
            cmd.Dispose()
            cmd = Nothing
            cEJECUTAR.Close()
        Catch ex As Exception
            tsInfoSQL.Text = "Error en la consulta: " & Mid(ex.Message, 1, 100)
            tsEstado.Image = LCDR.My.Resources.Resources.ico_rechazado
        End Try