why cant I access data from a JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

why cant I access data from a JOIN

Author

Topic

huge
Starting Member

31 Posts

Posted - 2002-03-16 : 12:11:41

Ok firstl lets say I have two tables:

-------------
Messages
-------------
MsgID
Sender
Recipient
DateSent
MsgRead (bit)
...

AND

-------------
UserLogin
-------------
UserID
Username
...

Ok now I want to do an inner JOIN of these two tables. My gateway is ASP, so I create a recordset that connects to the Stored procedure that has the follwing:

CREATE PROCEDURE sproc_getmessage
(
@Username varchar(16),
@MessageID bigint
)
AS

SELECT *
FROM Messages AS a INNER JOIN UserLogin AS p
ON a.Sender = p.Username
WHERE a.MessageID = @MessageID
GO

Where @username is the username of the user that is currently logged in, this is passed form asp. Now when I test this in QA it works great and gives me both tables in a nicely ordered fashion, there are no naming conflicts. When I truy this in ASP by accessing
Ors("Sender") it works fine and can display, but if i have Ors("UserID") then i get the error (Where ORS is the Recordset, and the connection is established):

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

I really dont understand whats going on. Shouldnt a JOIN basically create a single table from the two that I ask it to. Then how come I cant access it as a single table?

Please help
HuGe

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-16 : 12:40:34

Do you have a UserID in both tables?
Try

SELECT Sender = a.Sender ,
UserID = p.UserID
FROM Messages AS a INNER JOIN UserLogin AS p
ON a.Sender = p.Username
WHERE a.MessageID = @MessageID

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

huge
Starting Member

31 Posts

Posted - 2002-03-16 : 13:27:14

Hey sorry for your waste of time it was a dumb @$$ mistake that I spent waaaaaaaaaay too much time on. You see I had 2 sprocs, the first one was messed so I made another one. When I made the join statemnt I had updated the first one, but the ASP page was linking to the second one. SO it was a big dumb, stupid, idiotic mistake. Sorry again, what I had worked great

HuGE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources