mssql result set

[arielex]

Hi,

I have a question related to the mssql resultset.
First the code

<%
set conn = server.createobject("ADODB.Connection")
conn.open "sdb", "sa", ""


dim inewCmrTroubleId
inewCmrTroubleId=0

'define iCmrTroubleId
SQLCmrTroubleId = "SELECT max(iCmrTroubleId) from CmrTrouble"
set iCmrTroubleId=conn.execute(SQLCmrTroubleId)


if inewCmrTroubleid < 0 then 'there is no record in the table
inewCmrTroubleId = 0

else
inewCmrTroubleId=iCmrTroubleId(0)+1 'increase highest service call with one
Response.Write("id = "&inewCmrTroubleId & " year = " & iCmrTroubleYear)
end if

%>

thing is, the max sql statements returns always something. E.g the number 4. But i like to check if the returned value is a valid number or just an empty number. the line
if inewCmrTroubleid < 0
doesn't check at all..... :mad: maybe because the value is empty
and no, the function isEmpty doesn't work either

please help

[allida77]

I dont see where you set inewCmrTroubleid to anything except 0.
Anyways whatever variable you use to "=conn.execute()" that will be the one you compare with.

e.g.
'define iCmrTroubleId
SQLCmrTroubleId = "SELECT max(iCmrTroubleId) As iTroubleIDCnt from CmrTrouble"
set iCmrTroubleId=conn.execute(SQLCmrTroubleId)


if iCmrTroubleId("iTroubleCnt ") < 0 then 'there is no record in the table
inewCmrTroubleId = 0

[arielex]

no, that doesn't work

the result of the max querie (an integer i think) is there. Only it's empty. Because it is empty it cannot be compared to zero

also isnull and isempty don't work

aaargh. I can do a separate querie and count the rows. if that result is zero i know the answer. But that's not really pretty coding eh?

[rcreyes]

The function MAX returns NULL not 0 when there is no record in the table, for example:


Select Max(fieldName) from Table Where ID=12

This statement will return a NULL value if the condition is not met.

So, in your case the

Select max(iCmrTroubleId) As iTroubleIDCnt from CmrTrouble

will return NULL if the table is empty. If you want to have the SQL return 0 then use the ISNULL function, for example:

Select IsNull(Max(fieldName),0) from Table Where ID=12

OR in your case

Select IsNull(max(iCmrTroubleId),0) As iTroubleIDCnt from CmrTrouble


Both query will return 0 if the condition is not met or in your case, if the table is empty


Hope this works....

Thanks,
Ray

[whammy]

Or you could use my "NullToZero()" function:

Function NullToZero(byVal str) '''''''''''''''''''
If IsNull(str) Then str = 0
NullToZero = str
End Function '''''''''''''''''''''''''''''''''''''


Once you retrieve the value from the database. However, rcreyes' method is much more efficient (and faster). :)

[rcreyes]

Thanks whammy!

A follow-up to the orginal post -- I noticed that you are using the function IsEmpty(). This function simply check if a variable has been initialized by having initial value assigned to it (other than EMpty). For example consider the following example:

DIm x

Msgbox IsEmpty(x) 'Display TRUE
Msgbox IsEmpty(y) 'Display TRUE


Set x = nothing

Msgbox IsEmpty(x) 'Display FALSE
Msgbox IsEmpty(y) 'Display TRUE

So you can see, using the IsEmpty function against a NULL result will always return FALSE, for example:

Set RS = Conn.Execute("Select max(col) from table1")

Response.Write IsEmpty(RS(0))

Will display false, assuming the TABLE is empty.

[marko1103]

OK, I looked at your initial code...

I hope that you have picked up from subsequent replies that the sql statement must have an "AS" in it:

"select max(whatever) AS whatever"

Just making sure you got that one.

Peace out!

[marko1103]

OK:

"select max(whatever) as whatever from wherever"

can't forget the table name!

[rcreyes]

I am sorry MARCO1103, but I have to disagree with you with the "AS", the keyword AS is optional (in MS-SQL). For example:

Select max(colname) from table1

Select a=max(colname) from table1

Select max(colname) as a from table1

All of these are valid SQL statement.

Thanks,
Ray

[whammy]

Actually, thank YOU rcreyes, because I couldn't remember the syntax for getting null or an alternate value... sometimes I would like to use it like this:

Select IsNull(fieldName,"&nbsp;") from Table Where ID=12

Is that the correct syntax for defining an alternate result? I know many developers who could use this but don't.

[rcreyes]

Yes, assumming you are testing for a NULL, another one is the COALESCE( n1, n2, n3, n4,,,,,nth) function

For example, using MS-SQL:

Select NewValue = COALESCE (
Select value from table1 where ID=2,
Select value from table2 where ID=2,
Select value from table3 where ID = 2)


COALESCE will scan the non-null (or empty) value from left to right, once found it will stop and assign the value, for example
if the first sub select returns NULL, the second returns 5 and the third returns 67

the value of NewValue will be 5 (since this is the first non-null value)


Thanks,
Ray

[whammy]

Cool.. that had nothing to do with what I was asking (since my &nbsp; was replaced by a space in the thread, but it's even more valuable nevertheless. ;)