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

Posted on 2006-05-17
Last Modified: 2009-02-01
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.
Question by:kkamm
    LVL 34

    Accepted Solution

    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.

    LVL 1

    Author Comment


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    Convert money to nchar 4 34
    Wordmerge 8 30
    Richtextbox formatting 8 18
    Find key in dictionary list 6 19
    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now