Vb newb needs help on data import/export

[joaotsetsemoita]

Hello everyone,

im completly new to vb.net and I was assigned to do a simple piece of
software that just had to select from um db in a MS access data base
and insert into a SQL server Database. The structure tables are
exactly the same so there's no need in data conversation.

My idea was to fill a datatable with the results from my
oledbdataadapter and then use that dataset to update on my
sqldataadapterm, however I cant find the way to specify the connection
string to my sqldataadapter to my SQL database. Probably this is not
the best way to achieve this but got this idea from this link:
http://www.dotnet247.com/247reference/msgs/40/203742.aspx where this
guys is trying to do exactly the same thing I want.

My code looks like this.

Dim strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" &
Application.StartupPath & "\sapo.mdb"
Dim cnConn As New OleDbConnection(strConn)
Dim accessAdapter As New OleDbDataAdapter("select * from
transactions", cnConn)
Dim dtAccessData As New DataTable
accessAdapter.Fill(dtAccessData)
Dim sqlAdapter As New SqlDataAdapter
sqlAdapter.Update(dtAccessData)

Perhaps some sugestion how to put this code working or some other
sugestion how to achieve this goal by any other way.

Any help is highly appreciated

Thanks in advance

Joao

 

[Charlie Brown]

Hello everyone,

im completly new to vb.net and I was assigned to do a simple piece of
software that just had to select from um db in a MS access data base
and insert into a SQL server Database. The structure tables are
exactly the same so there's no need in data conversation.

My idea was to fill a datatable with the results from my
oledbdataadapter and then use that dataset to update on my
sqldataadapterm, however I cant find the way to specify the connection
string to my sqldataadapter to my SQL database. Probably this is not
the best way to achieve this but got this idea from this link:http://www.dotnet247.com/247reference/msgs/40/203742.aspxwhere this
guys is trying to do exactly the same thing I want.

My code looks like this.

Dim strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" &
Application.StartupPath & "\sapo.mdb"
Dim cnConn As New OleDbConnection(strConn)
Dim accessAdapter As New OleDbDataAdapter("select * from
transactions", cnConn)
Dim dtAccessData As New DataTable
accessAdapter.Fill(dtAccessData)
Dim sqlAdapter As New SqlDataAdapter
sqlAdapter.Update(dtAccessData)

Perhaps some sugestion how to put this code working or some other
sugestion how to achieve this goal by any other way.

Any help is highly appreciated

Thanks in advance

Joao

You have the correct idea already, but you need to make a separate
dbConnection for the sqlAdapter.

Dim strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" &
Application.StartupPath & "\sapo.mdb"
Dim cnConn As New OleDbConnection(strConn)
Dim accessAdapter As New OleDbDataAdapter("select * from
transactions", cnConn)
Dim dtAccessData As New DataTable
accessAdapter.Fill(dtAccessData)
cnConn.Close()
accessAdapter.Dispose()
cnConn.Dispose()

Dim sqlConnString as String = "YOUR SQL CONNECTION STRING HERE"
Dim dbConn as New SqlClient.SqlConnection(sqlConnString)
Dim dbCmd as New SqlClient.SqlCommand("INSERT SQL STATEMENT", dbConn)
Dim sqlAdapter As New SqlDataAdapter(dbCmd)
sqlAdapter.Update(dtAccessData)
dbConn.Close()
sqlAdapter.Dispose()
dbCmd.Dispose()
dbConn.Dispose()

Look at the additional lines of code, its a down and dirty version,
and could be cleaner but should get you going. Always remember to
close your connections and dispose of objects that implement
iDisposable.

 

[rowe_newsgroups]

You have the correct idea already, but you need to make a separate
dbConnection for the sqlAdapter.

Dim strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" &
Application.StartupPath & "\sapo.mdb"
Dim cnConn As New OleDbConnection(strConn)
Dim accessAdapter As New OleDbDataAdapter("select * from
transactions", cnConn)
Dim dtAccessData As New DataTable
accessAdapter.Fill(dtAccessData)
cnConn.Close()
accessAdapter.Dispose()
cnConn.Dispose()

Dim sqlConnString as String = "YOUR SQL CONNECTION STRING HERE"
Dim dbConn as New SqlClient.SqlConnection(sqlConnString)
Dim dbCmd as New SqlClient.SqlCommand("INSERT SQL STATEMENT", dbConn)
Dim sqlAdapter As New SqlDataAdapter(dbCmd)
sqlAdapter.Update(dtAccessData)
dbConn.Close()
sqlAdapter.Dispose()
dbCmd.Dispose()
dbConn.Dispose()

Remember to open those connection objects first....

Look at the additional lines of code, its a down and dirty version,
and could be cleaner but should get you going. Always remember to
close your connections and dispose of objects that implement
iDisposable.

I would also recommend either wrapping the methods in a Using block or
using try...finally structure to make sure the Db objects get
disposed. Also, IIRC, closing the connection is unnecessary if you are
calling Dispose.

Thanks,

Seth Rowe

 

[joaotsetsemoita]

Remember to open those connection objects first....


I would also recommend either wrapping the methods in a Using block or
using try...finally structure to make sure the Db objects get
disposed. Also, IIRC, closing the connection is unnecessary if you are
calling Dispose.

Thanks,

Seth Rowe- Hide quoted text -

- Show quoted text -

Thanks for your replies, I will try that however something is still
missing me.

If in the first piece of code I assign all the results of the "select
* from transactions" to a datatable, why do I need an insert and how
is gonna be that insert, in the second piece of code? Can't I just
pick the datatable and roughly insert it in the SQL server data base
using the sqldataadapter.update?

 

[Miro]

Just a quick FYI...
There is a data import thru the SQL Server.
In SQL Server 2005 it calls it ""SSIS" "SQL Sever Integration Services".
It is launched form the same SQL Server Management Studio database
"Tasks" option.
Apparently the option is very simple and intuitive.

Might save you a lot of time.

I have never used this ..but i pulled this out of the book I'm reading.

M.

 

[joaotsetsemoita]

Just a quick FYI...
There is a data import thru the SQL Server.
In SQL Server 2005 it calls it ""SSIS" "SQL Sever Integration Services".
It is launched form the same SQL Server Management Studio database
"Tasks" option.
Apparently the option is very simple and intuitive.

Might save you a lot of time.

I have never used this ..but i pulled this out of the book I'm reading.

M.







- Show quoted text -

Hi Miro. Thanks for you tip but im using sql express 2005 which doesnt
have any import/export feature.

Anyway, I need to transfer data in a daily bases from an access DB to
a SQL db. This transfer will hapen when a new access db will be FTP to
the server every morning. Must be an automated system. That's why I
need to transfer the data using a VB program.

 

[Charlie Brown]

Thanks for your replies, I will try that however something is still
missing me.

If in the first piece of code I assign all the results of the "select
* from transactions" to a datatable, why do I need an insert and how
is gonna be that insert, in the second piece of code? Can't I just
pick the datatable and roughly insert it in the SQL server data base
using the sqldataadapter.update?- Hide quoted text -

- Show quoted text -

The dataadapter will require that you provide it with an update or
insert command unless you use a CommandBuilder to create one from your
SQL SELECT statement.

To use a commandbuilder, check out the following article
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

Basically you give the Adapter a SELECT command, then call
CommandBuilder.GetUpdateCommand and will populate your dataadapter
with the correct information.

As per the comment from Seth, you will need to open the connection
first by calling dbConn.Open prior to the Update method. You do not
need to do that before the Fill method, as fill will open the
connection for you.

 

[Guest]

Joao,

You might be able to use the SqlBulkCopy class to accomplish this task.

Kerry Moorman

 

[joaotsetsemoita]

Joao,

You might be able to use the SqlBulkCopy class to accomplish this task.

Kerry Moorman













- Show quoted text -

I wanna to thank everyone who posted here to help me. I managed to
achieve my gold using Kerry's sugestion.

With the class SQLBulk (which is not recognized by inteli sense, I
dont know why) I could do exatcly what I was looking for.

Next task: put the program listening a directory for new .MDB files
and run automaticly.

Many thanks to everyone

Joao

 

[Guest]

Joao,

You probably already know this, but the FileSystemWatcher class will help
you accomplish that task.

Kerry Moorman

 

[Cor Ligthert[MVP]]

Can't I just

pick the datatable and roughly insert it in the SQL server data base
using the sqldataadapter.update?

Yes as long as you use a connectionString

www.connectionstrings.com

And a SqlCommandbuilder to create the update SQL dynamicly

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

Cor

 

[Charlie Brown]

Joao,

You probably already know this, but the FileSystemWatcher class will help
you accomplish that task.

Kerry Moorman







- Show quoted text -

Kerry,
SQLBulk was a great suggestion, didn't even cross my mind... really on
your game today.

 

[Guest]

Just a tip and you probably are already aware that any AutoIncrement field in
your Access database tables may cause you problems when trying to sync with
any similiar field in the Sql database.