CODEWITHSUNDEEP
asp-classic
Wade73
Wade73

Reputation: 4509

ADO RecordSet Issue - Missing Data

I am having an very weird issue. Recently, one of my customers moved their website (classic asp) to a new server (2008/IIS7/MSSQL 2005) and now I am getting recordsets where fields have no data. When I profile the SQL Query, I see the query running, and when I rerun it in SSMS it returns data for all the fields. For example the sql query returns:

Col1          Col2
John Smith    Blue
Jane Doe      Red

but when I loop through the dataset I get

Col1          Col2
John Smith    
Jane Doe

No idea how this is happening and I am wondering if anyone else has run into an issue like it? Here is the code:

dim db
Set db = Server.CreateObject("ADODB.Connection")
DSN = "dsn=provider=sqloledb;Data Source=.;User Id=;Password=;Initial Catalog="
db.Open DSN

Set rs = db.Execute(query)

do while not rs.EOF
    'do stuff...

    rs.movenext
loop

rs.Close
db.Close
set rs = nothing
set db = nothing

Thanks for the help.

Wade

Upvotes: 2

Views: 3683

Answers (2)

Wade73
Wade73

Reputation: 4509

The issue was a field that was nvarchar(max), when I changed it to nvarchar(2000) the data showed up. Apparently, not able to find anything to back this up, there was a issue with classic asp and these fields. The workaround, if you need the nvarchar(max), is to place it last in the select statement.

HTH

Wade

Update: Apparently, this is also an issue with text and ntext. Thanks to Keith for mentioning this.

Upvotes: 4

Shadow Wizard
Shadow Wizard

Reputation: 66388

When you have Memo field or nvarchar(max) in your case the value in the recordset is being reset after the first time you read it. Why? Microsoft.

So, such code will fail to show the value in the second Response.Write line:

do while not rs.EOF
    Response.Write(rs("memofield"))
    Response.Write(rs("memofield"))
    rs.movenext
loop

Solution is store the value in local variable then use this variable:

Dim strCurValue
do while not rs.EOF
   strCurValue = rs("memofield")
   Response.Write(strCurValue)
   Response.Write(strCurValue)
   rs.movenext
loop

Upvotes: 3

Related Questions