Visual basic dotnet Writting the same data to 2 different databases

Question

Hi guys I have 2 data bases I am currently working with. data base number 1 has some data that needs to written to base number 2 Due to security concerns and the databases is on 2 different servers it was decided to make a windows form application to interact with the 2 data bases.

the database structure is as follows

Database name=Number 1 tables names=A,B,C,D,E

Database name=Number 2 tables names=W,X,Y,Z

Currently I can write the data to the tables in data number 1 from my text boxes but i can't have the same data write to the table in database number 2.

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

Assuming you can't or don't want to pursue these options, and just want to go between two databases on different servers right from your winforms app, this snippet is a quick example of something that would work.

Imports System.Data.SqlClient

Public Sub ReadWriteDB1(sqlStatement)
    WriteData("Server=server1;Database=database1;User Id=sa;Password=pwd;", sqlStatement)
End Sub

Public Sub ReadWriteDB2(sqlStatement)
    WriteData("Server=server2;Database=database2;User Id=sa;Password=pwd;", sqlStatement)
End Sub

Private Sub WriteData(connectionString As String, sqlStatement As String)
    Dim cn As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(sqlStatement, cn)
    cn.Open()
    cmd.ExecuteNonQuery()
End Sub

edited May 23 '17 at 12:27

Community

1
1

answered Sep 27 '14 at 03:14

soniczoo

1

1. both DB have a different structure.but the data types are the same for the columns that need to be populated are the same. for example in DB number1 table A1 data type is set to varchar(50) which is the same in DB number2 table X2 is set to varchar(50). both tables in the databases are flat files. so logically it should work but it doesn't i can email my code for anyone to review it and let me know where i went wrong thanks – MIGHTY_TEFLON Oct 02 '14 at 01:06

cyberhubert · Answer 2 · 2014-09-27T15:24:06.860

If db1 correctly saves data, I bet it's not the app problem - rather something with db2. Try to check the following:

Check if both databases (or tables you are trying to write) have same stucture?
Check if the user you are connecting to db2 has rights to write?
Is it possible to write to db2 if you use plain sql query? Try to issue couple insert queries with structure and data takenfrom real app. You can use tool like SQL Server Profiler to capture a few queries and then reissue them to db2
You've mention about changing table names. How about schema differences between these two databases? It may be required to change whole (prefixed) table name from dbo.A to sth.W instead of simply replacing Ato W in the query
I don't know nothing about the data you are trying to store, but it might to be a foreign keys problem. Ensure that both databases have synchronized "dictionary tables" - however this kind of problem should be reported by ADO.NET in exception message.

If above fails, we'll need some code responsible for persistence.

1. both DB have a different structure.but the data types are the same for the columns that need to be populated are the same. for example in DB number1 table A1 data type is set to varchar(50) which is the same in DB number2 table X2 is set to varchar(50). both tables in the databases are flat files. so logically it should work but it doesn't i can email my code for anyone to review it and let me know where i went wrong thanks — MIGHTY_TEFLON, Oct 02 '14 at 01:06

Visual basic dotnet Writting the same data to 2 different databases

2 Answers2