[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

[jmcilhinney]

Have you made the obvious checks like a) the query returns some records and b) some of those records have a 'sync' value of 0?

[edgarbenilde]

which part? i no it the same because i just copy that code to make to two and i just change the table name of the database... both two database has sync field.. which line are you saying JMCILHINNEY please?

[edgarbenilde]

i open the table of the post # 2, i found the data but when i run the system it will not display data in the dataview, dataset, datatable.

why, please help?

[edgarbenilde]

Hi! JMCILHINNEY, what part are you saying? i can't understant what do you mean is.. because in both table i have the sync field, by default it always (0) Zero value when it is upload it will change to (1) one.

so, i didn't understand what you mean is...

[edgarbenilde]

anybody please help

[edgarbenilde]

thanks every body i got my code working.. the code above are correct, the reason why it doesn't display data as result because the user enter value is the sync field is "o" instead of Zero (0).

Thanks for help JM