Data type mismatch

[Niki_S]

I have a checkbox in my grid. When I'm going to select using the checkbox it says,

Error in check2 - Value: Data type mismatch​

I set currentcontrol = check2 and sparse = .T. in my column.

How can I fix this issue?

Thank you

 

[mjcmkrsr]

Hi Niki S.

Checkboxes accept .T./.F./.NULL. or 0/1/2 as values (see the help file). Please check the Vartype() of the underlying field - it must be "L" or "N"

hth

MarK

 

[Niki_S]

This is how I get data to the grid. I have a combo box and when I select a factory I want to get selected data to the grid. For that I did something like this in the click event.

_Fact = thisform.cboFactory.value

stra = "SELECT cDesignation as Designation FROM HRIS.dbo.vHRIS_Deletion WHERE cFact=?_Fact AND cDesignation <> 'NULL' GROUP BY cDesignation ORDER BY cDesignation "
SQLEXEC(hndOps,stra,'_Des')

THISFORM.grdDes.RECORDSOURCE = "_Des"
​

After this I created a grid with two columns as 'Destination' and 'Select'. From the above code I can get the data to my grid. After that I add a checkbox into my column2. And I did like this,

CurrentControl = Check1
Sparse = .F.
​

And how to check vartype of underlying field?

Thank you.

 

[Chris Miller]

You don't have a second field for a second column, you can't do that, you have to extend the SLExec result cursor to include a logical field to be able to bind an additional checkbox column to it, otherwise you have no data to bind to. Extending the grid with a column doesn't extend your cursor and with any column you have more than the recordsource cursor you will have datatype problems as there is no controlsource at all for such additional columns.

You can create a field in your SQL:

stra = "SELECT cDesignation as Designation, CAST(0 as bit) as Select FROM HRIS.dbo.vHRIS_Deletion WHERE cFact=?_Fact AND cDesignation <> 'NULL' GROUP BY cDesignation ORDER BY cDesignation "

CAST(0 as bit) as Select

The CAST part is creating a value as a specific data type, for MSSQL the equivalent to FoxPros Logical field is a bit field. Bit field values are 0 or 1, and come over to VFP as .F. or .T. (false or true).

Now the _DEs cursor will have a field called Select that should have all false (.F.) values at the start, so all unchecked checkboxes.

After the user picked one or more, you find all selected rows by Scan For Select, for example.

Chriss

 

[Niki_S]

Thank you Chriss, I did as you said. But still the error message is coming. And also I can't tick the checkbox.
What should I do for the next?

 

[Chris Miller]

What do you set for the check1.controlsource? That should be the new field "Select".

Chriss

 

[Chris Miller]

By the way, if this is your effort to create a multiselect list, the easiest way to do it is still to use a listbox, which has a multiselect property. Neither a combobox nor a grid are easy to extend for multiple selections.

Chriss

 

[mjcmkrsr]

Hi Niki S.

Please have a look a the code below. It's a sketch of how you can add a checkbox to a grid and multi-select data from a cursor/table into a second grid. You just click the desired checkbox(es), run the SQL which on top generates additional fields. Enjoy

PUBLIC oForm

oForm = NEWOBJECT("frmForm")
oForm.Show

Read Events

Close all
Clear All

RETURN

**********

DEFINE CLASS chkBox AS CheckBox
	Visible = .T.
	Caption = ""
	
	PROCEDURE Click()
		This.Parent.Parent.Refresh()

	ENDPROC 
ENDDEFINE 

**********

DEFINE CLASS frmForm AS Form
	AutoCenter = .T.
	Caption = "Grid - multi selecting from cursor"
	Height = 480
	Width = 600
	MinHeight = This.Height
	MinWidth = This.Width
	Themes = .F.
	
	ADD OBJECT lblOData AS Label WITH ;
		Caption = "Original Data", ;
		Left = 12, ;
		Top = 42, ;
		Anchor = 3
	 
	ADD OBJECT lblSelection AS Label WITH ;
		Caption = "Results", ;
		Left = 300, ;
		Top = 42, ;
		Anchor = 3

	ADD OBJECT grdGrid AS Grid WITH ;
		ColumnCount = -1, ;
		Left = 12, ;
		Top = 60, ;
		Width = 270, ;
		Height = 396, ;
		Anchor = 1 + 2 + 4, ;
		RecordSource = "curTemp"
	
		PROCEDURE grdGrid.Init() 
			WITH this 
				.SetAll("DynamicBackcolor", "IIF(curtemp.f3, RGB(0,125,125), RGB(255,255,255))","Column")
				.SetAll("DynamicForecolor", "IIF(curtemp.f3, RGB(255,255,255), RGB(0,0,0))","Column")
				.Column1.ReadOnly = .T.
				.Column2.ReadOnly = .T.
		
			ENDWITH  

			WITH This.Column3
				.Sparse = .F.
				.AddObject("chkBoxSelection", "chkBox")
				.CurrentControl = "chkBoxSelection"

			ENDWITH 	
		ENDPROC 

	ADD OBJECT grdResults AS Grid WITH ;
		ColumnCount = -1, ;
		Left = 300, ;
		Top = 60, ;
		Width = 270, ;
		Height = 396, ;
		Anchor = 1 + 2 + 4 + 8, ;
		Visible = .F.

	ADD OBJECT cmdSearch AS CommandButton WITH ;
		Left = 12, ;
		Top = 12, ;
		Height = 24, ;
		Width = 90, ;
		Caption = "SQL"
	
		PROCEDURE cmdSearch.Click()
			
			SELECT f1, f2, ALLTRIM(SUBSTR(f2,4)) + ALLTRIM(STR(f1)) as f6, INT(f1/4) as f5, f3 FROM curTemp WHERE f3 ;
				ORDER BY 1 ;
				INTO CURSOR curTemp2
				
			IF _tally > 0

				WITH Thisform.grdResults
					.Visible = .T.
					.ColumnCount = -1
					.RecordSource = "curTemp2"
					.DeleteColumn()

				ENDWITH
			ELSE

				Thisform.grdResults.Visible = .F.

			ENDIF 
			
			Thisform.Refresh()
			
		ENDPROC

	ADD OBJECT cmdReset AS CommandButton WITH ;
		Left = 300, ;
		Top = 12, ;
		Height = 24, ;
		Width = 90, ;
		Caption = "Reset"
		
		PROCEDURE cmdReset.Click()

			IF FILE("curtemp2")
				SELECT curtemp2
				USE
			ENDIF 

			WITH Thisform.grdResults
				.Visible = .F.
			ENDWITH

			SELECT curtemp
			
			UPDATE curTemp SET f3 = .F.
			
			LOCATE 

			Thisform.Refresh()
			
		ENDPROC

	PROCEDURE Destroy
		ThisForm.Release()
		Clear Events

	ENDPROC

	PROCEDURE Load
		CREATE CURSOR curTemp (f1 I AUTOINC NEXTVALUE 1 STEP 1, f2 C(20), f3 L)
		
		FOR i = 1 TO 50
			INSERT INTO curTemp (f2, f3) VALUES ("T" + SYS(2015), .F. )
		
		ENDFOR

		LOCATE 

	ENDPROC
ENDDEFINE
**********

hth

MarK

 

[Niki_S]

The issue is, in the first run I can appear checkboxes in my grid and when I select a factory from the combo box, the checkboxes didn't appear and it appears 'T and F'.
How can I change this 'T and F' into checkboxes?

First look

Second look

How can I fix this issue?

Thank you

 

[Niki_S]

I changed my grid into a listbox. And I did as below,

_Fact = thisform.cboFactory.value

stra = "SELECT cDesignation as Designation FROM HRIS.dbo.vHRIS_Deletion WHERE cFact=?_Fact AND cDesignation <> 'NULL' GROUP BY cDesignation ORDER BY cDesignation "
SQLEXEC(hndOps,stra,'_Des')


thisform.List1.RowSourceType = 2
thisform.List1.RowSource =('_Des')
​

And I set Multiselect = .T. . But I can't select multiple records. Is there have something else to do?

Thank you

 

[mjcmkrsr]

Hi Niki S,

You have to hold down the CTRL key + click the item. Please do also have a look at the help file.

hth

MarK

 

[Niki_S]

Thank you Mark. Now that issue is ok.
Now I want to get the selected record into a cursor. As and example, if I select 'A,B,C,D' from my listbox, I want to get them into a cursor.
For that I did something like this, But I get only the last value 'D' 4 times into my cursor.

_Designation = thisform.List1.Value

stra = "SELECT cDesignation "
stra = stra + "from HRIS.dbo.vHRIS_Deletion where cDesignation = ?_Designation "
SQLEXEC(hndOps,stra,'Detl')

​

How can I get 'A,B,C,D' into my cursor?

Thank you.

 

[mjcmkrsr]

Hi Niki S,

You may want to store the results in an array or a string - CSV file (please also see thread184-1818390) and then your query would look like

if it's a string - CSV file

... WHERE cDesigantion IN cString - CSV file

or

if it's an array

... WHERE ASCAN(gaARRAY, cDesignation) != 0

hth

MarK

 

[Niki_S]

Can you please tell me the way of creating string - CSV file and do the other things?

Thank You

 

[mjcmkrsr]

Hi Niki S

I already gave you plenty of examples including how to save the items in a string and in an array - please examine thread184-1818390

hth

MarK