• Welcome, Guest. Please login.
 
May 20, 2019, 12:48:12 pm

News:

Welcome to the SQLitening support forums!


Tricky Save Routine

Started by Fredrick Ughimi, April 28, 2019, 05:15:54 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

I have a tricky situation trying to save values from a textbox and MLG on a FORM into a table at the same time.

On the FORM we have 3 values from Textboxes and 4 values from the MLG. On MLG there are two or three
rows to be saved at a time.

Has anyone done this? Any pointer would be appreciated.
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

April 28, 2019, 10:26:24 pm #1 Last Edit: April 28, 2019, 10:28:12 pm by Fredrick Ughimi
This saves but giving undesirable results. Three rows instead of two. Repeating the second row:

rows = 2
cols  =7
 
RowStart=1    'select first row from mlg
RowEnd=2     'select last  row from mlg
ColStart=1    'need entire record
ColEnd=cols
ReDim FromGrid(RowStart To RowEnd,ColStart To ColEnd) As String 'dim array
MLG_GETEx hJournalGrid,FromGrid()                                      'fill array
For r = RowStart To RowEnd
    For c = colStart To colEnd
        sb.Add slbuildbindDat(FromGrid(r,c),"T") 'add bind string into stringbuilder
    Next
Next
slExeBind slBuildInsertOrUpdate("tblJournalEntry", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?"), _
slBuildBindDat(SQLiteDate(m_sDate), "T") & _
slBuildBindDat(m_sReference, "T") & _ 
slBuildBindDat(m_sNarration, "T") & _
sb.String,"V" + Format$(cols)
? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title

I might have to break table into two.

Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello CJ,

Thanks for the heads up and great code as always. Since you did not use MLG and Textbox in your codes, it is a bit difficult to follow and adapt.

Regards.

Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL r,c,rows,cols,hdlg AS LONG
slOpen "junk.db3","C"
slexe  "drop table if exists t1"
slexe  "create table if not exists t1(text1,text2,text3,grid1,grid2,grid3,grid4)"
rows = 20
cols = 7
REDIM sData(1 TO rows) AS STRING
REDIM sCol(1 TO cols)  AS STRING
FOR r = 1 TO rows
 FOR c = 1 TO cols
  SELECT CASE AS LONG c
   CASE 1:CONTROL GET TEXT hDlg, 1001 TO sCol(c)
   CASE 2:CONTROL GET TEXT hDlg, 1002 TO sCol(c)
   CASE 3:CONTROL GET TEXT hDlg, 1003 TO sCol(c)
   CASE 4:sCol(c)=""
   CASE 5:sCol(c)=""
   CASE 6:sCol(c)=""
   CASE 7:sCol(c)=""
  END SELECT
  sCol(c) = slBuildBindDat(sCol(c),"T")
 NEXT
 sData(r) = JOIN$(sCol(),"")
NEXT
slExebind "insert into t1 values(?,?,?,?,?,?,?)",JOIN$(sData(),""),"V7"
LOCAL sRecordSet() AS STRING
slSelAry "select rowid,* from t1",sRecordSet(),"Q9"
? JOIN$(sRecordSet(),$CR),%MB_SYSTEMMODAL,"done"
END FUNCTION

Fredrick Ughimi

Thanks CJ. The thing is the values entered in the textboxes (Date, Narration, Reference) is usually a record, while the values entered on the grid (Account, Description, Credit, Debit) are usually two records or more per transaction. That is doing the double entry in Journal Entry of Accounting.

I would post a picture of my form in my next post whenever electricity comes on.

Best regards.
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

I am not getting notifications of your posts today by email?
Anyway, sounds like an MLG issue getting the values?

DIM x AS LONG
DIM s(1 to 7) AS STRING
'Fill element s(1) to s(3) from textboxes
'Fill element s(4) to s(7) from grid
'Bind and insert
FOR x = 1 to 7
  s(x) = slBuildBindDat(s(x))
next
slExebind "insert into t1 values(?,?,?,?,?,?,?)",JOIN$(s(),""),"V7"

Fredrick Ughimi

Hello Cj,

Attached JournalEntry.jpgis my form.
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

May 02, 2019, 01:08:36 am #7 Last Edit: May 03, 2019, 10:07:46 am by cj Reason: Blank line between functions
I don't know what column/row from the grid to put into the table columns.
I was thinking this might apply to your grid.

Table:  create table if not exists t1(invoice,client,code,qty,amt,DateTime)
            create table if not exists tblNextInvoice(counter INTEGER PRIMARY KEY AUTOINCREMENT)

Invoice number is 0 for each new unposted item for each client
Invoice: insert into t1 values(0, 87, 'paper', 1, 500,    datetime('now','localtime'))
Invoice: insert into t1 values(0, 87, 'chair', 1, 19995,  datetime('now','localtime'))

Post:    update t1 set invoice = counter+1 where client = clientnum and invoice = 0
All unposted entries for a client (invoice number 0) updated to next highest invoice counter



#INCLUDE "sqlitening.inc"  'fredrick8.bas for reference
%DropTables=1

FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG, s AS STRING
  slOpen  "junk.db3","C"
  IF %DropTables THEN slExe "drop table if exists t1":slexe "drop table if exists tblNextInvoice"

  slexe  "create table if not exists t1(invoice,client,code,qty,amt,DateTime)
  slexe  "create table if not exists tblNextInvoice(counter INTEGER PRIMARY KEY AUTOINCREMENT)

  CreateTestData

  PostClient 1
  PostClient 11
  Postclient 14
  PostClient 128
END FUNCTION

FUNCTION showall(sMsg AS STRING) AS STRING
  LOCAL sql AS STRING
  sql = "select invoice,client,code," +_
        "qty,printf('%.2f',amt*.01) as price,datetime from t1 order by client"

  LOCAL sarray() AS STRING
  slSelAry sql,sarray(),"Q9"
  ? JOIN$(sarray(),$CR),%MB_SYSTEMMODAL,sMsg
END FUNCTION

SUB PostClient(ClientNum AS LONG)
  LOCAL s AS STRING
  LOCAL nextInv AS LONG

  slexe "begin exclusive"  'lock others out
  slSel "select counter from tblNextInvoice"
  DO WHILE slGetRow  'only 1 record, this will closeset automatically
    nextinv = VAL(slf(1)) + 1 'new next highest invoice
  LOOP
  IF nextinv = 0 THEN 'only applies to first invoice ever created
    slexe "insert into tblNextInvoice values(0)" 'make counter 0
    nextinv = 1
  END IF
  slexe "update tblNextInvoice set counter = counter + 1"
  s = CHR$("update t1 set invoice=",FORMAT$(nextInv)," where client=",FORMAT$(ClientNum)," and invoice=0")
  slexe s
  slexe "end"

  ShowAll "Post client"+STR$(ClientNum)
END SUB

SUB CreateTestData
  LOCAL x AS LONG
  slexe "begin exclusive"
  FOR x = 1 TO 3
    'slexe                      inv,cli, code,  qty,amt,    date_time
    slexe  "insert into t1 values(0,1,  'chair', 1, 4995,  datetime('now','localtime'))"
    slexe  "insert into t1 values(0,11, 'pencil',1, 95,    datetime('now','localtime'))"
    slexe  "insert into t1 values(0,14, 'desk',  1, 13995, datetime('now','localtime'))"
    slexe  "insert into t1 values(0,92, 'ink',  1, 135,  datetime('now','localtime'))"
    slexe  "insert into t1 values(0,128,'sofa',  1, 64995, datetime('now','localtime'))"
  NEXT
  slexe "end"

  ShowAll "Any unposted invoices show as invoice 0"
END SUB

Fredrick Ughimi

Hello Cj,

QuoteI don't know what column/row from the grid to put into the table columns.

All columns of the grid and all rows containing records because the rows could contain up to 3 records sometimes, but most of the times 2 records.

Kind regards.

Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Thank you, Cj.

Your last post gave me an idea that works. Using the same table twice. Not the most elegant code, but it works.
It does what I want.

m_sDate = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_DPKDATE)
             
m_sReference = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE)
If Trim$(m_sReference) = "" Then
      MsgBox "Please, enter Reference", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE
      Exit Method
End If
               
m_sNarration = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION)
If Trim$(m_sNarration) = "" Then
      MsgBox "Please, enter Narration", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION
      Exit Method
End If
             
m_sAccount1 = MLG_Get(hJournalGrid, 1, 1)
m_sDescription1 = MLG_Get(hJournalGrid, 1, 2)
m_sDebit1 = MLG_Get(hJournalGrid, 1, 3)
m_sCredit1 = MLG_Get(hJournalGrid, 1, 4)   
             
m_sAccount2 = MLG_Get(hJournalGrid, 2, 1)
m_sDescription2 = MLG_Get(hJournalGrid, 2, 2)
m_sDebit2 = MLG_Get(hJournalGrid, 2, 3)
m_sCredit2 = MLG_Get(hJournalGrid, 2, 4)
             
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
   & $Nul & m_sCredit1)
slExe "End"
       
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
   & $Nul & m_sCredit2)
slExe "End"

MLG_PUT hJournalGrid,6,3,TotalCredit(hJournalGrid),0
MLG_PUT hJournalGrid,6,4,TotalDebit(hJournalGrid),0

? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

Function MyTransaction as LONG
  slexe "begin Immediate"
  slexe  "first insert"
  IF slgetchangecount =0  then slexe "rollback":EXIT FUNCTION
  slexe  "last insert"
  IF slgetchangecount =0  then slexe "rollback":EXIT FUNCTION
  FUNCTION= 1
  slexe "end"
END FUNCTION