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!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

handle column count Grid on VFP 3

Status
Not open for further replies.

bharons

Technical User
Jan 23, 2019
29
0
1
ID
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.
grid1_idgrbc.jpg


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.
grid2_hwahxv.jpg


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

grid3_lnyazq.jpg


I need advise and sugesstion to dealing with it.
 
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
 
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.
grid7_ljupkl.jpg


i store Chriss script at interactivechange combobox
grid5_uquulm.jpg


then i got error like below
grid8_hhzwqp.jpg


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.
 
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
 
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]
 
Don't care for the grid.columncount property, you know it isw FCOUNT().

Chriss
 
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
 
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]
 
Hi,

Something like below?

Code:
ThisForm.textbox2.Value = ThisForm.grdSales.RecordSource

hth

MarK
 
...

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

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
 
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
 
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][love2]
 
Hi bharons,

You're most welcome. Appreciate your feedback.

MarK
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top