Variable does not contain a valid result set

[Sparticuz]

I've seen this error many different places, but I've never been able to figure out what the problem is. It's a pretty cryptic error message:

XHTTPRequest Error:

500 Internal Server Error
Script ErrorError:Script:" /jklive/__a5RunGrid.a5w" line:62
x_out = a5_ajax_grid(tmpl)
Variable does not contain a valid result set while getting result set data column 'Notes'.


Alpha Five/11.0 Build/3381-4096 at DMC214 Port 80

I've usually gotten around it by rebuilding the a5wcmp, but in this case, it's not an option.
When I take out the notes field, it just errors on the previous field. In this case, Notes is a label and it errors if it's null or filled in.
Is there anyway to get a 'better' error message!

EDIT: sorry, it shows on row save.
EDIT2: More info, mysql, no update security, so anyone can edit the row.

[aburningflame]

Re: Variable does not contain a valid result set

Your save may be failing.

Variable does not contain a valid result set happens when you do a SELECT and the select fails, but then you try to access the resultset. Since the select failed, the resultset variable is not valid.

Hence, variable does not contain a valid resultset.

Maybe this will help you.
If you dont have any custom sql in this grid, its probably that the SELECT statement for the grid is failing.
Does your grid INNER JOIN by any chance? In that case after saving, the parent table would not have any child records and the inner join would fail.
But usually this is a different error.

Try looking for custom sql selects, try relooking at your grid SQL, and then if you have a inner join change it to a left join, in that order.

[Sparticuz]

Re: Variable does not contain a valid result set

The save actually goes through, as does my xbasic code in AfterUpdateRecord. I've reverted back to an older version of the component and the error went away, then tried making the updates I had made, one at a time, until the error came back.
It came back with this code

sql_lookup(args.rtc.cn,"gen_territories","TerritoryID="+Data.Territory_fk,"TerritoryName")

. I am going to try a few things (different cn object, arguments) and report back.

[aburningflame]

Re: Variable does not contain a valid result set

It is not finding your territory. (Hence resultset invalid) and you are not doing anything to capture the error.
You may want to do the following

dim territoryName as C=convert_type(sql_lookup(args.rtc.cn,"gen_territories","TerritoryID="+Data.Territory_fk,"TerritoryName"),"C")

This will return a blank if no territory with the id Data.Territory_fk is found.

if thats not acceptable you need to do something like:

dim territory as C=convert_type(sql_lookup(args.rtc.cn,"gen_territories","TerritoryID="+Data.Territory_fk,"TerritoryName"),"C")
if territory<>"" then
    'continue processing
else 
     'some error handling here to output an error message 
     'output your error with e.abort and e.errorText, or whatever error mechanism you can use
      e.flagError=.t.
     e.errorText="could not load territory: "+Data.Territory_fk
end if

[aburningflame]

Re: Variable does not contain a valid result set

Your cn object should be fine, sql_lookup also takes a string for cn. So you can do sql_lookup("::name::myconnname", etc etc
Also I would suggest using args if Data.Territory_fk is user-provided.

dim args as SQL::Arguments
args.set("tid",Data.Territory_fk)
sql_lookup("::name::conn","gen_territories","TerritoryID=:tid","TerritoryName",args)

[Sparticuz]

Re: Variable does not contain a valid result set

So it appears to be the args.rtc.cn object. I changed it to be my own sql object and it worked. (I also added in the arguments, but that's just because it's good practice)
It's just annoying that I had one line of code, now i have like 8 because I have to setup and tear down the connection.

dim args as sql::Arguments
args.add("territory",Data.Territory_fk)
dim cn as sql::connection
flag = a5SQLConnectionOpen(cn,"::name::DynamicConnection_jkapp", session)
if .not. flag then
	write2log("Connect to DB",cn.CallResult.text)
end if
Territory_fk_render = sql_lookup(cn,"gen_territories","TerritoryID=:territory","TerritoryName",args)
cn.FreeResult()
cn.Close()
delete cn

[aburningflame]

Re: Variable does not contain a valid result set

you dont need your own object, just use the sql_lookup("::name::DynamicConnection_jkapp" .....) unless it doesnt resolve properly, in which case you can use
sql_lookup(session.__protected__jkapp,"tablename"....)

Also, there is no need for a flag variable when opening your connection, why the extra code?
Use this:

if a5SQLConnectionOpen(cn,"::name::DynamicConnection_jkapp",session) then
       'do stuff
else
      write2log("connect to db",cn.CallResult.text)
end if

Anyways the neatest and most stable solution would be this I believe:

dim args as sql::Arguments
args.add("territory",Data.Territory_fk)
dim territory as C=convert_type(sql_lookup("::name::DynamicConnection_jkapp","gen_territories","TerritoryID=:territory","TerritoryName",args),"C")
if territory<>"" then
     Territory_fk_render = territory
else
     Territory_fk_render="Error occurred loading territory: "+Data.Territory_fk
end if

[Sparticuz]

Re: Variable does not contain a valid result set

I was under the impression that if I used Dynamic Connections, that I couldn't call it directly (sql_lookup("::name::DynamicConnection_jkapp" .....)), and that I had to use an object, otherwise it would just open ::name::DynamicConnection_jkapp instead of looking at the session variable.

I do like the second thing you said about getting rid of flag and just throwing the open into an if/else (does xbasic have try/catch?). I think, however, I will move to get rid of the flag. It would be a major search/replace on my part though.

[aburningflame]

Re: Variable does not contain a valid result set

I was under the impression that if I used Dynamic Connections, that I couldn't call it directly (sql_lookup("::name::DynamicConnection_jkapp" .....)), and that I had to use an object, otherwise it would just open ::name::DynamicConnection_jkapp instead of looking at the session variable. - This may be the case, use session.__protected__jkapp instead. (You can use an a5w_info.a5w page with the code <%a5 ?a5w_info() %> and run the page to see what session variable is actually storing your dynamic connection. In that case it would be safer to use sql_lookup(session.__protected__jkapp,"tablename"... instead.

I do like the second thing you said about getting rid of flag and just throwing the open into an if/else (does xbasic have try/catch?). I think, however, I will move to get rid of the flag. It would be a major search/replace on my part though. - It was just an FYI for cleaner code and better reliability. Your method works, as long as your testing the flag variable in every instance. However, you really dont need to store hte result of conn.open and conn.execute. You can just use it as the condition in an if.

if cn.Open("::name::conn") then
      'continue
      cn.close()
else
      'failure
end if

does xbasic have try/catch?
As far as I know, I dont think so.
For the most part you do not need a try catch, you should use ifs to validate conditions and ensure that exceptions arent thrown.
However, there are some cases when you need wrap something in a try/catch.

The closest equivalent AS FAR AS I KNOW, I believe is something like this:
There may be try/catch, but Im not going to dig through documentation for it.

on error goto errhandler
some code
some more code
end

errhandler:
errormsg=error_text_get(error_code_get())