CODEWITHSUNDEEP
sql-servervbscriptasp-classicadodb
MarceloBarbosa
MarceloBarbosa

Reputation: 915

Force ADODB return EOF if length of returned data equals zero

I have one query that returns a single element.

This record has a empty data (Length = 0, but not null).

I Want to know if is possible force the query return EOF if the data length equals zero.

sql="select Code from AS_Table where NumAtend = 1234 "
set rs = Con1.execute(sql)

if not rs.eof then
    Json("status") = true
else
    Json("status") = false
end if

I Know you can test it after the "IF", but don't want to know how to fix that (Because I already know).

My question is "if" it is possible force an EOF if the len(Code)=0

Upvotes: 1

Views: 699

Answers (3)

Gary Richter
Gary Richter

Reputation: 537

To do this programatically through your application, you could filter your result set.

sql="select Code from AS_Table where NumAtend = 1234 "
set rs = Con1.execute(sql)
rs.filter = "status <> ''"

if not rs.eof then
    Json("status") = true
else
    Json("status") = false
end if

This will give you rs.EOF if the "status" field is empty

Upvotes: 0

user692942
user692942

Reputation: 16682

I added a comment earlier but seen as though everyone has jumped on the answer wagon here goes;

SELECT Code FROM AS_Table WHERE NumAtend = 1234 AND LEN(Code) > 0

By adding the LEN(Code) > 0 will cause only results with Code with a length greater then 0 to be returned.

In each instance you want a ADODB.Recordset to be returned or you will not be able to check the Recordset.EOF property this is because the ADODB.Recordset will have a Recordset.State of adStateClosed and any attempt to access it's properties will return an error (adErrObjectClosed).

 Error No.   | Description
------------------------------------------------------------------
-2146824578  | Operation is not allowed when the object is closed.

What about using RETURN?

The problem with RETURN is you will not get a resultset back so the ADODB.Recordset will be in the state adStateClosed and the same as above will apply.

Useful Links

Upvotes: 2

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38755

You can't force ADO to change its defined behaviour (unless you patch the .dll). Either add an If clause/statement to your code or a WHERE clause ("... WHERE codelength > 0") to your SQL statement.

Upvotes: 0

Related Questions