[RESOLVED] Data is empty

[craigreilly]

I am running this select statement against my MSSQL Database and some of the data is empty. When I running it in SQL Query - I see the data just fine. But in my ASP Page - nothing.
In particular - questioname right now. Although, I had problems with each and every one of my fields below questionname.
This code worked 100% fine against my MySQL Database - but not the MSSQL.
Please help -

connstr="DSN=survey;UID=username;PWD=password"
set conn=server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout=60
conn.open=connstr
sql="select id, [question] as questionname, [header] as hr, [row] as rowrow, questionno, keyword, maxanswers from eventsurveyquestion where survey='W' and id<>36 order by questionno, row"

set res=conn.Execute(sql)

do while not res.eof
        headerinfo = cstr(res("hr"))
        questionname = cstr(res("questionname"))
        lettername =  cstr(res("rowrow") )
        questionno = res("questionno")

        response.write questionname & "<BR>"
        res.movenext
loop
res.close
conn.close

[honeybee]

Does it work without the column aliasing?

.

[craigreilly]

nope. straight SQL select didn't work either.
The header column is a "Y" or "N" only. It used to be 1 or 0 - but caused some issued in the same place.
Now, when I moved the question above the header, the data showed up. Something about that header field, maybe the name... i dunno.

do while not res.eof
        questionname = cstr(res("questionname"))
        headerinfo = cstr(res("hr"))
        lettername =  cstr(res("rowrow") )
        questionno = res("questionno")

        response.write questionname & "<BR>"
        res.movenext
loop

[MarkT]

Not sure it makes a difference but I would try the syntax

questionname = res.Fields("questionname").Value

You can also get away without the CStr.

[craigreilly]

it seems the question prefers to be first.
if i out it second, third, fourth - no good.
even trying: res.Fields("questionname").Value did not work.
the header doesn't seem to work if i put it in the third or fourth position.
so, in order for it to work, I have to do
Question (string)
Header (string)
Row (string)
QuestionNumber (integer)

no other order works.

[craigreilly]

just as a thought, I also tried

res.fields(0).value

and if question (Field 1) is at the top, it works; otherwise, fail.

someone with a similar issue, appears to be an MS Field Type problem:
http://www.cjboco.com/blog.cfm/post/...g-empty-string

My ASP field.type on Question is: 203 (adLongVarWChar [ie: ntext]). The other string fields (header, row) are type 200 (adVarChar).

The link above explains what to do to fix it. However, the solution has 1 ntext field. what does one do with multiple ntext fields?

Would love to hear if others are having this issue too with ColdFusion or ASP Pages... I do not think I am having this issue in VB.NET.

[honeybee]

That's the first time I have come across a problem like this. What's the data in the Question field? Is it ordinary, or any special characters or stuff like that?

.

[craigreilly]

Not that I can see... standard Survey type questions:

On a scale of 0%-100%; how would you rate your overall experience with our company?

Seems straightforward.
I will likely change to a character field instead of a text field to fix this up.