Why the filed value is set to ""?

[bostjank]

Hi!

I have an interesting problem which I sometimes come by when I'm retrieving values from a database. I try, for example, to show the value in the filed named "Name"


set rs = conn.Execute(sSQL)

If rs("Name") <> "" Then
Response.Write "Name: " & rs("Name")
Else
Response.Write "Name: no data"
End If

It sometimes happens that the value in the "Name" field is <> then "", but the value is not shown, as if the value of rs("Name") would be set to "" once I checked its value. I can avoid this problem so that I first asign the value to a variable and then operate with that:


set rs = conn.Execute(sSQL)

sName = rs("Name")
If sName <> "" Then
Response.Write "Name: " & sName
Else
Response.Write "Name: no data"
End If

As I said, I can avoid the problem, but I wonder what's the problem, since this problem doesn't always occur (when it does, it of course applies to whole recordset, not just random records) and the soluton is more time consuming than the "original".

Does anybody know what can I do? This is how I access the database (Access)


<%
sConn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("database.mdb")
Set conn = Server.CreateObject("adodb.connection")
conn.open sConn,"",""

sSQL = "..."
set rs = conn.Execute(sSQL)

Do While Not rs.EOF
...
Loop
%>

Thanks,
Bostjan :thumbsup:

[oracleguy]

It could be with the select statement your using???

Have you tried changing the dbase driver in the connection code? That may clear it up.

It's definetly a strange problem.

[whammy]

If you think you're going to get null values back sometimes, I usually use

"SELECT ISNULL(fieldname,'texthere') FROM tablename"

That will give you a default value back of "texthere" for anything that is Null...

[bostjank]

HI!

The thing is that this problem occurs even though there is a value other than NULL or "" in a specified field.
I know it is a strange problem - and above all it does not always occur. That's why I must sometimes (on some pages) first create a new variable and operate with it, and sometimes I can operate with rs("..").

Bostjan

[whammy]

Are you by chance getting something from a "text" field in your query before the other values mysteriously disappear? ;)

[bostjank]

When I retrieve the value from a certain field for the first time, it's OK, but if I try the same a couple of lines of code after that I get "".
I was trying to find an example in a project am working on, but I couldn't find the page where I noticed the problem (again). I will poste a complete code as soon as I find the page I was talking about.

Bostjan

[oracleguy]

Try adding:

option=16387

to the end of your connection code... cuz i think i read somewhere that it is supposed to correct for that problem. Not sure at all... but it can't hurt to try.