Dataset in ADO.NET - add 1 new record

[briancps]

Hi - can anyone help

I am trying to add one record to a simple table in an Access 2002 DB using a Dataset. only 4 fields, 2 text, a date and a double with 2 decs and a autoincrement ID.

I followed the standard code from the examples and books I have but when I try to update the adapter I get a 'Syntax Error in INSERT Command' I have no idea what insert command has been generated.

The same code works for updating existing records by using the ID I want to update in the SELECT for the original Dataset.
I have tried selected ID of zero to give an empty dataset.

I have shown the code below

Private Sub DataUpdateNew()
'this updates the database with details entered
MessageBox.Show("Updating data from Controls into Database")
Dim sSql As String

Dim cn As New OleDb.OleDbConnection(dbcConnection)

'find the selected record
sSql = "SELECT * FROM TableTest" ' WHERE ID = " & mnCurrentId & " ORDER BY Name"

'open the ADO.NET Dataset
Dim cmd As New OleDb.OleDbCommand(sSql, cn)

'open the Data Adapter
Dim adapter As New OleDb.OleDbDataAdapter(cmd)

'create a dataset and fill it with the required information
Dim dsTableTest As New DataSet()
cn.Open()
'the following command fills the default values and constraints such as autonumber fields
adapter.FillSchema(dsTableTest, SchemaType.Mapped, "TableTest")
adapter.Fill(dsTableTest, "TableTest")

Dim row As DataRow

' add a blank row
row = dsTableTest.Tables("TableTest").NewRow

'update it with the new values
row("Name") = "New Name"
row("Address1") = "New Address"
row("Date") = Now()
row("Charge") = 0
dsTableTest.Tables("TableTest").Rows.Add(row)

'create the Command Builder
Dim cb As New OleDb.OleDbCommandBuilder(adapter)
adapter = cb.DataAdapter

'update the records
Dim nRowsAffected As Integer

Try
nRowsAffected = adapter.Update(dsTableTest, "TableTest")
Catch myerror As Exception
MessageBox.Show(myerror.Message)
Finally
End Try

cn.Close()


End Sub

If anyone can help I would greatly appreciate it-my background is 10 years VB Desktop apps - (no client server) using DAO & ADo - just having problems coming to grips with this

thanks BH

[briancps]

can anyone help or does anyone know the syntax or format to display the commandtext.

nRowsAffected = adapter.Update(dsTableTest, "TableTest")
this gives a 'Syntax error in INSERT Command"

but if I try to display the command text as :

MessageBox.Show(adapter.InsertCommand.CommandText)
I get an 'Object reference not set to a reference of an object'

Am I doing something stupid - still learning
If I could see the command I could probably fix it

thanks

[EagleEye]

It appears that you create your data adapter within your code. If this is true then you need to create you insert command.

Assumptions:
daNew - the name of your oleDB DataAdapter
TN - the name of the table
col1, col2, col3 - the 3 columns(fields) of your table

____________________________________
Dim oleDBInsComm1 as new system.data.oledb.oledbcommand

oledbinscomm1.commandtext = "INSERT into TN (col1, col2, col3) values (@col1, col2, col3)"

daNew.InsertCommand = oleDBInsComm1
____________________________________


Give that a try .. I might just misunderstand your problem but I think this will help.

[briancps]

Thanks for your reply - Ive benn strruggling with this for days. I was just about to try what you suggested although I was trying to persist with the commandbuilder object which handles inserts, updates and deletes in the one dataset without knowing which row is which.

However, it occured to me that in my test database I have fields called 'Name' and 'Date' - obviously reserved word in an INSERT but not in an UPDATE ???

Changed the names to NAmeTest and DateTest and now works fine - I feel like a real Dork !!!