Problems adding a new row to an ms access database via VB.net

[Nimion]

Im getting the "Syntax error in INSERT INTO statement." at my
da.Update(ds, "Expenses") line in code... heres my full code to follow.

------------------------------------------------------------------------------------------------------------
Private Sub ConnectToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConnectToolStripMenuItem.Click

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\eneal\Desktop\BudgetingDatabase.mdb"

con.Open()

MsgBox("A Connection to the Database is now open")




sqlExp = "Select * FROM Expenses"
da = New OleDb.OleDbDataAdapter(sqlExp, con)
da.Fill(ds, "Expenses")

End sub
----------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------------

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click



Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("Expenses").NewRow()
dsNewRow.Item("Expense Description") = Expense_DescriptionTextBox.Text
ds.Tables("Expenses").Rows.Add(dsNewRow)
da.Update(ds, "Expenses") ****This is the line my problems at*

MsgBox("New Record added to the Database")


End Sub
------------------------------------------------------------------------------------------------------



I've searched high an low to an answer for this problem but nothing has seemed remotly close to what I'm experiencing.

[Dharmaraju]

if you want to update the data from the dataadapter you should keep the priamry key in your table

[kenobewan]

What is your insert statement?

[Nimion]

Well I tried it with and without a primary key. And I was under the impression the Command Builder built the insert into statment automatically based upon the select?

[kenobewan]

I'm not good at impressions, so I'll just my opinion. If you dont have an insert statement, usually in the dataadapter, there will be no insertion. What software are you using? I'd be reluctant to use a 'command builder' that based its insert statements on my select statements.

[Dharmaraju]

Use only one adapter and only one select statement of the updatable table. Easily you can the error .

Otherwise
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

' add the following line
da.insertcommand=cb.getinsertcommand()

dsNewRow = ds.Tables("Expenses").NewRow()
dsNewRow.Item("Expense Description") = Expense_DescriptionTextBox.Text
ds.Tables("Expenses").Rows.Add(dsNewRow)
da.Update(ds, "Expenses") ****This is the line my problems at*

MsgBox("New Record added to the Database")


Use the select statement and fill the adapter after that you perform the row addition.

[Nimion]

Ok I made sure I had one adpater, and it was being filled. I went into debug mode and took a look at the insert command that was built and it was correct. But I get that same error at the same spot... Insert Into was wrong.. I tried playing with it for a few hours but it seems something isnt matching up right behind the scenes and I cant seem to pinpoint it.

[Nimion]

Ok I've re-written the entire code from scratch... Now I get an entirly different error. The dreaded "No value given for one or more required parameters." at the da.update line... Now I thought the ?'s meant those were parameters? I replaced all the ?'s with 0's for a test, and it added a new row....with all 0's. So I know its something with the ?'s and it not seeing the parameters in the add.row..

New Code Below:

Dim CN As New OleDb.OleDbConnection

CN.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\eneal\Desktop\BudgetingDatabase.mdb"

'Open connection
cn.Open()

'Create Select Statement
Dim sql As String = "SELECT [Ref Number], [Expense Amount], [Expense Description], [Expense Date], Vendor, [PO Number], [Req Number] FROM Expenses"

'Create update/insert statement
Dim updcmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(" Insert Into Expenses ([Ref Number], [Expense Amount], [Expense Description], [Expense Date], Vendor, [PO Number], [Req Number]) Values (?,?,?,?,?,?,?)")

Dim da As New OleDb.OleDbDataAdapter(sql, CN)

Dim ds As New DataSet()
da.FillSchema(ds, SchemaType.Source, "Expenses")
'Fill the DataTable.
da.Fill(ds, "Expenses")

' Create an auxiliary CommandBuilder object for this DataAdapter.
Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)
da.InsertCommand = updcmd
da.DeleteCommand = cmdBuilder.GetDeleteCommand
da.UpdateCommand = cmdBuilder.GetUpdateCommand

With ds.Tables("Expenses")
Dim dr As DataRow = .NewRow
dr("Expense Description") = "QWERTY Test"
dr("Ref Number") = 5
dr("Expense Amount") = 5
dr("Expense Date") = "5/5/05"
dr("Vendor") = "Fiftys"
dr("PO Number") = 555
dr("Req Number") = 5555
.Rows.Add(dr)
End With

'Send changes to the database, and disconnect.
da.Update(ds, "Expenses")

cn.Close()

[Nimion]

Well I figured it out...I did something revolutionary. I went into the database and made all the fieldnames without a space between them -.-'...

It seems that the commandbuilder wasnt putting in the [ ]'s and I didnt know how to get it to use the bracekts... so I just made that small modification (doesnt really affect anything). And now it works like a charm!... Lol. Thanks for everyone who attempted to help me :)