handle column count Grid on VFP 3

[bharons]

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.., like below.

 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.

 

[mjcmkrsr]

Hi Chriss,

I compared the two solutions (green - mine / orange - yours / see below). Yours is not only more straightforward but also more elegant. Thanks again for the hint.

**************************************************
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 txtCell as Textbox WITH ;
		Left = 12 + 240, Top = 12
	
	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"
		
[highlight #8AE234]		PROCEDURE cboChoice.GotFocus()
			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 This
				.REQUERY()
				.Refresh()
			ENDWITH 
		
		ENDPROC[/highlight] 
		
	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 #FCAF3E]		PROCEDURE grdSales.AfterRowColChange()
		   LPARAMETERS tiColumn
		   
		   Thisform.txtCell.Value = Evaluate(Field(tiColumn))
		   
		ENDPROC[/highlight] 
	
	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
*********************************************

MarK

 

[Chris Miller]

MarK,

well, yes, I didn't need the code you had for the button going through all fields and build an array to pick from in a comobox. Of course, if you do without that it's shorter. bharons could also have extracted from your code, how to read fields by their field number = column number via the FIELDS() function.

You can, of course, also read out the columns controlsource, which would be more generally working, not only when you populate a grid by all fields of a workarea. but only some fields of it you set into column controlsources. On the other hand, you can always create a cursor that only has all fields you want to show in the grid and have the column number reflect the field number 1:1

Then you could have a combobx in a column which displays an item from a table that's selected by a foreign key ID in the grid cursor, and you'd likely want to show the picked combobox item instead of the foreign key id. So it surely isn't the end of the story.

Chriss

 

[bharons]

I have thoughts that deviate from the topic. How do I add a double click event to the code provided to call another table cursor based on the contents of the cell value, below my code.

LPARAMETERS nColIndex
thisform.text2.Value = EVALUATE(this.Columns(m.nColIndex).ControlSource)
Local lcSQL1
Use In (Select('crsResult1'))
TEXT TO m.lcSQL1 TEXTMERGE noshow		
	select top(100) * from dbo.nasabah where cif = this.value
ENDTEXT

SQLExec(Thisform.nHandle, m.lcSQL1 ,'crsResult1')

when i browse my crsResult1 cursor table, theres i got error that alias name not found(), while i store at double click event. i got error that alias name not found()

 

[Chris Miller]

Take what you learned from thread184-1830511 and add error handling to the SQLExec to see what's wrong:

LPARAMETERS nColIndex
thisform.text2.Value = EVALUATE(this.Columns(m.nColIndex).ControlSource)
Local lcSQL1, lnResult
Use In (Select('crsResult1'))
TEXT TO m.lcSQL1 TEXTMERGE noshow		
	select top(100) * from dbo.nasabah where cif = this.value
ENDTEXT

lnResult = SQLExec(Thisform.nHandle, m.lcSQL1 ,'crsResult1')
If lnResult<0
   Aerror(laError)
   Suspend
Endif

Chriss

 

[bharons]

thanks Chriss its done.. i just forgot the last code..

LPARAMETERS nColIndex
thisform.text2.Value = EVALUATE(this.Columns(m.nColIndex).ControlSource)

[highlight]Local lcSQL1, lcValue, lnResult
lcValue = EVALUATE(this.Columns(m.nColIndex).ControlSource)
[/highlight]
Use In (Select('crsResult1'))
TEXT TO m.lcSQL1 TEXTMERGE noshow		
	select top(100) * from dbo.nasabah where cif = [highlight #FCE94F]?m.lcValue[/highlight]
ENDTEXT

lnResult = SQLExec(Thisform.nHandle, m.lcSQL1 ,'crsResult1')
If lnResult<0
   Aerror(laError)
   Suspend
Endif

are is right that i store at afterRowcolChange event not in Double click event.

 

[Chris Miller]

thisform.text2.Value = EVALUATE([highlight #FCE94F]this.Columns([highlight #8AE234]m.[/highlight]nColIndex).ControlSource[/highlight])

It seems you have picked up what I said about FIELDS(nColIndex). Unfortunately using nColIndex as the index of the Columns(m.nColIndex) is not making it generally correct, too. Only when you disallo moving columns. By default every grid column has a property Movable set .T. and a user can move a column by dragging the header.

The AfterRowColChange() parameter nColIndex will be the column number in visible order, though, so when you switch column1 and 2 by either moving column1 to the right or column2 to the left, you get nColIndex=1 for column2 and nColIndex=2 for column1. Try it in MarKs and my samples. That's a crux in all this. The simple solution ios to set all movable properties to .f., so such column swaps can't happen, not even by a slip of the mouse.

I wonder where you got m. from, nobody here explained it. It's not wrong, of course.

Chriss

 

[bharons]

thanks.. Chriss for suggestion.. Regarding m identification or the letter m in front of lcValue, I just copied the writing from a question in the SQL Server Development forum. i mean infront ncolindex .., also i cant store anchor property at my grid, in Mark or Chriss Grid are created by define class, my grid are created with i store grid from control panel vfp.

 

[Chris Miller]

Anchor is the wrong property, it's the Movable property of grid Columns. You can always set this at runtime, and as you do population and repopuplate the grid with every setting of RecordSource, you will also need to do this at runtime for every column, if you want to prevent the columns to be moved.

Chriss

 

[Chris Miller]

the letter m in front of lcValue, I just copied the writing from a question in the SQL Server Development forum. i mean infront ncolindex ..smile2

And did you look at what m. means?

Chriss

 

[mjcmkrsr]

Hi,

You may want to check the help file for both properties (ANCHOR and MOVABLE) and what they are good for.

hth

MarK

 

[bharons]

i don't know about (m.) is, i just copy it., i think (m.) is kind like symbol memovariable.

by the way can i call statement to open sql server database with script in vfp like below.

Local KONEKSI, lnResult 
STORE SQLCONNECT('murya1','murya2','murya3_database') TO KONEKSI 
lnResult = SQLExec(KONEKSI, "select * from dbo.?m.lcValue where ?m.lcValue1 = ?m.lcValue2" ,"crsResult1") 
If lnResult < 0
    	Aerror(laError)
    	Suspend
    	sqldisconnect(KONEKSI) 
else
 	SQLExec(KONEKSI, "select * from dbo.?m.lcValue where ?m.lcValue1 = ?m.lcValue2" ,"crsResult1")
 	select crsResult1
 	browse
 	sqldisconnect(KONEKSI) 
Endif

and from laError i got code 1526 ---> "Connectivity Error: [Microsoft][ODBC SQL Server Drive][SQL Server]incorrect syntact near '@P1'."

and variable lcValue detemine from combobox vfp where lcValue is table name, lcValue1 and lcValue2 determine from grid afterrowcollchange, where lcValue1 is field name and lcValue2 is value from field name, may be this question sound so funny and it's ridiculous to ask, well I just asking..

 

[Chris Miller]

SQL Server won't allow a table or field name to be a parameter of a query. parameters in queries are strictly values, not names.

Chriss

 

[Chris Miller]

There's no reasaon to give up, though, you don't need to pass over the names of table and field as parameters.

Chriss

 

[bharons]

oh okay thanks Chriss. for advise.