OleDbException when trying to update an Access database

Question

I'm following all the instructions that I can see in the web, including MSDN:https://msdn.microsoft.com/en-us/library/ms233819.aspx

Unfortunatelly, no matters how much I change the code, it always return to me an error: "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll", and the update is not done. This is my code:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim connect3 As New OleDbConnection("Provider=Microsoft.ACE.OleDb.12.0;" & "Data Source =C:\Users\Fernando\Documents\Visual Studio 2012\Projects\Agenda_DP\Agenda_DP\AgendaDP.accdb")
    connect3.Open()
    Dim cmd3 As OleDbCommand = connect3.CreateCommand()
    cmd3.CommandText = "UPDATE Temas SET [Numero] = @p1, [Tema] = @p2, [Observacoes] = @p3 WHERE [Numero] = @1"
    cmd3.Parameters.AddWithValue("@p1", Me.ComboBox1.Text)
    cmd3.Parameters.AddWithValue("@p2", Me.ComboBox2.Text)
    cmd3.Parameters.AddWithValue("@p3", Me.TextBox3.Text)
    cmd3.ExecuteNonQuery()
    connect3.Close()
End Sub

I don't understand why this is happening.

Has this ever happened to anyone? How did you solved it? Can anyone help me with this issue?

`"UPDATE Temas SET [Numero] = @p1, [Tema] = @p2, [Observacoes] = @p3 WHERE [Numero] = @1"` - what is @1? — Reg Edit, May 30 '15 at 13:43

score 1 · Accepted Answer · answered May 30 '15 at 13:46

1

When using System.Data.OleDb, parameters are purely positional and their names do not matter. So, you cannot use the same parameter name twice and .Add it only once; you would have to .Add it twice.

However, in your case you have [Numero] in your WHERE clause so there is no need to SET its value. You can just do

cmd3.CommandText = "UPDATE Temas SET [Tema] = ?, [Observacoes] = ? WHERE [Numero] = ?"
cmd3.Parameters.AddWithValue("?", Me.ComboBox2.Text)  ' Tema
cmd3.Parameters.AddWithValue("?", Me.TextBox3.Text)  ' Observacoes
cmd3.Parameters.AddWithValue("?", Me.ComboBox1.Text)  ' Numero
cmd3.ExecuteNonQuery()

answered May 30 '15 at 13:46

Gord Thompson

116,920
32
215
418

1

I still like to use names, even though they only work as a reminder for me. :) – Fionnuala May 30 '15 at 13:48
1

@Fionnuala To be honest, I do, too. However in cases like this using `?` is a good visual reminder that the "names" of parameters don't mean anything to OleDb. – Gord Thompson May 30 '15 at 13:53

OleDbException when trying to update an Access database

1 Answers1