Is this a grid bug?

Moderator: Rathinagiri

Post Reply
Kana
Posts: 27
Joined: Sun Feb 07, 2010 2:48 pm

Is this a grid bug?

Post by Kana »

In the example below I can not get value for the last cell in the grid
If I insert a new item and when I try the updates the same item, I receive the following message:

1. - 1111
--
--
- 3333

2. - 1111
--
--
- 3333

Why
mainform.sqlgrid.cell (mainform.sqlgrid.value, 3) and mainform.sqlgrid.item (mainform.sqlgrid.value) [3]
do not give a value?

Code: Select all

***********************************
#include 'minigui.ch'

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

#define adUseNone   1
#define adUseServer 2
#define adUseClient 3

static oCreateCon, oRecordSet
procedure main	

  OpenMdb()

	define window mainform;
		at 0,0 width 400 height 400 title 'Demo SQL ADO (Access)';
		Main;
		on release  ( oCreateCon:Close() );
		on init     ( fill_grid(1) );								
		font 'ms sans serif' size 8

    on key control+down of mainform action griditem(1)			
    on key delete       of mainform action griditem(3)			

		define grid sqlgrid
			row 10
			col 10
			width 362
			height 320
			headers {'id','firstname', 'lastname'}		
			widths { 55, 150, 150}
			on change 	mainform.statusbar.item(1) := "Slog: "+ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))	
			allowedit .t.
			inplaceedit {}
			columncontrols	{ ;
					{'TEXTBOX','NUMERIC',repl('9',10)} , ;
					{'TEXTBOX','CHARACTER'}, ;
					{'TEXTBOX','CHARACTER'} ;					
					}
			columnvalid     { ;
					{ || if( Empty ( This.CellValue ), .f. ,  ) } , ;
					{ || if( Empty ( This.CellValue ), .f. ,  ) } , ;
					{ || if( Empty ( This.CellValue ), .f. , griditem(2)) } ;
					} 
		end grid
		define statusbar
			statusitem "Slog: -"	
			date
		end statusbar
	end window
	mainform.center
	activate window mainform
return

procedure fill_grid(n)				
	local i := 1
	mainform.sqlgrid.Deleteallitems
	oRecordSet:Open("Select * from users order by id",oCreateCon,adOpenKeyset,adLockPessimistic)
	if oRecordSet == NIL
	  msgstop('connection error')		
	else
		for i= 1 to oRecordSet:RecordCount()
			mainform.sqlgrid.additem( {oRecordSet:Fields("ID"):Value, oRecordSet:Fields("Firstname"):Value, oRecordSet:Fields( "Lastname" ):Value})
			oRecordSet:MoveNext()
	  next		
		mainform.sqlgrid.value:=n
	end
  oRecordSet:close()
	mainform.sqlgrid.setfocus
return

procedure griditem(n)												
	local Cad := ""							
	do case
	case n == 1 
	  record_fill(n)
	case n == 2
		msginfo("1. -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+chr(13)+;
						"   -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+chr(13)+;
						"   -"+mainform.sqlgrid.item(mainform.sqlgrid.value)[3]+chr(13)+;
						"   -"+This.CellValue)
		record_fill(n)
	case n == 3
		Cad := "DELETE FROM users WHERE id="+str(mainform.sqlgrid.cell(mainform.sqlgrid.value,1))
		if msgyesno('Delete record?'+hb_osnewline()+mainform.sqlgrid.cell(mainform.sqlgrid.value,2),'Yes')
			oRecordSet:Open(Cad,oCreateCon,adOpenKeyset,adLockPessimistic)
			if oRecordSet == NIL
				msgstop('delete error')
			else
				n := mainform.sqlgrid.value
				mainform.sqlgrid.deleteitem( n )
				mainform.sqlgrid.value := iif(n > 1, n-1, 1)
				mainform.statusbar.item(1) := "Slog "+;	
					ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))
			end
		  //oRecordSet:close()
			mainform.sqlgrid.setfocus
		end
	endcase
return

procedure record_fill(n)																							
	local cad := ""
	if n = 1
		Cad := "INSERT INTO users (firstname, lastname) VALUES (space(10), space(10))" 	
	else
		msginfo("2. -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+chr(13)+;
						"   -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+chr(13)+;
						"   -"+mainform.sqlgrid.item(mainform.sqlgrid.value)[3]+chr(13)+;
						"   -"+This.CellValue)
		Cad := "UPDATE users SET firstname='"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+;
																 "', lastname='"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+;	//"', lastname='"+This.CellValue+;
																 "' WHERE id="+str(mainform.sqlgrid.cell(mainform.sqlgrid.value,1))
	end
	oRecordSet:Open(Cad,oCreateCon,adOpenKeyset,adLockPessimistic)
	if oRecordSet == NIL
		msgstop('update error')			
	end
	//oRecordSet:close()
	if n == 1
		fill_grid(mainform.sqlgrid.itemcount+1)
	else
		fill_grid(mainform.sqlgrid.value)
	end
	mainform.statusbar.item(1) := "Slog "+ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))
return		

procedure OpenMdb()
  Local oCreateCatalog 
  if !file('demo.mdb') 
    oCreateCatalog=CreateObject("ADOX.Catalog")
    oCreateCatalog:Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
    oCreateCon:=CreateObject("ADODB.Connection")
    oCreateCon:Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
    oCreateCon:Execute( "CREATE TABLE users ([id] autoincrement, [FirstName] char(10), [LastName] char(10))" )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('James', 'Bond')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lara', 'Kroft')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Super', 'Man')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lat'  , 'šđčćž')"  )
    oRecordSet:=CreateObject("ADODB.Recordset")
  else
  	oCreateCon:=CreateObject("ADODB.Connection")
	  oCreateCon:Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
	  oRecordSet:=CreateObject("ADODB.Recordset")
  end 
return nil
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: Is this a grid bug?

Post by Roberto Lopez »

Kana wrote:In the example below I can not get value for the last cell in the grid
If I insert a new item and when I try the updates the same item, I receive the following message:

1. - 1111
--
--
- 3333

2. - 1111
--
--
- 3333

Why
mainform.sqlgrid.cell (mainform.sqlgrid.value, 3) and mainform.sqlgrid.item (mainform.sqlgrid.value) [3]
do not give a value?

Code: Select all

***********************************
#include 'minigui.ch'

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

#define adUseNone   1
#define adUseServer 2
#define adUseClient 3

static oCreateCon, oRecordSet
procedure main	

  OpenMdb()

	define window mainform;
		at 0,0 width 400 height 400 title 'Demo SQL ADO (Access)';
		Main;
		on release  ( oCreateCon:Close() );
		on init     ( fill_grid(1) );								
		font 'ms sans serif' size 8

    on key control+down of mainform action griditem(1)			
    on key delete       of mainform action griditem(3)			

		define grid sqlgrid
			row 10
			col 10
			width 362
			height 320
			headers {'id','firstname', 'lastname'}		
			widths { 55, 150, 150}
			on change 	mainform.statusbar.item(1) := "Slog: "+ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))	
			allowedit .t.
			inplaceedit {}
			columncontrols	{ ;
					{'TEXTBOX','NUMERIC',repl('9',10)} , ;
					{'TEXTBOX','CHARACTER'}, ;
					{'TEXTBOX','CHARACTER'} ;					
					}
			columnvalid     { ;
					{ || if( Empty ( This.CellValue ), .f. ,  ) } , ;
					{ || if( Empty ( This.CellValue ), .f. ,  ) } , ;
					{ || if( Empty ( This.CellValue ), .f. , griditem(2)) } ;
					} 
		end grid
		define statusbar
			statusitem "Slog: -"	
			date
		end statusbar
	end window
	mainform.center
	activate window mainform
return

procedure fill_grid(n)				
	local i := 1
	mainform.sqlgrid.Deleteallitems
	oRecordSet:Open("Select * from users order by id",oCreateCon,adOpenKeyset,adLockPessimistic)
	if oRecordSet == NIL
	  msgstop('connection error')		
	else
		for i= 1 to oRecordSet:RecordCount()
			mainform.sqlgrid.additem( {oRecordSet:Fields("ID"):Value, oRecordSet:Fields("Firstname"):Value, oRecordSet:Fields( "Lastname" ):Value})
			oRecordSet:MoveNext()
	  next		
		mainform.sqlgrid.value:=n
	end
  oRecordSet:close()
	mainform.sqlgrid.setfocus
return

procedure griditem(n)												
	local Cad := ""							
	do case
	case n == 1 
	  record_fill(n)
	case n == 2
		msginfo("1. -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+chr(13)+;
						"   -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+chr(13)+;
						"   -"+mainform.sqlgrid.item(mainform.sqlgrid.value)[3]+chr(13)+;
						"   -"+This.CellValue)
		record_fill(n)
	case n == 3
		Cad := "DELETE FROM users WHERE id="+str(mainform.sqlgrid.cell(mainform.sqlgrid.value,1))
		if msgyesno('Delete record?'+hb_osnewline()+mainform.sqlgrid.cell(mainform.sqlgrid.value,2),'Yes')
			oRecordSet:Open(Cad,oCreateCon,adOpenKeyset,adLockPessimistic)
			if oRecordSet == NIL
				msgstop('delete error')
			else
				n := mainform.sqlgrid.value
				mainform.sqlgrid.deleteitem( n )
				mainform.sqlgrid.value := iif(n > 1, n-1, 1)
				mainform.statusbar.item(1) := "Slog "+;	
					ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))
			end
		  //oRecordSet:close()
			mainform.sqlgrid.setfocus
		end
	endcase
return

procedure record_fill(n)																							
	local cad := ""
	if n = 1
		Cad := "INSERT INTO users (firstname, lastname) VALUES (space(10), space(10))" 	
	else
		msginfo("2. -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+chr(13)+;
						"   -"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+chr(13)+;
						"   -"+mainform.sqlgrid.item(mainform.sqlgrid.value)[3]+chr(13)+;
						"   -"+This.CellValue)
		Cad := "UPDATE users SET firstname='"+mainform.sqlgrid.cell(mainform.sqlgrid.value,2)+;
																 "', lastname='"+mainform.sqlgrid.cell(mainform.sqlgrid.value,3)+;	//"', lastname='"+This.CellValue+;
																 "' WHERE id="+str(mainform.sqlgrid.cell(mainform.sqlgrid.value,1))
	end
	oRecordSet:Open(Cad,oCreateCon,adOpenKeyset,adLockPessimistic)
	if oRecordSet == NIL
		msgstop('update error')			
	end
	//oRecordSet:close()
	if n == 1
		fill_grid(mainform.sqlgrid.itemcount+1)
	else
		fill_grid(mainform.sqlgrid.value)
	end
	mainform.statusbar.item(1) := "Slog "+ltrim(str(mainform.sqlgrid.value))+" od "+alltrim(str(mainform.sqlgrid.itemcount))
return		

procedure OpenMdb()
  Local oCreateCatalog 
  if !file('demo.mdb') 
    oCreateCatalog=CreateObject("ADOX.Catalog")
    oCreateCatalog:Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
    oCreateCon:=CreateObject("ADODB.Connection")
    oCreateCon:Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
    oCreateCon:Execute( "CREATE TABLE users ([id] autoincrement, [FirstName] char(10), [LastName] char(10))" )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('James', 'Bond')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lara', 'Kroft')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Super', 'Man')"  )
    oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lat'  , 'šđčćž')"  )
    oRecordSet:=CreateObject("ADODB.Recordset")
  else
  	oCreateCon:=CreateObject("ADODB.Connection")
	  oCreateCon:Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
	  oRecordSet:=CreateObject("ADODB.Recordset")
  end 
return nil
Could you be so kind to post the complete including the file(s) needed to test it.

TIA.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
Kana
Posts: 27
Joined: Sun Feb 07, 2010 2:48 pm

Re: Is this a grid bug?

Post by Kana »

Roberto Lopez wrote: Could you be so kind to post the complete including the file(s) needed to test it.
TIA.

Hi, Roberto

This is all code, you need it with a text editor save the file for example demoado.prg and translate the call .. \ .. \ build.bat%

Regards,
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: Is this a grid bug?

Post by Roberto Lopez »

Kana wrote:
Roberto Lopez wrote: Could you be so kind to post the complete including the file(s) needed to test it.
TIA.

Hi, Roberto

This is all code, you need it with a text editor save the file for example demoado.prg and translate the call .. \ .. \ build.bat%

Regards,
And the data file that you use to fill the grid?
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
Kana
Posts: 27
Joined: Sun Feb 07, 2010 2:48 pm

Re: Is this a grid bug?

Post by Kana »

Roberto Lopez wrote:
Kana wrote:
Roberto Lopez wrote: Could you be so kind to post the complete including the file(s) needed to test it.
TIA.

Hi, Roberto

This is all code, you need it with a text editor save the file for example demoado.prg and translate the call .. \ .. \ build.bat%

Regards,
And the data file that you use to fill the grid?

Code: Select all

if !file('demo.mdb') 
oCreateCatalog=CreateObject("ADOX.Catalog")
oCreateCatalog:Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
oCreateCon:=CreateObject("ADODB.Connection")
oCreateCon:Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb")
oCreateCon:Execute( "CREATE TABLE users ([id] autoincrement, [FirstName] char(10), [LastName] char(10))" )
oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('James', 'Bond')" )
oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lara', 'Kroft')" )
oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Super', 'Man')" )
oCreateCon:Execute( "INSERT INTO users (FirstName, LastName) VALUES ('Lat' , 'šđčćž')" )
oRecordSet:=CreateObject("ADODB.Recordset")
else
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: Is this a grid bug?

Post by Roberto Lopez »

Kana wrote:
if !file('demo.mdb')
oCreateCatalog=CreateObject("ADOX.Catalog")
<...>
OOPS! :)

Sorry, I've not seen that.

I'll take a look at it ASAP.

Thanks for the report.

PS: For future reports, please, try to create a smaller, simpler sample showing the problem.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: Is this a grid bug?

Post by Roberto Lopez »

Kana wrote:In the example below I can not get value for the last cell in the grid
If I insert a new item and when I try the updates the same item, I receive the following message:
Since to find what is exactly wrong with your code, could take to me a lot of hours, I've decided to do a small modification to test 'cel' property (the demo is attached).

As you'll see (using the new menu option) you can retrieve cell property for any row and col and it is working perfectly.

If you are not able to solve the problem and still thinking that it is a 'cell' property problem, please, create a simple, sample showing the cell property malfunction and post it here.

TIA.
Attachments
GRID.RAR
(580.8 KiB) Downloaded 388 times
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
Kana
Posts: 27
Joined: Sun Feb 07, 2010 2:48 pm

Re: Is this a grid bug?

Post by Kana »

I removed my procedure clausule valid and entered your code from the menu
File-> 'Get Cell'

I'm interested in the following:

After the start the demo, when I press:

1. CTRL + down
2. ENTER x 2
3. 1111
4. ENTER
5. 3333
6. Enter Row 5
7. Enter Col. 3

Msg info that is " ' not '3333"

I hope that I managed to explain what interested me.

Why can not I read the value of LAST cells in the valid clausule except This.CellValue?
Attachments
Is_it_bug.ZIP
(698.27 KiB) Downloaded 330 times
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: Is this a grid bug?

Post by Roberto Lopez »

Kana wrote:I removed my procedure clausule valid and entered your code from the menu
File-> 'Get Cell'

I'm interested in the following:

After the start the demo, when I press:

1. CTRL + down
2. ENTER x 2
3. 1111
4. ENTER
5. 3333
6. Enter Row 5
7. Enter Col. 3

Msg info that is " ' not '3333"

I hope that I managed to explain what interested me.

Why can not I read the value of LAST cells in the valid clausule except This.CellValue?
Because, you are asking about cell content inside the valid procedure, so, you are still editing, so, the new value was not accepted yet, so, this new value is not available for 'cell' property yet, so, this new value is available in 'This.CellValue' only, so, this is not a bug :)

When you finish the 'valid' procedure returning .t., the new value will be accepted and available for the 'cell' property.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
Kana
Posts: 27
Joined: Sun Feb 07, 2010 2:48 pm

Re: Is this a grid bug?

Post by Kana »

Roberto Lopez wrote: Because, you are asking about cell content inside the valid procedure, so, you are still editing, so, the new value was not accepted yet, so, this new value is not available for 'cell' property yet, so, this new value is available in This.CellValue property only, so, this is not a bug :)

When you finish the 'valid' procedure returning .t., the new value will be accepted and available for the 'cell' property.

Thanks Roberto,


I have to change the logic and code in some places as:

"UPDATE users SET firstname = '" + mainform.sqlgrid.cell (mainform.sqlgrid.value, 2) +;
" ', Lastname ='" + mainform.sqlgrid.cell (mainform.sqlgrid.value, 3) +; //"', lastname = ' "+ + This.CellValue;
" 'WHERE id =" + str (mainform.sqlgrid.cell (mainform.sqlgrid.value, 1))

Regards,
Post Reply