ASP recordset retrieval problem (SQL Server)

[AlanMF]

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]

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

 

[AlanMF]

Thanks Mark.

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

Alan

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

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

 

[Bob Barrows [MVP]]

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

Thanks Mark.

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

Alan

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

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]

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 & "<BR>"
Response.Write "ProdID = " & rs("ProdID") & "<BR>"
Response.Write "strdescription = " & strdescription & "<BR>"
Response.Write "strcredits = " & strcredits & "<BR>"
Response.Write "strImage = " & strImage & "<BR>"
Response.Write "Capacity = " & rs("Capacity") & "<BR>"
....

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

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

Thanks Mark.

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

Alan

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


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

--
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]]

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

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 & "<BR>"
Response.Write "ProdID = " & rs("ProdID") & "<BR>"
Response.Write "strdescription = " & strdescription & "<BR>"
Response.Write "strcredits = " & strcredits & "<BR>"
Response.Write "strImage = " & strImage & "<BR>"
Response.Write "Capacity = " & rs("Capacity") & "<BR>"

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]

Hi Bob,

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

Thanks so much.

Alan

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

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 & "<BR>"
Response.Write "ProdID = " & rs("ProdID") & "<BR>"
Response.Write "strdescription = " & strdescription & "<BR>"
Response.Write "strcredits = " & strcredits & "<BR>"
Response.Write "strImage = " & strImage & "<BR>"
Response.Write "Capacity = " & rs("Capacity") & "<BR>"

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"