Share some cool features

[RodM]

I am working on my first Alpha Five project and wanted to share some cool features I found. I am creating a grid for references and I need a certain amount of each type; Professional, Personal and Educational. I wanted to be able to do this on the front-end instead of telling the user that they had done something wrong through validation. Here is how I solved the problem.

In the OnInitialValueCalculate event I put the following code:

dim newsql as getsql
dim result as SQL::resultset
newsql.connect = "SIS"
newsql.args.ADD("userid",2)
newsql.sqlquery = "SELECT  Sum( if( type = 'Professional' , 1, 0) )  AS professional,  Sum( if( type = 'Educational' , 1, 0) )  AS educational,  Sum( if( type = 'Personal' , 1, 0) )  AS personal 
   FROM reference 
   WHERE user_iduser = :userid"
result = newsql.querySQL()
SELECT
   CASE result.data("professional") < 1
	e.newValues.type = "Professional"
   CASE result.data("educational") < 1
	e.newValues.type = "Educational"
   CASE result.data("personal") < 1
	e.newValues.type = "Personal"
end SELECT

The code will fill out the type field with each of the selections in a disabled field for the user. BTW, getsql is a class I wrote to handle all of the SQL connection and retrieval.

Since I wanted to control how many records were generated, I put the following code in the OnGridExecute event:

dim newsql as getsql local
dim result as SQL::resultset local
newsql.connect = "SIS"
newsql.args.Add("userid",2)
newsql.sqlquery = "SELECT COUNT(*) AS references 
   FROM reference 
   WHERE user_iduser = :userid" 
result = newsql.QuerySQL()
if result.data("references") = 3
	e.tmpl.DetailView.edit.allow_insert = .f.
end if

This will not allow inserts once the user has three records created. The user can delete a record and the system will know what record was deleted and will be allow the user to insert a new record with the correct type.

Rod

[MichaelCarroll]

Re: Share some cool features

Rod,

I believe you solved my query from my last post. However I have put my SQL string in quotes like you have and it will not compile. Can you share how you built your class for getsql?

Thanks,

Michael Carroll

[RodM]

Re: Share some cool features

Michael,

Here is the code for getsql. I hope to soon create an error class that would run inside of it. Remember, it needs to get built in the new Class editor.

define class global GetSQL
	'DIM a connection variable
	dim connect as c
	dim sqlquery as c
	'args are entered as args.add("state", "MA")
	dim args as SQL::Arguments 
	function ConSQL as sql::connection()
		dim cn as SQL::Connection
		dim flagResult as l
		flagResult = cn.open("::Name::"+self.connect)
		if flagResult = .f. then 
			ui_HTML_msg_box("Error","Error - Could not connect to database. Error reported was:  <br>" + cn.CallResult.text)
			end 
		'else
			'ui_msg_box("Success","Connected Successfully "+cn.CallResult.text)
		end if
		ConSQL = cn
	end function	
	function QuerySQL as sql::ResultSet()
		dim cn as SQL::Connection
		cn = ConSQL()
		dim flagResult as l
		flagResult = cn.open("::Name::"+self.connect)
		if flagResult = .f. then 
			ui_HTML_msg_box("Error","Error - Could not connect to database. Error reported was:  <br>" + cn.CallResult.text)
			end 
		'else
			'ui_HTML_msg_box("Success","Connected Successfully "+cn.CallResult.text)
		end if
	'Specify if you are using Portable SQL syntax, or not
	cn.PortableSQLEnabled = .t. 
	'Execute the Query
	flagResult = cn.Execute(self.sqlquery,self.args)
	if flagResult = .f. then 
		ui_HTML_msg_box("Error","Error - "+cn.CallResult.text)
		cn.close()
		end 
	end if 

	QuerySQL = cn.ResultSet
	end function
end class

Rod

[MichaelCarroll]

Re: Share some cool features

SWEET!

[RodM]

Re: Share some cool features

Just to keep myself honest, I found some code errors in the code in the OnInitialValueCalculate event. I did not account for a resultset that was empty.

'add one reference type per record
dim newsql as GetSQL
dim result as SQL::resultset
newsql.connect = "SIS"
newsql.args.ADD("userid",2)
'check if there are any records in the table
newsql.sqlquery = "SELECT COUNT(*) as chkcount FROM reference where user_iduser = :userid"
result = newsql.querySQL()
if result.data("chkcount") > 0
	newsql.sqlquery = "SELECT  Sum( if( type = 'Professional' , 1, 0) )  AS professional,  Sum( if( type = 'Educational' , 1, 0) )  AS educational,  Sum( if( type = 'Personal' , 1, 0) )  AS personal FROM reference WHERE user_iduser = :userid"
	result = newsql.querySQL()
	SELECT
		CASE result.data("professional") < 1
			e.newValues.type = "Professional"
		CASE result.data("educational") < 1
			e.newValues.type = "Educational"
		CASE result.data("personal") < 1
			e.newValues.type = "Personal"
	end SELECT
else
	e.newValues.type = "Professional"
end if