ASP SQL Query does'nt return one field?

[ETN]

Hi,

A simple question for the wiz out there.. I have an ASP query that SELECT 17 fields..(from 1 table and 1 view).

I have a column named DESCRIPTION and after I do:
Set rsInfost=sConn.Execute(SQLt)
response.Write("desc: " & rsInfost("description"))

it give me nothing : "".

It's very strange because for the inserting it works and when I look into MS SQL Server 2000 i see the data. but I just cant get it...

Now I make another connection which i only SELECT that colum, i.e.:
SQLtest="SELECT DESCRIPTION from submission where NO=3

and it works... !!!

It's very strange and I don't understand... i have solve my problem by making 2 queries.. but it's ridiculous.. i wonder if someone has an idea out there ?

by the way my column DESCRIPTION is a TEXT(16).
Thanks.

 

[FesterSXS]

can you post the SQL statement please


Tony

http://www.SpiraxSarco.com

 

[Veep]

This should fix you up:

http://www.aspfaq.com/show.asp?id=2188

 

[ETN]

Veep: thanx for the link, I've looked but didnt see nothing particular that could be it. Anyway if it was that the value of my field description would not return anything in a single query (with one field selected only)..

Here is my code:
SQLInfosUser="SELECT DEMAND.USER,CONTACTS.LNAME,CONTACTS.FNAME,DEMAND.PHONE,DEMAND.EMAIL,DEMAND.RANK,DEMAND.DIVISION,DEMAND.PLACE, "

SQLInfosUser=SQLInfosUser & "DEMAND.NO_ROOM, DEMAND.EXT, DEMAND.date_demand,DEMAND.date_requested, DEMAND.DESCRIPTION, DEMAND.NO, "

SQLInfosUser=SQLInfosUser & "DEMAND.TITLE, DEMAND.DIV_EXEC, DEMAND.COD_PRIOR_REQ,DEMAND.OBJECT "

SQLInfosUser=SQLInfosUser & "FROM DEMAND INNER JOIN CONTACTS ON DEMAND.USER = CONTACTS.LOGIN WHERE DEMAND.NO=" & request.Form("histo")

Set rsInfosUser=sConn.Execute(SQLInfosUser)

>> then: response.write(rsInfosUser("description")
>> return ""

while response.write(rsInfosUser("object") which is a text(16) too, return the text field OK.

like i've said the inserting works fine.. but not the select.. and when I do:

SQLt="SELECT DESCRIPTION from demand where NO=" & request.Form("histo")

Set rsInfost=sConn.Execute(SQLt)
response.Write("desc: " & rsInfost("description"))

>> it works..

So to conclude I don't think it's a database problem neither a code problem... what can it be ? Maybe there is a setting for the maximum of column or memory to put in the buffer ? I really don't know.. Hope you understand my problem...

Thanks again!

 

[bastienk]

if you repsonse.write the sql statement out before execution and look at it...is it well formed? are the quotes [if any] in the correct places?


Bastien

Cat, the other other white meat

 

[Veep]

I agree with bastienk. Response.write your sql. The information in the link is stating that you should request your TEXT datatype (description) column last in your SQL statement. Also you should immediately assign it to a variable and use the variable to display it. It's basically pointing out MDAC version quirks that exist. SELECTING one column and 17 are two different things and that's probably why you are having luck selecting one (unless the join is the culprit which you should check with Query Analyzer). Realistically though you should stick that SQL statment into a stored procedure and just pass the request.Form("histo") parameter to it.

 

[DreXor]

looks like there's a probability that one of these multiple tables has a description field in it as well as one of the others, perhaps you're getting the wrong table's description field.. and re-reading the sample and SQL stmt :

SQLtest="SELECT DESCRIPTION from submission where NO=3
( works )

SQLInfosUser=SQLInfosUser & "DEMAND.NO_ROOM, DEMAND.EXT, DEMAND.date_demand,DEMAND.date_requested, DEMAND.DESCRIPTION, DEMAND.NO, "

you're getting description from a different table

 

[ETN]

Thanks for the help guys.. actually there is no problem with my query, it works ok. and the SQL statement is parsing ok...

I've had another field to my database and guess what ? it stop showing one of my variable ... so i've put this new field in my 2nd query (selecting description,newfield) and bing it worked again... so i don't know like i've said before it's probably a buffer memory setting... my 2 text(16) fields are maybe taking all the memory in the buffer on the connection.. even if directly after executing my query i set the value to variables to liberate the connection (set rsInfosUser=Nothing)...

Maybe also instead of doing:
Set rsInfosUser=sConn.Execute(SQLInfosUser)

I should do an open, adOpenStaticOnly or something like that to take less memory ?