Handle TEXT FIELDS in Recordset Using ASP

hi there,

I have a problem when i tried to get data from mssql server using recordset in asp language. For example, i have table containing 5 fields. One of them is text field and the rest are varchar.

here i list the table structure (table name: Info):

Field Name Field Type Length

---

Name varchar 50

Gender varchar 50

Address text 16

Status varchar 50

Age varchar 50

---

Sample data inside table info.

Name Gender Address Status Age

---

Ali male MAL S 26

this is my code:

set rs = server.createobject ("adodb.recordset")

rs.open "select * from info", connectionstring

if not rs.eof then

name = rs("name")

gender = rs("gender")

address = rs("address")

status = rs("status")

age = rs("age")

end if

rs.close

what happend here, i managed to get name = Ali, gender = male but for address, status and age is nothing, just empty string.What was really happened, does anyone knows this problem.

If I change

rs.open "select * from info", connectionstring

to

rs.open "select * from info", connectionstring, 3

i managed to get all the info but i have to do a lot of code change for my program. Is there any solution to this problem?

i'm using win server 2003 and mssql server 2000

thanks for cooperation from you guys.

please accept my apologize for my bad language.

regards

kharulli

Firts you need to move all your BLOB-type fields to the end of the list of your fields in a selection list of the SELECT statement. It means your SELECT should look like

SELECT [Name], Gender , Status, Age, Address FROM ....

Now, after you query data, you need to get GetChunk method of the field to read the data in a case if it is longer than 255 characters. Otherwise you could use Value property

|||

thanks VMazur for quick replies,

I tried and its works. But it seems i must do a lot code conversion to overcome this problem. Its this MSSQL weaknesses or blob-type just work like this? Does MSSQL have a solution to this problem. Sorry to cause trouble to your guys in answering my post.

best regards

kharulli.

|||I believe this is how provider handles BLOB fields. They have to be at the end of your selection. Reason for this is, probably, memory allocation|||thanks for the info. it helped me a lot.