ASP recordset retrieval problem (SQL Server)

Discussion in 'ASP General' started by AlanMF, Nov 14, 2005.

  1. AlanMF

    AlanMF Guest

    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, Nov 14, 2005
    #1
    1. Advertising

  2. AlanMF

    Mark Schupp Guest

    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" <> wrote in message
    news:...
    >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, Nov 15, 2005
    #2
    1. Advertising

  3. AlanMF

    AlanMF Guest

    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" <> wrote in message
    > news:...
    > >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, Nov 15, 2005
    #3
  4. 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" <> wrote in message
    >> news:...
    >>> 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], Nov 15, 2005
    #4
  5. AlanMF

    AlanMF Guest

    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" <> wrote in message
    > >> news:...
    > >>> 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"
    >
    >
    >
    AlanMF, Nov 15, 2005
    #5
  6. 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"
    Bob Barrows [MVP], Nov 15, 2005
    #6
  7. AlanMF

    AlanMF Guest

    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"
    >
    >
    >
    AlanMF, Nov 15, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Grison
    Replies:
    3
    Views:
    1,316
    Bjorn Abelli
    May 6, 2004
  2. ArtySin

    textbox retrieval from sql server database

    ArtySin, Dec 2, 2006, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    686
    ArtySin
    Dec 2, 2006
  3. Hung Huynh
    Replies:
    8
    Views:
    298
    Bob Barrows
    Sep 24, 2003
  4. Aaron Bertrand - MVP

    ASP and SQL Server Recordset

    Aaron Bertrand - MVP, Jan 21, 2004, in forum: ASP General
    Replies:
    2
    Views:
    124
    Tom Kaminski [MVP]
    Jan 21, 2004
  5. Darren Smith
    Replies:
    2
    Views:
    154
    Darren Smith
    Jan 28, 2004
Loading...

Share This Page