How to test for an empty SQL result in ASP

问题:

I am running a query from ASP using a MySQL database, I want to create a variable (ssResult) based on the result with a person's name (fullname), if the record does not exist I want to assign the text 'N/A' to the variable, code below, I currently use a function getOther for my database connections which passes the column name "fullname":

ssResult = getOtherElse("SELECT fullname FROM table WHERE id=" & inArr(j), "fullname")

Below is the code for the function getOtherElse which only works when a result is returned but not when there is an empty result:

Function getOtherElse(inSQL, getColumn)
    Dim conn, rstemp
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open myDSN
    Set Session("lp_conn") = conn
    Set rstemp = Server.CreateObject("ADODB.Recordset")
    rstemp.Open inSQL, conn 
    if not rstemp.eof then
        rstemp.movefirst
        getOtherElse=rstemp.fields(getColumn)
    else
        getOtherElse="N/A"
    end if
    rstemp.close
    set rstemp=nothing
    conn.close
    set conn=nothing
End Function

Thanks!

回答1:

You could try changing the line

if not rstemp.eof then

with

if rstemp.RecordCount > 0 then

回答2:

Replace this block of code:

if not rstemp.eof then
    rstemp.movefirst
    getOtherElse=rstemp.fields(getColumn)
else
    getOtherElse="N/A"
end if

with this block of code:

Dim output
output = "N/A"

If Not rstemp.eof Then
    rstemp.movefirst
    value = rstemp.fields(getColumn)

    If trim(value) = "" Then
        value = "N/A"
    End If
End If

getOtherElse = value

The above code always assumes nothing is being returned, until the connection actually sets it as being returned. And then the the value is checked for an empty string and sets the value also as "N/A"

回答3:

Why not change the SQL to only pull out a result with a First Name, that way the "N/A" would apply:

sResult = getOtherElse("SELECT fullname FROM table WHERE id=" & inArr(j), "fullname AND fullname<>''")