My goal is to get data from an XML file into a couple of tables in an Access
database. The XML file is a little complex so I need control over what I do
(I can't just read it into a dataset).
The way I have it now is ennumerating through my XML file and for each
record, running an INSERT INTO SQL statement to put the values in the
database.
Is this going to be okay with 50 or so records (hitting the database with 50
or so INSERT commands for each record) or one of the other methods:
1) continue using an INSERT INTO statement for each record or
2) use an ADO recordset or
3) make a datatable from the XML, (but it is kind of complex though) and
then sync it up with the database.
Thanks for the help!
Mike 4 2177
I'd say the Insert Into statements are a worthy choice.
You might choose to batch them together to reduce network traffic and speed
up the performance.
--
I hope this helps,
Steve C. Orr, MCSD, MVP http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com
"Mike Hnatt" <do**@gladstone-inc.com> wrote in message
news:vq************@corp.supernews.com... My goal is to get data from an XML file into a couple of tables in an
Access database. The XML file is a little complex so I need control over what I
do (I can't just read it into a dataset).
The way I have it now is ennumerating through my XML file and for each record, running an INSERT INTO SQL statement to put the values in the database.
Is this going to be okay with 50 or so records (hitting the database with
50 or so INSERT commands for each record) or one of the other methods:
1) continue using an INSERT INTO statement for each record or 2) use an ADO recordset or 3) make a datatable from the XML, (but it is kind of complex though) and then sync it up with the database.
Thanks for the help! Mike
Thanks Steve,
After reading a bit, I realized that the best strategy was to take the
table(s) from the database and load them into a dataset (but to keep the
dataset small, I used SELECT * FROM MyTable WHERE 1=2 so as to have no
records). I then set the command builder to an insertcommand. Then all I
had to do was load my tables in the dataset with the info, then when all was
ready I could make one update to the dataset. Pretty neat for .NET! (as
opposed to the old recordsets in ADO).
Mike
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:Ob**************@TK2MSFTNGP11.phx.gbl... I'd say the Insert Into statements are a worthy choice. You might choose to batch them together to reduce network traffic and
speed up the performance.
-- I hope this helps, Steve C. Orr, MCSD, MVP http://Steve.Orr.net Hire top-notch developers at http://www.able-consulting.com "Mike Hnatt" <do**@gladstone-inc.com> wrote in message news:vq************@corp.supernews.com... My goal is to get data from an XML file into a couple of tables in an Access database. The XML file is a little complex so I need control over what
I do (I can't just read it into a dataset).
The way I have it now is ennumerating through my XML file and for each record, running an INSERT INTO SQL statement to put the values in the database.
Is this going to be okay with 50 or so records (hitting the database
with 50 or so INSERT commands for each record) or one of the other methods:
1) continue using an INSERT INTO statement for each record or 2) use an ADO recordset or 3) make a datatable from the XML, (but it is kind of complex though) and then sync it up with the database.
Thanks for the help! Mike
Just a side technical note, as you have already solved the problem
correctly. The only way to do multiple record inserts is to insert each
record individually. When you use a DataSet to do multiple inserts, it does
them individually, but automatically. That makes it, as you've noticed, an
excellent tool for the job.
--
HTH,
Kevin Spencer
Microsoft MVP
..Net Developer http://www.takempis.com
Big Things are made up of
Lots of Little Things.
"Mike Hnatt" <do**@gladstone-inc.com> wrote in message
news:vq************@corp.supernews.com... Thanks Steve, After reading a bit, I realized that the best strategy was to take the table(s) from the database and load them into a dataset (but to keep the dataset small, I used SELECT * FROM MyTable WHERE 1=2 so as to have no records). I then set the command builder to an insertcommand. Then all I had to do was load my tables in the dataset with the info, then when all
was ready I could make one update to the dataset. Pretty neat for .NET! (as opposed to the old recordsets in ADO). Mike
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message news:Ob**************@TK2MSFTNGP11.phx.gbl... I'd say the Insert Into statements are a worthy choice. You might choose to batch them together to reduce network traffic and speed up the performance.
-- I hope this helps, Steve C. Orr, MCSD, MVP http://Steve.Orr.net Hire top-notch developers at http://www.able-consulting.com "Mike Hnatt" <do**@gladstone-inc.com> wrote in message news:vq************@corp.supernews.com... My goal is to get data from an XML file into a couple of tables in an Access database. The XML file is a little complex so I need control over
what I do (I can't just read it into a dataset).
The way I have it now is ennumerating through my XML file and for each record, running an INSERT INTO SQL statement to put the values in the database.
Is this going to be okay with 50 or so records (hitting the database with 50 or so INSERT commands for each record) or one of the other methods:
1) continue using an INSERT INTO statement for each record or 2) use an ADO recordset or 3) make a datatable from the XML, (but it is kind of complex though)
and then sync it up with the database.
Thanks for the help! Mike
I have nothing against this dataset method. It certainly does make things
simple.
But in many cases you can get better performance by batching multiple insert
statements together into one long command string. That way only one
statement is sent across your network as opposed to N number of individual
statements.
I guess it depends on your priorities. Simplicity vs. Performance.
Of course both methods get significantly more complex when multiple user
concurrency issues are involved.
--
I hope this helps,
Steve C. Orr, MCSD, MVP http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com
"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:OY**************@TK2MSFTNGP11.phx.gbl... Just a side technical note, as you have already solved the problem correctly. The only way to do multiple record inserts is to insert each record individually. When you use a DataSet to do multiple inserts, it
does them individually, but automatically. That makes it, as you've noticed, an excellent tool for the job.
-- HTH,
Kevin Spencer Microsoft MVP .Net Developer http://www.takempis.com Big Things are made up of Lots of Little Things.
"Mike Hnatt" <do**@gladstone-inc.com> wrote in message news:vq************@corp.supernews.com... Thanks Steve, After reading a bit, I realized that the best strategy was to take the table(s) from the database and load them into a dataset (but to keep the dataset small, I used SELECT * FROM MyTable WHERE 1=2 so as to have no records). I then set the command builder to an insertcommand. Then all
I had to do was load my tables in the dataset with the info, then when all was ready I could make one update to the dataset. Pretty neat for .NET!
(as opposed to the old recordsets in ADO). Mike
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message news:Ob**************@TK2MSFTNGP11.phx.gbl... I'd say the Insert Into statements are a worthy choice. You might choose to batch them together to reduce network traffic and speed up the performance.
-- I hope this helps, Steve C. Orr, MCSD, MVP http://Steve.Orr.net Hire top-notch developers at http://www.able-consulting.com "Mike Hnatt" <do**@gladstone-inc.com> wrote in message news:vq************@corp.supernews.com... > My goal is to get data from an XML file into a couple of tables in
an Access > database. The XML file is a little complex so I need control over what I do > (I can't just read it into a dataset). > > The way I have it now is ennumerating through my XML file and for
each > record, running an INSERT INTO SQL statement to put the values in
the > database. > > Is this going to be okay with 50 or so records (hitting the database with 50 > or so INSERT commands for each record) or one of the other methods: > > 1) continue using an INSERT INTO statement for each record or > 2) use an ADO recordset or > 3) make a datatable from the XML, (but it is kind of complex though) and > then sync it up with the database. > > Thanks for the help! > Mike > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matthew Louden |
last post by:
The following ASP code yields the following error, but actually the new
record is stored
in database. The same error happens when the application deletes a record,
such as
sqlStmt ="delete from...
|
by: and |
last post by:
hi everyone,
i am using oracle 9.2.0 and i have written a simple jdbc java program
to insert a record within a for loop to a table using jdbc thin
driver(refer to the bottom of this email for the...
|
by: Charles Law |
last post by:
Here's one that should probably have the sub-heading "I'm sure I asked this
once before, but ...".
Two users are both looking at the same data, from a database. One user
changes the data and...
|
by: kokul_k |
last post by:
Hi..
I'm facing a problem during inserting records in Mysql database. My
table consists of 9 fields. When i try to insert 1000 records to this
table using for loop it takes a lot of time.. nearly...
|
by: vbDavidC |
last post by:
I am adding a new record to a table via a dataset/adapter.
I have got the following to work for me but I am wondering if there is
a better way to do this.
I am having to have something in my...
|
by: MLH |
last post by:
Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("","MyTable...
You can never be...
|
by: EricLondaits |
last post by:
Hi,
I have an ASP.NET page with a ListBox that is data bound to a table
with a single field (it holds a list of valid IDs). The page also has
a textBox into which you can add new valid IDs, one...
|
by: John Hopfield |
last post by:
hi all,
I have a table with lots of stock movements. ( stockmovements)
Once per year the user need to run a routine that move these records
into a table (history) and delete the movements from...
|
by: pabs1111 |
last post by:
Hi All
I'm trying to insert records using VBA in Access into a mysql database and I'm getting the above error. The code I'm using I have found on the web, and in all frankness don't understand it...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| | |