inserting data into two tables

  1. 4 months ago

    Arief S

    May 13 Bandung - Indonesia

    hi,

    i am using mysql database, I want to do inserting the data into two different tables in a time, is it possible to do it?
    everytime its executed, only the first routine is working, the second one never gone to work.

    the sequence that i do,

    1. connect to the database
    2. call routine 1 targetted into table 1
    3. call routine 2 targetted into table 2

    anything that i missed?
    I was thinking that i need to closed the database after inserting, and connect it again for the second routine, but still the same.

    thanks
    regards
    arief

    did you use the debugger to trace where the program goes ?
    it should be easy to add a break at the end of first routine, and see where it goes from there ?

  2. Tim P

    May 13 Pre-Release Testers Rochester, NY

    Are you checking for errors after the SQL executes?

  3. Arief S

    May 13 Bandung - Indonesia

    yes, I do, but its seems the second routine is not executed.

    i am following the code from example project, in database section.

    thanks
    regards
    arief

  4. Jay M

    May 13 Pre-Release Testers, Xojo Pro NC, USA

    It should work if coded properly. We need to see your actual code to determine what's wrong.

  5. Arief S

    May 13 Bandung - Indonesia

    oke,
    this is first routine

      if Window1.Listbox4.ListCount>0 then
        Dim StartTime as Double = Microseconds
        dim totalrow as integer
        totalrow = window1.Listbox4.listcount-1
        for x as integer = 0 to totalrow
          dim a,b,c,d,e,f,g,h,ii,j,k,l,m,n as string
          a=window1.Listbox4.cell(x,0)
          b=window1.Listbox4.cell(x,1)
          c=window1.Listbox4.cell(x,2)
          d=window1.Listbox4.cell(x,3)
          e=window1.Listbox4.cell(x,4)
          f=window1.Listbox4.cell(x,5)
          g=window1.Listbox4.cell(x,6)
          h=window1.Listbox4.cell(x,7)
          ii=window1.Listbox4.cell(x,8)
          j=window1.Listbox4.cell(x,9)
          k=window1.Listbox4.cell(x,10)
          l=window1.Listbox4.cell(x,11)
          m=window1.Listbox4.cell(x,12)
          m=Window1.l_shop.text
          
          If AddTeamRow_all_report(a,b,c,d,e,f,g,h,ii,j,k,l,m,n) then
            Label6.Text = label3.text +" data successfully uploaded"
          Else
            Label6.Text = "There was an error adding the data."
          End If
          
          If AddTeamRow_all_report1(a,b,c,d,e,f,g,h,ii,j,k,l,m,n) then
            Label6.Text = label3.text +" data successfully uploaded"
          Else
            Label6.Text = "There was an error adding the data."
          End If
        next
        
        Dim ThisTime as Double = Microseconds
        Label6.Text = label3.text +" data successfully uploaded in "+  CStr((ThisTime-StartTime)/1000000) + " Seconds"
        Label4.Text="0"
      end if

    This is method of AddTeamRow_all_report1

      Dim row As New DatabaseRecord
      
      // ID will be added automatically
      row.Column("date") = tgl
      row.Column("jam") = jam
      row.Column("chk_id") = chk_id
      row.Column("code_item") = code_item
      row.Column("nama_item") = nama_item
      row.Column("qty") = qty
      row.Column("harga") = harga
      row.Column("diskon") = diskon
      row.Column("total") = total
      row.Column("customer") = customer
      row.Column("no_member") = no_member
      row.Column("payment_type") = payment_type
      row.Column("waiter") = waiter
      row.Column("shop") = shop
      
      '===
      
      dim aa,bb,cc as string
      dim fdz as  folderitem
      dim tisx as TextInputStream
      fdz =GetFolderItem("shop" )
      
      if fdz <> nil and fdz.Exists then
        tisx = fdz.OpenAsTextFile
        aa=tisx.Readline.Trim
      end if
      
      bb=Lowercase(aa)
      cc="all_report"
      '===
      
      'MsgBox str(cc)
      
      mDB.InsertRecord(cc, row)
     
      
      If mDB.Error Then
        Label6.Text = "DB Error: " + mDB.ErrorMessage
        Return False
      End If

    and the second routine,

      if Window1.Listbox4.ListCount>0 then
        Dim StartTime as Double = Microseconds
        dim totalrow as integer
        totalrow = window1.Listbox4.listcount-1
        for x as integer = 0 to totalrow
          dim a,b,c,d,e,f,g,h,ii,j,k,l,m as string
          a=window1.Listbox4.cell(x,0)
          b=window1.Listbox4.cell(x,1)
          c=window1.Listbox4.cell(x,2)
          d=window1.Listbox4.cell(x,3)
          e=window1.Listbox4.cell(x,4)
          f=window1.Listbox4.cell(x,5)
          g=window1.Listbox4.cell(x,6)
          h=window1.Listbox4.cell(x,7)
          ii=window1.Listbox4.cell(x,8)
          j=window1.Listbox4.cell(x,9)
          k=window1.Listbox4.cell(x,10)
          l=window1.Listbox4.cell(x,11)
          m=window1.Listbox4.cell(x,12)
          
          If AddTeamRow1(a,b,c,d,e,f,g,h,ii,j,k,l,m) then
            Label6.Text = label3.text +" data successfully uploaded"
            
          Else
            Label6.Text = "There was an error adding the data."
          End If
        next
        
        'insert_data_all_report
        
        dim oSock as new HTTPSocket
        dim sGoogleText as String = oSock.Get("https://www.google.com", 5)
        if sGoogleText = "" or oSock.ErrorCode <> 0 then
          Window1.l_connection.text= "No Internet Connection"
        Else
          dim z,c as FolderItem
          
          z=GetFolderItem("xdata.feed")
          if z.Exists then z.Delete
          
          Dim ThisTime as Double = Microseconds
          Label6.Text = label3.text +" data successfully uploaded in "+  CStr((ThisTime-StartTime)/1000000) + " Seconds"
          Label4.Text="0"
        end if
      end if

    this is the method of AddTeamRow1

      Dim row As New DatabaseRecord
      
      // ID will be added automatically
      row.Column("date") = tgl
      row.Column("jam") = jam
      row.Column("chk_id") = chk_id
      row.Column("code_item") = code_item
      row.Column("nama_item") = nama_item
      row.Column("qty") = qty
      row.Column("harga") = harga
      row.Column("diskon") = diskon
      row.Column("total") = total
      row.Column("customer") = customer
      row.Column("no_member") = no_member
      row.Column("payment_type") = payment_type
      row.Column("waiter") = waiter
      
      '===
      
      dim aa,bb,cc as string
      dim fdz as  folderitem
      dim tisx as TextInputStream
      fdz =GetFolderItem("shop" )
      
      if fdz <> nil and fdz.Exists then
        tisx = fdz.OpenAsTextFile
        aa=tisx.Readline.Trim
      end if
      
      bb=Lowercase(aa)
      cc=bb+"_report"
      '===
      
      'MsgBox str(cc)
      
      mDB.InsertRecord(cc, row)
      
      'MsgBox "insert done"
      
      If mDB.Error Then
        Label6.Text = "DB Error: " + mDB.ErrorMessage
        Return False
      End If
      
      Return True
      
  6. Dave S

    May 13 San Diego, California USA

    well just from a quick glance at that code.... I would offer one word.... "SQL"

    the amount of code, headaches, and overall confusion would be reduced to nearly nothing...

  7. Jay M

    May 13 Pre-Release Testers, Xojo Pro NC, USA

    I agree that this code is long and seemingly redundant. But without getting into refactoring the code, what result do you get that indicates the second routine didn't work? An error message? Some other message? What proves that the second routine was even called?

  8. Arief S

    May 13 Bandung - Indonesia

    What proves that the second routine was even called?

    I called the second routine after first routine executed.

    I also put Msgbox in the second routine, but its never showed up.

    does anyone has any sample to do this in more simple ways? I just want to insert data into two different tables, but the data is the same.

    thanks
    regards,
    Arief

  9. Jean-Yves P

    May 13 Pre-Release Testers, Xojo Pro Answer Europe (France, Besançon)

    did you use the debugger to trace where the program goes ?
    it should be easy to add a break at the end of first routine, and see where it goes from there ?

  10. Emile S

    May 14 Europe (France, Strasbourg)

    Best answer for this Conversatrion goes to Jean-Yves.

    And, stop using MsgBox for debugging purposes.

  11. Peter M

    May 14 Pre-Release Testers, Xojo Pro Copenhagen

    In the begining of your process you can use;

    System.DebugLog CurrentMethodName + " Entered the Method"

    and in the end;

    System.DebugLog CurrentMethodName + " Exited the Method"

    You can follow the result in the debug messagescreen!

  12. Jay M

    May 14 Pre-Release Testers, Xojo Pro NC, USA

    @Arief S I called the second routine after first routine executed.

    I also put Msgbox in the second routine, but its never showed up.

    You didn't include the code where you're calling the routines, so we have no way to determine why.

    Also, when sharing your code for a method, select the method in the navigator and choose copy (right-click in navigator). This will copy the entire method definition, including parameters and return type, if any.

    In addition, in your original post the first routine contains calls to two methods, AddTeamRow_all_report and AddTeamRow_all_report1. You didn't include the code for the first one, plus the results of the second call overwrite the status information in Label6 from the first one.

    @Arief S does anyone has any sample to do this in more simple ways? I just want to insert data into two different tables, but the data is the same.

    I haven't tried this, but you should be able to just call InsertRecord twice, once for each table but using the same "row" that's already been loaded with values.

  13. Arief S

    May 15 Bandung - Indonesia

    Hi, thanks for all the response..

    When I do debugging the code, its does not have any problem.
    The problem coming from mysql database table which is not allowing the app to write. when I do creating new table in the same database, everything run perfectly.

    thanks
    regards,
    arief

or Sign Up to reply!