473,692 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.L ANGUAGE_NAME, CARD_TYPES.TYPE _NAME, CARD_SUBTYPES.S UBTYPE_NAME,
CARD_STYLES.STY LE_NAME, CARD_SET.SET_NA ME, CARD_RARITY.RAR ITY_TEXT, CARD_COLORS.COL OR_NAME,
CARD_QUALITY.QU ALITY_NAME FROM (((((((CARDS INNER JOIN CARD_TYPES ON CARDS.TYPE_ID =
CARD_TYPES.TYPE _ID) LEFT JOIN CARD_SUBTYPES ON CARDS.SUBTYPE_I D = CARD_SUBTYPES.S UBTYPE_ID) INNER
JOIN CARD_STYLES ON CARDS.STYLE_ID = CARD_STYLES.STY LE_ID) INNER JOIN CARD_RARITY ON CARDS.RARITY_ID
= CARD_RARITY.RAR ITY_ID) INNER JOIN CARD_COLORS ON CARDS.COLOR_ID = CARD_COLORS.COL OR_ID) INNER JOIN
CARD_SET ON CARDS.SET_ID = CARD_SET.SET_ID ) INNER JOIN CARD_QUALITY ON CARDS.QUALITY_I D =
CARD_QUALITY.QU ALITY_ID) INNER JOIN CARD_LANGUAGE ON CARDS.LANGUAGE_ ID = CARD_LANGUAGE.L ANGUAGE_ID
WHERE lower(cast(card s.card_name as varchar)) like '%test%' order by cards.card_name ,
card_set.set_na me, card_styles.sty le_name desc, card_language.l anguage_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.CreateOb ject("ADODB.Rec ordset")
rsCards.Open TempSQL, dbDatabase, adOpenDynamic

Do While rsCards.Eof = False

....

If IsNull(rsCards( "subtype_name") ) = True Then

Response.Write( rsCards("type_n ame"))

Else

Response.Write( rsCards("type_n ame") & " - " & rsCards("subtyp e_name"))

End If

....

rsCards.MoveNex t

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_na me" 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_n ame")
TempVar = rsCards("subtyp e_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 2068
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******** ************@gi ganews.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.L ANGUAGE_NAME, CARD_TYPES.TYPE _NAME,
CARD_SUBTYPES.S UBTYPE_NAME,
CARD_STYLES.STY LE_NAME, CARD_SET.SET_NA ME, CARD_RARITY.RAR ITY_TEXT,
CARD_COLORS.COL OR_NAME,
CARD_QUALITY.QU ALITY_NAME FROM (((((((CARDS INNER JOIN CARD_TYPES ON
CARDS.TYPE_ID =
CARD_TYPES.TYPE _ID) LEFT JOIN CARD_SUBTYPES ON CARDS.SUBTYPE_I D =
CARD_SUBTYPES.S UBTYPE_ID) INNER
JOIN CARD_STYLES ON CARDS.STYLE_ID = CARD_STYLES.STY LE_ID) INNER JOIN
CARD_RARITY ON CARDS.RARITY_ID
= CARD_RARITY.RAR ITY_ID) INNER JOIN CARD_COLORS ON CARDS.COLOR_ID =
CARD_COLORS.COL OR_ID) INNER JOIN
CARD_SET ON CARDS.SET_ID = CARD_SET.SET_ID ) INNER JOIN CARD_QUALITY ON
CARDS.QUALITY_I D =
CARD_QUALITY.QU ALITY_ID) INNER JOIN CARD_LANGUAGE ON CARDS.LANGUAGE_ ID =
CARD_LANGUAGE.L ANGUAGE_ID
WHERE lower(cast(card s.card_name as varchar)) like '%test%' order by
cards.card_name ,
card_set.set_na me, card_styles.sty le_name desc,
card_language.l anguage_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.CreateOb ject("ADODB.Rec ordset")
rsCards.Open TempSQL, dbDatabase, adOpenDynamic

Do While rsCards.Eof = False

...

If IsNull(rsCards( "subtype_name") ) = True Then

Response.Write( rsCards("type_n ame"))

Else

Response.Write( rsCards("type_n ame") & " - " &
rsCards("subtyp e_name"))

End If

...

rsCards.MoveNex t

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_na me" 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_n ame")
TempVar = rsCards("subtyp e_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_da rkfalz.com> wrote in message
news:OI******** ******@TK2MSFTN GP11.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...defiant ly = 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.L ANGUAGE_NAME, CARD_TYPES.TYPE _NAME
CARD_SUBTYPES.S UBTYPE_NAME, CARD_STYLES.STY LE_NAME,
CARD_SET.SET_NA ME, CARD_RARITY.RAR ITY_TEXT, CARD_COLORS.COL OR_NAME,
CARD_QUALITY.QU ALITY_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_na me" 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_n ame")
TempVar = rsCards("subtyp e_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("t ype_name")))

etc.....

Curt
"Dave" <im@not.telling > wrote in message
news:_q******** ************@gi ganews.com...
"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:OI******** ******@TK2MSFTN GP11.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("subtyp e_name") = "" Then

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

Bob Lehmann

"Dave" <im@not.telling > wrote in message
news:aP******** ************@gi ganews.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.L ANGUAGE_NAME, CARD_TYPES.TYPE _NAME, CARD_SUBTYPES.S UBTYPE_NAME, CARD_STYLES.STY LE_NAME, CARD_SET.SET_NA ME, CARD_RARITY.RAR ITY_TEXT, CARD_COLORS.COL OR_NAME, CARD_QUALITY.QU ALITY_NAME FROM (((((((CARDS INNER JOIN CARD_TYPES ON CARDS.TYPE_ID = CARD_TYPES.TYPE _ID) LEFT JOIN CARD_SUBTYPES ON CARDS.SUBTYPE_I D = CARD_SUBTYPES.S UBTYPE_ID) INNER JOIN CARD_STYLES ON CARDS.STYLE_ID = CARD_STYLES.STY LE_ID) INNER JOIN CARD_RARITY ON CARDS.RARITY_ID = CARD_RARITY.RAR ITY_ID) INNER JOIN CARD_COLORS ON CARDS.COLOR_ID = CARD_COLORS.COL OR_ID) INNER JOIN CARD_SET ON CARDS.SET_ID = CARD_SET.SET_ID ) INNER JOIN CARD_QUALITY ON CARDS.QUALITY_I D = CARD_QUALITY.QU ALITY_ID) INNER JOIN CARD_LANGUAGE ON CARDS.LANGUAGE_ ID = CARD_LANGUAGE.L ANGUAGE_ID WHERE lower(cast(card s.card_name as varchar)) like '%test%' order by cards.card_name , card_set.set_na me, card_styles.sty le_name desc, card_language.l anguage_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.CreateOb ject("ADODB.Rec ordset")
rsCards.Open TempSQL, dbDatabase, adOpenDynamic

Do While rsCards.Eof = False

...

If IsNull(rsCards( "subtype_name") ) = True Then

Response.Write( rsCards("type_n ame"))

Else

Response.Write( rsCards("type_n ame") & " - " & rsCards("subtyp e_name"))
End If

...

rsCards.MoveNex t

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_na me" 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_n ame")
TempVar = rsCards("subtyp e_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
1425
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 </b></font></td>") Response.Write("<td><font face=""Verdana"" size=""2""><b> Description </b></font></td>") ' make a couple of column headers
4
1906
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( @intCustomerID AS int ) AS
6
3729
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 '*cobol*' When I use this query to build recordset I get empty recordSet back when the BookInfo table has entries that qualify for this criteria. To
9
4349
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 predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
0
1763
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 in a datasheet. The user double clicks on a row in that datasheet and a main form (pop up) opens bound to a table with a continuous subform bound to a query.
6
6545
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 works fine, but passing the resulting recordset back to the sub's caller is not working out.
12
20681
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
5886
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 function returns a long string. This works. 2. A query has a calculated field based on the custom function above. This works when the query is run directly.
9
1830
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 on its own. The code still runs fine from the .mde. If I undo the change (single line) I STILL get the error. The .mdb compiles fine.
0
8608
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8540
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9086
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8963
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
5820
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4324
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2241
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1959
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.