insert error. help please!

[exile07]

Haven't been able to figure out what the problem is. I'm just trying to add the following to an Access DB. It's probably very simple but I've tried everything! Error reads - Syntax error in INSERT INTO statement. It points to the "da.Update(ds, "Tasks")" line. The objRows are accepting the values passed to them.

Private Sub btnAddTaskSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddTaskSave.Click

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\college\database.mdb"

con.Open()

sql = "SELECT CustomerID, Description, Date, EmpID, InvoiceID FROM Tasks"

da = New OleDb.OleDbDataAdapter(sql, con)

Dim cb As New OleDb.OleDbCommandBuilder(da)
da.Fill(ds, "Tasks")

Dim objRow As DataRow
'Create a new DataRow object for this table

'Create a new DataRow object for this table
objRow = ds.Tables("Tasks").NewRow

objRow("CustomerID") = lblAddTaskFName.Text
objRow("Description") = txtAddTaskDescription.Text
objRow("Date") = dtpAddTask.Value
objRow("EmpID") = 2 'cboAddTaskEmployee.SelectedItem
objRow("InvoiceID") = 4

'Officically add the Datarow to table
ds.Tables("Tasks").Rows.Add(objRow)

da.Update(ds, "Tasks")

con.Close()

MsgBox("New Task Added")

End Sub

Many thanks

 

[Rocksteady]

Might help if you actually use the commandbuilder.

da.UpdateCommand = cb.GetUpdateCommand()

I didnt look very close though.

 

[jmcilhinney]

Might help if you actually use the commandbuilder.

da.UpdateCommand = cb.GetUpdateCommand()

I didnt look very close though.

That's completely unnecessary. You only need to get the Commands if you intend to edit them manually.

That said, if there's a syntax error in your INSERT statement then the first thing to do would be to take a look at the INSERT statement:

MessageBox.Show(cb.GetInsertCommand().CommandText)

My first guess would be that one or more of your column names are reserved words, but if that's the case it seems odd that your query doesn't fail too.

 

[cjard]

Please (please) go to the DW2 link in my signature, and read the article "Creating a Simple Data APplication"

It will set you off on the road to doing data access properly and painlessly.. Right now there is a huge amount of stuff youre not doing, and your code is not very.. um.. correct with respect to Object Oriented methodologies

 

[cjard]

ps; downloading all tasks from the database, just so you can add a new one, and send tha tone back.. is lame, bad code (sorry.. but it's a very dumb thing to do) - read the article and it will set you straight

pps; please dont post code here without using code tags. take a read of this for more info: http://www.vbdotnetforums.com/misc.php?do=bbcode