MSDE vs SQL Server Express 2005

I upgraded from MSDE to SQL server Express 2005.  My asp code that has inner and outer joins in the SQL statements doesn't seem to work the same.  I can write the SQL statement to I.E. and paste it in SQL Management studio and it give results.  The same query running in ASP vbScript under IIS doesn't returns no records.  If I simplfied the query to just one table, it will return records.

Any ideas on what the difference might be?
GeekRUSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
Are you using TSQL join syntax for your outer joins?

ex:
Select * from YourTable1 t1
,yourtable2 t2
where t1.id *= t2.id


Or ANSI standard syntax

Select * from YourTable1 t1
left outer yourtable2 t2
on t1.id = t2.id

0
GeekRUSAuthor Commented:
Like this
Or ANSI standard syntax

Select * from YourTable1 t1
left outer yourtable2 t2
on t1.id = t2.id
0
BrandonGalderisiCommented:
Well I can assure you that queries will be the same in 2000 and 2005 if you are using the ANSI syntax.  Do you want to post your query so that it can be analyzed?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

GeekRUSAuthor Commented:
SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden FROM TeamDT RIGHT OUTER JOIN PlayersInPic ON TeamDT.Id = PlayersInPic.TeamDTID RIGHT OUTER JOIN Photos INNER JOIN EventList ON Photos.EventID = EventList.EventID ON PlayersInPic.PhotoID = Photos.PhotoID WHERE Photos.PhotoID =25751

Thanks for your help!
0
Louis01Commented:
This should not make a difference, but try...
SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden 
  FROM TeamDT 
	RIGHT OUTER JOIN PlayersInPic 
	 ON TeamDT.Id = PlayersInPic.TeamDTID 
	RIGHT OUTER JOIN ((SELECT * FROM Photos WHERE PhotoID = 25751) Photos INNER JOIN EventList ON Photos.EventID = EventList.EventID)
	 ON PlayersInPic.PhotoID = Photos.PhotoID 

Open in new window

0
BrandonGalderisiCommented:
Ok.  I reorganized this to make it look more normal to me.  What you want here is everything from photos and event list, with the matching records from playersinpic and teamdt, for photo it 25751?

SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden
FROM TeamDT
RIGHT OUTER JOIN PlayersInPic
  ON TeamDT.Id = PlayersInPic.TeamDTID
RIGHT OUTER JOIN Photos
  ON PlayersInPic.PhotoID = Photos.PhotoID
INNER JOIN EventList
ON Photos.EventID = EventList.EventID
WHERE Photos.PhotoID =25751
0
GeekRUSAuthor Commented:
If the record (photoid) exist in Playersinpic I don't want it in the result.
0
BrandonGalderisiCommented:
So how about you explain what you DO want?

You want a list of photos (or one photo)? YES/NO
You want the teams "IN" the photo(s)? " YES/NO
You want the people "IN" the photo(s)? YES/NO


0
GeekRUSAuthor Commented:
You want a list of photos  YES
You want the teams "IN" the photo(s)? " YES
You want the people "IN" the photo(s)? NO

I have a list of photos, a list of players and a list of what players are in what photo.
So if there is a photoid that does not exist in the Playersinpic table I want that in the result
The followiing quert works in Management Studio put not in the code under IIS.

SELECT     Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden
FROM         TeamDT RIGHT OUTER JOIN
                      PlayersInPic ON TeamDT.Id = PlayersInPic.TeamDTID RIGHT OUTER JOIN
                      Photos INNER JOIN
                      EventList ON Photos.EventID = EventList.EventID ON PlayersInPic.PhotoID = Photos.PhotoID
WHERE     (Photos.EventID = 145) AND (PlayersInPic.Inden IS NULL) AND (Photos.PhotoID >= 0)
ORDER BY Photos.PhotoID

0
GeekRUSAuthor Commented:
I figured this one out...  I don't understand it but this works.  I changed the OS from sever 2003 web edition to server 2003 SBE.  I also changed the DB from MSDE to SQL Express 2005

The fix was adjusting the line of code the opened the query.
It used to look like this.
     Images.Open Query, adoConnect, 3, 3, 1
No it looks like this
    Images.Open Query, adoConnect

Thanks For your effort.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
To translate that into English, you changed the way you were opening the recordset from:
adOpenStatic CursorType, adLockOptimistic LockType and the Options set to adCmdText.
To the default:
adOpenForwardOnly and adLockReadOnly

And your right it has nothing to do with the version of SQL Server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.