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 2206
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**@gladsto ne-inc.com> wrote in message
news:vq******** ****@corp.super news.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.ne t> wrote in message
news:Ob******** ******@TK2MSFTN GP11.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**@gladsto ne-inc.com> wrote in message news:vq******** ****@corp.super news.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**@gladsto ne-inc.com> wrote in message
news:vq******** ****@corp.super news.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.ne t> wrote in message news:Ob******** ******@TK2MSFTN GP11.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**@gladsto ne-inc.com> wrote in message news:vq******** ****@corp.super news.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***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:OY******** ******@TK2MSFTN GP11.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**@gladsto ne-inc.com> wrote in message news:vq******** ****@corp.super news.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.ne t> wrote in message news:Ob******** ******@TK2MSFTN GP11.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**@gladsto ne-inc.com> wrote in message news:vq******** ****@corp.super news.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 test where username='2323'" Any ideas? Thanks!
<%
Dim objRS, sqlStmt
Set objRS = Server.CreateObject ("ADODB.Recordset")
sqlStmt = "insert into test VALUES ('2341', '2341');"
|
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 sql statement). Using
the same program, when i insert 20000 records from 1 process, time is
about 100 seconds. However, when i insert 1000 records from 20
process, time is about 1000 seconds, which is 10 times.
Since i am developing a OLTP system...
|
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 commits it. How does the other user get the updated
view without polling for changes? Is there some sort of callback mechanism
that can be set up on the dataset or connection?
TIA
|
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 5 mins. How can i
reduce this time?? Can u plz help me to reduce this insertion time...
Thanks in advance
Kokul
|
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 DS before I can add records, since
DS does not exist. I'd rather not have a SELECT since I really don't
need any records in my dataset that I will be adding a new record to.
Dim dsNewRow As DataRow
| |
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 certain that MyVar will be
set to the key-field value that was created
when the Append query ran. Now, there are
other ways to do it - I know - that will ensure
you 'nab' the correct record. But I was wondering
|
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 per line (this is
in order to make the process of adding new IDs easy, since it's only
done at time of configuration).
I have no problem with retrieving the contents of the textBox,
splitting it into a string array, validating the individual...
|
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 the original
table (stockmovements).
My application is writtend in Borland Delphi. (interfacing with db2
with sql statements and stored procedure written in SQL)
|
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 as I'm only a beginner. However the code does work if I'm using a select statement to retrieve records, but it does not work when I'm trying to do an insert statement.
Any pointers/adivce would be really appreciated! The error is thrown on...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |