Solved

Multiple items to be inserted

Posted on 2006-11-04
2
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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