Insert ONE row into ONE table in ONE Mdb using OLEDB DataSet. FULL DISCLOSURE (No Pseudocode)

After many different (failed) attempts at the same seemingly BASIC task I am forced to go REMEDIAL in my question.

No points will be awarded unless the code or explanation is something that is verifiable and repeatable. I am sorry that I have to make this stipulation but I have had enough of the "soft" solutions I have found through MSDN,EE,etc.  (i.e many different solution paths to do same thing that sometimes work and sometimes do not)

Something as oft-repeated as inserting A ROW into A DATABASE using an OLEDB DataSet MUST have a degenerate BASE command sequence that cannot be simplified further and WORKS every time. I realize there are a lot of factors to consider but I am only interested in the most BASIC checklist of requirements to get data into a table.

That being said here is the setup:

One MDB: MasterProblemList.mdb
One Table in MDB: MedicalProblemList
Five Fields in Table: UniqueID(PK),Teir1,Teir2,Teir3,Teir4

Objects in VB.Net code:

 Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "F:\BGV\Visual Studio Projects\VB.Net projects\Stack test\MedProblemListStacktest\MedProblemListStacktest\MasterProblemList.mdb"

Dim cnn As New OleDb.OleDbConnection   'Connection
Dim ds As New Data.DataSet()                  'Dataset
Dim dtMPL As New DataTable                     'Table
Dim daMPL As New OleDb.OleDbDataAdapter   'Data adapter
Dim drMPL As DataRow                    'Datarow
Dim dtmMPL As New DataTableMapping       'Table mapping
Dim cmd As New OleDb.OleDbCommand      'Command

Question: In order to INSERT ONE ROW into the MDB,what sequence of commands and assignments (using these objects) is required? I am particuliarly interested in what mechanism actually writes the DataSet changes to the source database. Yeah, I've tried .Update with .EndEdit,etc.-doesn't do a thing. Again-verifiable and repeatable steps only.
Who is Participating?
SanclerConnect With a Mentor Commented:
The following assumes that the UniqueID(PK) is an Access Autonumber field.

'Write connection string - you've got that
 Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "F:\BGV\Visual Studio Projects\VB.Net projects\Stack test\MedProblemListStacktest\MedProblemListStacktest\MasterProblemList.mdb"

'Assign to Connection - can do it with the Dim Statement
Dim cnn As New OleDb.OleDbConnection(strConnString)   'Connection

'Write SQL Select statement
Dim mySQL as String = "SELECT * FROM MedicalProblemList"

'Assign Select statement and connection to dataadapter - can do it with the Dim statement
Dim daMPL As New OleDb.OleDbDataAdapter(mySQL, cnn)   'Data adapter

'Fill datatable - no need for dataset or table mapping
Dim dtMPL As New DataTable                     'Table

'Use command builder to generate Insert statement - no need for separate oledbcommand
Dim cb As New OleDbCommandBuilder(daMPL)

'Create new row from table
Dim drMPL As DataRow = dtMPL.NewRow                    'Datarow

'Code needed here to put values in the new row
'drMPL("Teir1") = ?
'drMPL("Teir2") = ?
'drMPL("Teir3") = ?
'drMPL("Teir4") = ?

'Add new row to table

'Update database

Provided the conditions you have stated are met (and the assumption about the PK field is correct) this should work every time.  It is about as basic a sequence as you can get, and simplified as far as possible by getting a command builder to create the InsertCommand and its associated parameters.  Adding the new row to the table sets its row state flag to indicate that it needs saving.  The update method then calls the InsertCommand.  

An error that is often made, which causes apparently similar methods not to work, is that .AcceptChanges is called before the .Update.  But that cancels the row state flags, so that the InsertCommand does not recognise any new row as requiring saving.  You didn't ask that, but it may explain some of your failed experiences.

kkammAuthor Commented:

Thank you for the response. I was actually able to get my code working shortly after posting. I was missing the dtMPL.Rows.Add(drMPL) command. Points are awarded for positive confirmation of the sequence of working commands.

I am just digging into .Net and I was a little frustrated by all the long and cluttered descriptions of Dataset usage. A concise base-case explanation was needed and hopefully this entry will help others in a similiar situation.

Thanks again
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.