Null value breaking result set

[ssouhrada]

Hello,

I call a stored procedure via classic ASP (1.1). I loop through the results and print out items on a page. Pretty basic concept but what I'm finding is that if a row from the database comes back as null, the next row doesn't display even though it's not null and has data in it. See the code below:

details1Query = "EXECUTE storedprocedure"
set details1RS = conn.Execute(details1Query)
do while not details1RS.eof
	Response.Write("value1: " & details1RS("value1") & "<br>")
	Response.Write("value2: " & details1RS("value2") & "<br>")
                Response.Write("value3: " & details1RS("value3") & "<br>")
	Response.Write("value3: " & details1RS("value4") & "<hr>")
details1RS.MoveNext
loop

value1 has text - it will print out fine
value2 is null - Nothing prints
value3 has text - nothing prints
value4 has text - it will print out fine

If I comment out value2 then value3 prints. Any ideas why this would be occuring? It appears that if a value is null then it breaks the next row underneath it. The stored procedure that it is calling joins multiple tables together. value2 and value3 are from different tables. Any information would be helpful.

Thanks

[DrBunchman]

Hi ssouhrada,

Thats an odd one, have you got any error trapping on your page?

You should be able to fix this by checking for NULLS within your stored procedure. Take a look at the following example:

SELECT
Name,
ISNULL(Address, ' ') AS Address
FROM PersonTable

The ISNULL function will return whatever you like (in this case a space) whenever it finds a null value.

Hope this helps,

Dr B

[ssouhrada]

Hello,

Thanks for responding. That ISNULL feature of stored procedures will come in handy. What I found that solved my issue was actually the order that the results were coming back via the stored procedure.

Stored Procedure: "storedprocedur e"

Select value1, value2, value3, value4 from table


When looping through the results on the page if I were to access value3 first then value1 and value2 were no longer available. This is something that I had to learn the hard way. I don't believe it has anything to do with the value being null or not but just having the earlier results in the table no longer accessible. So what I will need to do is adjust the order of results in the stored procedure to build my page.

Thanks