Handling Null values 1

[solo7]

I have a SQL statement running in a loop which I know will only return 1 text value OR it will return no value as it doesn't exist, a NULL value.
What's the best way to handle this?
Will I need to build a Recordset for this single value return?


solo7

 

[PHV]

I think it would help us to help you if you posted some relevant code, don't you think so ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solo7]

Ok PHV & others here it is :-

'open a recordset to hold the booked rooms
'
set rsRoomsBooked= Server.CreateObject("ADODB.Recordset")

'open recordset to browse through rooms
'
Set rsRooms = Server.CreateObject("ADODB.Recordset")

'Path and connection are the same as before so we dont need to define these again
'----- Concate the SQL string for All rooms
'
StrSQL="SELECT TblRooms.Fld_RoomsIndex, TblRooms.Fld_Room FROM TblRooms;"

'----- Perform the query
'
rsRooms.Open strSQL, conn

'move to the first record
'
rsRooms.movefirst

'Start the loop
'
while rsRooms.eof=false 

response.write("<tr>")

for Intx=0 to 13	'Start the Inner loop
		
DteDate= (FormatDateTime((date()+intx),vbshortdate))'Date incrementing day by 1 (American format)
IntDay=mid(DteDate,4,2)
IntMonth=MonthName(left(DteDate,2), True)
IntYear=right(DteDate,2)
'Leaving a blank space allows the cell to word wrap so join the elements
'with a ASCII space
DteDate = IntDay & "/" &  IntMonth & "/" & IntYear
VarTemp = VarTemp 'brought over from the first ASP page
		
StrSql=""     'reset the string
StrSql="SELECT TblBookings.Fld_BookingsIndex, TblBookings.Fld_RoomsIndex, TblBookings.Fld_PeriodIndex, TblBookings.Fld_Dte FROM TblBookings"
StrSql= StrSql & " WHERE (((TblBookings.Fld_RoomsIndex)=" & rsRooms("Fld_RoomsIndex") & ") AND ((TblBookings.Fld_PeriodIndex)=" & VarTemp & ") AND ((TblBookings.Fld_Dte)=#" & DteDate & "#));"	

rsRoomsBooked.Open strSQL, conn
		
if isnull(rsRoomsBooked("Fld_RoomsIndex"))=true then
Strtemp=""""
else
response.write ("<td>" & rsRoomsBooked("Fld_RoomsIndex") & "</td>")
end if
		
next 	'back to the start of the inner loop
  
response.write ("</tr>")

'move the outside loop on one record
'
rsRooms.movenext

wend		'start the outside loop again

solo7

 

[PHV]

And the problem is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solo7]

I'm getting an error :

ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.

when the query is supposed to run ie, at this line

rsRoomsBooked.Open strSQL, conn

I thought this might be because some of the values returned would be NULL ??

solo7

 

[PHV]

Are you sure that VarTemp is correct at this time ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dilettante]

I just eyeballed this quickly, but isn't that Recordset already open when you try to Open it on the second pass through the inner loop? Seems to me you'll need to Close it first.

Wouldn't it be easier to just Execute your query against the Connection object? No need to Close and re-Close then.

Get rid of the Server.CreateObject() for the inner Recordset up near the top of your source fragment, and in the loop use:

Set rsRoomBooked = conn.Execute(strSql)

This of course presumes conn is a Connection object rather than a String variable.

 

[solo7]

Thanks for your thoughts on this guys.

I've taken dilettante's view and got rid of the Server.CreateObject() which seemed more sensible to me. However I am now getting errors of


ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.


I know the SQL string works, it checks the criteria of 3 fields and returns a single matching result of 4 fields OR the SQL finds no matches and I presume the returned result is NULL??

so if my SQL returns with no matches in field1,field2 and field3 how do I trap for this - I'm pretty sure my code is ok now up to this point;- (famous last words!!)

Set rsRoomsBooked = conn.Execute(strSql)
					
if isnull(rsRoomsBooked)=true then
StrTemp= 0
else
StrTemp = rsRoomsBooked("Fld_BookingsIndex")
end if

solo7

 

[PHV]

And what about this ?
If rsRoomsBooked.BOF Or rsRoomsBooked.EOF Then
StrTemp = 0
Else
StrTemp = rsRoomsBooked("Fld_BookingsIndex")
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solo7]

Thanks PHV,
That seems to have had the desired effect ;>)

solo7