[AccessD] VB.Net using ADO.Net to pass data from one DB toAnother

Hi Vlad,

I'm glad it helped.
These are "MS Gods"  (Anders Hejlsberg first of all -
http://en.wikipedia.org/wiki/Anders_Hejlsberg) who created that "miracle".

As Gustav noted the better place to ask .NET related questions here is
dba-VB.

BTW, it should be possible to avoid using temp db/tables while importing
.csv files.
There are many approaches - the simplest one is to just write a wrapper
class getting .csv file loaded into memory
(System.IO.File.ReadAllLines(...)), and splitting every line into fields
presented as properties with all lines returned as an Array or List<...> ...

When importing .csv files into MS SQL databases there is a "trick" to wrap a
.csv file into a custom IDataReader and to use SqlBulkCopy. Then importing
of .csv files becomes a "snap"...

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 27 ?????? 2010 ?. 17:30
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] VB.Net using ADO.Net to pass data from one DB
toAnother

Hi Shamil,

You're a God! I've got it working thanks to you... Thank you, thank you,
thank you! I was going nuts trying to get that to work. Wasted 2 days on
it... Here is the converted code to VB.Net:

    Public Sub AppendTempDataToMainTbl()
        Dim sourceDbFullPath As String = App_Path() & "TempImport.mdb" 
        Dim destionationDbFullPath As String = GetIniSetting("DBPath",
"SystemSettings") 

        Using sourceConnection As New
OleDbConnection(GetSourceConnectionString) 
            sourceConnection.Open()
            Dim sql1 As String = "SELECT * FROM tblTempImport WHERE TempID >
10" 
            Dim cmd1 As New OleDbCommand(sql1, sourceConnection)
            Dim rdr As OleDbDataReader = cmd1.ExecuteReader()

            Using destinationConnection As New
OleDbConnection(GetDestConnectionString) 
                destinationConnection.Open()

                While rdr.Read()
                    Dim sql2 As String = String.Format("INSERT INTO
tblMainData (RecDate, RecTime) values ('{0}','{1}')",
rdr("RecDate").ToString(), rdr("RecTime").ToString()) 
                    Dim cmd2 As New OleDbCommand(sql2,
destinationConnection)
                    cmd2.ExecuteNonQuery()
                End While
            End Using

            rdr.Close()
        End Using

    End Sub

Many thanks again...

Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Saturday, 27 November 2010 11:51 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] VB.Net using ADO.Net to pass data from one DB
toAnother

Hi Vlad,

You can do it like that (C#):

using System.Data.OleDb;

namespace DotNetLanguages
{
    public class TestClass
    {
        public static void Run()
        {
            string sourceDbFullPath = @"c:\temp\sourceDb.mdb";
            string destionationDbFullPath = @"c:\temp\targetDb.mdb";

            using (OleDbConnection sourceConnection = new
OleDbConnection(MakeAccDdbConnectionStr(sourceDbFullPath)))
            {
                sourceConnection.Open();
                string sql1 = "select [SourceField] from [SourceTable]";
                OleDbCommand cmd1 = new OleDbCommand(sql1,
sourceConnection);
                OleDbDataReader rdr = cmd1.ExecuteReader();
 
                using (OleDbConnection destinationConnection = new
OleDbConnection(MakeAccDdbConnectionStr(destionationDbFullPath)))
                {
                    destinationConnection.Open();  

                    while (rdr.Read())
                    {
                        string sql2 = string.Format("insert into
[MyTable](MyField) values ('{0}')", rdr["SourceField"].ToString());
                        OleDbCommand cmd2 = new OleDbCommand(sql2,
destinationConnection);
                        cmd2.ExecuteNonQuery();  
                    }
                }
                rdr.Close();
 
            }
        }

        public static string MakeAccDdbConnectionStr(string fullPath)
        {
            {
                return string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0}",
                    fullPath);
            }
        }
    }
} 

Watch line wraps.

For VB.NEt version you can use free online translation
http://www.developerfusion.com/tools/convert/csharp-to-vb/

There could be myriads other ways to do your task in .NET - the above
working sample is just a "quick & dirty" solution but it should work well
(watch out to duplicate quotes if you have some in your source data
fields)...


Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 27 ?????? 2010 ?. 12:30
To: 'access group'
Subject: [AccessD] VB.Net using ADO.Net to pass data from one DB to Another

Hi Everyone,

 

Long time no speak.

 

Sorry for posting a VB.Net and ADO.Net question here, but I've wasted so
much time on this, I thought maybe someone from my old AccessD Group might
know the answer. This is my first solo attempt at a VB.Net app, so be kind.

 

I am developing quite simple application for work that imports a CSV file
into an Access DB that is created on the fly for the task. Everything works
as expected and the data is posted to the table ready for
manipulation/changing and prepared for the transfer and import into the file
separate Access DB.

 

For the life of me I can't figure out how to open 2 separate DB's and
transfer data from one to the other. In fact, trying to find decent examples
of using VB.Net with ADO.Net is in itself a task.

 

Can anyone here please give me an example? One that shows the insert data
syntax? Something like I'm used to would be handy as in similar methodology
to DAO like:

 

Dim dbs01 as dao.database 'Source DB

Dim dbs02 as dao.database 'Destination DB

Dim rst01 as dao.recordset

Dim rst02 as dao.recordset

Dim StrSQL as string

 

Set dbs01 = CurrentDb()

Set dbs02 = OpenDatabase(BELocation)

 

Etc etc ..

 

With rst01

                Do until rst01.EOF

                                With rst02

                                                !MyField = rst01!SourceField

                                End with

                loop

End with

 

Any help would be most appreciated.

 

Thanks

 

Vlad



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com







-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com