How to Select all data from localhost then insert in server network

Question

0.00/5 (No votes)

See more:

#This is my localhost connectionstring

VB

con.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};database=schemalocal;option=0;port=3306;server=localhost;uid=root;pwd=root"

#This is for server connectionstring

VB

con.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};server=192.168.1.1;database=schemanetwork;uid=root;pwd=root;option=0"

#My tables in schemalocal is the same value at schemanetwork

I want to get data from localhost then transfer to server network
basis query ->

SQL

Insert into schemanetwork.table1 select * from schemalocal;

Posted 30-Sep-14 19:39pm

Member 11088078

Updated 1-Oct-14 1:09am

Nelek

v3

Add a Solution

Comments

RossMW 1-Oct-14 2:16am

Depending on SQL version, have you looked at SSIS or older DTS?

Member 11088078 1-Oct-14 2:20am

I want to apply it my application using vb.net, when I click the button my data in local host will transfer to my server network. Im doing this because our router is not consistent working. im using MySQL Administrator

RossMW 1-Oct-14 2:23am

Unfortunately I'm on a silly iPad at the moment so can't write code at moment, but depending on the table you may just have to transverse the local table a row at a time and insert the data into the server table

Member 11088078 1-Oct-14 4:11am

In my schemalocal I have table table1 and columns name, middle, lastname. Same as in schemanetwork. so that im using this query as basis --> Insert into schemanetwork.table1 select * from schemalocal

MohamedHassanAli 1-Oct-14 3:46am

Have you tried Linked Servers ? i believe you can use it for your purpose. Check the link below.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Member 11088078 1-Oct-14 4:16am

I dont know how to use that, right now my application is runnung for localhost only, And also I can do that through network, but in my situation the router is not consistent so that im doing manual collect from local to server.

RossMW 1-Oct-14 4:22am

For that to work the server need to be linked and the tables referenced by the servername.dbo.tablename. When you are referring to a local host I presume you are referring to a database on your local computer( like sql express). If this is an application to be run by many portable users then it would not be practice or advisable to use linked servers between the sql server and local databases. Hence as stated before you are only left with using two connections, one reading and the second to write the data to the server database.

Member 11088078 1-Oct-14 4:27am

Im using this application for 1 client only, Im using connection string for local and network connections. in my database im using mysql 5.0

Member 11088078 1-Oct-14 4:29am

Dim sourceConnection As New OdbcConnection(callLocal())
Dim destinationConnection As New OdbcConnection(callNetwork())
Dim adapter As New OdbcDataAdapter("SELECT * from ft ", sourceConnection)
Dim insertCommand As New OdbcCommand("INSERT INTO tblcollected", destinationConnection)
How about this logic code?

RossMW 1-Oct-14 4:53am

You are on the right track. I'm not familiar with odbc or MySQL but once you have your connection you need a reader to read the data as stated below..

Member 11088078 1-Oct-14 4:33am

Could you give me an example so that I can understand what you mean.

RossMW 1-Oct-14 4:50am

Unfortunately With an iPad i will not be able to give an example until tomorrow, but the princple is the have a reader for your select adaptor. Loop through the records reading the data into variables or dynamically creating a sql statement for your insert statement to be run against your destination connection then moving through the loop to the next record. If your not sure about readers you will find plenty of examples on the web. Hope this helps.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jems_007 · Answer 1 · 2014-10-01T00:39:00

You can write your code like this :

VB

Dim LocalCN As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};database=schemalocal;option=0;port=3306;server=localhost;uid=root;pwd=root" )
Dim ServerCN As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.1.1;database=schemanetwork;uid=root;pwd=root;option=0")

'for get data from local
LocalCN.open()
Dim adp As New OdbcDataAdapter("SELECT * from schemanetwork.table1", LocalCN)
adp.Fill(dt)
LocalCN.close()


'for insert data to online server
ServerCN.open()
Dim cmd As New OdbcCommand("INSERT INTO schemanetwork.table1(a) values(" & dt.rows(0).item("a").tostring() &")", ServerCN)
cmd.ExecuteNonQuery()
ServerCN.close()

Jems_007 · Answer 2 · 2014-10-01T22:33:00

VB

Dim LocalCN As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};database=schemalocal;option=0;port=3306;server=localhost;uid=root;pwd=root" )
Dim ServerCN As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.1.1;database=schemanetwork;uid=root;pwd=root;option=0")

'for get data from local
LocalCN.open()
Dim adp As New OdbcDataAdapter("SELECT * from schemanetwork.table1", LocalCN)
adp.Fill(dt)
LocalCN.close()

'for insert data to online server
ServerCN.open()

For i = 0 To dt.Rows.Count - 1

Dim cmd As New OdbcCommand("INSERT INTO schemanetwork.table1(a,b,c) values('" & dt.rows(i).item("a").tostring() &"','" & dt.rows(i).item("b").tostring() &"','" & dt.rows(i).item("c").tostring() &"')", ServerCN)
cmd.ExecuteNonQuery()


Next

ServerCN.close()

How to Select all data from localhost then insert in server network

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0