Help with write to external database

E

eyenight

New Member
Licensed User
Longtime User
I'm trying to write some data on listview and sqlite to SQL server. I'm using HttpClient and ExecuteRemoteQuery like on MySQL tutorial. I have no problems when I tried to insert 1 data. But when I tried to insert more than 1, only 1 data got inserted to SQL server. Then I add some delay on the loop, sometimes I got all data inserted, but sometimes only a few data.

Here is part of my code:
B4X: 
Lne = 0
For i = 0 To ListViewTable.Size - 1
   MnuCde = (ListViewTable.GetItem (i))
   
   Dim Cursor5 As Cursor
   Cursor5 = SQL1.ExecQuery("SELECT a.SubMnuCde, a.ItmCde, a.MnuNme, a.MnuPrc, b.Qty, b.RMK FROM Menu a " & _
      "left join TmpOrd b on a.MnuCde = b.MnuCde where a.MnuCde = '" & MnuCde & "'")
   Cursor5.Position = 0
   SubMnuCde = (Cursor5.GetString("SubMnuCde"))
   ItmCde = (Cursor5.GetString("ItmCde"))
   LngDcr = (Cursor5.GetString("MnuNme"))
   SrtDcr = (Cursor5.GetString("MnuNme"))
   Prc = (Cursor5.GetString("MnuPrc"))
   Qty = (Cursor5.GetString("Qty"))
   RMK = (Cursor5.GetString("RMK"))
   Cursor5.Close
   
   UoM = "" 
   IviRef = "" 
   OrdTpe = "" 

   Dsc = 0 
   Amt = Prc * Qty
   GstChkQty = 0
    Lne = Lne + 1
            
   ExecuteRemoteQuery("INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln)" & _
            " VALUES ('" & ref & _
            "','" & MnuCde & _
            "','" & SubMnuCde & _
            "','" & ItmCde & _
            "','" & Module1.ChgSingleQuote(LngDcr) & _
            "','" & Module1.ChgSingleQuote(SrtDcr) & _
            "','" & UoM & _
            "'," & Prc & _
            "," & Dsc & _
            "," & Qty & _
            "," & Amt & _
            ",'" & IviRef & _
            "'," & Lne & _
            "," & GstChkQty & _
            ",'" & OrdTpe & _
            "','" & Module1.ChgSingleQuote(RMK) & _
            "','" & DLU & _
            "','" & UsrID & _
            "','" & RefCln & "')", CapOrdDtlDB)
            
   Log("INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln)" & _
            " VALUES ('" & ref & _
            "','" & MnuCde & _
            "','" & SubMnuCde & _
            "','" & ItmCde & _
            "','" & Module1.ChgSingleQuote(LngDcr) & _
            "','" & Module1.ChgSingleQuote(SrtDcr) & _
            "','" & UoM & _
            "'," & Prc & _
            "," & Dsc & _
            "," & Qty & _
            "," & Amt & _
            ",'" & IviRef & _
            "'," & Lne & _
            "," & GstChkQty & _
            ",'" & OrdTpe & _
            "','" & Module1.ChgSingleQuote(RMK) & _
            "','" & DLU & _
            "','" & UsrID & _
            "','" & RefCln & "')")
            
   Module1.Wait(1500)
Next

Here is the log when I tried to insert 7 data:
B4X: 
INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','1_2_4','1_2','null','Yellow Lemon Ice Tea','Yellow Lemon Ice Tea','',10000,0,2,20000,'',1,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','1_4_5','1_4','null','White Sangria','White Sangria','',10000,0,3,30000,'',2,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','2_4_8','2_4','null','Ducky Duck Confit','Ducky Duck Confit','',10000,0,3,30000,'',3,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','2_5_2','2_5','null','Be'' Burger','Be'' Burger','',10000,0,2,20000,'',4,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','2_6_4','2_6','null','Hawaiian PIZAA','Hawaiian PIZAA','',10000,0,1,10000,'',5,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','1_10_2','1_10','null','French Press','French Press','',10000,0,2,20000,'',6,0,'','','2012-04-23 17:00:15','raffael','')

INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln) VALUES ('84d20120400009','1_9_6_2','1_9_6','null','Jose Cuervo','Jose Cuervo','',10000,0,1,10000,'',7,0,'','','2012-04-23 17:00:15','raffael','')

Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]
Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]
Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]
Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]
Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]
Response from server: <br />


<b>Warning</b>:  odbc_fetch_row() [<a href='function.odbc-fetch-row'>function.odbc-fetch-row</a>]: No tuples available at this result index in <b>C:\xampp\htdocs\connect.php</b> on line <b>24</b><br />
[]

As you can see, I'm trying to insert 7 data, but only got 6 response from server, and only 5 data got inserted to my SQL server database.

Sorry for my english..

Edit: Sorry, got 5 response from server. The other 1 response is from another single data.
 
Last edited:
Sort by date Sort by votes
E

eyenight

New Member
Licensed User
Longtime User
Never mind, solved. I changed my code to:
B4X: 
Lne = 0
SQLInsQuery = ""
For i = 0 To ListViewTable.Size - 1
    MnuCde = (ListViewTable.GetItem (i))
    
    Dim Cursor5 As Cursor
    Cursor5 = SQL1.ExecQuery("SELECT a.SubMnuCde, a.ItmCde, a.MnuNme, a.MnuPrc, b.Qty, b.RMK FROM Menu a " & _
        "left join TmpOrd b on a.MnuCde = b.MnuCde where a.MnuCde = '" & MnuCde & "'")
    Cursor5.Position = 0
    SubMnuCde = (Cursor5.GetString("SubMnuCde"))
    ItmCde = (Cursor5.GetString("ItmCde"))
    LngDcr = (Cursor5.GetString("MnuNme"))
    SrtDcr = (Cursor5.GetString("MnuNme"))
    Prc = (Cursor5.GetString("MnuPrc"))
    Qty = (Cursor5.GetString("Qty"))
    RMK = (Cursor5.GetString("RMK"))
    Cursor5.Close
    
    UoM = "" 
    IviRef = "" 
    OrdTpe = "" 

    Dsc = 0 
    Amt = Prc * Qty
    GstChkQty = 0
    Lne = Lne + 1
                
    SQLInsQuery = SQLInsQuery & (" SELECT '" & ref & _
                  "','" & MnuCde & _
                  "','" & SubMnuCde & _
                  "','" & ItmCde & _
                  "','" & Module1.ChgSingleQuote(LngDcr) & _
                  "','" & Module1.ChgSingleQuote(SrtDcr) & _
                  "','" & UoM & _
                  "'," & Prc & _
                  "," & Dsc & _
                  "," & Qty & _
                  "," & Amt & _
                  ",'" & IviRef & _
                  "'," & Lne & _
                  "," & GstChkQty & _
                  ",'" & OrdTpe & _
                  "','" & Module1.ChgSingleQuote(RMK) & _
                  "','" & DLU & _
                  "','" & UsrID & _
                  "','" & RefCln & "' UNION ALL")
                
Next

SQLInsQuery = SQLInsQuery.SubString2(0, (SQLInsQuery.Length - 10))   
ExecuteRemoteQuery("INSERT INTO CapOrdDtl (ref,MnuCde,SubMnuCde,ItmCde,LngDcr,SrtDcr,UoM,Prc,Dsc,Qty,Amt,IviRef,Lne,GstChkQty,OrdTpe,RMK,DLU,UsrID,RefCln)" & SQLInsQuery, CapOrdDtlDB)

You can close this thread.. :)
 
Upvote 0
You must log in or register to reply here.
Top