ASP/VBSCRIPT sql MAX return problem

Could we see some code? I'm not sure what you're doing/trying to do.

Regardless, using max on a column in an empty table will yield null. If you'd rather have it return say, 0 as default, you could use isnull(max(column), 0), or the more versatile coalesce (which can take more than one alternative).

Set oRs1 = oConn.Execute("SELECT max(orgtypeorder) FROM orgtypes WHERE orgtypeownerID ="& CurrentOrgID)
If oRs1.EOF Then
%>No Org Types found for this Current OrgID<%
NewOrderValue = 1
Else
%>Max orgtypeorder=<%=oRs1(0)%><%
NewOrderValue = oRs1(0)+1
End If

The newordervalue is just a simple variable. And I have a couple debug satements inside each condition there. I tried the isnull(max(... but it gave me an sql error.

Following that is the instert statement, which fails due to NewOrderValue being null.

AFAIR, using an aggregate function like that on an empty table will always return a result (one row to be exact). Hence it will never reach the code after the EOF condition.

What error do you get when using isnull?

Is there a condition that I can use to hit that first set of code? I've tried stuff like If oRs(0) = NULL Then ... no luck though.

I think you can check against Is Nothing, and a recall something with DbNull as well (it's a long time ago I used ASP), not sure. Nevertheless, if you get the isnull function to work, you're code will potentially be much cleaner:

Set oRs1 = oConn.Execute("SELECT isnull(max(orgtypeorder), 0) FROM orgtypes WHERE orgtypeownerID = " & CurrentOrgID)If Not oRs1.EOF Then ' <- should never happen, really  NewOrderValue = CLng(oRs1(0)) + 1End If

I tried this with the ISNULL (did some googling) Set oRs1 = oConn.Execute("SELECT MAX(ISNULL(orgtypeorder, 0)) FROM orgtypes WHERE orgtypeownerID ="& CurrentOrgID) and no luck. However, i changed the condition from

If oRs.EOF Then... to If Not oRs.EOF Then (same content in the if statement) and it now works! I was sure I tried that and just wanted to double check, and put your addition in if needed. Thanks.

Quote:

Original post by agm_ultimatex SELECT MAX(ISNULL(orgtypeorder, 0)) FROM orgtypes ..

You use the functions in the wrong order.

ISNULL(param1, param2) will return the first argument (param1) is it's not null, otherwise it will return the second argument (param2). Because MAX will give you back a null for a table with no rows, you can use ISNULL(MAX(orgtypeorder), 0) to change that in to a 0. So the syntax should be:

SELECT ISNULL(MAX(orgtypeorder), 0) FROM orgtypes ..