I'm trying to create a class to store my connection string and general functions for insert, update and delete.
Testing the INSERT function, it seems to complete the process without any errors but the phisical DB doesn't have the new registry.
Class code:
Imports System.Data.OleDb
Public Class ControlBD
' Connection string
Private ConBD As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=inventariosBD.accdb;")
' Data Set
Private ds As New DataSet
' Data Adapter
Private da As New OleDb.OleDbDataAdapter
' SQL queries
Public Sql As String
Public Sub cargarDS(tabla)
sql = "Select * from " & tabla
da = New OleDb.OleDbDataAdapter(sql, ConBD)
da.Fill(ds, tabla)
End Sub
Public Sub insertar(tabla As String, valores As Array)
' Command Builder
Dim cb As New OleDb.OleDbCommandBuilder()
Dim dsRegistro As DataRow = ds.Tables(tabla).NewRow()
For i = 0 To UBound(valores)
dsRegistro.Item(valores(i)(0)) = valores(i)(1)
Next
cb.DataAdapter = da
ds.Tables(tabla).Rows.Add(dsRegistro)
da.Update(ds, tabla)
End Sub
End Class
Form Load code:
Public Class formPrincipal
Public ControlBD As New ControlBD
Private Sub formPrincipal_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim tabla As String = "productos"
Dim vals(4) As Array
ControlBD.cargarDS(tabla)
vals(0) = {"grupo", "mi_grupo'"}
vals(1) = {"nombre", "mi_nombre"}
vals(2) = {"medida", "mi_medida"}
vals(3) = {"cantidad", "50"}
vals(4) = {"precio", "80"}
ControlBD.insertar(tabla, vals)
End Sub
Finally, when I place a breakpoint at the update line da.Update(ds, tabla) VisualStudio shows the following:
ADO.NET: Ejecutar NonQuery "INSERT INTO productos (grupo, nombre, medida, cantidad, precio) VALUES (?, ?, ?, ?, ?)". Se ejecutó el texto de comando "INSERT INTO productos (grupo, nombre, medida, cantidad, precio) VALUES (?, ?, ?, ?, ?)" en la conexión "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=inventariosBD.accdb;" y devolvió el número de filas afectadas.
So the query has question marks instead of the values I want to insert, but VS debugger shows my DataSet has the new values correctly. Where is the mistake?
Solved
I added the DataBase at the Solutions Explorer of Visual Studio so it automatically added the database to the Bin/Debug folder.
I was checkig the database from Visual Studio, but I realized it was showing me the original DB and not the one at Bin/Folder.
In other words... Visual Studio was showing me another DataBase than the one being affected by my code.
New Issue
So my code INSERTS INTO the database, but it replaces the same registry, it's like an UPDATE not an INSERT even though the output query says INSERT INTO as shown above.
Dim rowsAffected = da.Update(ds, tabla). If it is non zero, the db is being updated. You also might want to consider scrapping that helper and moving Product related DB code into a Product class. This would allow the code to be more specific to the task.GetUpdateCommandon theCommandBuilderor else it won't have an update command. Same with delete and insert. I recommend you write your own queries and not rely on a command builder.