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 6 1672
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
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 ***
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 ***
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
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 ***
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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 -...
|
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.
|
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...
|
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...
| |
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...
|
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...
|
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:
...
|
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,
|
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...
|
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...
| |
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. ...
|
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...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
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...
|
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...
| |