[RESOLVED] Upload data from different database and format separated computer

[edgarbenilde]

Hi

someone please guide me here, i can't understand how it happen. but this code 1 is working but the second code it did not work (but it is 100 % same code i just change it to the table name). i can't display the data result into DATAVIEW in post # 2, in post # 1 work perfect..


The database are:
Local: access 2003
Server: MySQL server

Please help, jmcilhey, RobDogg, Kliema... ur are the best here please do guide me..

Post # 1 - Working

Private Sub TSRUpload()
        If (Me.Ping.Send("PH180", 500).Status) = Net.NetworkInformation.IPStatus.Success Then
	    Dim ds As DataSet = New DataSet()
            Dim tblLocal As New DataTable
            Dim tblServer As New DataTable
            Dim myDataview As New DataView
            Dim insertCom As New MySql.Data.MySqlClient.MySqlCommand
            Dim updateCom As New OleDb.OleDbCommand
            Dim updateComLocal As New MySql.Data.MySqlClient.MySqlCommand


            ab = Me.UsernameTextBox.Text
            bc = mdiTSMobile.myTPHCODE1


            Dim conn As MySqlConnection = New MySqlConnection("server=ph180;user id=sa;Password=password;persist security info=True;database=mytsmobile")
            conn.Open()
            Dim conlocal As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & My.Application.Info.DirectoryPath & "\TSmobile.mdb")
            conlocal.Open()

            Dim da As MySqlDataAdapter = New MySqlDataAdapter("Select * from TSR", conn)
            da.Fill(ds, "TSR")
            tblLocal = TSMobileDataSet.Tables("TSR")
            myDataview = tblLocal.DefaultView()
            myDataview.RowFilter = "sync = 0"
            tbllocalCount = myDataview.Count
            tblLocal = TSMobileDataSet.Tables("TSR")
            myDataview = tblLocal.DefaultView()
             tbllocalCount = myDataview.Count

            tblServer = ds.Tables("TSR")
            tblServerCount = tblServer.Rows.Count
            insertCom.Connection = conn
            updateCom.Connection = conlocal
            If tbllocalCount <> 0 Then
                For Each myDatarow In tblLocal.Select("sync = 0")
                      insertCom.CommandText = "insert into TSR (ID, ComputerReg, tsCode, tsName, tsGroup, Email, tsPassword, SecurityQuestion, sync, UpdatesStatus, SMRP, PMIS, Phase, Scope, Project, SalesHistory, Pricelist, UploadAll, DownloadAll)" & _
                 " values ('" & myDatarow.Item(0).ToString & "','" & myDatarow.Item(1).ToString & _
                                "','" & myDatarow.Item(2).ToString & "','" & myDatarow.Item(3).ToString & "','" & myDatarow.Item(4).ToString & "','" & myDatarow.Item(5).ToString & _
                                "','" & myDatarow.Item(6).ToString & "','" & myDatarow.Item(7).ToString & "','" & myDatarow.Item(8).ToString & "','" & myDatarow.Item(9).ToString & _
                                "','" & myDatarow.Item(10).ToString & "','" & myDatarow.Item(11).ToString & "','" & myDatarow.Item(12).ToString & "','" & myDatarow.Item(13).ToString & _
                                "','" & myDatarow.Item(14).ToString & "','" & myDatarow.Item(15).ToString & "','" & myDatarow.Item(16).ToString & "','" & myDatarow.Item(17).ToString & "','" & myDatarow.Item(18).ToString & "');" ','" & myDatarow.Item(19).ToString & "'
                    insertCom.ExecuteNonQuery()
                Next myDatarow
                updateCom.CommandText = " UPDATE `TSR` SET `sync` = '1' where `sync`='0'"
                updateCom.ExecuteNonQuery()
                 conn.Close()
                conlocal.Close()
                conn.Dispose()
                conlocal.Dispose()
                Me.ToolTip1.Show("Successfully updates tsr", Me, pntcontact, 13500)
            End If
        End If
    End Sub

Post # 2 - Did not work (even once) and it did not give any error sign.., please help this post # 2 work

Private Sub ContactUpload()
        If (Me.Ping.Send("PH180", 500).Status) = Net.NetworkInformation.IPStatus.Success Then
            Dim ds As DataSet = New DataSet()
            Dim tblLocal As New DataTable
            Dim tblServer As New DataTable
            Dim myDataview As New DataView
            Dim insertCom As New MySql.Data.MySqlClient.MySqlCommand
            Dim updateCom As New OleDb.OleDbCommand
            Dim updateComLocal As New MySql.Data.MySqlClient.MySqlCommand

	    ab = Me.UsernameTextBox.Text
            bc = mdiTSMobile.myTPHCODE1

            Dim conn As MySqlConnection = New MySqlConnection("server=ph180;user id=sa;Password=password;persist security info=True;database=mytsmobile")
            conn.Open()
            Dim conlocal As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & My.Application.Info.DirectoryPath & "\TSmobile.mdb")
            conlocal.Open()

            Dim da As MySqlDataAdapter = New MySqlDataAdapter("Select * from contact", conn)
            da.Fill(ds, "contact")
            tblLocal = TSMobileDataSet.Tables("Contact")
            myDataview = tblLocal.DefaultView()
            myDataview.RowFilter = "sync=0"
            tbllocalCount = myDataview.Count
            tblLocal = TSMobileDataSet.Tables("Contact")
            myDataview = tblLocal.DefaultView()
            
            tbllocalCount = myDataview.Count
           
            tblServer = ds.Tables("Contact")
            tblServerCount = tblServer.Rows.Count
            insertCom.Connection = conn
            updateCom.Connection = conlocal
            If tbllocalCount <> 0 Then
                For Each myDatarow In tblLocal.Select("sync = 0")
                    insertCom.CommandText = "insert into contact (conID, tscode, cuscode, conname, telephone, mobile, position, dept, email, sync)" & _
                        " values ('" & myDatarow.Item(0).ToString & "','" & myDatarow.Item(1).ToString & _
                        "','" & myDatarow.Item(2).ToString & "','" & myDatarow.Item(3).ToString & "','" & myDatarow.Item(4).ToString & "','" & myDatarow.Item(5).ToString & _
                        "','" & myDatarow.Item(6).ToString & "','" & myDatarow.Item(7).ToString & "','" & myDatarow.Item(8).ToString & "','" & myDatarow.Item(9).ToString & "');"
                    insertCom.ExecuteNonQuery()
                Next myDatarow
                updateCom.CommandText = " UPDATE `Contact` SET `sync` = '1' where `sync`='0'"
                updateCom.ExecuteNonQuery()
                conn.Close()
                conlocal.Close()
                conn.Dispose()
                conlocal.Dispose()
                Me.ToolTip1.Show("Successfully upload contacts", Me, pntcontact, 13500)
            End If
        End If
    End Sub