variable type mismatch - empty recordset

[Timothy Rice]

I need to display the result of a SQL query on a web page - see the following code:

-------- START -----------------------------------
Dim cs as C
Dim cn as ole::adodb.connection
Dim rs as ole::adodb.recordset
Dim nOrderTotal as n = 0

cs = "Provider=..."
sqlText = "SELECT SUM(LineValue) As OrderTotal FROM ..."
rs.open(sqlText,cn)
nOrderTotal = rs.fields.item("OrderTotal")
rs.close()
cn.close()
-------- END -------------------------------------

It works fine as long as there is a Row in the Recordset, but if the recordset is empty (which is perfectly possible), then I get "variable type mismatch" error for the line "nOrderTotal = rs.fields.item("OrderTotal")".

I tried to Dim nOrderTotal as A - but that didn't make any difference. I've also tried to cover an empty recordset eventuality by checking first for "IF rs.RecordCount = 0 then ..." , but no joy there either (and I then set rs.CursorType = 3 - 'Static').

Thanks.

[Edward Larrabee]

RE: variable type mismatch - empty recordset

This one is outside of my area of expertise. However...

try testing to see if the open() method returns a True value. You can also try using eval_valid() to test if the rs.fields.item() variable exists.

[Selwyn Rabins]

RE: variable type mismatch - empty recordset

you need to test that you don't have an empty record set.


if rs.eof = .f. then
nOrderTotal = rs.fields.item("OrderTotal")
else
nOrderTotal = 0
end if

[Timothy Rice]

RE: variable type mismatch - empty recordset

I couldn't get anything out of open().

For EVAL_VALID():

eval_valid("rs.fields.item("OrderTotal")") always returns .F., regardless of whether the rs. has records or is empty. (I suspect that the double-quotes around OrderTotal are not welcome), and...

eval_valid("rs.fields.item('OrderTotal')") (note single quotes around OrderTotal) always returns .T., regardless of the presence of any records in the rs.

[Timothy Rice]

RE: variable type mismatch - empty recordset

When the "variable type mismatch" problem first arose (appearing to indicate that I needed to provide a get-out clause in case there were no records), rs.eof was my natural reaction. I've just tried it again, and it returns .f., with or without any records in the rs.

[Timothy Rice]

RE: variable type mismatch - empty recordset

Thinking further: what if the SQL statement IS returning a record, but because SUM() comes to nothing (because no records meet the WHERE criteria), the value in OrderValue is NULL, as opposed to zero?

If this is the case, a Null value can't be stored to a numeric Variable (hence "variable type mismatch"); so is there a function in A5 to test for a Null value and, if necessary, convert it to a zero? (For what it's worth, database is MS SQL Server.)

[Timothy Rice]

RE: variable type mismatch - empty recordset

GOT IT!

As the SQL statement is looking at SQL Server, we can use SQL Server ISNULL function to convert a blank field to zero, as follows:

sqlText = "SELECT ISNULL(SUM(LineValue),0) As OrderTotal FROM ...."