SQL Server Text Column Affects Results Returned to Classic ASP

Question

Using classic asp, I am trying to query a SQL Server database as follows:

strSQL = "select column_1, column_2, column_3, column_4  from someview " &_
         "where RecordNo=" & i
set rs=conn.Execute(strSQL)

if not rs.eof then
    A = rs("column_1")
    B = rs("column_2")
    C = rs("column_3")
    D = rs("column_4")
end if

Column_3 is an NText type, other columns are varchar or int (sometimes there can be more than four columns), but the query returns only 1 record due to the where clause.

On the ASP page, the results change - sometimes A, B, D are filled, sometimes not - but all the columns in the view contain data (when I query SQL Server, I see the expected results - all the columns contain data) If I delete column_3, which is NText from strSQL, everything works fine.

I have seen this behavior on several other pages in the past. If I modify ASP to get column_3 separately:

 strSQL = "select column_3 from someview where RecordNo=" & i

NText data will return correctly.

SQL Server, ASP? NTEXT , - , ?

EDIT: , - ODBC SQL Server ( = {SQL Server};).

Answer 1

. Microsoft - -.

NText last SELECT, . , NText. , .

, , - . !

Answer 2

, :

1 :

arr = rs.Getrows

if IsArray(arr) then
 A = arr(0)
 B = arr(1)
 C = arr(2)
 D = arr(3)
end if

:

aryList = rec.GetRows

iCount = Ubound(aryList,2)

For i = 0 to iCount
  A = aryList(0,i)
  B = aryList(1,i)
  C = aryList(2,i)
  D = aryList(3,i)
' Do something with A,B,C,D
Next

Answer 3

ntext varchar .

Answer 4

Unicode ( ntext) -unicode (varchar). , ASP , .

( unicode unicode ).

Answer 5

, :

When a record set column value is empty using ADO / ASP and you have one row of data, you can work around this problem by using the parameterized Command statement and returning the row value to a variable:

Some typewritten code, hopefully, explains what I mean:

' DB connection
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.CursorLocation = adUseClient
objCon.Open pubDbConnString

' statement construction
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objCon

cmd.CommandText = "SELECT ?=T.Column1, ?=T.Column2 From Table T WHERE ID=?"
cmd.CommandType = adCmdText
' add parameters
cmd.Parameters.Append cmd.CreateParameter("@column1Data", adVarChar, adParamOutput, 8000)
cmd.Parameters.Append cmd.CreateParameter("@column2Data", adTinyInt, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@id", adBigInt, adParamInput)
cmd.Parameters("@id").value = 1

set objRS = cmd.Execute

@column1Datawill contain a large string. objRS will not actually have any entries in it, so keep that in mind.

In theory, this should also work with named parameters with the same results, but I have not tested this.