ASP - ASP recordset retrieval problem (SQL Server)

Author

ASP recordset retrieval problem (SQL Server)

AlanMF

2005-11-14, 6:55 pm

A problem recently cropped up that I have not seen before and I am wondering
anyone has seen this one?

The 2 SQl Select statements on my ASP page differ by the addition of one
column ("Description") but in the 2nd case, column content is dropped
(blank). I can get somewhat different results by adding other columns to the
Select.

---------------------------------------------------
1)
SQLQuery = Select ProdID, ProdTitle, credits, [Image], IsCurrentShow
from tblProd where IsActive = 1 ORDER by IsCurrentShow Desc, ProdTitle

sample row data returned:

ProdID = 18 (ok)
strCredits = Norman Miller, Harold Pinter (ok)
strImage = lil moon image (ok)

----------------------------------------------------
2)
SQLQuery = Select ProdID, ProdTitle, credits, [Image], IsCurrentShow,
Description from tblProd where IsActive = 1 ORDER by IsCurrentShow Desc,
ProdTitle

sample row data returned:

ProdID = 18 (ok)
strDescription = Great Show, what ho? (ok)
strCredits = (dropped - see above)
strImage = (dropped - see above)
-----------------------------------------------

This happens only with ASP web pages, not with SQL Query Mgr. or Enterprise
Mgr.

I assume my system has been corrupted somehow, but what can be done about it?

Thanks.
Alan

Mark Schupp

2005-11-14, 6:55 pm

Try setting "description" into a local variable and referencing it from the
variable instead of from the recordset.

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

--
--Mark Schupp

"AlanMF" <AlanMF@discussions.microsoft.com> wrote in message
news:BD2BF3F9-8CE7-444F-8AE0-FF83C773F5C5@microsoft.com...
>A problem recently cropped up that I have not seen before and I am
>wondering
> anyone has seen this one?
>
> The 2 SQl Select statements on my ASP page differ by the addition of one
> column ("Description") but in the 2nd case, column content is dropped
> (blank). I can get somewhat different results by adding other columns to
> the
> Select.
>
> ---------------------------------------------------
> 1)
> SQLQuery = Select ProdID, ProdTitle, credits, [Image], IsCurrentShow
> from tblProd where IsActive = 1 ORDER by IsCurrentShow Desc, ProdTitle
>
> sample row data returned:
>
> ProdID = 18 (ok)
> strCredits = Norman Miller, Harold Pinter (ok)
> strImage = lil moon image (ok)
>
> ----------------------------------------------------
> 2)
> SQLQuery = Select ProdID, ProdTitle, credits, [Image], IsCurrentShow,
> Description from tblProd where IsActive = 1 ORDER by IsCurrentShow Desc,
> ProdTitle
>
> sample row data returned:
>
> ProdID = 18 (ok)
> strDescription = Great Show, what ho? (ok)
> strCredits = (dropped - see above)
> strImage = (dropped - see above)
> -----------------------------------------------
>
> This happens only with ASP web pages, not with SQL Query Mgr. or
> Enterprise
> Mgr.
>
> I assume my system has been corrupted somehow, but what can be done about
> it?
>
> Thanks.
> Alan
>

AlanMF

2005-11-14, 9:55 pm

Thanks Mark.

I had already assigned the var. strDescription = rs("Description"), etc.
when the problem arose.

Alan

"Mark Schupp" wrote:

> Try setting "description" into a local variable and referencing it from the
> variable instead of from the recordset.
>
> also see: http://www.aspfaq.com/show.asp?id=2188
>
>
> --Mark Schupp
>
>
> "AlanMF" <AlanMF@discussions.microsoft.com> wrote in message
> news:BD2BF3F9-8CE7-444F-8AE0-FF83C773F5C5@microsoft.com...
>
>
>

Bob Barrows [MVP]

2005-11-14, 9:55 pm

Show us:
1) the connection string you are using so we know if you are using ODBC
(bad) or OLE DB (good)
2) the datatypes of the columns involved
3) the code that displays this symptom

AlanMF wrote:[color=darkred]
> Thanks Mark.
>
> I had already assigned the var. strDescription = rs("Description"),
> etc. when the problem arose.
>
> Alan
>
> "Mark Schupp" wrote:
>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

AlanMF

2005-11-14, 9:55 pm

I am using ODBC:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=SC3Sys;UID=sc3user;PWD=sc3user"

The DDL used to create the table:
CREATE TABLE [tblProd] (
[ProdID] [int]
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
[ProdTitle] [nvarchar] (100) NOT NULL ,
[StartDate] [smalldatetime] NOT NULL DEFAULT '1900-01-01' ,
[EndDate] [smalldatetime] NOT NULL DEFAULT '1900-01-01' ,
[FiscalYear] [int] NOT NULL DEFAULT 1900,
[Credits] [ntext] NULL ,
[Image] [ntext] NULL ,
IsActive bit NOT NULL DEFAULT 0,
IsCurrentShow bit NOT NULL DEFAULT 0 ,
[Description] [ntext] NULL ,
[Capacity] [int] NOT NULL DEFAULT 90 ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The relevant ASP code:
SQLQuery = "SELECT [ProdID], [ProdTitle], [StartDate], [EndDate],
[FiscalYear], [Credits], [Image], [IsActive], [IsCurrentShow], [Description],
[Capacity] FROM [SC3].[dbo].[tblProd] where IsActive = 1 ORDER by
IsCurrentShow Desc, ProdTitle"

Set rs = objConn.Execute(SQLQuery)

if not rs.eof and not rs.bof then
do while not rs.eof
intMaxSeats = rs("Capacity")
strdescription = rs("Description")
strcredits = rs("Credits")
strProdTitle = rs("ProdTitle")
strImage = RS("Image")

Response.Write "strProdTitle = " & strProdTitle & " "
Response.Write "ProdID = " & rs("ProdID") & " "
Response.Write "strdescription = " & strdescription & " "
Response.Write "strcredits = " & strcredits & " "
Response.Write "strImage = " & strImage & " "
Response.Write "Capacity = " & rs("Capacity") & " "
....

Please let me know what additional info. you may needrovide. Thanks.

"Bob Barrows [MVP]" wrote:

> Show us:
> 1) the connection string you are using so we know if you are using ODBC
> (bad) or OLE DB (good)
> 2) the datatypes of the columns involved
> 3) the code that displays this symptom
>
> AlanMF wrote:
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob Barrows [MVP]

2005-11-15, 7:55 am

As I suspected, Description is a text column (ntext) and you are
encountering an old odbc bug that will likely never get fixed. Switch to
using the native sqloledb provider (http://www.aspfaq.com/show.asp?id=2126)
and this problem will be resolved (this is covered in one of the KB articles
cited in the aspfaq article that Mark cited). If you insist on using the
obsolete odbc driver, then you must list the text/ntext column last in your
SELECT list (again, this was mentioned in the aspfaq article).

Bob Barrows

AlanMF wrote:
> I am using ODBC:
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "DSN=SC3Sys;UID=sc3user;PWD=sc3user"
>
> The DDL used to create the table:
> CREATE TABLE [tblProd] (
> [ProdID] [int]
> IDENTITY(1,1)
> PRIMARY KEY CLUSTERED,
> [ProdTitle] [nvarchar] (100) NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL DEFAULT '1900-01-01' ,
> [EndDate] [smalldatetime] NOT NULL DEFAULT '1900-01-01' ,
> [FiscalYear] [int] NOT NULL DEFAULT 1900,
> [Credits] [ntext] NULL ,
> [Image] [ntext] NULL ,
> IsActive bit NOT NULL DEFAULT 0,
> IsCurrentShow bit NOT NULL DEFAULT 0 ,
> [Description] [ntext] NULL ,
> [Capacity] [int] NOT NULL DEFAULT 90 ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> The relevant ASP code:
> SQLQuery = "SELECT [ProdID], [ProdTitle], [StartDate], [EndDate],
> [FiscalYear], [Credits], [Image], [IsActive], [IsCurrentShow],
> [Description], [Capacity] FROM [SC3].[dbo].[tblProd] where IsActive =
> 1 ORDER by IsCurrentShow Desc, ProdTitle"
>
> Set rs = objConn.Execute(SQLQuery)
>
> if not rs.eof and not rs.bof then
> do while not rs.eof
> intMaxSeats = rs("Capacity")
> strdescription = rs("Description")
> strcredits = rs("Credits")
> strProdTitle = rs("ProdTitle")
> strImage = RS("Image")
>
> Response.Write "strProdTitle = " & strProdTitle & " "
> Response.Write "ProdID = " & rs("ProdID") & " "
> Response.Write "strdescription = " & strdescription & " "
> Response.Write "strcredits = " & strcredits & " "
> Response.Write "strImage = " & strImage & " "
> Response.Write "Capacity = " & rs("Capacity") & " "
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

AlanMF

2005-11-15, 6:55 pm

Hi Bob,

Switching to the sqloledb conn. string completely sloved my problem.

Thanks so much.

Alan

"Bob Barrows [MVP]" wrote:

> As I suspected, Description is a text column (ntext) and you are
> encountering an old odbc bug that will likely never get fixed. Switch to
> using the native sqloledb provider (http://www.aspfaq.com/show.asp?id=2126)
> and this problem will be resolved (this is covered in one of the KB articles
> cited in the aspfaq article that Mark cited). If you insist on using the
> obsolete odbc driver, then you must list the text/ntext column last in your
> SELECT list (again, this was mentioned in the aspfaq article).
>
> Bob Barrows
>
> AlanMF wrote:
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Sponsored Links