473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Strange Recordset/Query Problem

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
Jul 19 '05 #1
8 2045
Man that is a mess to sort though....I'm not even sure what the problem
exactly is BUT I think, as I understood it, that the IsNull check isn't
finding your NULL value? Correct? Are you sure it's NULL and not ""
(blank/empty)?

Curt
"Dave" <im@not.telling> wrote in message
news:aP********************@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

Jul 19 '05 #2
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OI**************@TK2MSFTNGP11.phx.gbl...
Man that is a mess to sort though....I'm not even sure what the problem
exactly is BUT I think, as I understood it, that the IsNull check isn't
finding your NULL value? Correct? Are you sure it's NULL and not ""
(blank/empty)?

Curt


Heh, yeah, I knew it'd be tough to read.

Basically the problem is two-fold. The IsNull check isn't
finding the NULL value, and even when it goes to the ELSE
clause, the display of the type_name field doesn't work.

The subtype_name field is most defiantly NULL. I ran the
query directly against the database and it returns all of
the data like it should. likewise, there's defiantly data
in the type_name field.

I'm stumped as to why adding in those two lines of code,
that reference the columns, and do nothing else, would all
of a sudden make things work.

- Dave
Jul 19 '05 #3
> The subtype_name field is most defiantly NULL. I ran the
query directly against the database and it returns all of
the data like it should. likewise, there's defiantly data
in the type_name field.


Sorry...defiantly = definitely.

- Dave
Jul 19 '05 #4
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.
Jul 19 '05 #5
Hmm.... try converting them to strings to display them and see what happens

Response.Write(CStr(rsCards("type_name")))

etc.....

Curt
"Dave" <im@not.telling> wrote in message
news:_q********************@giganews.com...
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OI**************@TK2MSFTNGP11.phx.gbl...
Man that is a mess to sort though....I'm not even sure what the problem
exactly is BUT I think, as I understood it, that the IsNull check isn't
finding your NULL value? Correct? Are you sure it's NULL and not ""
(blank/empty)?

Curt


Heh, yeah, I knew it'd be tough to read.

Basically the problem is two-fold. The IsNull check isn't
finding the NULL value, and even when it goes to the ELSE
clause, the display of the type_name field doesn't work.

The subtype_name field is most defiantly NULL. I ran the
query directly against the database and it returns all of
the data like it should. likewise, there's defiantly data
in the type_name field.

I'm stumped as to why adding in those two lines of code,
that reference the columns, and do nothing else, would all
of a sudden make things work.

- Dave

Jul 19 '05 #6
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:aP********************@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

Jul 19 '05 #7
> 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


BAM! That got it. It's working perfectly now. I was using the
connect string that was given to me by the hosting company,
so that's why I was using MSDASQL, but changing it to SQLOLEDB
fixed it all.

Thanks a million Bob! And thanks to everyone who bothered to
slog through that code and give suggestions!

- Dave

P.S. Sorry Bob...I replied at first via e-mail instead of the
group.
Jul 19 '05 #8
Dave wrote:

P.S. Sorry Bob...I replied at first via e-mail instead of the
group.


I have no problem with you replying via email. It's just that I will
probably see the reply sooner in the newsgroup, given that I seldom check
that email account.

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.
Jul 19 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: HenryW | last post by:
I have a section of ASP code that dynamically builds a table. And per usual it writes out to the table like so: Response.Write("<td><font face=""Verdana"" size=""2""><b> Status...
4
by: Ola Tuvesson | last post by:
I'm having a really weird problem. When running the SP below in query analyzer the calculated column "Subscribed" is returned as expected: ------------- CREATE PROCEDURE get_mailinglists( ...
6
by: HKM | last post by:
Hello, I have a query engine that builds the SQL Query to obtain the recordSet. Following is an Exmaple Query that my QueryBuilder outputted SELECT * FROM BookInfo WHERE BookName LIKE...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
0
by: William Wisnieski | last post by:
Hello Everyone: I'm having a very strange problem occurring with my Access 2000 database. I call it the "mystery record." Here's the story: I have a query by form that returns a record set...
6
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query...
12
by: John | last post by:
I can't get my head around this! I have the following code: <% .... Code for connection to the database ... .... Code for retrieving recordset ... If Not rs.EOF Then ... Do something...
5
by: Henrik | last post by:
The problem is (using MS Access 2003) I am unable to retrieve long strings (255 chars) from calculated fields through a recordset. The data takes the trip in three phases: 1. A custom public...
9
by: sara | last post by:
Hi - I have code (below) that ran as recently as Monday. We decided to change the location for the output reports, and now the code errors (13 Type Mismatch) on the query. The query runs fine...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.