Solved

Multiple items to be inserted

Posted on 2006-11-04
2
182 Views
Last Modified: 2010-04-23
Hi folks

Im thinking about the best way to handle inserting multiple inserts. I have a form that generates multiple records that need to be inserted into a access file. I could use multiple insert statements or a dataadapter.update ( which im a bit clueless about). It seems to me multiple inserts in inefficient.

Any help would be appreciated
Steve
0
Comment
Question by:stephenz43
2 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17873823
In fact, multiple inserts - coded efficiently - can be more efficient than a dataadapter.  That is because a dataadapter is, really, only a user-friendly wrapper for multiple commands - Delete, Insert, Update on each datatable row that needs it - and because it can do all sorts of things besides inserts, the overhead relating to those can make it slower than custom-written code.  But, having said that, the effect would only be noticeable where the "multiple" meant (probably) hundreds.  And, even then, the ease of use (in terms of coding) a dataadapter may be an overriding advantage.

So what is it you need to know about dataadapters?  For a start, here's an extract from a recent post of mine

>>
You only need two objects declared at form level

    Private myAdapter As OleDbDataAdapter
    Private myTable As New DataTable

There is no harm in declaring others - e.g. connection, currencymanager - at form level if you wish, but, with just those two you can manage.

To function properly, myAdapter will need instantiating - for which it will need a connection and a select statement - and the necessary update commands will need to be created for it.  Once myAdapter has been set up it can then be used to fill myTable and it can be accessed from anywhere on the form to update the database.  So the form load sub needs code like this

   'create the connection that we need for myAdapter
   Dim ConnectionString As String = <your string goes here>
   Dim con As New OleDbConnection(ConnectionString)

   'instantiate myAdapter
   myAdapter = New OleDbDataAdapter("select * from users", con)
   'as the connection is now "part of" myAdapter we won't need to refer to it again

   'get the update commands
   dim cb As New OleDbCommandBuilder(myAdapter)
   'the update commands are now, in effect, "part of" myAdapter
   'technically, that is not strictly correct, but it's OK to treat it as if it were correct

   'use myAdapter to fill myTable
   myAdapter.Fill(myTable)

   'bind myTable to the DataGridView
   DataGridView1.DataSource = myTable

And that's enough to get things started.

Now, when any change is made to the data in the datagridview it will also be made in myTable.  This won't automatically happen as soon as any change is made in a single cell, but it will happen automatically as soon as the suer moves to another row.  But your code can force it to happen even while the focus remains on the same row with this code

   BindingContext(myTable).EndCurrentEdit

Whenever you then want changes that have been made in the datatable to get back to the database all the code you need is

   'make sure first that there are no outstanding edits
   BindingContext(myTable).EndCurrentEdit
   'then update the database from the datatable
   myAdapter.Update(myTable)

It's up to you where you put that code.  It is not necessary (so far as datahandling within your app is concerned) for it to be called every time any record is altered.  All alterations - deletions, insertion of new records, amendment of existing records - are saved up by the datatable and, when that code is called, the dataadapter (or the commandbuilder on its behalf) will check each record to see what, if anything, needs doing and use the appropriate command - Delete, Insert or Update - to do it.  But, on the other hand, if you feel you need to do it after every change, you can have buttons to achieve that.  Obviously, you will want to make sure it is called at least once, before your app closes.

There can be all sorts of bells and whistles added to the basic code outlined above.  But, as it stands, it will do the job.
<<

That was written for a specific context, but the meat of it will apply to all contexts.

If it's not specific enough for your purposes, come back with more detailed questions.

Roger
0
 

Author Comment

by:stephenz43
ID: 17873857
Roger...thanks for the insight....and guidence...

Steve
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 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