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 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
"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
> 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
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.
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
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
> 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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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(
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
| |