Error when saving to access database

[Michael_35]

Hi All,I am coidng a movie database using vb.ebt (VS 2013) and using access (2013) I am trying to code my save button, but every time i run the program and try to save my data I get the below error

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in INSERT INTO statement.

here is my code:

  If inc <> -1 Then

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsnewrow As DataRow
            dsnewrow = ds.Tables("Films").NewRow()
            dsnewrow.Item("Title") = txtTitle.Text
            dsnewrow.Item("Director") = txtDirector.Text
            dsnewrow.Item("Certificate") = cmbCertificate.Text
            dsnewrow.Item("Genre") = cmbGenre.Text
            dsnewrow.Item("Date") = dpDate.Text
            dsnewrow.Item("IMDB Link") = txtIMDB.Text
            dsnewrow.Item("Youtube Link") = txtYotube.Text
            ds.Tables("Films").Rows.Add(dsnewrow)
            da.Update(ds, "Films") 'this is where the error is
            NavigateRecords()
            MessageBox.Show("Record Added to Database")
        End If

Any help will be appreciated.

PS new to VB, so please go easy

Thanks

[DaveAmour]

Is there anything dodgy in any of those text fields?

[Michael_35]

Well in the date and genre fields these have values left in after the textboxes have been cleared. Would that make a difference?

[Michael_35]

I tried updating one text field "Title" and nothing else and still got the same error

[DaveAmour]

I don't think so - the error is a syntax error in the insert statement.

Do you have any single apostrophes maybe?

[Michael_35]

No, I dont have an insert statement.There is nothing in teh guide i am using to say that i need to use an insert statement.

[DaveAmour]

The code under the hood will be generating an insert statement and that is where the problem is. Do all of these dsnewrow.Item("Title") match database column names exactly - the spaces may be an issue?

[Michael_35]

Yes they do match the columns exactly. I have the delete button working perfectly, and it makes changesd to the access file,but that doesnt refernce particualr items, just deletes the entire record.

[DaveAmour]

Sorry can't think of anything else then.

[Michael_35]

Thank you for your help, much appreciated

[DaveAmour]

One last thing - if it's easy then change your table columns to have no spaces and change them in your code - worth a try if its easy to do.

[Michael_35]

does it mater that my program is using an OLEDB and ODBC?

[DaveAmour]

Not sure - does any other database code work? If yes then it should be ok.

[Reverend Jim]

That is why I prefer to avoid the complicated method. Too many ways to bollux things up. Try this

Dim con As New OleDbConnection(your connection string here)
Dim cmd As New OleDbCommand("", con)

cmd.CommandText = "INSERT INTO Films " _
                & "  (Title, Director, Certificate, Genre, " _
                & "   [Date], [IMDB Link], [Youtube Link] " _
                & "VALUES(?, ?, ?, ?, ?, ?, ?)"

cmd.Parameters.AddWithValue("@parm1", txtTitle.Text)
cmd.Parameters.AddWithValue("@parm2", txtDirector.Text)
cmd.Parameters.AddWithValue("@parm3", cmbCertificate.Text)
cmd.Parameters.AddWithValue("@parm4", cmbGenre.Text)
cmd.Parameters.AddWithValue("@parm5", dpDate.Text)
cmd.Parameters.AddWithValue("@parm6", txtIMDB.Text)
cmd.Parameters.AddWithValue("@parm7", txtYotube.Text)

con.Open()
Dim result As Integer = cmd.ExecuteNonQuery()
con.Close()

Note the square brackets around fields with embedded blanks and reserved names like Date.