SQLitening Support Forum

I have been working on a little address program using FF and SQLitening as a learning project.  I have been able to write code to insert or update records into my database from individual textboxes, but how do you read records from a table into individual textboxes? I haven

I don't know if Powerbasic allows you to utilize tags. But I use VB. Which allows me to use tags on controls

For each textbox I have a tag similiar to this : 0Afieldname
   --  The 0 tells me if the field changes. It changes to 1 if the textbox changed
   -- The A represents what type of field it is. I use A-lphanumeric N-umeric.
       I can also use this to validate the information entered in the text box.
   -- The remainder of the tag tells me the field name.

1) When the user clicks on a text box to edit it. I store the existing value in a variable.
2) When the user exits the textbox I check to see if the value has changed. If the value has changed. I set the 0 value to 1. (This tells me the value has changed). If the value is already 1 (leave it -- this is the second change the user is making)
3) To save the information. I simply loop through all the text boxes and update the fields that have changed.I build a field string and a value string. I use the slsBuildInsertOrUpdate function to build my sqlquery and send it to the server.
4) To load the record I just reverse the procedure - I loop through the textboxes and assign the field value to textbox.  Typically I only access one table from my form.
5) To 'reset' the form I loop through the textboxes. Set the values to 0 and clear the textbox contents.

One big advantage to this is if I need to add a additional field I simply add the textbox set the field value and the software automatically 'catches' the additional field.

For my projects I use barcode scanners and signature pads and I utilize activex controls to integrate them in my software. I am working on getting things working with powerbasic but I have upgraded existing projects with no issues at all.  This has allowed me to upgrade projects to client/server very quickly. I have written a small program that allows me to enter the database table and it automatically creates the general form I just have to go in and move the textboxes to their final positions.

For a given dialog window, I generally write a function that will read the database and populate the cells of a grid (Farpoint Spread control).  I can then call that function from the WM_INITDIALOG notification as well as a separate "refresh" handler that can be called as needed.

Thanks for the help and replies.

Tony

Hello Tony,

in FF3 you would use this code to assign a field value to a textbox:

FF_TextBox_SetText( HWND_FORM1_TEXT1, slF(1, 0 ) )

Attached you find a small FF3 project of a Data Browser example.


Rolf

Thanks Rolf.  I'll be studying the code later today.

Tony

Rolf,
I was studying your code and was intrigued by this line of code

  dbName = app.Path & "sample.db3"

I know what it does, but where does

The App keyword is part of an implementation of FireFly, similar to the one in VB. Below is the text from the older FF2 help file. I think it was just forgotten in the FF3 help file.

Quote
FireFly allows access to many internal data items via the global APP variable.

Type APP_Type
      Comments        As Asciiz * %MAX_PATH   ' Comments         (Project Properties)
      CompanyName     As Asciiz * %MAX_PATH   ' Company Name     (Project Properties)
      EXEName         As Asciiz * %MAX_PATH   ' EXE name of program 
      FileDescription As Asciiz * %MAX_PATH   ' File Description (Project Properties)
      hInstance       As Long                 ' Instance handle of the program
      Path            As Asciiz * %MAX_PATH   ' Current Path to the EXE (with trailing backslash)
      ProductName     As Asciiz * %MAX_PATH   ' Product Name     (Project Properties)     
      LegalCopyright  As Asciiz * %MAX_PATH   ' Legal Copyright  (Project Properties) 
      LegalTrademarks As Asciiz * %MAX_PATH   ' Legal Trademarks (Project Properties)
      ProductMajor    As Long                 ' Product Major number     (Project Properties)
      ProductMinor    As Long                 ' Product Minor number     (Project Properties)
      ProductRevision As Long                 ' Product Revision number     (Project Properties)
      ProductBuild    As Long                 ' Product Build number     (Project Properties)
      FileMajor       As Long                 ' File Major number     (Project Properties)
      FileMinor       As Long                 ' File Minor number     (Project Properties)
      FileRevision    As Long                 ' File Revision number     (Project Properties)
      FileBuild       As Long                 ' File Build number     (Project Properties)
End Type


You can access any members of this Type variable from anywhere in your program.

For example, to display the current version of your program in an "About" box, simply do the following:

st$ = Format$(App.ProductMajor) & "." & Format$(App.ProductMinor) & "." & _
      Format$(App.ProductRevision) & "." & Format$(App.ProductBuild)
   
FF_Control_SetText HWND_FORM1_LABEL1, st$


The App variable is similar to the App variable similar to the one found in Visual Basic. An exception is that the PrevInstance member found in Visual Basic is implemented in FireFly as a separate function (i.e. FF_PrevInstance).

As you can see, many of the elements of the App variable come from the data that you set in the Project Properties dialog for your project.

BTW I think questions concerning FireFly should be better put into the FireFly forum, for many of the users of SQLitening they won't be very helpful.

Rolf

Thanks again.  I thought it looked a lot like VB and wasn't sure how it was working in FF.

This uses PBFORMS, but reads and writes rowid=1 into textbox's.

#PBFORMS CREATED V2.01
#COMPILE EXE "\sql\bin\textbox"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
#PBFORMS BEGIN INCLUDES
#INCLUDE ONCE "WIN32API.INC"
#PBFORMS END INCLUDES
#PBFORMS BEGIN CONSTANTS
%DIALOG1     =  101
%TEXTBOX1    = 1001
%TEXTBox2    = 1002
%TEXTBOX3    = 1003
%BTN_BUTTON1 = 1005
%BTN_BUTTON2 = 1006
%BTN_BUTTON3 = 1007
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
FUNCTION PBMAIN()
  slOpen "textbox.db3", "C"
  'slExe  "Drop table if exists t1"
  slExe  "create table if not exists t1(t1c1, t1c2,t1c3)"
  slsel  "select * from t1 where rowid = 1"
  IF ISFALSE(slGetRow) THEN 'no rows so create rowid 1
    ? "Create first row"
    slExe  "Insert into t1 values('1','2','3')"
  END IF
  ShowDIALOG1 %HWND_DESKTOP
END FUNCTION

CALLBACK FUNCTION ShowDIALOG1Proc()
  LOCAL textbox0,textbox1,textbox2,sql AS STRING
  SELECT CASE AS LONG CB.MSG
    CASE %WM_INITDIALOG
      GetQuery CB.HNDL
    CASE %WM_NCACTIVATE
      STATIC hWndSaveFocus AS DWORD
      IF ISFALSE CB.WPARAM THEN
        hWndSaveFocus = GetFocus()
      ELSEIF hWndSaveFocus THEN
        SetFocus(hWndSaveFocus)
        hWndSaveFocus = 0
      END IF
    CASE %WM_COMMAND
      SELECT CASE AS LONG CB.CTL
        ' /* Inserted by PB/Forms 06-05-2013 12:35:02
        CASE %BTN_BUTTON1
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            slExe  "Drop table if exists t1"
            slExe  "create table if not exists t1(t1c1, t1c2,t1c3)"
            CONTROL SET TEXT CB.HNDL, %TEXTBOX1, ""
            CONTROL SET TEXT CB.HNDL, %TEXTBOX2, ""
            CONTROL SET TEXT CB.HNDL, %TEXTBOX3, ""
            slExe  "Insert into t1 values('','','')"
          END IF

        CASE %BTN_BUTTON2
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            MSGBOX "%BTN_BUTTON2=" + FORMAT$(%BTN_BUTTON2), %MB_TASKMODAL
          END IF

        CASE %BTN_BUTTON3
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            MSGBOX "%BTN_BUTTON3=" + FORMAT$(%BTN_BUTTON3), %MB_TASKMODAL
          END IF

      END SELECT
    CASE %WM_CLOSE
      UpdateRecord CB.HNDL, 1
  END SELECT
END FUNCTION
FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG
#PBFORMS BEGIN DIALOG %DIALOG1->->
  LOCAL hDlg  AS DWORD

  DIALOG NEW hParent, "Textbox", 318, 86, 182, 207, %WS_POPUP OR %WS_BORDER _
    OR %WS_DLGFRAME OR %WS_CAPTION OR %WS_SYSMENU OR %WS_MINIMIZEBOX OR _
    %WS_MAXIMIZEBOX OR %WS_CLIPSIBLINGS OR %WS_VISIBLE OR %DS_MODALFRAME OR _
    %DS_3DLOOK OR %DS_NOFAILCREATE OR %DS_SETFONT, %WS_EX_CONTROLPARENT OR _
    %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX1, "", 10, 5, 164, 40, %WS_CHILD _
    OR %WS_VISIBLE OR %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR _
    %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR _
    %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD TEXTBOX, hDlg, %TEXTBox2, "", 10, 55, 163, 40, _
    %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR _
    %ES_AUTOHSCROLL OR %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE _
    OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX3, "", 10, 105, 163, 40, _
    %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR _
    %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR _
    %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD BUTTON,  hDlg, %BTN_BUTTON1, "Create table", 10, 179, 50, 15
  CONTROL ADD BUTTON,  hDlg, %BTN_BUTTON2, "Button2", 63, 179, 50, 15
  CONTROL ADD BUTTON,  hDlg, %BTN_BUTTON3, "Button3", 116, 179, 50, 15
#PBFORMS END DIALOG
  DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
#PBFORMS BEGIN CLEANUP %DIALOG1
#PBFORMS END CLEANUP
  FUNCTION = lRslt
END FUNCTION

SUB GetQuery(hDlg AS DWORD)
  slSel "SELECT * FROM T1 WHERE ROWID = 1"
  IF slGetRow THEN
    CONTROL SET TEXT hDlg, %TEXTBOX1, slf(1)
    CONTROL SET TEXT hDlg, %TEXTBOX2, slf(2)
    CONTROL SET TEXT hDlg, %TEXTBOX3, slf(3)
    slGetRow 'extra get needed to close
  END IF
END SUB

SUB UpdateRecord(hDlg AS DWORD,RecNum AS LONG)
  'this will fail if table was deleted"
  LOCAL s AS STRING
  CONTROL GET TEXT hDlg, %Textbox1 TO s
  s =  "'" + s + "'"
  slexe "Update t1 set t1c1 ="+s+" where rowid = " + STR$(RecNum)
  CONTROL GET TEXT hDlg, %Textbox2 TO s
  s = "'" + s + "'"
  slexe "Update t1 set t1c2 ="+s+" where rowid = " + STR$(RecNum)
  CONTROL GET TEXT hDlg, %Textbox3 TO s
  s = "'" + s + "'"
  slexe "Update t1 set t1c3 ="+s+" where rowid = " + STR$(RecNum)
END SUB


cj, in your UpdateRecord function you are doing 3 separate slExe's. Maybe more efficient to wrap it all in one transaction with one UPDATE SQL statement for all fields being changed in the RecNum?

The "App" variable in FireFly3 is found in the Help file under the topic "User Reference Guide" / "Special Functions".

Thanks to CJ and Paul for also responding.  It has been a great encouragement to have so many reply.

Paul, I guess I better reread the help file again. That is the second item I have missed.  I did look in Workspace under Special Functions, but didn't see it so I did go to that section in the help file.

Thanks,
Tony

Took Paul's suggestion and used a transaction.
Added a save button and a label displaying statements.
Might expand on this if anyone is interested.
#PBFORMS CREATED V2.01
#COMPILE EXE "\sql\bin\textbox"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
#PBFORMS BEGIN INCLUDES
#INCLUDE ONCE "WIN32API.INC"
#PBFORMS END INCLUDES
#PBFORMS BEGIN CONSTANTS
%DIALOG1    =  101
%TEXTBOX1   = 1001
%TEXTBOX2   = 1002
%STATEMENT  = 1003
%BTN_CREATE = 1004
%BTN_SAVE   = 1005
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
FUNCTION PBMAIN()
  ShowDIALOG1 %HWND_DESKTOP
END FUNCTION

CALLBACK FUNCTION ShowDIALOG1Proc()
  LOCAL s AS STRING
  SELECT CASE AS LONG CB.MSG
    CASE %WM_INITDIALOG
      slOpen "textbox.db3", "C"
      REM slExe  "Drop table if exists t1"
      slExe  "create table if not exists t1(t1c1, t1c2)"
      slsel  "select * from t1 where rowid = 1"
      IF ISFALSE(slGetRow) THEN 'no rows so create rowid 1
         CONTROL SET TEXT CB.HNDL, %STATEMENT, "Insert first record"
         slExe  "Insert into t1 values('box1','box2')"
      END IF
      GetQuery CB.HNDL, 1 'get recid = 1
    CASE %WM_NCACTIVATE
      STATIC hWndSaveFocus AS DWORD
      IF ISFALSE CB.WPARAM THEN
        hWndSaveFocus = GetFocus()
      ELSEIF hWndSaveFocus THEN
        SetFocus(hWndSaveFocus)
        hWndSaveFocus = 0
      END IF
    CASE %WM_COMMAND
      SELECT CASE AS LONG CB.CTL

        CASE %BTN_CREATE
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            s =  "Drop table if exists t1;"
            s = s + "create table if not exists t1(t1c1, t1c2);"
            s = s + "Insert into t1 values('box1','box2')"
            s = "BEGIN;" + s + ";END"
            slExe s
            CONTROL SET TEXT CB.HNDL, %STATEMENT, s
            GetQuery CB.HNDL, 1
          END IF

        CASE %BTN_SAVE
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
             UpdateRecord CB.HNDL, 1 'update recid = 1
            END IF
      END SELECT
    CASE %WM_CLOSE
      'UpdateRecord CB.HNDL, 1
  END SELECT
END FUNCTION

FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG
#PBFORMS BEGIN DIALOG %DIALOG1->->
  LOCAL hDlg   AS DWORD
  LOCAL hFont1 AS DWORD

  DIALOG NEW hParent, "Textboxes", 288, 102, 185, 170, %WS_POPUP OR _
    %WS_BORDER OR %WS_DLGFRAME OR %WS_CAPTION OR %WS_SYSMENU OR _
    %WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_CLIPSIBLINGS OR %WS_VISIBLE OR _
    %DS_MODALFRAME OR %DS_3DLOOK OR %DS_NOFAILCREATE OR %DS_SETFONT, _
    %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX1, "", 10, 5, 164, 40, %WS_CHILD OR _
    %WS_VISIBLE OR %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR _
    %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR _
    %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX2, "", 10, 55, 163, 40, %WS_CHILD OR _
    %WS_VISIBLE OR %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR _
    %ES_AUTOHSCROLL OR %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE _
    OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD LABEL,   hDlg, %STATEMENT, "", 10, 105, 163, 40, %WS_CHILD OR _
    %WS_VISIBLE OR %SS_LEFT, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR _
    %WS_EX_LTRREADING
  CONTROL ADD BUTTON,  hDlg, %BTN_CREATE, "Create table", 20, 150, 50, 15, _
    %WS_CHILD OR %WS_VISIBLE OR %BS_TEXT OR %BS_PUSHBUTTON OR %BS_CENTER OR _
    %BS_VCENTER, %WS_EX_LEFT OR %WS_EX_LTRREADING
  CONTROL ADD BUTTON,  hDlg, %BTN_SAVE, "Save", 120, 150, 50, 15, %WS_CHILD _
    OR %WS_VISIBLE OR %BS_TEXT OR %BS_PUSHBUTTON OR %BS_CENTER OR _
    %BS_VCENTER, %WS_EX_LEFT OR %WS_EX_LTRREADING

  FONT NEW "Courier New", 8, 0, %ANSI_CHARSET TO hFont1

  CONTROL SET FONT hDlg, %STATEMENT, hFont1
#PBFORMS END DIALOG
  DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
#PBFORMS BEGIN CLEANUP %DIALOG1
  FONT END hFont1
#PBFORMS END CLEANUP
  FUNCTION = lRslt
END FUNCTION

SUB GetQuery(hDlg AS DWORD,RecNum AS LONG)
  LOCAL s AS STRING
  s = "SELECT * FROM T1 WHERE ROWID=" + FORMAT$(RecNum)
  slSel s
  CONTROL SET TEXT hDlg, %STATEMENT, s
  IF slGetRow THEN
    CONTROL SET TEXT hDlg, %TEXTBOX1, slf(1)
    CONTROL SET TEXT hDlg, %TEXTBOX2, slf(2)
    slGetRow 'extra get needed to close
  END IF
END SUB

SUB UpdateRecord(hDlg AS DWORD,RecNum AS LONG)
  'this will fail if table was deleted"
  LOCAL s,s1,s2 AS STRING
  CONTROL GET TEXT hDlg, %Textbox1 TO s1
  s1 =  "'" + s1 + "'"
  s = "Update t1 set t1c1="+s1+" where rowid=" + FORMAT$(RecNum)

  CONTROL GET TEXT hDlg, %Textbox2 TO s2
  s2 = "'" + s2 + "'"
  s = s + ";Update t1 set t1c2="+s2+" where rowid=" + FORMAT$(RecNum)

  s = "BEGIN;" + s  + ";END" 'wrap within BEGIN/END
  CONTROL SET TEXT hDlg, %STATEMENT, s
  slExe s
END SUB

Maybe even one UPDATE instead of two: 

s = "BEGIN IMMEDIATE; UPDATE t1 SET t1c1 = " + s1 + ", t1c2 = "  + s2 + "  where rowid=" + FORMAT$(RecNum) + "; END TRANSACTION;"
slExe s


Should also validate the s1 and s2 values to ensure that any embedded single quote characters are double escaped, otherwise your sql will fail. For example, the name O'Leary would fail... it needs to be O''Leary.