handle column count Grid on VFP 3

bharons · Jul 24, 2024

Another question based on update data table sql, i have a grid1 based on combobox vfp and textbox. Actually i taken this script or code from Mr. Cetin with his permission.. [wink]

, like below.

Code:

 Local lcSQL

        Use In (Select('crsResult'))
        
        TEXT TO m.lcSQL TEXTMERGE noshow		
        	select top(1000) * from << TRIM(crsTables.TableName) >>
        ENDTEXT
        
     	SQLExec(Thisform.nHandle, m.lcSQL ,'crsResult')
        With Thisform.grdViewer
        	FOR gnCount = 1 TO FCOUNT( )  && Loop for number of fields
   			thisform.text3.value = ALLTRIM(STR(gnCount))
			NEXT
            .ColumnCount = -1
            .RecordSource = "crsResult"
            .AutoFit()
        Endwith

If i use columncount = -1 by code and columncount = -1 by property columncount grid (grdViewer) show me name column or fieldname.

where textbox value 10 is how many column on table
if i use columncount = -1 by code and columncount = 10 by property columncount grid (grdViewer) not show me name column or fieldname.

Now i want to dealing with textbox, I if click on column grdViewer my textbox will show its value (textbox controlsource from grdViewer column) eg. thisform.text2.controlsource = app_direk.handset_id or thisform.text2.value = this.value (on column1.text1 grdViewer)
And on my combobox are listed table name with many column name or field name more than 10 columncount like picture below its tablename has 29 fieldname or columnname

I need advise and sugesstion to dealing with it.

Chris Miller · Jul 24, 2024

You have some nonsense code here:

Code:

FOR gnCount = 1 TO FCOUNT( )  && Loop for number of fields 
     thisform.text3.value = ALLTRIM(STR(gnCount))
NEXT

It does what it says it does, loops for every field, so in the first case from 1 to 10 and in the other case from 1 to 29. It sets the same textbox text3 value to all these numbers, ending in 10 or 29. The loop is too fast to see any of the lower numbers, so why do that in a loop?

Well, that could be done by

Code:

thisform.text3.value = FCOUNT()

So what did you want to do in the loop? Did you want to set the header captions to the fieldnames? Then that would be done by

Code:

FOR gnCount = 1 TO FCOUNT( )  && Loop for number of fields 
     thisform.grid1.header1.caption = FIELDS(gCount)
NEXT
thisform.text3.value = ALLTRIM(STR(FCOUNT( )))

I don't understand your other questions, sorry. There's obvviously a language barrier that hinders you to clearly describe your goals, setting the captions just was something I thought would be usefule, though if you set the columncount of the grid to 0 (not -1) and then set the RecordSource, you'd get the column (field) names as header captions, I think.

Chriss

bharons · Jul 24, 2024

by Chriss sugesstion, where place i should store your script.
at interactive change combobox or something else, because my script are inside interactive change combobox.. [ponder]

Code:

 Local lcSQL 
Use In (Select('crsResult')) 
TEXT TO m.lcSQL TEXTMERGE noshow		 	
select top(1000) * from << TRIM(crsTables.TableName) >> 
ENDTEXT  
SQLExec(Thisform.nHandle, m.lcSQL ,'crsResult') 
With Thisform.grdViewer 	
     .columncount = -1 	
     .RecordSource = "crsResult" 	
     .AutoFit() 
Endwith

for textbox3 are not dealing with all, for me just make sure how many fieldname that table I pick up from combobox.

i store Chriss script at interactivechange combobox

then i got error like below

my purpose or my goals about this question is connected with my question before.
filling textbox2 for lcValue and textbox1 for lcValue1 (sorry iam change textbox4 to textbox2 for cif and textbox6 to textbox1 for waktu / datetime datatype)

Code:

If m.KONEKSI > 0 	
local lcValue, lcValue1 	
lcValue = thisform.text2.value 	
lcValue1 = thisform.text1.value 	
TEXT TO lcSQL NOSHOW TEXTMERGE 	
     UPDATE DBO.nasabah SET waktu = ?m.lcValue1 	
     where cif = ?m.lcValue 	
ENDTEXT  	

lnReturnvalue = SQLEXEC(KONEKSI, lcSQL) 	
If lnReturnvalue<0    		
   Aerror(laError)    		
   suspend 	
Else   		
   Messagebox('Data Telah Tersimpan..??') 	
Endif 
endif

step in
1. I pick up database from combobox.
2. Click CIF number for lcValue / textbox2 value is from this fieldname / refering from grid value with click event on column
3. Determine lcValue1 or textbox1 value.
4. command button to safe change dbo.nasabah like Chriss Script.

mjcmkrsr · Jul 24, 2024

Hi,

The header is contained in a column and the function is spelled FIELD() - no "s". Hence it should be

Code:

for gnCount = 1 to FCOUNT()
ThisForm.grdViewer.Columns(gnCount).Header1.Caption = Field(gnCount)
endfor

hth

MarK

bharons · Jul 24, 2024

Hay Mark, nice to meet you. By your suggestion. I store your script beneath interactive change combobox, and its running well.. [smile2]

Thanks for Chriss Advise and Mark suggestion. Both of you are.. [thumbsup2]

persons who helped me in solving this trivial problem.
and more thing, how to refering value from column grid to textbox1.. [ponder]

, because this grid has columncount = -1.. [ponder]

Chris Miller · Jul 24, 2024

Don't care for the grid.columncount property, you know it isw FCOUNT().

Chriss

Chris Miller · Jul 24, 2024

I think this code alone would do it:

Code:

Local lcSQL

Use In (Select('crsResult'))
        
TEXT TO m.lcSQL TEXTMERGE noshow		
  Select top(1000) * from <<TRIM(crsTables.TableName) >>
ENDTEXT
        
SQLExec(Thisform.nHandle, m.lcSQL ,'crsResult')
With Thisform.grdViewer
     .ColumnCount = 0
     .RecordSource = "crsResult"
     .AutoFit()
Endwith
thisform.text3.value = FCOUNT()

In the line the RecordSource of the grid is set, it will adapt to that cursor, have as many columns in the same order as the cursor and have column names in the headers. If you don't see a columncount the same as fcount after you do that, I don't know why, but I also wouldn't care, the Grid has all the columns and you know the count from FCOUNT.

Chriss

bharons · Jul 25, 2024

i think the matter for grid is finish.
another question, after i have handle that column grid. its show me header or name column or fieldnama on table. it is posible for me to determine textbox2 vfp from picture above has controlsource from grdViewer [ponder]

mjcmkrsr · Jul 25, 2024

Hi,

Something like below?

Code:

ThisForm.textbox2.Value = ThisForm.grdSales.RecordSource

hth

MarK

mjcmkrsr · Jul 25, 2024

...

and a little demo how to increase/decrease the number of columns in a grid, to show the underlying RECORDSOURCE and to show the cell values of a specific row (although this is also feasible with TEXTBOXes and their CONTROLSOURCEs)

Code:

**************************************************
PUBLIC oForm

oForm = NEWOBJECT("form1")
oForm.Show
Read Events
Close all
Clear All
RETURN


**************************************************

DEFINE CLASS form1 AS Form
	AutoCenter = .T.
	Caption = "Sales"
	ShowTips = .T.
	Height = 480
	Width = 648
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
	
	ADD OBJECT lblSpinner as Label WITH ;
		Left = 12, Top = 12, Autosize = .T., Caption = "Number of Columns :"

	ADD OBJECT txtRecordSource as Textbox WITH ;
		Left = 12 + 360, Top = 12, ReadOnly = .T.

	ADD OBJECT grdSales AS Grid WITH ;
		Left = 12, ;
		Top = 48, ;
		Width = 624, ;
		Height = 420, ;
		Anchor = 1 + 2 + 4 + 8, ;
		Visible = .F., ;
		ColumnCount = -1, ;
		RecordSource = "curSales"

	ADD OBJECT cmdDoIt as CommandButton WITH ;
		Top = 12, ;
		Left = 120 + 120, ;
		Height = 24, ;
		Width = 120, ;
		Caption = "Show Cellvalues"
	
		PROCEDURE cmdDoit.Click()
			LOCAL li_Count, li_ColumnCount
			LOCAL ARRAY laValues[1]
			
			li_ColumnCount = MAX(1, ThisForm.spnColumns.Value)
			
			DIMENSION laValues[li_ColumnCount]
			
			FOR li_Count = 1 to li_ColumnCount
				laValues[li_Count] = EVALUATE(FIELD(li_Count))
				WAIT WINDOW + laValues[li_Count] TIMEOUT 1
			ENDFOR 

		ENDPROC 
	
	ADD OBJECT spnColumns as Spinner WITH ;
		Left = 12 + 120, ;
		Top = 12, ;
		SpinnerLowValue = 1, ;
		ToolTipText = "Increment or decrement to show number of columns"
		
		PROCEDURE spnColumns.InteractiveChange()
			LOCAL li_Count, li_Columns
			
			li_Columns = ThisForm.grdSales.ColumnCount
			
			This.SpinnerHighValue = li_Columns
			
			ThisForm.grdSales.Visible = .T.
			
			FOR li_Count = 1 TO li_Columns
				ThisForm.grdSales.Columns(li_Count).Visible = .F.
			ENDFOR 	
			
			FOR li_Count = 1 TO This.Value
				ThisForm.grdSales.Columns(li_Count).Visible = .T.
			ENDFOR 
			
			ThisForm.txtRecordSource.Value = ThisForm.grdSales.RecordSource	

			ThisForm.Refresh()

		ENDPROC
	
	PROCEDURE Destroy
		CLOSE ALL
		Clear Events

	ENDPROC

	PROCEDURE Load

		CREATE CURSOR curSales (C_CID C(4), C_Descri C(20), N_Price N(10,2), L_Paid L)
		
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI01", "A" + SYS(2015), 41.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI02", "A" + SYS(2015), 468.26, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI03", "A" + SYS(2015), 44.24, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI04", "A" + SYS(2015), 3.27, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI08", "A" + SYS(2015), 14.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI09", "A" + SYS(2015), 1054.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI10", "A" + SYS(2015), 384.27, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI07", "A" + SYS(2015), 164.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI06", "A" + SYS(2015), 10884.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI05", "A" + SYS(2015), 384.27, .T.)

		LOCATE 
		
	ENDPROC

ENDDEFINE
*********************************************

hth

MarK

Chris Miller · Jul 25, 2024

It'll always be crsRe4sult, as that's the name you gave the cursor in

Code:

SQLExec(Thisform.nHandle, m.lcSQL ,'[highlight #FCE94F]crsResult[/highlight]')

So why would you want to show that, it's always the same.
And the SQL Server table is what the user picks from a combobox, as far as I see.

Think of goals to achive, that would make more sense. If you want to learn something about the grid, then how about this task:

As far as I see AutoFit() sets columns widths so that every value is fully visible. That's fine, but now either the sum of column widths is too high to fit the grid width, or it is too low and you have some space. So one way to handle this would be to set the grid width to the sum of column widths, wouldn't it? How about you find out how to determine that?

Chriss

bharons · Jul 25, 2024

Hay Mark, your suggestion is near that i want, show all data in single row that column or row i have been press. my question is just simple.
that textbox2 changes along with one of the rows of data I press, not based on a particular column or row in the grid?
wherever the row and column I press, textbox2 contains the data in the row and column that I press?
and I use the command button as a command to save data in SQL Server by updating data based on certain criteria in the grid. and the criteria I mean are not based on column 1 but from various columns considering that there are many tables in the combo box listed.

Chris Miller · Jul 25, 2024

bharons said:
that textbox2 changes along with one of the rows of data I press, [highlight #FCE94F]not based on a particular column or row[/highlight] in the grid?

bharons said:
wherever the row and column I press, textbox2 contains the data in the row and column that I press?

You contradict yourself.

Do we need to teach English here, first?

What do you want? Do you want clicking into a Cell of the grid - that is a particular row and column, you said the opposite of that with "not", do you realize that? - do you want that click to also show the cell value to be copied into textbox text2? I'll skip the why question....

That needs code in the grid, which you could write into it when designing the form, but that would be deleted whenever you set a new recordsource. Because you delete all columns and create new ones. So that will require something to change drastically, either you stop using this simple method of grid population to preserve code you write into the grid, grid columns, and column controls events and methods or you make use of bindevents to bind the clicking to a separate handler that does what you want - that's advanced OOP programming and I don't see you ready for that. Or you create a column class, which also involves creating a class - OOP - that would be used to build the grid columns when you set the recordsource. I don't know what best to advise, here. But it's not as simple as you think it should be.

Chriss

Chris Miller · Jul 25, 2024

Let me see whether I overthink this. Controls and Columns of the grid are deleted, so any code you write in there in the designer is lost when populating the grid, but event and method code of the grid itself stays.

You could use the AfterRowColChange event of the grid to program the copying of the cell value. This grid event has the parameter nColIndex, which is the column. You don't need to know the row, because it will be the current record of the grids recordsource automatically. So all you need to know is the columns controlsource, i.e. which is the Nth field of the cursor, and that's just FIELD(nColIndex).

Chriss

mjcmkrsr · Jul 25, 2024

Hi,

bharons said:
Hay Mark, your suggestion is near that i want, show all data in single row that column or row i have been press. my question is just simple.
that textbox2 changes along with one of the rows of data I press, not based on a particular column or row in the grid?

I'm very sorry but I do NOT understand what you want.

Since in my example ALL the cell values of a specific row are written into the array, you may easily pick from that array the value you need. Or am I misunderstanding?

hth

MarK

mjcmkrsr · Jul 25, 2024

...

maybe you want something like below (modified former demo)

Code:

**************************************************
PUBLIC oForm

oForm = NEWOBJECT("form1")
oForm.Show
Read Events
Close all
Clear All
RETURN


**************************************************

DEFINE CLASS form1 AS Form
	DIMENSION laValues[1]
	
	AutoCenter = .T.
	Caption = "Sales"
	ShowTips = .T.
	Height = 480
	Width = 648
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
	
	ADD OBJECT lblSpinner as Label WITH ;
		Left = 12, Top = 12, Autosize = .T., Caption = "Number of Columns :"

	ADD OBJECT cboChoice as ComboBox WITH ;
		Left = 492, Top = 12, Width = 132, Style = 2, RowSourceType = 5, RowSource = "ThisForm.laValues"
		
		PROCEDURE cboChoice.GotFocus()
			ThisForm.cmdDoit.Click()
		
		ENDPROC 
		
	ADD OBJECT grdSales AS Grid WITH ;
		Left = 12, ;
		Top = 48, ;
		Width = 624, ;
		Height = 420, ;
		Anchor = 1 + 2 + 4 + 8, ;
		Visible = .F., ;
		ColumnCount = -1, ;
		RecordSource = "curSales"

	ADD OBJECT cmdDoIt as CommandButton WITH ;
		Top = 12, ;
		Left = 120 + 120, ;
		Height = 24, ;
		Width = 246, ;
		Caption = "Show cell values of active row in comboBox"
	
		PROCEDURE cmdDoit.Click()
			LOCAL li_Count, li_ColumnCount
			
			li_ColumnCount = MAX(1, ThisForm.spnColumns.Value)
			
			DIMENSION ThisForm.laValues[li_ColumnCount]
			
			FOR li_Count = 1 to li_ColumnCount
				ThisForm.laValues[li_Count] = EVALUATE(FIELD(li_Count))
			ENDFOR 

			WITH ThisForm.cboChoice
				.REQUERY()
				.Refresh()
			ENDWITH 

		ENDPROC 
	
	ADD OBJECT spnColumns as Spinner WITH ;
		Left = 12 + 120, ;
		Top = 12, ;
		SpinnerLowValue = 1, ;
		ToolTipText = "Increment or decrement to show number of columns"
		
		PROCEDURE spnColumns.InteractiveChange()
			LOCAL li_Count, li_Columns
			
			li_Columns = ThisForm.grdSales.ColumnCount
			
			This.SpinnerHighValue = li_Columns
			
			ThisForm.grdSales.Visible = .T.
			
			FOR li_Count = 1 TO li_Columns
				ThisForm.grdSales.Columns(li_Count).Visible = .F.
			ENDFOR 	
			
			FOR li_Count = 1 TO This.Value
				ThisForm.grdSales.Columns(li_Count).Visible = .T.
			ENDFOR 
			
			ThisForm.Refresh()

		ENDPROC
	
	PROCEDURE Destroy
		CLOSE ALL
		Clear Events

	ENDPROC

	PROCEDURE Load

		CREATE CURSOR curSales (C_CID C(4), C_Descri C(20), N_Price N(10,2), L_Paid L)
		
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI01", "A" + SYS(2015), 41.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI02", "A" + SYS(2015), 468.26, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI03", "A" + SYS(2015), 44.24, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI04", "A" + SYS(2015), 3.27, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI08", "A" + SYS(2015), 14.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI09", "A" + SYS(2015), 1054.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI10", "A" + SYS(2015), 384.27, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI07", "A" + SYS(2015), 164.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI06", "A" + SYS(2015), 10884.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI05", "A" + SYS(2015), 384.27, .T.)

		LOCATE 
		
	ENDPROC

ENDDEFINE
*********************************************

hth

MarK

Chris Miller · Jul 25, 2024

Simpler (I think)

Code:

**************************************************
PUBLIC oForm

oForm = NEWOBJECT("form1")
oForm.Show
Read Events
Close all
Clear All
RETURN


**************************************************

DEFINE CLASS form1 AS Form
	DIMENSION laValues[1]
	
	AutoCenter = .T.
	Caption = "Sales"
	ShowTips = .T.
	Height = 480
	Width = 648
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
	
	ADD OBJECT lblSpinner as Label WITH ;
		Left = 12, Top = 12, Autosize = .T., Caption = "Number of Columns :"

	ADD OBJECT spnColumns as Spinner WITH ;
		Left = 12 + 120, ;
		Top = 12, ;
		SpinnerLowValue = 1, ;
		ToolTipText = "Increment or decrement to show number of columns"
		
		PROCEDURE spnColumns.InteractiveChange()
			LOCAL li_Count, li_Columns
			
			li_Columns = ThisForm.grdSales.ColumnCount
			
			This.SpinnerHighValue = li_Columns
			
			ThisForm.grdSales.Visible = .T.
			
			FOR li_Count = 1 TO li_Columns
				ThisForm.grdSales.Columns(li_Count).Visible = .F.
			ENDFOR 	
			
			FOR li_Count = 1 TO This.Value
				ThisForm.grdSales.Columns(li_Count).Visible = .T.
			ENDFOR 
			
			ThisForm.Refresh()
		ENDPROC

    Add OBJECT txtCell as Textbox WITH ;
		Left = 12 + 240, ;
		Top = 12

	ADD OBJECT grdSales AS Grid WITH ;
		Left = 12, ;
		Top = 48, ;
		Width = 624, ;
		Height = 420, ;
		Anchor = 1 + 2 + 4 + 8, ;
		Visible = .F., ;
		ColumnCount = -1, ;
		RecordSource = "curSales"
		
[highlight #FCE94F]		Procedure grdSales.AfterRowColChange()
		   Lparameters nColumn
		   Thisform.txtCell.Value = Evaluate([highlight #8AE234]Fields(nColumn)[/highlight])
		Endproc[/highlight]
	
	
	PROCEDURE Destroy
		CLOSE ALL
		Clear Events

	ENDPROC

	PROCEDURE Load

		CREATE CURSOR curSales (C_CID C(4), C_Descri C(20), N_Price N(10,2), L_Paid L)
		
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI01", "A" + SYS(2015), 41.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI02", "A" + SYS(2015), 468.26, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI03", "A" + SYS(2015), 44.24, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI04", "A" + SYS(2015), 3.27, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI08", "A" + SYS(2015), 14.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI09", "A" + SYS(2015), 1054.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI10", "A" + SYS(2015), 384.27, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI07", "A" + SYS(2015), 164.25, .T.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI06", "A" + SYS(2015), 10884.25, .F.)
		INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI05", "A" + SYS(2015), 384.27, .T.)

		LOCATE 
		
	ENDPROC

ENDDEFINE
*********************************************

Chriss

mjcmkrsr · Jul 25, 2024

Hi Chriss,

Great. Thanks for the hint.

MarK

bharons · Jul 25, 2024

I got goosebumps when I saw this answer, actually this is what I was looking for. Thank you for your time and understanding in helping me learn about VFP grids. [love2]

mjcmkrsr · Jul 25, 2024

Hi bharons,

You're most welcome. Appreciate your feedback.

MarK

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

handle column count Grid on VFP 3

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Technical User

Programmer

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor