1

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Marco Sanchez
  • 788
  • 7
  • 22
  • The question marks are a good sign: it means you've created a parameterized query instead of concatenating parameter values into a string: the database "server" is responsible for dealing with the parameter values, not the code - and that's excellent. – Mathieu Guindon Sep 02 '16 at 16:27
  • Good to know questions marks are fine, thanx... but I still have the problem :( the phisical database doesn't get any changes at all – Marco Sanchez Sep 02 '16 at 16:35
  • There are several problems with your approach. You might start by capturing the update results: `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. – Ňɏssa Pøngjǣrdenlarp Sep 02 '16 at 17:12
  • I think you have to call `GetUpdateCommand` on the `CommandBuilder` or 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. – Crowcoder Sep 02 '16 at 19:02

1 Answers1

0

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.

Marco Sanchez
  • 788
  • 7
  • 22