SQL Server - How to read values from "text" fields

Asked By Goran Matosevic on 20-Jul-14 10:32 AM
How to read data from fields of type "ntext" ?
When I use ADO from my application, and try to read the value from recordset, I get nothing. Example:

sql="select * from table1"
set objRecrodSet=objConnection.Execute(sql)
myvariable=objRecordSet("myfield")

If "myfield" is of type "ntext" or "text", myvariable is empty. Why??? How to read it??

Try casting as ADO renderable datatype - Asked By Dexter Dotnetsky on 11-Sep-01 03:40 PM

For example,
sql="Select cast(ntextfield as nvarchar)"

See SQL Server books online for more details.

another possibility - Asked By Heaven's Martini on 11-Sep-01 04:01 PM

is taht


you need to explicitly read the columns out in order with your text field being the last field in your select statement.

I can't remember why, but that was somehting I read a while back.

hth.

Typo? - Asked By Robbe Morris on 11-Sep-01 04:53 PM

There is a typo in the set statement.

The forum posts here are nText data types are brought back using a recordset object with the .Open method .v your method of using the .Execute method of the Connection object.

Do you have any special characters in the data?
We also don't always have the nText field as the last column in the recordset.  What version of SQL Server are you using?
are you talking to me? - Asked By Heaven's Martini on 20-Jul-14 10:33 AM
If you are.  It was something I read in SS 7.0.

I don't think it happens all the time, but I did read it has a "best practice" to select it last, and i remember at the time, it eliminated my problems.  

don't know much beyond that.
Hmmm... - Asked By Robbe Morris on 11-Sep-01 05:36 PM
Never had that as a problem with 7.0.  Interesting...

If I remember correctly, Goran deals with international special characters that have caused him problems in the past.
No helpful answers.. :-( - Asked By Goran Matosevic on 20-Jul-14 10:34 AM
Yes I deal with international special characters, but that's not the problem heare. 
I have a table, with a lot of columns (sql 7), and I need to use the "select * from" statement. The other fields that are not of type nText or Text are reading fine, but that one field that is of type nText is returning nothing.
Can please someone tell me the exact solution! I'm using ADO in Visual Basic, like I have writen in initial post.
Hmmm... - Asked By Robbe Morris on 11-Sep-01 08:24 PM
This is tough.  nText is such a common data type for our queries and have never had the problems you are discussing.

The northwind database table "Categories" has an nText data type column (SQL Server 2000 so double check to see if it exists in 7.0).  Try running a test query against that table and see if the nText data shows up.

Include the regional setting on your PC for testing purposes.  Perhaps we can duplicate the problem with your specific regional setting.

Please include your "exact" Visual Basic function and which version of ADO you are using.
Back to my other - Asked By Dexter Dotnetsky on 12-Sep-01 05:12 PM
I don't think you can get away with "select * from" if there are NTEXT datatype fields in the table(s).

Try an explicit select with a cast to type "NVARCHAR" or 
(for ASCII charset) "VARCHAR", naming explictly each field in your SELECT STATEMENT.
And the solution is... - Asked By Goran Matosevic on 20-Jul-14 10:35 AM
I must read ntext field first !
Here how it goes:

sql="select * from table1"
set rs=conn.execute(sql)
do while not rs.eof
  msgbox rs("myntextfield")
  msgbox rs("someotherfield")
  rs.movenext
loop

If I read the someotherfield in first msgbox, and the ntextfield in second I wont get nothing in second! But if I read the ntextfield first, I'm getting both values !
This is strange !!!! And stupid!
My experience - Asked By Mira culix on 23-Oct-01 04:40 PM
don't use select *, use the fieldNames
the Text filds must be the last in the List!!
This is according to microsoft documentation.

===========================================================

Most time you can avoid this problem when defining Fields with varchar(8000) or nvarchar(4000), thats a lot of text.
And you can search this fields!