locked
vfp and sql code combine RRS feed

  • Question

  •  hi everyon,
    i just need some comments on the codes below. i used it in my sales order form, it was running ok but sometimes
    encountered inbalance in my fifo_inventory table, sometimes it hungs up. any improvements on this or correction
    is higly appreciated. thank you very much




    SELECT INVOICE_TEMP GO top SCAN thisform.item_code2.Value=item_code1 thisform.item_name2.Value=item_name1 thisform.qty_pck2.Value=qty_pack thisform.qty_pcs2.Value=qty_pcs thisform.srp_pck2.Value=srp_pack thisform.srp_pcs2.Value=srp_pcs thisform.cost_pck2.Value=cost_pack thisform.cost_pcs2.value=cost_pcs thisform.unit2.Value=unit1 thisform.total2.Value=total thisform.vendor2.Value=vendor thisform.count2.Value=count1 thisform.qty1.Value=qty_orig thisform.itemx.Value=item_type thisform.salesa2.value=sales_acct thisform.costa2.value=cost_acct THISFORM.EXP1.Value=EXP1 thisform.lot.Value=lot xa=thisform.item_code2.value xb=thisform.item_name2.value xc=thisform.qty_pck2.Value xd=thisform.qty_pcs2.Value xe=thisform.srp_pck2.Value xf=thisform.srp_pcs2.Value xg=thisform.cost_pck2.Value xh=thisform.cost_pcs2.value xi=thisform.unit2.Value xj=thisform.total2.Value xk=thisform.vendor2.Value xl=thisform.count2.Value xxx=thisform.qty1.Value itemx=thisform.itemx.Value salesa=thisform.salesa2.value costa=thisform.costa2.value EXP2=THISFORM.EXP1.Value lot1=thisform.lot.Value con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 TEXT TO m.lcInsert NOSHOW PRETEXT 15 insert into so2(date1,so1,item_id,item_name,qty_pck,qty_pcs,srp_pck,srp_pcs,cost_pck,cost_pcs,type1, vendor,count1,unit1,total1,period,month1,status1,qty_orig,area1,area2,sales_rep,item_type,class1,aged,name1,sales_acct,cost_acct,exp1,lot) values (?m.a,?m.b,?m.xa,?m.xb,?m.xc,?m.xd,?m.xe,?m.xf,?m.xg, ?m.xh,?m.xn,?m.xk,?m.xl,?m.xi,?m.xj,?m.n,?m.o,?m.xm,?m.xxx,?m.e,?m.p,?m.h,?m.itemx,?m.g,?m.xm,?m.d,?m.salesa,?m.costa,?m.exp2,?m.lot1) ENDTEXT exec1=SQLEXEC(m.con1, m.lcInsert) =SQLDISCONNECT(con1) ENDIF DO WHILE xxx>0 con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 exec1=SQLEXEC(con1, "select * from FIFO_INVENTORY WHERE ITEM_CODE1=?xa order by date1") IF exec1>0 SET SAFETY off COPY TO c:\cci_pharma\temps\FIFO_INVENTORY csv ENDIF =SQLDISCONNECT(con1) ENDIF USE IN SELECT([c:\cci_pharma\dbfs\fifo_inventory.dbf]) GO top thisform.item_code2.Value=item_code1 thisform.qty3.Value=qty_orig thisform.date1.value=date1 IF xxx>thisform.qty3.value con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 exec1=SQLEXEC(con1, "delete FROM fifo_inventory where item_code1=?thisform.item_code2.value and date1=?thisform.date1.value") =SQLDISCONNECT(con1) ENDIF con1=SQLCONNECT('consumer_connect','erika3','tariki') xxx=xxx-thisform.qty3.value LOOP ENDIF IF xxx=thisform.qty3.value con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 exec1=SQLEXEC(con1, "delete FROM fifo_inventory where item_code1=?thisform.item_code2.value and date1=?thisform.date1.value") =SQLDISCONNECT(con1) ENDIF EXIT ENDIF IF xxx<thisform.qty3.value con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 exec1=SQLEXEC(con1, "update fifo_inventory set qty_orig=qty_orig-?xxx where item_code1=?thisform.item_code2.value and date1=?thisform.date1.value") =SQLDISCONNECT(con1) ENDIF EXIT ENDIF ENDDO ENDSCAN SELECT bor_temp GO top SCAN c2=item_code1 d2=item_name1 e2=qty1 f2=unit1 g2=total1 h2=vendor1 i2=srp con1=SQLCONNECT('consumer_connect','erika3','tariki') IF con1>0 exec1=SQLEXEC(con1, "insert into back_order(date1,so_number,item_code1,item_name1,qty1,unit1,total1,vendor1,status1,customer,area,srp,sales_rep); values(?a,?b,?c2,?d2,?e2,?f2,?g2,?h2,?xm,?d,?e,?i2,?h)") =SQLDISCONNECT(con1) ENDIF ENDSCAN
    Sunday, December 6, 2009 3:58 AM

Answers

  • Your problem may be in 

     IF xxx>THISFORM.qty3.VALUE
     con1=SQLCONNECT('consumer_connect','erika3','tariki') 
     IF con1>0 
     exec1=SQLEXEC(con1, "delete FROM fifo_inventory where item_code1=?thisform.item_code2.value and date1=?thisform.date1.value")
     =SQLDISCONNECT(con1) 
     ENDIF 
     con1=SQLCONNECT('consumer_connect','erika3','tariki')
     xxx=xxx-THISFORM.qty3.VALUE 
     LOOP 
     ENDIF 

    In the 2nd SQLCONNECT where you are creating connections in a loop and not disconnecting. I would have 1 connection throught the PRG so that I can then have transactions. The way you have done it, half the transaction might get saved and the other half may not be executed if the connection is not successful. 
     In 
     TEXT TO m.lcInsert NOSHOW PRETEXT 15 
    insert into so2(date1,so1,item_id,item_name,qty_pck,qty_pcs,srp_pck,srp_pcs,cost_pck,cost_pcs,type1, vendor,count1,unit1,total1,period,month1,status1,qty_orig,area1,area2,sales_rep,item_type,class1,aged,name1,sales_acct,cost_acct,exp1,lot) values (?m.a,?m.b,?m.xa,?m.xb,?m.xc,?m.xd,?m.xe,?m.xf,?m.xg, ?m.xh,?m.xn,?m.xk,?m.xl,?m.xi,?m.xj,?m.n,?m.o,?m.xm,?m.xxx,?m.e,?m.p,?m.h,?m.itemx,?m.g,?m.xm,?m.d,?m.salesa,?m.costa,?m.exp2,?m.lot1) 
    ENDTEXT 
    The SQL does not necessary have to take a variable as a parameter. Any expression will do. to ?THISFORM.item_code2.VALUE can be used instead of ?m.xa. In any case, you should use better variable names and make them local.
    • Marked as answer by Young_fox Sunday, December 6, 2009 12:58 PM
    • Unmarked as answer by Young_fox Sunday, December 6, 2009 1:12 PM
    • Marked as answer by Young_fox Sunday, December 6, 2009 6:50 PM
    Sunday, December 6, 2009 11:55 AM
  • I think in your loop in the IF statements 
     IF xxx=thisform.qty3.value 
     After finishing this, you should make xxx=0 

    IF xxx<thisform.qty3.value

    again after this, you should make xxx=0
    • Marked as answer by Young_fox Sunday, December 6, 2009 6:49 PM
    Sunday, December 6, 2009 5:07 PM

All replies

  • Your problem may be in 

     IF xxx>THISFORM.qty3.VALUE
     con1=SQLCONNECT('consumer_connect','erika3','tariki') 
     IF con1>0 
     exec1=SQLEXEC(con1, "delete FROM fifo_inventory where item_code1=?thisform.item_code2.value and date1=?thisform.date1.value")
     =SQLDISCONNECT(con1) 
     ENDIF 
     con1=SQLCONNECT('consumer_connect','erika3','tariki')
     xxx=xxx-THISFORM.qty3.VALUE 
     LOOP 
     ENDIF 

    In the 2nd SQLCONNECT where you are creating connections in a loop and not disconnecting. I would have 1 connection throught the PRG so that I can then have transactions. The way you have done it, half the transaction might get saved and the other half may not be executed if the connection is not successful. 
     In 
     TEXT TO m.lcInsert NOSHOW PRETEXT 15 
    insert into so2(date1,so1,item_id,item_name,qty_pck,qty_pcs,srp_pck,srp_pcs,cost_pck,cost_pcs,type1, vendor,count1,unit1,total1,period,month1,status1,qty_orig,area1,area2,sales_rep,item_type,class1,aged,name1,sales_acct,cost_acct,exp1,lot) values (?m.a,?m.b,?m.xa,?m.xb,?m.xc,?m.xd,?m.xe,?m.xf,?m.xg, ?m.xh,?m.xn,?m.xk,?m.xl,?m.xi,?m.xj,?m.n,?m.o,?m.xm,?m.xxx,?m.e,?m.p,?m.h,?m.itemx,?m.g,?m.xm,?m.d,?m.salesa,?m.costa,?m.exp2,?m.lot1) 
    ENDTEXT 
    The SQL does not necessary have to take a variable as a parameter. Any expression will do. to ?THISFORM.item_code2.VALUE can be used instead of ?m.xa. In any case, you should use better variable names and make them local.
    • Marked as answer by Young_fox Sunday, December 6, 2009 12:58 PM
    • Unmarked as answer by Young_fox Sunday, December 6, 2009 1:12 PM
    • Marked as answer by Young_fox Sunday, December 6, 2009 6:50 PM
    Sunday, December 6, 2009 11:55 AM
  • hi tushar,

    First, thank you very much for extending time responding on my concern.

    the second sql connection actually does not exist in my actual code i may have mistaken it during my typing.

    i will try to improve things as you have suggested. if you will allow, i am going to ask you one thing.

              does my code is ok the way i express it.? not to enclude your comments/suggestion

    my worries is in the do while statement, i am afraid if i did it right specially the looping.

    its a very big help for a beginner like me. thank you again



    youg fox
    • Marked as answer by Young_fox Sunday, December 6, 2009 6:49 PM
    • Unmarked as answer by Don Tan Tuesday, December 8, 2009 5:09 PM
    Sunday, December 6, 2009 1:10 PM
  • I think in your loop in the IF statements 
     IF xxx=thisform.qty3.value 
     After finishing this, you should make xxx=0 

    IF xxx<thisform.qty3.value

    again after this, you should make xxx=0
    • Marked as answer by Young_fox Sunday, December 6, 2009 6:49 PM
    Sunday, December 6, 2009 5:07 PM