homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam

The Geek Culture Forums


Post New Topic  New Poll  Post A Reply
my profile | directory login | | search | faq | forum home
  next oldest topic   next newest topic
» The Geek Culture Forums   » Other Geeky Stuff   » Ask a Geek!   » Good Database, Good Query, Bad Data With ASP

 - UBBFriend: Email this page to someone!    
Author Topic: Good Database, Good Query, Bad Data With ASP
Stereo

Solid Nitrozanium SuperFan!
Member # 748

Member Rated:
5
Icon 5 posted April 27, 2004 08:19      Profile for Stereo     Send New Private Message       Edit/Delete Post   Reply With Quote 
Hello, me again. Got another crazy problem. (Is that a sigh I hear?)

I have those ASP report scripts I take care of. We updated the database they refer to from Access to SQL Server, so I tweaked the scripts so they would run again (date formats and some keywords changed). They returned some data, so I thought all was fine.

Wrong.

Last Friday, I was told the data returned in one script was bad. I do a check, and Hell's gates open before me. The very same query generated with ASP, when feeded directly to SQL server, has good results, but consistently returns bad data in the scripted page.

Anyone has an idea of what's going on? I think it's a SQLServer driver problem. OTOH, our sysadmin found out the servers' are missing a couple of SP. Could the answer be that easy?

I'm at lost on this problem. I'm a programmer, not a sysadmin! [shake head]

--------------------
Eppur, si muove!

Galileo Galilei

Posts: 2289 | From: Gatineau, Quebec, Canada | Registered: Apr 2001  |  IP: Logged
Allan
SuperFan!
Member # 1717

Member Rated:
5
Icon 1 posted April 27, 2004 13:17      Profile for Allan     Send New Private Message       Edit/Delete Post   Reply With Quote 
Yeah, I remember the last time, that was pretty good fun... OK not for you but I enjoyed it.

Oracle is my thing and I have fortunately been able to become less and less familiar with MSSQL but some things to check:

The asp script is accessing the same user schema as the running the script direct.

The asp script is not converting datatypes on the fly?

if you are comparing date values make sure you truncate the days back to equal units: for example 02-may-2004 00:00:00 = 02-may-2004 00:00:00 but 02-may-2004 09:00:00 != 02-may-2004 17:00:00

check for mixed cases, you may have different settings. If you need to do string comparisons force both expressions to uppercase or use upper case constants against forced expressions.

watch out for nulls they may be treated differently in ASP?

hope this helps

Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002  |  IP: Logged
Jace Raven

Solid Nitrozanium SuperFan!
Member # 2444

Icon 1 posted April 27, 2004 13:25      Profile for Jace Raven         Edit/Delete Post   Reply With Quote 
<obligatory line of advise>Never expect to have sex with a donkey and not get kicked</advise>
Posts: 1791 | Registered: Nov 2003  |  IP: Logged
Allan
SuperFan!
Member # 1717

Member Rated:
5
Icon 1 posted April 27, 2004 13:54      Profile for Allan     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by Jace Raven:
<obligatory line of advise>Never expect to have sex with a donkey and not get kicked</advise>

Jace, did you get the threads mixed up or have you just been smoking some unfeasably strong weed?
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002  |  IP: Logged
Stereo

Solid Nitrozanium SuperFan!
Member # 748

Member Rated:
5
Icon 1 posted April 27, 2004 14:17      Profile for Stereo     Send New Private Message       Edit/Delete Post   Reply With Quote 
(It could be that Jace was referring to the first line of your answer. Or that I am using MS product and having problem.)

A couple of things I know for sure: it's not the dates - I send them in format yyyymmdd, and they are interpreted as yyyymmdd 00:00:00 (and they are not compared for equatlity). Also, there are no comparisons with strings, so that's not the problem.

I'm pretty much sure it's not a schema or owner/permission problem, as the query via ASP returns data following the type of what's asked, and from the good tables, but it doesn't fit the filtering (people in the wrong division, etc.)

I'll check about nulls.

I've checked a few other scripts; they are all fine. Maybe comparing how they differ will give me a hint.

Keep trhowing ideas around. It could very well be something very stupid. Or something I have no control over. I'll tell you as soon as I find the culprit. Thanks.

--------------------
Eppur, si muove!

Galileo Galilei

Posts: 2289 | From: Gatineau, Quebec, Canada | Registered: Apr 2001  |  IP: Logged
Allan
SuperFan!
Member # 1717

Member Rated:
5
Icon 1 posted April 27, 2004 14:44      Profile for Allan     Send New Private Message       Edit/Delete Post   Reply With Quote 
If I've got a really tricky problem I use a count(*) for the return set and remove the where clause. Then build it back item by item, that way you can find what item makes the results different
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002  |  IP: Logged
Stereo

Solid Nitrozanium SuperFan!
Member # 748

Member Rated:
5
Icon 7 posted April 28, 2004 09:40      Profile for Stereo     Send New Private Message       Edit/Delete Post   Reply With Quote 
Well, guess what? I looked beyond the query, and found out the results were compared to another query... on a string field. I've uppercased every occurence of that field, and now the results make more sense (I still have to double check in order to put the official 'bug squashed' stamp on the case).

Crazy little things one find out from someone else's scripts... Was it really that hard to use a nested query instead? Bah! [devil wand] [crazy] [Embarrassed]

Thanks for the help anyway.

Posts: 2289 | From: Gatineau, Quebec, Canada | Registered: Apr 2001  |  IP: Logged
maven
Geek-in-Training
Member # 2714

Member Rated:
2
Icon 1 posted May 18, 2004 10:47      Profile for maven     Send New Private Message       Edit/Delete Post   Reply With Quote 
problem: you are using ASP and MSSQL on a Windows platform.

solution: use PHP and MySQL on a linux platform. (or windows, when you throw in that whole cross-compatibility thing...)

done.

(oh yeah, PHP5 is supposed to come with a compiler (like ASP's .NET) and is supposed to be fully object-oriented.

--------------------
"Never offend people with style when you can offend them with substance."

Posts: 32 | From: East Peoria, IL | Registered: May 2004  |  IP: Logged
dragonman97

SuperFan!
Member # 780

Member Rated:
4
Icon 1 posted May 18, 2004 10:51      Profile for dragonman97   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by maven:
problem: you are using ASP and MSSQL on a Windows platform.

solution: use PHP and MySQL on a linux platform. (or windows, when you throw in that whole cross-compatibility thing...)

done.

(oh yeah, PHP5 is supposed to come with a compiler (like ASP's .NET) and is supposed to be fully object-oriented.

Unacceptable answer. It may be a technically superior solution (though I'll disagree about PHP), but many times, one does not the power or authority to change an existing setup, and must make the current system work.

--------------------
There are three things you can be sure of in life: Death, taxes, and reading about fake illnesses online...

Posts: 9331 | From: Westchester County, New York | Registered: May 2001  |  IP: Logged
maven
Geek-in-Training
Member # 2714

Member Rated:
2
Icon 1 posted May 18, 2004 10:58      Profile for maven     Send New Private Message       Edit/Delete Post   Reply With Quote 
unfortunately enough.

just wait until the fifth installment of PHP is done. it may just change your mind.

--------------------
"Never offend people with style when you can offend them with substance."

Posts: 32 | From: East Peoria, IL | Registered: May 2004  |  IP: Logged
Allan
SuperFan!
Member # 1717

Member Rated:
5
Icon 1 posted May 18, 2004 11:11      Profile for Allan     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by maven:
unfortunately enough.

just wait until the fifth installment of PHP is done. it may just change your mind.

No, it won't. You don't pay consultants 500 USD + per day to reprogram your system with all related change management because a scripting language is better.
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002  |  IP: Logged
maven
Geek-in-Training
Member # 2714

Member Rated:
2
Icon 6 posted May 18, 2004 11:19      Profile for maven     Send New Private Message       Edit/Delete Post   Reply With Quote 
pff. i'd do it for free.

although... if you know anyone who wants to pay me 500 bucks a day for this, tell 'em to email me.

--------------------
"Never offend people with style when you can offend them with substance."

Posts: 32 | From: East Peoria, IL | Registered: May 2004  |  IP: Logged
Stereo

Solid Nitrozanium SuperFan!
Member # 748

Member Rated:
5
Icon 1 posted May 18, 2004 12:48      Profile for Stereo     Send New Private Message       Edit/Delete Post   Reply With Quote 
Well, we'll be redoing the website over the summer, and we're changing to <drumroll>ASP.net!</drumroll> Yeah! ... Um, sorry, I mean: Bleah!

Give me OSX Server and WebObject anytime, and for database, mySQL would do fine, while there are other options.

But, working for the government and all, some stupid decisions are made, like the Uniform Platform Policy - everyone: Windows only! (Ok, not true. That's more like if you use something else, you're on your own. Which wouldn't be that bad. But as dman guessed, I don't have decision power over those things, plus I'd have to take into account the one who'll take care after me may not* know a thing about other options.)

All in all, just a another reason to go on with my master degree, so I can easily get out of here if worse comes to worst.

P.S.: For those who may care, the results are in, and the conditional admission is now definitive! (A- on the course I needed a B to go on. And A on the other.) Woohoo!

*End of afternoon language musing: if "can not" can be shortened into "can't", why not use "main't" fo "may not"?

--------------------
Eppur, si muove!

Galileo Galilei

Posts: 2289 | From: Gatineau, Quebec, Canada | Registered: Apr 2001  |  IP: Logged
nekomatic
SuperFan!
Member # 376

Member Rated:
5
Icon 3 posted May 19, 2004 02:00      Profile for nekomatic     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by Stereo:
if "can not" can be shortened into "can't", why not use "main't" fo "may not"?

If you look back a few years in literature (as a scientist, I really had to fight the instinct to type "the literature" [Wink] ) I think you'll find "mayn't" in common use. Try Alice's Adventures in Wonderland for a start (can't swear it's in there, but you'll enjoy re-reading it in any case [Big Grin] ) Incidentally, what you'll also find in Alice's... (unless you have a modern edition where they've tidied it up) is the technically correct (if awkward) approach to apostrophisation in abbreviations like "sha'n't", "ca'n't", etc.

Personally I find all these forms quite endearing, and perhaps we should start a campaign for their reintroduction. Who's with me? Callipygous? [Smile]

Posts: 822 | From: Manchester, UK | Registered: Mar 2000  |  IP: Logged
Stereo

Solid Nitrozanium SuperFan!
Member # 748

Member Rated:
5
Icon 14 posted May 19, 2004 03:59      Profile for Stereo     Send New Private Message       Edit/Delete Post   Reply With Quote 
Thanks Neko. I always thought I'd someday get myself some British and US classicals in their original language. Maybe it's time for me to do so!

--------------------
Eppur, si muove!

Galileo Galilei

Posts: 2289 | From: Gatineau, Quebec, Canada | Registered: Apr 2001  |  IP: Logged
nekomatic
SuperFan!
Member # 376

Member Rated:
5
Icon 10 posted May 21, 2004 02:38      Profile for nekomatic     Send New Private Message       Edit/Delete Post   Reply With Quote 
Lewis Carroll would surely be a good place to start...

...save Tristram Shandy for later on... maybe when you have about a month with nothing to do [Big Grin]

Posts: 822 | From: Manchester, UK | Registered: Mar 2000  |  IP: Logged


All times are Eastern Time  
Post New Topic  New Poll  Post A Reply Close Topic    Move Topic    Delete Topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:

Contact Us | Geek Culture Home Page

2015 Geek Culture

Powered by Infopop Corporation
UBB.classicTM 6.4.0



homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam