Solved

Multiple items to be inserted

Posted on 2006-11-04
2
164 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Zoom web page in asp.net 2 36
Performance enhancement 39 26
Total in textboxes 9 35
Unable  to create new object 9 24
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

15 Experts available now in Live!

Get 1:1 Help Now