Cazs Cazs - 3 months ago 19
MySQL Question

Mysql DataAdapter Update Method - Doesn't Save Changes

I'm using MySQL in vb.net
Datagridview displayed correctly and everything works properly, the problem arises when I try to update the changes in the database with the update (method)
By default the data is updated in memory "is erased students with ID =" 2 ""
But in the database is not updated, I'm reading the documentation and should work with update
datosAlumnos.Update(ds, "alumnos")

Imports MySql.Data.MySqlClient
Public Class Form1
Dim con As New MySqlConnection
Dim stringCon As String = "server=localhost; user id=root; password=; database=centroeducativo"
Dim listViewAlumnos As New ListBox()

Dim ds As New DataSet()
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Try
con.ConnectionString = stringCon
con.Open()
Dim datosTablas As New MySqlDataAdapter("SHOW TABLES", con)
Dim datosAlumnos As New MySqlDataAdapter("SELECT * FROM alumnos ORDER BY Nombre", con)
Dim datosAsignaturas As New MySqlDataAdapter("SELECT * FROM asignaturas", con)
Dim datosMatriculas As New MySqlDataAdapter("SELECT * FROM matriculas", con)

datosTablas.Fill(ds, "tablas")
datosAlumnos.Fill(ds, "alumnos")
datosAsignaturas.Fill(ds, "asignaturas")
datosMatriculas.Fill(ds, "matriculas")

con.Close()

Dim tabla As DataTable
tabla = ds.Tables("tablas")
Dim fila As DataRow
Me.ListBox1.Items.Clear()
For Each fila In tabla.Rows

Me.ListBox1.Items.Add(fila.Item("Tables_in_centroeducativo"))
Next
Dim filaBorrada As DataRow() = ds.Tables("alumnos").Select("id=2")
filaBorrada(0).Delete()
datosAlumnos.Update(ds, "alumnos")
Catch ex As Exception

End Try
End Sub


Private Sub formularioTabla(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim curItem As String = ListBox1.SelectedItem.ToString()
Select Case curItem
Case "alumnos"
FormularioAlumno()
Case "asignaturas"
FormularioAsignaturas()
Case "matriculas"
FormularioMatriculas()
Case Else
MsgBox("none")
End Select
End Sub

Private Sub FormularioAlumno()
Panel1.Controls.Clear()
Dim dv As DataView = ds.Tables("alumnos").DefaultView
con.Close()
Dim DataGridView As New DataGridView()
Panel1.Controls.Add(DataGridView)
DataGridView.AutoSize = True
DataGridView.DataSource = dv
DataGridView.Columns("id").Visible = False
End Sub
End Class

Answer

It seems you forget to set InsertCommand, UpdateCommand and specially Deletecommand.

To make a data adapter update data, it should have those commands. You can set those commands manually or using a MySqlCommandBuilder.

Dim myConn As New MySqlConnection("Connection String")
Dim myDataAdapter As New MySqlDataAdapter()
myDataAdapter.SelectCommand = New MySqlCommand("Select Query", myConn)
Dim myCommandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)
Comments