inserting data into two tables

Databases
1

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

2

Are you checking for errors after the SQL executes?

3

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

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

5

oke,
this is first routine

[code] 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[/code]

This is method of AddTeamRow_all_report1

[code] 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[/code]

and the second routine,

[code] 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[/code]

this is the method of AddTeamRow1

[code] 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
[/code]

6

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

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

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

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

Best answer for this Conversatrion goes to Jean-Yves.

And, stop using MsgBox for debugging purposes.

11

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

[quote=436265:@Arief Sarjono]I called the second routine after first routine executed.

I also put Msgbox in the second routine, but its never showed up.[/quote]
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.

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

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