• Welcome, Guest. Please login.
 
September 17, 2019, 09:56:32 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 8 9 10
91
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.
92
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.

93
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.

94
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.
95
General Board / Money no leading zero using CA...
Last post by cj - April 18, 2019, 09:39:27 pm
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION
 
96
You've got Questions? We've got Answers! / Re: sql query
Last post by cj - April 13, 2019, 01:27:46 pm
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'group_by_having.bas alias wilson3.bas 4/13/19

 LOCAL sql     AS STRING
 LOCAL sresult AS STRING

 slopen "group_having.db3","C"

 slexe  "drop table if exists iTable"
 slexe  "drop table if exists hTable"
 slexe  "create table if not exists iTable (iKey unique,iQty INTEGER)"
 slexe  "create table if not exists hTable (hKey,       hQty INTEGER)"

 slexe  "insert into iTable values ('bear',1),('dog',0),('fox',3)"
 slexe  "insert into hTable values ('dog',3) ,('dog',2),('fox',4)"

 sql =  "select iKey as Code,sum(hQty)as Held,iQTY as OnHand from hTable,iTable" +_
        " where iKey=hKey" +_
        " group by hKey" + _
        " having Held > iQTY" 'changed sum(hQty) to Held

 sresult = getrs(sql)
 ? sresult,,"Held Report"

END FUNCTION

FUNCTION getrs (sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9 E0"
 IF slGetErrorNumber THEN ? slGetError,,"GetData":EXIT FUNCTION
 IF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
 ELSE
  FUNCTION = "No records found"
 END IF
END FUNCTION


'Testing new forum software 4/18/19
'Can edit, but can't create a new thread  or reply
97
You've got Questions? We've got Answers! / Re: sql query
Last post by cj - April 12, 2019, 04:49:33 pm
QuoteI want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current 
inventory level.

1. Add OnHold column to Inventory table
2. select * from table1 where qty < OnHold"

QuoteIn theory the system will never allow a user to put on hold if there is not an enough inventory.

UpdateteFlag& = Increment(sCode$,HoldMore)  ' return 1 if successful (doesn't allow out of limits)

slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"

Test program
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION Increment(sCode AS STRING, hold AS LONG) AS LONG
'Update OnHold if qty& >= OnHold and hold > 0
IF hold < 1 THEN EXIT FUNCTION 'updating by less than 1 could be endless
slexe "update table1 set OnHold=OnHold+"+STR$(hold) + " where code='" + sCode$ + "' and qty >= OnHold+" + STR$(hold)
FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION PBMAIN () AS LONG
LOCAL sCode         AS STRING
LOCAL HoldMore      AS LONG
LOCAL UpdateFlag    AS LONG
gs+= CHR$("Update OnHold until >=Quantity",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
CreateTable

scode = "wilson"
HoldMore = 1
DO 'test Increment function by holding more until limit reached
  ShowTable "select * from table1 where code = '" + sCode + "'"
  UpdateFlag = Increment(sCode,HoldMore)
LOOP WHILE UpdateFlag

gs+= CHR$($CR,$CR,"Qty <= OnHold",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
ShowTable "select * from table1 where Qty <= OnHold"
? gs,,"Done"
END FUNCTION

SUB ShowTable(sql AS STRING)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
IF UBOUND(sArray) > 0 THEN
  gs = gs + JOIN$(sArray(),$CR) + $CR
ELSE
  gs = "No data" + $CR
END IF
END SUB

SUB CreateTable
LOCAL sCode AS STRING
LOCAL hold,UpdateFlag AS LONG
slOpen "table1","C"
slexe "drop table if exists table1"
slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"
END SUB
98
You've got Questions? We've got Answers! / sql query
Last post by D. Wilson - April 09, 2019, 04:10:12 pm
I 'know it can be done - I am just having trouble creating it'

I have two tables

1) Inventory
2) Holds

I want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current inventory level.

In theory the system will never allow a user to put on hold if there is not an enough inventory.

But I want to run the query on program startup to create a dashboard for the user to know those inventory items have 'issues'

There could be multiple holds per inventory item

I know it would involve sum and group by. But I am stuck trying to create the needed sql query.

Any help would be appreciated.
99
You've got Questions? We've got Answers! / Re: -14 Invalid set
Last post by D. Wilson - April 09, 2019, 04:05:37 pm
I found the issue was with a grid control I was using.

I solved the problem by creating a 'system lock' flag that will prevent the user from loading another record till the other record has been loaded and the flag has been cleared
100
You've got Questions? We've got Answers! / Re: -14 Invalid set
Last post by cj - March 30, 2019, 02:52:06 am
Incorrect sql statements are caught by slSel, but a zero length sql statement will not error until slGetColumnCount with an error -14.

Added this error check:
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION

Also added:
If rownum = 0 then ERASE sRecordSet:EXIT FUNCTION ' so a big empty array is not returned when there is no data to return.


#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN()
LOCAL sql,sRecordSet(),sColumnDelimiter,sReturned AS STRING
sColumnDelimiter = "  "
slopen "sample.db3","C"
sql = "select rowid,manuf,price from parts limit 3"
sReturned = GetRecordSet(sql,sRecordSet(),sColumnDelimiter)
IF LEN(sReturned) THEN ? sReturned
END FUNCTION

FUNCTION GetRecordSet(sql AS STRING,sRecordSet() AS STRING,sColumnDelimiter AS STRING) AS STRING
LOCAL colnum,rownum,columns,highelement AS LONG
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION
slsel sql,0,"E0"

IF slGetErrorNumber THEN                          'execute the sql statement
   ? sql + $CR +$CR + slGetError,%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet"
   EXIT FUNCTION
END IF

columns = slGetColumnCount                        'number of columns to return

DIM sCol(1 TO columns) AS STRING                  'avoid concatenation of current row
highelement = 50000                               'arbitrary top limit of recordset array
REDIM sRecordset(1 TO highelement) AS STRING

DO WHILE slGetRow                                 'read recordset loop
  INCR rownum                                      'actual number of reads rows
  IF rownum > highelement THEN                     'redim recordset array if needed
    highelement = highelement + 50000
    REDIM PRESERVE sRecordset(1 TO highelement)
  END IF
  FOR colnum = 1 TO columns                        'read columns of current row
   sCol(colnum) = slf(colnum)                      'place into current column array
  NEXT
  sRecordset(rownum) = JOIN$(sCol(),sColumnDelimiter)  'concatenate columns into current recordset(row)
LOOP

IF rownum = 0 THEN
    ERASE sRecordSet
    ? "No data",%MB_SYSTEMMODAL,"GetRecordSet"
    EXIT FUNCTION      'exit if no rows returned
END IF

REDIM PRESERVE sRecordset(1 TO rownum)            'shrink array to actual size
FUNCTION = JOIN$(sRecordSet(),$CR)
END FUNCTION                     
Pages 1 ... 8 9 10