Clearing a StringGrid

[AndrewT]

I have a dialog that displays queried data in a StringGrid. I have figured out how to add a column for the user to enter data into the StringGrid object that I can then save into a new CSV file. This all works great until I want to go to the next queried record.

I have a "Clear" button that clears both CSV file variables (displayed and edited) and I set the dialog property, so the StringGrid looks clear. But when I load the next record into the StringGrid, I get the extra column with the edited values in it from the previous record. I don't know where these values are being stored, so I can't clear them, but if I restart the program they are gone.

Anyone have any ideas on how I should be clearing a StringGrid object properly?

Thanks,
Andrew T.

[JRL]

I have figured out how to add a column for the user to enter data into the StringGrid object...
...I have a "Clear" button that clears both CSV file variables (displayed and edited)

Interesting, sounds like you've made a single column editable. If you set the StringGrid "options" property to "goEditing" the StringGrid cells are editable but then all of the cells are editable. Are you using two StringGrids? Do you have a separate edit object in which the user types the new data then presses a button that places the data in the "editable" cell? Perhaps you have an editable StringGrid then you set FixedCols to a value one less than the total column quantity? I'd be interested to know how you made a single column editable.

In any case. Without seeing your code I don't know what is causing your issue. I suspect you have a variable that still contains the previous data or that you are inadvertently retrieving the data from where ever you stored it.

[AndrewT]

To get the editable column, I set the column number to one more than the data I am receiving via the query and making the StringGrid editable. I have looked at all of the variables and the data is completely cleared out, so my theory is that there is some behind the scenes memory that I can't obviously clear.

Since I am doing a query on a database, you can't run my code directly, but here it is for looking at.

Code: Select all

'PONumber'
  end
  object POGrid: tMSStringGrid
    Left = 34
    Top = 74
    Width = 695
    Height = 391
    TabStop = False
    ColCount = 6
    Options = [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine, goRangeSelect, goColSizing, goEditing]
    TabOrder = 5
    ColWidths = (
      60
      180
      240
      60
      80
      60)
  end
  object btnDone: tMSButton
    Left = 749
    Top = 86
    Width = 75
    Height = 25
    Caption = 'Done'
    TabOrder = 3
    DoBrowse = False
    BrowseStyle = fbOpen
  end
  object btnClear: tMSButton
    Left = 751
    Top = 130
    Width = 75
    Height = 25
    Caption = 'Clear'
    ModalResult = 1
    TabOrder = 4
    DoBrowse = False
    BrowseStyle = fbOpen
  end
end
EndDialog>PODialog

AddDialogHandler>PODialog,Password,OnExit,LogIn
AddDialogHandler>PODialog,PONumber,OnExit,POLokUp
AddDialogHandler>PODialog,POGrid,OnSetEditText,doEdit
AddDialogHandler>PODialog,btnDone,OnClick,doFinish
AddDialogHandler>PODialog,btnClear,OnClick,doClear

Label>Start
Show>PODialog,Cleard
IF>Cleard=1
CloseDialog>PODialog
Goto>Start
Endif
end

Srt>LogIn

END>LogIn

Srt>POLokUp
  GetDialogProperty>PODialog,PONumber,Text,varPONum
  Let>ReCap="Meas","Rec'd"
  Length>%varPONum%,POLen
  Let>POCat=10-%POLen%
  Let>POAdd=0
  Repeat>POAdd
    Let>POAdd=POAdd+1
    Let>varPONum=0%varPONum%
  Until>POAdd=POCat
  SetDialogProperty>PODialog,PONumber,Text,varPONum
  Let>CSVStr=
  Let>TBL=PurchasingDetail
  Let>LokUpStr=Select %TBL%.LineNo, LTRIM(RTRIM(%TBL%.DetailNo)) as "DetailNo", LTRIM(RTRIM(%TBL%.Description)) as "Description", %TBL%.OrderedQty, LTRIM(RTRIM(%TBL%.Meas)) as "Meas" from %TBL% where (%TBL%.InvNo ='%varPONum%' and %TBL%.DetailType ='1')
  Let>file=%SCRIPT_DIR%\POout.csv
  //DBQuery>dbT,LokUpStr,Tracking,Track_recs,Track_fields
  VBRun>SQLToCSV,ConStrT,LokUpStr,file
  ReadFile>file,CsvFile
  StringReplace>CsvFile,"Meas",%ReCap%,CsvFile
  SetDialogProperty>PODialog,POGrid,LoadFromCSV,CsvFile
//**BREAKPOINT**
  GetDialogProperty>PODialog,POGrid,RowCount,NumRows
  Let>NumRows=NumRows-1
END>POLokUp

SRT>doEdit
  GetDialogProperty>PODialog,POGrid,SaveToCSV,EditCsvFile
END>doEdit

SRT>doFinish
  IfFileExists>%Desktop_Dir%\%varPONum%.csv
   DeleteFile>%Desktop_Dir%\%varPONum%.csv
  Endif
  Wait>0.5
  WriteLn>%Desktop_Dir%\%varPONum%.csv,result,%EditCsvFile%
  ExecuteFile>%Desktop_Dir%\%varPONum%.csv
END>doFinish

SRT>doClear
  Let>varPONum=
  Let>CSVFile=
  Let>EditCsvFile=
  SetDialogProperty>PODialog,PONumber,Text,%varPONum%
  SetDialogProperty>PODialog,POGrid,LoadFromCSV,CsvFile
END>doClear

Thanks,
Andrew T

[JRL]

Andrew,
Took a while but I think I found how to resolve the issue. You are correct that the data seems to be stored in the StringGrid object and comes back on its own if it not explicitly removed/changed.

Run the following script (which is an adulterated version of the script you posted).
Type a number in the "PO Number" field. (1 for example)
Press Tab. A message box will appear displaying the current "Fake" data
Close the message box and the "Fake" data will appear in the dialog grid.
Put a "Y" in the Rec'd column and the message box will reappear with the current fake data including the just typed "Y"
Close the message box and press tab to leave the cell. message box will appear again. Close it.
Pick the "Clear" button. Message box will display the current grid contents and will clear the grid.

NOTICE the top row still exists.

Start again by typing a number in the "PO Number" field. (2 for example)
Press Tab. A message box will appear displaying the current "Fake" data. NOTICE there is no "Y" at the end of the row.
Close the message box and the "Fake" data will appear in the dialog grid. NOTICE the previously entered "y" is displayed.

I believe this is what you were experiencing.

Now stop the script, go to the end of the script and add a comma at the end of each of the data lines.

Row1,45,bracket,1,EA,
Row2,53,bolt,5,EA,
Row3,55,plate,1,EA,
Row4,78,spindle,1,EA,

Restart the script and redo the procedure above and the user added data will be gone after the "Clear" button is pressed.

Apparently every StringGrid cell that had any data must individually be reset to nothing to appear blank. Adding the commas does that.

I added a note to the script about the Format> function that might or might not interest you.

Code: Select all

//As posted the dialog was malformed so I
//reconstructed it as well as I could.
//I know it was missing at least one object called "PONumber"
//So I added an edit object named "PONumber".
Dialog>PODialog
object PODialog: TForm
  Left = 395
  Top = 226
  HelpContext = 5000
  BorderIcons = [biSystemMenu]
  Caption = 'CustomDialog'
  ClientHeight = 550
  ClientWidth = 900
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = True
  ShowHint = True
  OnTaskBar = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 760
    Top = 184
    Width = 32
    Height = 13
    Caption = 'PO Number'
  end
  object POGrid: tMSStringGrid
    Left = 34
    Top = 74
    Width = 695
    Height = 391
    TabStop = False
    ColCount = 6
    Options = [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine, goRangeSelect, goColSizing, goEditing]
    TabOrder = 0
    ColWidths = (
      60
      180
      240
      60
      80
      60)
  end
  object btnDone: tMSButton
    Left = 749
    Top = 86
    Width = 75
    Height = 25
    Caption = 'Done'
    TabOrder = 1
    DoBrowse = False
    BrowseStyle = fbOpen
  end
  object btnClear: tMSButton
    Left = 751
    Top = 130
    Width = 75
    Height = 25
    Caption = 'Clear'
    ModalResult = 1
    TabOrder = 2
    DoBrowse = False
    BrowseStyle = fbOpen
  end
  object PONumber: TEdit
    Left = 755
    Top = 202
    Width = 121
    Height = 21
    TabOrder = 11
  end
end
EndDialog>PODialog

AddDialogHandler>PODialog,Password,OnExit,LogIn
AddDialogHandler>PODialog,PONumber,OnExit,POLokUp
AddDialogHandler>PODialog,POGrid,OnSetEditText,doEdit
AddDialogHandler>PODialog,btnDone,OnClick,doFinish
AddDialogHandler>PODialog,btnClear,OnClick,doClear

Label>Start
Show>PODialog,Cleard

IF>Cleard=1
CloseDialog>PODialog
Goto>Start
Endif


Srt>LogIn
END>LogIn
Srt>POLokUp

SetDialogProperty>PODialog,POGrid,LoadFromCSV,vData
  GetDialogProperty>PODialog,PONumber,Text,varPONum
  Let>ReCap="Meas","Rec'd"
///////////////////////////////
  Length>%varPONum%,POLen
  Let>POCat=10-%POLen%
  Let>POAdd=0
  Repeat>POAdd
    Let>POAdd=POAdd+1
    Let>varPONum=0%varPONum%
  Until>POAdd=POCat
///////////////////////////////
//Previous 7 lines can be replaced with the following format line
//If you have version 14.2 or later.
  Format>%.10d,varPONum,varPONum
  
  SetDialogProperty>PODialog,PONumber,Text,varPONum
  
  //Where else is this variable used?
  Let>CSVStr=
  
  //Skipping your database query
  Goto>POLokUpEnd
  
  Let>TBL=PurchasingDetail
  Let>LokUpStr=Select %TBL%.LineNo, LTRIM(RTRIM(%TBL%.DetailNo)) as "DetailNo", LTRIM(RTRIM(%TBL%.Description)) as "Description", %TBL%.OrderedQty, LTRIM(RTRIM(%TBL%.Meas)) as "Meas" from %TBL% where (%TBL%.InvNo ='%varPONum%' and %TBL%.DetailType ='1')
  Let>file=%SCRIPT_DIR%\POout.csv
  //DBQuery>dbT,LokUpStr,Tracking,Track_recs,Track_fields
  
  //the following line will do nothing unless you have the "SQLToCSV"
  //VBScript function somewhere in your script and the script has it loaded.
  VBRun>SQLToCSV,ConStrT,LokUpStr,file
  ReadFile>file,CsvFile
  
  Label>POLokUpEnd
  //Inserting FakeData
  LabelToVar>FakeData,CsvFile
  ////////////////////
  StringReplace>CsvFile,"Meas",%ReCap%,CsvFile
  MDL>CsvFile
  SetDialogProperty>PODialog,POGrid,LoadFromCSV,CsvFile
//**BREAKPOINT**
  GetDialogProperty>PODialog,POGrid,RowCount,NumRows
  Let>NumRows=NumRows-1
END>POLokUp
SRT>doEdit
  GetDialogProperty>PODialog,POGrid,SaveToCSV,EditCsvFile
  MDL>EditCsvFile
END>doEdit
SRT>doFinish
  IfFileExists>%Desktop_Dir%\%varPONum%.csv
   DeleteFile>%Desktop_Dir%\%varPONum%.csv
  Endif
  Wait>0.5
  WriteLn>%Desktop_Dir%\%varPONum%.csv,result,%EditCsvFile%
  ExecuteFile>%Desktop_Dir%\%varPONum%.csv
END>doFinish
SRT>doClear

**BREAKPOINT**
  Let>varPONum=
  Let>CSVFile=
  Let>EditCsvFile=
  SetDialogProperty>PODialog,PONumber,Text,%varPONum%
  SetDialogProperty>PODialog,POGrid,LoadFromCSV,CsvFile
END>doClear


/*
FakeData:
LineNo,"DetailNo","Description","OrderedQty","Meas"
Row1,45,bracket,1,EA
Row2,53,bolt,5,EA
Row3,55,plate,1,EA
Row4,78,spindle,1,EA
*/

[AndrewT]

Thank you for your help! This is what I feared I would have to do, but I hadn't figured out how to manipulate my queried data yet to add a "blank". Next task I guess.

EDIT: Turns out adding this one line makes it work.

Code: Select all

StringReplace>CsvFile,%CRLF%,%COMMA%%CRLF%,CsvFile

Thanks as well for the heads up on the Format function. Wish I had known about that sooner as I have a number of programs that need zero padding like this.

Andrew T.

[JRL]

StringReplace>CsvFile,%CRLF%,%COMMA%%CRLF%,CsvFile

Good fix!

As for the Format> function. Back about the beginning of the century I wrote one of my first Macro Scheduler programs and used it in a number of macros I wrote back then. It was so I could get a date x number of days from a specified date. It was about 200 lines. I replaced it with

Let>vDate=1/28/2016
Add>vDate,90

[AndrewT]

So I am still working with this program and the StringGrid for data entry. Like JRL alluded to earlier, the I had to make the whole grid editable, which may or may not be OK for deployment. If I could "Lock" certain columns, that would be nice. I am thinking I can overwrite the edited data with the original data based on the cell location if it becomes an issue.

The next trick is to figure out how to go down to the next row in the grid when Enter is pressed (like in Excel). This might be easy if the OnSetEditText event triggered on Enter, but it seems to trigger as soon as any key is pressed, ie. if I enter 20 into the cell, then the OnSetEditText is triggered once for the 2, once for the 0 and once for the Enter at the end. Unfortunately pressing Enter keeps the same cell selected, just ends the edit. Pressing the down arrow works well, but it requires people to learn new habits, and we all know how easy that is.

Any ideas?

Thanks,
Andrew

[JRL]

Just a thought:

Code: Select all

AddDialogHandler>PODialog,POGrid,OnKeyPress,srtEnter

SRT>srtEnter
  If>SRTENTER_KEY=13
    Press down
  EndIf
END>srtEnter

[AndrewT]

And that is why I ask! It would have taken me forever to figure it out and my solution won't have been that simple.

Thank you,
Andrew

[AndrewT]

OK, me again.

As I think I have explained before, I use the StringGrid to display data from a SQLtoCSV query, then add/edit that data and then save this data back to a CSV file that I then use to later query in another program. The problem I am running into is that when I retrieve the data from the StringGrid as the SaveToCSV parameter, it loses the quotes around the strings that don't have spaces in them. This causes problems when I query the data from the resultant CSV file.

Here is an example of the data out of the SQLToCSV script.

Code: Select all

"5","K-60456KL4006","FAUCET 4cc DECK 6SWIVELSPOUT"

Here is the same data after it comes out of the StringGrid as the SaveToCSV parameter. Note that the quotes around the data in the first two column sets have been stripped away.

Code: Select all

5,K-60456KL4006,"FAUCET 4cc DECK 6SWIVELSPOUT"

I doubt there is anything I can do directly to the StringGrid to fix it at this time, but I am wondering if someone has a quick way to put the quotes back in before I write it out to the final file.

Thanks,
Andrew