Trying to Write a New Record

[Curt Emich]

I'm trying to insert a new record into an MS Access table. The code below
compiles and runs, but it doesn't write a record into the table. Can
someone tell me why?

Dim thisSelectCommand As New OleDb.OleDbCommand("SELECT * FROM Sessions",
Me.OleDbConnection1)

Dim thisDataAdapter As New OleDb.OleDbDataAdapter(thisSelectCommand)

Dim thisDataset As New System.Data.DataSet()

thisDataAdapter.Fill(thisDataset, "Sessions")

thisDataAdapter.InsertCommand = New OleDb.OleDbCommand("INSERT INTO Sessions
(Notes) VALUES('Yadda')")

thisDataAdapter.Update(thisDataset, "Sessions")

thisDataset.AcceptChanges()

 

[Miha Markic [MVP C#]]

Hi Curt,

And where is the new record?
You've defined only insert command which won't work with dataset even if
there was a new record.
Either: thisDataAdapter.InsertCommand.ExecuteNonQuery() // don't forgot to
assign a valid connection and/or transaction instance
or: configure properly InsertCommand (see ado.net help topics) and add a
record to thisDataset.Tables["Session"] and then invoke Update.

 

[William Ryan eMVP]

You aren't doing anything to cause it to. Rowstate/HasChnages is
exclusively what tells the dataadapter to do anything (here's a discussion
of the subject) http://www.knowdotnet.com/articles/datasetmerge.html

So even though you've hard coded an update in the updatecommand, that's
never getting called. Also, the dataadapter updates rows one by one and as
it does it, it calls AcceptChanges. No need to call it afterward.

HTH,

Bill

 

[Curt Emich]

I'm not getting it. Here's my new code, but still not dice.



Try

Dim thisSelectCommand As New OleDb.OleDbCommand("SELECT *
FROM Sessions", Me.OleDbConnection1)
Dim thisDataAdapter As New
OleDb.OleDbDataAdapter(thisSelectCommand)
Dim thisDataset As New System.Data.DataSet()
Dim thisTable As New System.Data.DataTable()
Dim thisRow As DataRow

thisDataAdapter.Fill(thisDataset, "Sessions")
' thisDataAdapter.InsertCommand = New
OleDb.OleDbCommand("INSERT INTO Sessions (Notes) VALUES('Yadda')")

thisTable = thisDataset.Tables("Sessions")
thisRow = thisTable.NewRow()
thisRow!Notes = "Testing"
thisTable.Rows.Add(thisRow)


System.Diagnostics.Debug.WriteLine(thisDataset.HasChanges.ToString())


thisDataAdapter.Update(thisDataset, "Sessions")

'thisDataset.AcceptChanges()




Catch
System.Diagnostics.Debug.WriteLine("error")
End Try

 

[Miha Markic [MVP C#]]

HI Curt,

You should really read the .net help topic:
Updating the Database with a DataAdapter and the DataSet

 

[Curt Emich]

Thanks, Mark. Read it. Didn't find it helpful.

 

[Ron Allen]

Curt,
Your Insert command is hard-wired to only insert one value ('Yadda').
You need something like this
=New OleDbCommand("INSERT INTO Sessions (Notes) VALUES (?)",
Me.OleDbConnection1)
thisDataAdapter.InsertCommand.Parameters.Add("@notes", OleDbType.VarChar,
xx, "Notes")
' I'm just guessing that this is a VarChar field -- replace xx with the
field size for real code.
The parameters must be added in the order found in the INSERT/UPDATE
commands as they aren't specified by name for OleDb.

Note that the connection is specified for the insert command as well. You
should also define the Update and Delete commands for proper operation.
Also if this table doesn't have a PK it needs one and if it isn't
autogenerated by the db it needs to be set. If it is autogenerated you
should handle the OnRowUpdated event and retrieve the correct key there.
I'd reccommend a copy of 'ADO.NET Core Reference' by David Sceppa from
MS Press as it has a lot of good examples and discussions of proper coding
techniques.

Ron Allen