Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: MOD Implementation
 Can't See the Forest		  New Topic  Topic Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  08:09:08  Show Profile
Here's a bit of SQL that I'm getting an 800a0cc1 ("Item cannot be found in the collection corresponding to the requested name or ordinal.") error on. I've looked at this until I'm blue in the face and cannot see why.


strSql="SELECT M.M_EMAIL, M.M_NAME, M.MEMBER_ID, T.T_AUTHOR, T.T_SUBJECT, T.TOPIC_ID FROM " & strMemberTablePrefix &_
"MEMBERS M INNER JOIN " & strTablePrefix & "TOPICS T ON M.MEMBER_ID=T.T_AUTHOR WHERE T.TOPIC_ID="& Topic_ID
Edited by - Carefree on 22 April 2009 08:09:36

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 22 April 2009 :  08:10:53  Show Profile  Visit HuwR's Homepage
are you sure the Topic_ID exists ?
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  08:12:22  Show Profile
Yes, the topic exists.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 22 April 2009 :  08:25:07  Show Profile  Visit HuwR's Homepage
can't see anything wrong with it at all, what does strSQL think it is when you execute the query ?
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  09:05:52  Show Profile
The Topic_ID number is filled in properly, nothing else gets past the error. I decided to simply break it up and rewrite it into two routines and now it works. No idea what was causing the error.
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 22 April 2009 :  09:36:43  Show Profile  Visit SiSL's Homepage
You can't use WHERE on MEMBERS table with TOPICS just INNER JOINed...

You should use following:

strSql="SELECT M.M_EMAIL, M.M_NAME, M.MEMBER_ID, T.T_AUTHOR, T.T_SUBJECT, T.TOPIC_ID FROM " & strMemberTablePrefix &_
"MEMBERS M INNER JOIN " & strTablePrefix & "TOPICS T ON (M.MEMBER_ID=T.T_AUTHOR AND T.TOPIC_ID="& Topic_ID & ")"
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Edited by - SiSL on 22 April 2009 09:39:14
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  09:45:22  Show Profile
Here's the output:
quote:

SELECT M.M_EMAIL, M.M_NAME, M.MEMBER_ID, T.T_AUTHOR, T.T_SUBJECT, T.TOPIC_ID FROM FORUM_MEMBERS M INNER JOIN FORUM_TOPICS T ON (M.MEMBER_ID=T.T_AUTHOR AND T.TOPIC_ID=577)


Result of attempted execution is the same:
quote:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 22 April 2009 :  09:48:46  Show Profile  Visit SiSL's Homepage
Try changing places of equalisation like T.T_AUTHOR = M.MEMBER_ID

Beyond that, I can't help to think those columns really have problems, if this SQL is the only thing that came output.
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Edited by - SiSL on 22 April 2009 09:51:11
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 22 April 2009 :  09:51:42  Show Profile  Send ruirib a Yahoo! Message
First I don't see why the JOIN clause needs a logical AND with the topic ID, but that should cause the error.

I would guess the error is not when the execution occurs, but when you try to access a field that wasn't selected. I would guess a misspelled field name, likely.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 22 April 2009 :  09:53:52  Show Profile  Visit SiSL's Homepage
Oh Rui is right, if you try to take some other field like T_LAST_POST etc. from TOPICS that didnt make it in SQL statement, you might end up like that.
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  10:06:53  Show Profile
Well, here's the entire routine. All the fields are retrieved perfectly when I break it into two parts, but the error persists when using a join. As you'll see, I don't call any fields which weren't specified and the column titles are correct.


				strSql="SELECT M.M_EMAIL, M.M_NAME, M.MEMBER_ID, T.T_AUTHOR, T.T_SUBJECT, T.TOPIC_ID FROM " & strMemberTablePrefix &_
				"MEMBERS M INNER JOIN " & strTablePrefix & "TOPICS T ON (T.T_AUTHOR=M.MEMBER_ID AND T.TOPIC_ID="& Topic_ID &")"
				set rsPost=my_Conn.Execute(strSql)
				if not rsPost.EOF then
					strT2=rsPost("T.T_SUBJECT")
					strT3=rsPost("M.M_EMAIL")
					strT4=rsPost("M.M_NAME")
				end if
				rsPost.Close
				set rsPost=Nothing
Go to Top of Page

Shaggy
Support Moderator

Ireland
6780 Posts
Posted - 22 April 2009 :  10:34:17  Show Profile
You shouldn't include the table aliases when referencing the fields; that's what's causing the error:

strT2=rsPost("T_SUBJECT")
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 22 April 2009 :  10:35:23  Show Profile  Visit SiSL's Homepage
You can't use T., M. aliasnames while retrieving them...

That's where it errors...

strT2=rsPost("T_SUBJECT")
strT3=rsPost("M_EMAIL")
strT4=rsPost("M_NAME")


Edit: ****, Shaggy beat me to it.
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Edited by - SiSL on 22 April 2009 10:35:51
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 22 April 2009 :  11:15:30  Show Profile  Visit HuwR's Homepage
it generaly helps if you provide all the code that is erroring would have saved a lot of time.
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 22 April 2009 :  11:22:04  Show Profile  Visit SiSL's Homepage
And a final tip, if there is an error on SQL statement, Error turns out line number where connection is made or recordset opened, if there are errors on other lines, line number on error message shows the line number where actual error is..
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 22 April 2009 :  15:17:28  Show Profile
Well, that was it, alright. But it never got to those line numbers to report an error - it always reported the error at the set rs line. If the silly debugger would (for once) report the actual line number with an error, it would sure help.

I love those error in line number 0 (or in a line number greater than exists in the file with the supposed error) reports. Somebody needs to write a debugger which works as well as the old Commodore basic version. If there was an error, you knew EXACTLY where it was.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000 Version 3.4.07