Re: Strange Recordset/Query Problem

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 14:08:52 -0400

Dave wrote:
> 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,

Which means it should be encapsulated in a stored procedure. Why send all
this text across the wire when you could be calling a stored procedure?

> but I thought
> I'd better include it for reference:
>
> SELECT CARDS.*,

Don't use *. You're forcing ADO to make an extra trip to the database to get
the column names.
You also might want to consider using table aliases. One of the things that
makes this so hard to read is the repetitition of all those long, upper-case
table names for each field in the SELECT clause, ON clauses, and WHERE
clause.

Also, SQL Server is not case sensitive, so you can save your eyes by using
proper case.

> 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

One thing you will like about SQL Server is that all these parentheses
around your joins are no longer necessary
>(((((((CARDS INNER JOIN CARD_TYPES ON
>
> If IsNull(rsCards("subtype_name")) = True Then
>
> 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.

Modify your query so subtype_name is never null, using ISNULL or COALESCE
(see SQL Server Books Online - BOL)

>
> 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")

Are you using ODBC to connect? If so, switch to te native SQL Server OLEDB
provider (SQLOLEDB). See www.able-consulting.com/ado_conn.htm for connection
string examples. There was a bug in the ODBC driver that causes data to be
"lost" unless it is immediately saved to a variable. That might be what's
going on here.

Bob Barrows 

-- 
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Relevant Pages

  • 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: 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: What is the best way to work with SQL SERVER data / tables?
    ... with the appropriate WHERE clause that restricts the dataset. ... * Security, Security, Security; I guess one couldn?t use Windows ... SQL Server and presto, everything would be available to him/her without ... INSTEAD OF triggers can be created on a view to make a view updatable. ...
    (comp.databases.ms-access)
  • Re: Near Clause usage
    ... NEAR clause using CONTAINS? ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... get the exact words I m looking for within my NEAR clause: ...
    (microsoft.public.sqlserver.fulltext)