Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP recordset retrieval problem (SQL Server)

Reply
Thread Tools

ASP recordset retrieval problem (SQL Server)

 
 
AlanMF
Guest
Posts: n/a
 
      11-14-2005
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

 
Reply With Quote
 
 
 
 
Mark Schupp
Guest
Posts: n/a
 
      11-15-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
 
AlanMF
Guest
Posts: n/a
 
      11-15-2005
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >

>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-15-2005
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:
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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"


 
Reply With Quote
 
AlanMF
Guest
Posts: n/a
 
      11-15-2005
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.

"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:
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >>> 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"
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-15-2005
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 & "<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"


 
Reply With Quote
 
AlanMF
Guest
Posts: n/a
 
      11-15-2005
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:
> > 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"
>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Browser IP retrieval problem JJ Computer Support 6 01-29-2009 03:42 AM
Data Retrieval/Formating problem Stephen Noronha ASP .Net 0 09-01-2005 10:39 PM
ASP.net ques: Webpage data retrieval and parsing news.microsoft.com ASP .Net 5 02-01-2005 05:15 AM
ASP.net Ques: Image Retrieval news.microsoft.com ASP .Net 2 01-17-2005 02:02 AM
RecordSet.Move or RecordSet.AbsolutePosition?? Hung Huynh ASP General 8 09-24-2003 11:07 AM



Advertisments