Setting active row in list control programatically

[coleresources]

Re: Setting active row in list control programatically

David,

I have an SQL 2008 back-end. I am receiving a database API error when executing the query that returns the row number and lot. I think it's the way we are trying to create the row number. I think rownum is used for a mysql database to create a rownumber in the query.

I can create a row number in a query I run against my database by using row_number() OVER(Order by Lotno) as rowno...That will execute successfully directly against the db but if I change my sql statement inside the xbasic function to reflect the change the rs.ToString breaks.

Here is the function

Error: error.png

function lotsWithRowNumber as c (e as p)

debug(1)

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments


flag = cn.open("::Name::SPLive")

if flag then
	args.Set("soid",e.rv._state.__args_C_soid)
	args.Set("sdid",e.rv._state.__args_C_sdid)
	sqlStmt = "select row_number() OVER(Order by LotNo) - 1 as rowno, LotNo as ln from buyer_view where salesofficeid = :soid and subdivisionid = :sdid;"
	flag = cn.Execute(sqlStmt,args)
	if flag then
		rs = cn.ResultSet
		dim firstRow as n = rs.Data("rowno")
		dim dataRows as c = rs.ToString(-1,1,.f.,"|")
		dataRows = "RowNo|LN" + crlf() + dataRows
		lotsWithRowNumber = dataRows
	else
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		lotsWithRowNumber = jscmd
		exit function
	end if  

else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	lotsWithRowNumber = jscmd
	exit function
end if

cn.FreeResult()
cn.close()




end function

[Davidk]

Re: Setting active row in list control programatically

I always think it's funny that some things are easier in SQL Server and some in MySQL. ROW_NUMBER() is quite nice. However, for some reason, as you've found, the ToString method doesn't like it. So, just create the string by hand... so to speak...

dim dataRows as c = ""
while (rs.NextRow())
     dataRows = dataRows + rs.Data("rowno") + "|" + rs.Data("FirstName") + crlf()
end while

You'll need to remove that last crlf().

[coleresources]

Re: Setting active row in list control programatically

David,

I'll give that a shot

How were you able to determine where the breakdown was exactly? Was the error enough for you to understand that it is the row_number() function that is the issue?

Trying to learn and grow a deeper understanding

[Davidk]

Re: Setting active row in list control programatically

I took the row_number() stuff out and ran it... then ToString works.

[coleresources]

Re: Setting active row in list control programatically

Makes sense

Ok so I no longer receive an error and when I watch lotsWithRowNumber in the debugger I see the values but the drop down list of the lots does not get populated. Can you see anything wrong with my function? Do I need to create column headers? Is that a requirement.

Screenshot of values showing in debugger and properties of lookup. Can you see anything I've missed?

debugger.pnglookup_properties.png

function lotsWithRowNumber as c (e as p)


debug(1)

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments


flag = cn.open("::Name::SPLive")

if flag then
	args.Set("soid",e.rv._state.__args_C_soid)
	args.Set("sdid",e.rv._state.__args_C_sdid)
	sqlStmt = "select row_number() OVER(Order by LotNo) - 1 as rowno, LotNo as ln from buyer_view where salesofficeid = :soid and subdivisionid = :sdid;"
	flag = cn.Execute(sqlStmt,args)
	if flag then
		rs = cn.ResultSet
		dim dataRows as c = ""
		while rs.NextRow()
     		dataRows = dataRows + rs.Data("rowno") + "|" + rs.Data("ln")
		end while
		lotsWithRowNumber = dataRows
	else
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		lotsWithRowNumber = jscmd
		exit function
	end if  

else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	lotsWithRowNumber = jscmd
	exit function
end if

cn.FreeResult()
cn.close()


end function

[Davidk]

Re: Setting active row in list control programatically

Yes... same as originally posted... and you're missing the crlf() at the end of each row...

dim dataRows as c = ""
while (rs.NextRow())
    	dataRows = dataRows + rs.Data("rowno") + "|" + rs.Data("FirstName") + crlf()
end while
dataRows = "RowNo|Firstname" + crlf() + dataRows
getCustTableRow = dataRows

[coleresources]

Re: Setting active row in list control programatically

It's working as expected now...That should be easy right? I really struggled with some of this but I do understand it now.

I had to uncheck "Allow data caching" inside of the lookup control before my list of lots that the xbasic function was creating would appear.

Why do you have to perform a refreshCascadingList on the parent lotList to pull the options in? I thought giving focus to that row would create the cascading affect?

Thanks for sticking with me on this David, I really appreciate it.

[Davidk]

Re: Setting active row in list control programatically

Excellent... glad you got it working.

Yes... with the dropdown data caching turned off, the callback happens every time.

Maybe you don't need to do a refreshCascadingLIst. Comment it out and try it. It may be that giving a row focus isn't enough... I was trying a lot of stuff and can't remember.

[coleresources]

Re: Setting active row in list control programatically

It doesnt work without it. Was just curious as setting focus to the row seems like it should work

[Davidk]

Re: Setting active row in list control programatically

There are really subtle differences between programmatically settings values and clicking to set values. Although setting focus to a row affects that List, I guess the process ends there and does not affect child Lists. Since there is a method to refresh cascading lists, then just setting focus to a row should do no more. That gives us the freedom to do what we need to do next. It's perfect.

[coleresources]

Re: Setting active row in list control programatically

I had not looked at it that way but it makes sense. Thanks again for all your help