Insert into access database not working

Question

I'm using Visual Studio 2015, Visual Basic Language. I want to INSERT INTO an Access Database a username ('Utilizador' in the code). This is the code I have:

Try
    Dim nconnect As New OleDbConnection("Provider=Microsoft.ACE.OleDb.12.0;" & "Data Source =|DataDirectory|S_Campo.accdb")
    nconnect.Open()
    Dim ncmd As OleDbCommand = nconnect.CreateCommand()
    ncmd.CommandText = "INSERT INTO Utilizador (Nome) VALUES (@p1)"
    ncmd.Parameters.AddWithValue("@1", Me.TextBox5.Text) 'Nome Do Utilizador
    ncmd.ExecuteNonQuery()
    nconnect.Close()
    MsgBox("Utilizador lançado com êxito", MsgBoxStyle.OkOnly, "Informação")

Catch ex As Exception
    MessageBox.Show(Err.Description)
End Try

It doesn't return me any error message, but the Data is not sent to the Database. However, UPDATE, and DELETE is working fine, using 'Parameters'.

What can is wrong with it?

`VB` & `VB.NET` are different, it looks like you're using `VB.NET` here. — AStopher, Jan 01 '16 at 23:13
How are you determining that it isnt inserting? The DB appears to be part of the project, what is the `Copy to Output Directory` setting? — Ňɏssa Pøngjǣrdenlarp, Jan 01 '16 at 23:21

score 2 · Accepted Answer · answered Jan 01 '16 at 23:23

2

Seems that you have parameter issue. Here

ncmd.CommandText = "INSERT INTO Utilizador (Nome) VALUES (@p1)"

your parameter is @p1. And here

ncmd.Parameters.AddWithValue("@1", Me.TextBox5.Text) 'Nome Do Utilizador

it is @1

In any case, here how you should check for success

If ncmd.ExecuteNonQuery() > 0 Then
    MessageBox.Show("Success!!")
End If

answered Jan 01 '16 at 23:23

T.S.

18,195
11
58
78

1

No it is not a parameter problem. OleDb doesn't use names for the parameters but the position. You could name them whatever you like, – Steve Jan 01 '16 at 23:39
@Steve This is the only thing I see "abnormal" here. My true belief that OP simply not checking in the right place that data is inserted. Hence my second part `ExecuteNonQuery() > 0` – T.S. Jan 01 '16 at 23:51
Instead I think the problem lies in the DataDirectory substitution string and the combined behavior of the _Copy To Output Directory_ property as hinted above from Plutonix and explained in [this answer](http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460) – Steve Jan 01 '16 at 23:56
I solved this problem with 2 changes: 1- I changed the path, from a relative to a absolut one. 2- I corrected the parameter, as T.S. sugestion. Now it's working fine. Thank you for your concern. – FerPessoa Jan 02 '16 at 11:37

Insert into access database not working

1 Answers1