473,847 Members | 2,462 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

OleDbDataAdapte r - merging two tables

Hello,

I am trying to plonk the entire contents of a dBase file into an
Access table.

I have scanned the dBase fields and created an empty Access table with
the same field formats.
I have also created oledb connections to both data sources.

Below is my attempt at achieving this, which doesn't work. I've
googled all day and had lots of clues but not found any code that
works for me. I think I'm close but obviously missing something quite
important.

It would be much appreciated if anyone could tell me what it is?
Private Sub test()

Dim strquerySource As String = "Select * from dBaseFile"
Dim strqueryDestina tion As String = "Select * from
accessTable"

Dim daSource As OleDbDataAdapte r
Dim daDestination As OleDbDataAdapte r

Dim tableSource As New DataTable
Dim tableDestinatio n As New DataTable

daSource = New OleDbDataAdapte r(strquerySourc e, cnDBASE)

'if line below is NOT used then no error but no update.
'if it IS used then get error as commented later.
daSource.Accept ChangesDuringFi ll = False

daSource.Fill(t ableSource)

'to visually check data - ITS OK, displays all data
Me.DataGridView DBASE.DataSourc e = tableSource
Me.DataGridView DBASE.Refresh()
'destination
daDestination = New OleDbDataAdapte r(strqueryDesti nation,
cnAccess)
daDestination.F ill(tableDestin ation)

'to visually check data - ITS OK, just shows headers
Me.DataGridView Access.DataSour ce = tableDestinatio n
Me.DataGridView Access.Refresh( )

'ERROR - IF daSource.Accept ChangesDuringFi ll = False
' "Update requires a valid InsertCommand when passed DataRow
collection with new rows."
' otherwisae no error but the Access table doesn't get
updated.

daDestination.U pdate(tableSour ce)
End Sub
Jun 27 '08 #1
6 1684
You can construct a system.drawing. point from the x and y co-ordinates so
you could save them as either a single string or as two seperate fields. I
would save each as a seperate field as it would be easier to report upon.

However, if you want to store them as two strings (or one string) the code
below shows how to reconstruct the system.drawing. point from a comma
seperated string.

Kind regards,

Rob
Code follows:

Dim sLocation As String = ""

sLocation = Me.Location.X.T oString + "," + Me.Location.Y.T oString

Me.Location = New System.Drawing. Point(0, 0)
MsgBox("moved")

Me.Location = New System.Drawing. Point(Split(sLo cation, ",")(0),
Split(sLocation , ",")(1))
MsgBox("moved back")

"baldrick" <ph**********@h otmail.comwrote in message
news:54******** *************** ***********@m44 g2000hsc.google groups.com...
Hello,

I am trying to plonk the entire contents of a dBase file into an
Access table.

I have scanned the dBase fields and created an empty Access table with
the same field formats.
I have also created oledb connections to both data sources.

Below is my attempt at achieving this, which doesn't work. I've
googled all day and had lots of clues but not found any code that
works for me. I think I'm close but obviously missing something quite
important.

It would be much appreciated if anyone could tell me what it is?
Private Sub test()

Dim strquerySource As String = "Select * from dBaseFile"
Dim strqueryDestina tion As String = "Select * from
accessTable"

Dim daSource As OleDbDataAdapte r
Dim daDestination As OleDbDataAdapte r

Dim tableSource As New DataTable
Dim tableDestinatio n As New DataTable

daSource = New OleDbDataAdapte r(strquerySourc e, cnDBASE)

'if line below is NOT used then no error but no update.
'if it IS used then get error as commented later.
daSource.Accept ChangesDuringFi ll = False

daSource.Fill(t ableSource)

'to visually check data - ITS OK, displays all data
Me.DataGridView DBASE.DataSourc e = tableSource
Me.DataGridView DBASE.Refresh()
'destination
daDestination = New OleDbDataAdapte r(strqueryDesti nation,
cnAccess)
daDestination.F ill(tableDestin ation)

'to visually check data - ITS OK, just shows headers
Me.DataGridView Access.DataSour ce = tableDestinatio n
Me.DataGridView Access.Refresh( )

'ERROR - IF daSource.Accept ChangesDuringFi ll = False
' "Update requires a valid InsertCommand when passed DataRow
collection with new rows."
' otherwisae no error but the Access table doesn't get
updated.

daDestination.U pdate(tableSour ce)
End Sub
Jun 27 '08 #2
Greetings,

I have observed that you can only use a DataAdapter to read data from an
Access table, but you can't Insert data to an Access table (or Update)
using an OleDataAdapter. Some reading I have done on this suggests that
this is intentional. So, the workaround that I have come up with is to
loop through your rows and insert each row individually using a straight
forward SelectCommand

For Each dr As DataRow In yourDataTable
da.SelectComman d.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToSt ring & "','" & dr("fld2").ToSt ring & "','" &
dr("fld3").ToSt ring & "'," & ...
da.SelectComman d.ExecuteNonQue ry
Next

You will have to delimit as required (single quotes for Text fields, and
probably for Date fields).

This may seem like a bit of a pain, but I feel that Microsoft is trying
to steer .Net away from Access -- keeping Access as a File-based com
system. I may be wrong, and if I am, hopefully someone else knows how
to use an OleDataAdapter Insert (and Update) command with Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #3
One Correction:

yourDataTable.R ows '--I forgot the .Rows part

For Each dr As DataRow In yourDataTable.R ows
da.SelectComman d.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToSt ring & "','" &
dr("fld2").ToSt ring & "','" & dr("fld3").ToSt ring & "',"
& ...
da.SelectComman d.ExecuteNonQue ry
Next


Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #4
On 27 May, 22:14, Rich P <rpng...@aol.co mwrote:
Greetings,

I have observed that you can only use a DataAdapter to read data from an
Access table, but you can't Insert data to an Access table (or Update)
using an OleDataAdapter. *Some reading I have done on this suggests that
this is intentional. *So, the workaround that I have come up with is to
loop through your rows and insert each row individually using a straight
forward SelectCommand

For Each dr As DataRow In yourDataTable
* da.SelectComman d.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToSt ring & "','" & dr("fld2").ToSt ring & "','" &
dr("fld3").ToSt ring & "'," & ...
* da.SelectComman d.ExecuteNonQue ry
Next

You will have to delimit as required (single quotes for Text fields, and
probably for Date fields).

This may seem like a bit of a pain, but I feel that Microsoft is trying
to steer .Net away from Access -- keeping Access as a File-based com
system. *I may be wrong, and if I am, hopefully someone else knows how
to use an OleDataAdapter Insert (and Update) command with Access.

Rich

*** Sent via Developersdexht tp://www.developersd ex.com***
Is it just Access? Are you saying that if the destination table was
SQL Server that it would work?

The code I'm going off is...

http://www.knowdotnet.com/articles/datasetmerge.html

Jun 27 '08 #5
I mean just Access. Last year I wrote DLL (VB2005 and C#)which I
explosed to the Com environment which used OLE and Sql DataAdapters for
manipulating data. I made references to this DLL from Access 2003 and
Excel 2003. The DLL worked fine with Excel but would error out when
used with Access. So I modified the DLL to read from a text file (I
would export data to a text file from Access) - then the DLL worked OK
from Access. So I deduced that it was the DataAdapter which did not
work with Access. I posted some questions on this, and someone (a tech
author) got back to me and suggested that this was intentional.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #6
Baldrick,

Please don't multipost, it is not my purpose to post multiple answers in
every newsgroup.

Use crossposting next time, which is sending the same message to all
newsgroups, that can be about your question.

We can then see if your question is already answered.

Thanks in advance.

Cor

"baldrick" <ph**********@h otmail.comschre ef in bericht
news:54******** *************** ***********@m44 g2000hsc.google groups.com...
Hello,

I am trying to plonk the entire contents of a dBase file into an
Access table.

I have scanned the dBase fields and created an empty Access table with
the same field formats.
I have also created oledb connections to both data sources.

Below is my attempt at achieving this, which doesn't work. I've
googled all day and had lots of clues but not found any code that
works for me. I think I'm close but obviously missing something quite
important.

It would be much appreciated if anyone could tell me what it is?
Private Sub test()

Dim strquerySource As String = "Select * from dBaseFile"
Dim strqueryDestina tion As String = "Select * from
accessTable"

Dim daSource As OleDbDataAdapte r
Dim daDestination As OleDbDataAdapte r

Dim tableSource As New DataTable
Dim tableDestinatio n As New DataTable

daSource = New OleDbDataAdapte r(strquerySourc e, cnDBASE)

'if line below is NOT used then no error but no update.
'if it IS used then get error as commented later.
daSource.Accept ChangesDuringFi ll = False

daSource.Fill(t ableSource)

'to visually check data - ITS OK, displays all data
Me.DataGridView DBASE.DataSourc e = tableSource
Me.DataGridView DBASE.Refresh()
'destination
daDestination = New OleDbDataAdapte r(strqueryDesti nation,
cnAccess)
daDestination.F ill(tableDestin ation)

'to visually check data - ITS OK, just shows headers
Me.DataGridView Access.DataSour ce = tableDestinatio n
Me.DataGridView Access.Refresh( )

'ERROR - IF daSource.Accept ChangesDuringFi ll = False
' "Update requires a valid InsertCommand when passed DataRow
collection with new rows."
' otherwisae no error but the Access table doesn't get
updated.

daDestination.U pdate(tableSour ce)
End Sub
Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4966
by: svdh | last post by:
I have posed a question last saturday and have advanced alot in the meantime. But I am still not there Problem is that I try to merging various fields from various tables in one document in Word 1. Query..I want to keep the fields seperatred. I do not want to sent on field with all accumulated languages from one person to Word. Each language should appear in the document in a separate cell Cross tables are not delivering the result I...
2
3824
by: Son Ha | last post by:
I want to copy some record from a Access database to another Access DB. My code as follow but not working. The destAdapter.Update() return 0 record affected. Tell me what's wrong in my code? public void CopyDataSet(int start, int end) { OleDbDataAdapter sourceAdapter = new OleDbDataAdapter("SELECT * FROM " + "WHERE ID > " + (start - 1).ToString() + " AND .id < " + (end + 1).ToString(), connectionString
2
1356
by: Dan Cooper | last post by:
I've got two datasets, each containing a single data table. dstDataSetA.Tables("TableA") dstDataSetB.Tables("TableB") I want to merge them together and delete any non-matching rows. dstDataSetB.Merge(dstDataSetA) doesn't do this. Any ideas or suggestions gratefully received.
0
5832
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database which indexes computer magazine articles for personal reference. I am developing a Visual Basic.NET program whose sole purpose is to enter new records into the database. No updates to existing entries, no deletions, and no display
7
1774
by: Jon Vaughan | last post by:
I have 2 datasets , one returned as a dataset from a webservice and one created client side form the same stored procedure that is returned from the webservice. I then try and merge the data, but they dont seem to merge. When I return a single table , this method works fine, but im returning 3 tables and the merge isnt happening. Is there anyway to find out how I can why a merge isnt occuring, as the merge doesnt return any errors,
1
3406
by: actimel01 | last post by:
I know this question has been asked many times before but I can't find an answer that fits my data! I have two Access databases. The tables in each have the exactly the same fields except for one (which was added into the tables in the second database). Thye do have different records. I want to merge the records from the tables from in the first database into the tables in the second. Other answers have suggested merging the tables in a...
1
1601
by: explode | last post by:
I made a oledbdataadapter with this code Public Sub Novo(ByVal nova1 As String, ByVal nova2 As String) Dim i As Integer Dim nova As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter Dim veza As OleDb.OleDbConnection = New OleDb.OleDbConnection(Global.Diplomski1.My.MySettings.Default.Studenti1Connection) Dim tableMapping As System.Data.Common.DataTableMapping = New System.Data.Common.DataTableMapping("Marko", "Table1")
2
2939
by: explode | last post by:
I made nova oledbdataadapter select update insert and delete command and connection veza. dataset is Studenti1data, I made it by the new data source wizard,and made datagridview and bindingsource draging Table1 to Form2. The select command works fine, but when I change the data and call update command I get a syntax error: System.Data.OleDb.OleDbException was unhandled ErrorCode=-2147217900 Message="Syntax error in UPDATE statement."...
0
1194
by: =?Utf-8?B?QmFkaXM=?= | last post by:
Hi, I'm trying to do mail merging in C#.NET and in one methods: Word.Application wrdApp; Word._Document wrdDoc; Object oMissing = System.Reflection.Missing.Value; Object oFalse = false; private void FillRow(Word._Document oDoc, int Row, string Text1,
0
9879
marktang
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...
0
10978
Oralloy
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...
0
10643
jinu1996
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...
0
10330
tracyyun
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...
0
9477
agi2029
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...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5907
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3158
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.