Im trying to INSERT data on a database with VB

Question

I'm trying to Insert data on a access DataBase using Visual Basic with OleDbCommand, but it keeps returning me this error:

Here's my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    myconnection.ConnectionString = connString

    Dim sql As String
    myconnection.Open()
    sql = "INSERT INTO Atletas ( Nome, Contacto, Email, dataNascimento, Morada, idEscalao ) VALUES( " & Text_Nome.Text & "','" & Text_Contacto.Text & "','" & Text_Email.Text & "','" & Data_Picker.Text & "','" & Text_Morada.Text & "','" & Combo_Escalao.Tag & ")"

    Dim cmd As OleDbCommand = New OleDbCommand(sql, myconnection)
    cmd.ExecuteNonQuery()
    myconnection.Close()
End Sub

**Do Not** concat string to make SQL. [Use SQL parameters](https://stackoverflow.com/q/29186317/1070452). And type the error message into your post, dont post links to pictures of error messages. — Ňɏssa Pøngjǣrdenlarp, Jun 21 '17 at 20:29

score 3 · Accepted Answer · 2017-06-21T21:39:04.700

Firstly, I suggest you take a serious look at using parameters. As you can see, had you been using parameters you would not have had the syntax error. It will also eliminate problems with names such as O'Hara or O'Kelly as Steve pointed out.

Secondly It also protects you from SQL injection attacks - see Bobby Tables.

Finally, implementing a using block is good practice when it comes to using database connections, just in case you forget to close a connection, it will be disposed of at the end of the using block.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Using con As New OleDb.OleDbConnection

      con.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                             "Data Source = database path here" 
      con.Open()

      Dim sql As String = "INSERT INTO Atletas (Nome, Contacto, Email, dataNascimento, Morada, idEscalao) VALUES (@nome, @contacto, @email, @datanascimento, @morada, @idescalao);"

      Dim sql_insert As New OleDbCommand

      With sql_insert
          .Parameters.AddWithValue("@nome", Text_Nome.Text)
          .Parameters.AddWithValue("@contacto", Text_Contacto.Text)
          .Parameters.AddWithValue("@email", Text_Email.Text)
          .Parameters.AddWithValue("@datanascimento", Data_Picker.Value.ToString("yyyy/MM/dd")) '''Assuming the value needed is a date only
          .Parameters.AddWithValue("@morada", Text_Morada.Text)
          .Parameters.AddWithValue("@idescalao", Cstr(Combo_Escalao.Tag))
          .CommandText = sql
          .Connection = con
          .ExecuteNonQuery()         
      End With

      con.close()

   End Using

End Sub

Thank you a lot. Your answer helped me a lot. – João Figueiredo Jun 22 '17 at 08:56 — João Figueiredo, Jun 22 '17 at 08:56

Racil Hilan · Answer 2 · 2017-06-21T22:05:00.920

2

You are missing two apostrophes, one at the beginning and another at the end. It's also good practice to end it with a semicolon. Try this:

sql = "INSERT INTO Atletas ( Nome, Contacto, Email, dataNascimento, Morada, idEscalao ) VALUES( '" & Text_Nome.Text & "','" & Text_Contacto.Text & "','" & Text_Email.Text & "','" & Data_Picker.Text & "','" & Text_Morada.Text & "','" & Combo_Escalao.Tag & "');"

However, as Plutonix suggested in his comment: Do Not concat string to make SQL. Use SQL parameters.

edited Jun 21 '17 at 22:05

answered Jun 21 '17 at 20:30

Racil Hilan

22,887
12
43
49

6

And now what happen with your answer if you get a name like O'Hara? Please do not limit yourself to point the obvious problem, but suggest, at least, the best practices. – Steve Jun 21 '17 at 20:41
@Steve Plutonix had already suggested that in his comment before I posted my answer, so I upvoted him and didn't feel like repeating what he already said. – Racil Hilan Jun 21 '17 at 21:51
1

And what would be the problem if you repeat that? I would see just an improvement to your answer not anything else. I haven't downvoted your answer but really a simple recap of the good practices will give the correct path to the OP and will keep the downvotes away. – Steve Jun 21 '17 at 22:00
@Steve Fine, hope it makes you happy now, uncle Steve :-) – Racil Hilan Jun 21 '17 at 22:07
Thank you a lot. Your answer helped me a lot. – João Figueiredo Jun 22 '17 at 08:58

Im trying to INSERT data on a database with VB

2 Answers2