Resolved Insert into Access database

[Berrilicious]

Good evening all,

I'm having some trouble allowing the user to add a new row and insert values into a Table in my database.
The Code is as below:

Try
            oledbcon.Open()
            Dim Table As DataTable = TVData.Tables("Television")
            Dim dsNewRow As DataRow

            dsNewRow = Table.NewRow()

            dsNewRow.Item(0) = TVModeltxt.Text
            dsNewRow.Item(1) = TVSizetxt.Text
            dsNewRow.Item(2) = TVWBHtxt.Text
            dsNewRow.Item(3) = TVWBWtxt.Text
            dsNewRow.Item(4) = TVHDMItxt.Text
            dsNewRow.Item(5) = TVScarttxt.Text
            dsNewRow.Item(6) = TVComptxt.Text
            dsNewRow.Item(7) = TVSvidtxt.Text
            dsNewRow.Item(8) = TVAOuttxt.Text
            dsNewRow.Item(9) = TVDOuttxt.Text

            Table.Rows.Add(dsNewRow)

            Dim insertCommand As New OleDbCommand("INSERT INTO Television (ModelNumber, Size, WallBracketH, WallBracketW, HDMI, Scart, Component, SVideo, AudioOut, DigitalOut) VALUES (?,?,?,?,?,?,?,?,?,?)", oledbcon)

            insertCommand.Parameters.Add("ModelNumber", OleDbType.VarChar, 0, "ModelNumber")
            insertCommand.Parameters.Add("Size", OleDbType.Integer, 0, "Size")
            insertCommand.Parameters.Add("WallBracketH", OleDbType.Integer, 0, "WallBracketH")
            insertCommand.Parameters.Add("WallBracketW", OleDbType.Integer, 0, "WallBracketW")
            insertCommand.Parameters.Add("HDMI", OleDbType.Integer, 0, "HDMI")
            insertCommand.Parameters.Add("Scart", OleDbType.Integer, 0, "Scart")
            insertCommand.Parameters.Add("Component", OleDbType.Integer, 0, "Component")
            insertCommand.Parameters.Add("SVideo", OleDbType.Integer, 0, "SVideo")
            insertCommand.Parameters.Add("AudioOut", OleDbType.Integer, 0, "AudioOut")
            insertCommand.Parameters.Add("DigitalOut", OleDbType.Integer, 0, "DigitalOut")

            dataAdaptor.InsertCommand = insertCommand

            dataAdaptor.Update(TVData, "Television")

            MsgBox(TVModeltxt.Text & " added to the Database.", MsgBoxStyle.Information, "Complete.")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            oledbcon.Close()
        End Try

The Error I am getting is Syntax error in INSERT INTO statement.

Can anyone spot whats wrong as its driving me mad?

Regards.

 

[Berrilicious]

I got it to run now with:

Dim insertCommand As New OleDbCommand("INSERT INTO `Television` (`ModelNumber`, `Size`, `WallBracketH`, `WallBracketW`, `HDMI`, `Scart`, `Component`, `SVideo`, `AudioOut`, `DigitalOut`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", oledbcon)

But when i close and re-open application It hasnt actualy updated database

Anyone explain why to me?

Regards.

 

[Berrilicious]

Solved...

Had 'Copy to output directory' set to 'Copy Always' not Copy if newer'

Found this in "cjard's" sig links, the only one of his i hadn't read till today.
So thank you.

 

[cjard]

You'll probably find that using a word like "Size" as a column name is a bad idea; such words are reserved words in many programming languages. Putting ` ` round it probably escaped it so access knew it was a column name rather than a function or something

That said, if you'd followed the tutorials given in the DW2 link in my sig, this would never have happened because the wizard would have generated code with `

DW2 is a lot easier means to achieving the same ends as youre currently struggling with