Re: Strange Recordset/Query Problem

From: Bob Lehmann (none)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 12:14:42 -0600

Just for grins, try this....
If Not rsCards("subtype_name") = "" Then

instead of this...
If IsNull(rsCards("subtype_name")) = True Then

Bob Lehmann

"Dave" <im@not.telling> wrote in message
news:aPCdnR8fdot_Db7cRVn-hQ@giganews.com...
> Hi all,
>
> I've been trying to figure this out for the last day and a half and
> it has me stumped. I've got a web application that I wrote that
> keeps track of trading cards I own, and I'm moving it from an
> Access 2000 database to a SQL Server 2000 database. Everything
> worked perfectly in Access, but I'm having trouble getting data to
> display in SQL Server.
>
> For reference, here's the query. It's big and nasty, but I thought
> I'd better include it for reference:
>
> SELECT CARDS.*, CARD_LANGUAGE.LANGUAGE_NAME, CARD_TYPES.TYPE_NAME,
CARD_SUBTYPES.SUBTYPE_NAME,
> CARD_STYLES.STYLE_NAME, CARD_SET.SET_NAME, CARD_RARITY.RARITY_TEXT,
CARD_COLORS.COLOR_NAME,
> CARD_QUALITY.QUALITY_NAME FROM (((((((CARDS INNER JOIN CARD_TYPES ON
CARDS.TYPE_ID =
> CARD_TYPES.TYPE_ID) LEFT JOIN CARD_SUBTYPES ON CARDS.SUBTYPE_ID =
CARD_SUBTYPES.SUBTYPE_ID) INNER
> JOIN CARD_STYLES ON CARDS.STYLE_ID = CARD_STYLES.STYLE_ID) INNER JOIN
CARD_RARITY ON CARDS.RARITY_ID
> = CARD_RARITY.RARITY_ID) INNER JOIN CARD_COLORS ON CARDS.COLOR_ID =
CARD_COLORS.COLOR_ID) INNER JOIN
> CARD_SET ON CARDS.SET_ID = CARD_SET.SET_ID) INNER JOIN CARD_QUALITY ON
CARDS.QUALITY_ID =
> CARD_QUALITY.QUALITY_ID) INNER JOIN CARD_LANGUAGE ON CARDS.LANGUAGE_ID =
CARD_LANGUAGE.LANGUAGE_ID
> WHERE lower(cast(cards.card_name as varchar)) like '%test%' order by
cards.card_name,
> card_set.set_name, card_styles.style_name desc,
card_language.language_name
>
> Again, under Access, this query works 100% (except for the "cast"
> function is had to ass for SQL Server). It returns the five rows
> that are expected and displays them to the screen.
>
> If I paste the above query directly into a SQL window and fire it
> off under SQL Server, it too works fine and returns the expected
> data. The problem is when I use it in an ASP web page. Check out
> the following code (the above query is stored in the variable
> "TempSQL":
>
> ----------------------------------------------------------------
> Set rsCards = Server.CreateObject("ADODB.Recordset")
> rsCards.Open TempSQL, dbDatabase, adOpenDynamic
>
> Do While rsCards.Eof = False
>
> ...
>
> If IsNull(rsCards("subtype_name")) = True Then
>
> Response.Write(rsCards("type_name"))
>
> Else
>
> Response.Write(rsCards("type_name") & " - " &
rsCards("subtype_name"))
>
> End If
>
> ...
>
> rsCards.MoveNext
>
> Loop
> ----------------------------------------------------------------
>
> The loop executes fine, and some items in the select statement
> get displayed, but for some reason, data from some of the joined
> tables won't display. In the above example, five rows should
> be returned (as verified by running the query directly against
> the database). The column "subtype_name" is NULL for each row,
> yet the first condition of the IF statement isn't called as it
> should be (since the condition is true), and instead it goes to
> the ELSE clause.
>
> Now what's really bizarre is that data in the column "type_name"
> doesn't display at all, even though there is data (again verified
> by querying the database directly).
>
> Here's the kicker. If I add the following two lines of code
> directly after the DO WHILE loop statement, everything works
> fine; the IF statement executes the first condition as it should,
> and the data gets displayed:
>
> TempVar = rsCards("type_name")
> TempVar = rsCards("subtype_name")
>
> Now why on earth won't the IF-THEN logic and data display work
> correctly unless those two lines are added in? I don't even use
> the data stored in the "TempVar" variable. It seems just the
> initial access of the two columns is enough to "wake up" the
> recordset so it will work correctly. Anyone know what's going
> on here?
>
> Sorry for the long, complicated post, but I couldn't figure out
> how to make it more concise than this. Any help welcome!
>
> - Dave
>
>

Relevant Pages

  • Re: Strange Recordset/Query Problem
    ... > display in SQL Server. ... table names for each field in the SELECT clause, ON clauses, and WHERE ... Also, SQL Server is not case sensitive, so you can save your eyes by using ... the IF statement executes the first condition as it should, ...
    (microsoft.public.inetserver.asp.general)
  • Strange Recordset/Query Problem
    ... Access 2000 database to a SQL Server 2000 database. ... display in SQL Server. ... the IF statement executes the first condition as it should, ...
    (microsoft.public.inetserver.asp.general)
  • Re: Strange Recordset/Query Problem
    ... > display in SQL Server. ... > function is had to ass for SQL Server). ... > The loop executes fine, and some items in the select statement ... the IF statement executes the first condition as it should, ...
    (microsoft.public.inetserver.asp.general)
  • Re: Too many unique fields to keep data distinct
    ... Columnist, SQL Server Professional ... selections on what data they want to see based on the selections they ... coming through the loop. ... If the id's match it doesn't display that ...
    (microsoft.public.sqlserver.programming)
  • Re: Interpreting the colon operator
    ... I tried this by using the print statement inside the loop. ... What follows then would be a display of ... is apparently affected by the fact that matlab is aware that 'Av' ... three-dimensional matrix even though the first index is restricted ...
    (comp.soft-sys.matlab)