Selecting from two tables...

[snowboardr]

I am getting this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

course/join.asp, line 37


using:

strSQL= "SELECT grade.gID,grade.userID,grade.courseID,course.cID,course.cTitle,course.cCredit,course.cQuiz FROM grade,course WHERE grade.courseID=course.cID AND grade.userID=" & strUserIDTmp

#
# Table structure for table 'course'
#

CREATE TABLE `course` (
  `cID` int(10) unsigned NOT NULL auto_increment,
  `cTitle` text,
  `about_text` longtext,
  `cCredit` int(10) unsigned default NULL,
  `sort_order` int(10) unsigned default NULL,
  `cQuiz` int(11) default NULL,
  `in_action` tinytext,
  `enroll_cost` text,
  `enroll_count` int(10) unsigned default NULL,
  `page_count` int(10) unsigned default NULL,
  PRIMARY KEY  (`cID`),
  UNIQUE KEY `cID` (`cID`),
  KEY `cID_2` (`cID`)
) TYPE=MyISAM;



#
# Table structure for table 'grade'
#

CREATE TABLE `grade` (
  `gID` int(10) unsigned NOT NULL auto_increment,
  `userID` int(11) unsigned default NULL,
  `number_wrong` int(10) unsigned default NULL,
  `courseID` int(11) unsigned default NULL,
  `left_off` text,
  `quiz_grade` text,
  `quiz_takecount` int(10) unsigned default NULL,
  `grade_date` text,
  PRIMARY KEY  (`gID`),
  KEY `id` (`gID`)
) TYPE=MyISAM;

www.vzio.com
ASP WEB DEVELOPMENT

 

[snowboardr]

Okay I got two different statements working now... which one would be best?

strSQL = "SELECT g.gID,g.userID,g.courseID,c.cID,c.cTitle,c.cCredit,c.cQuiz FROM grade AS g LEFT JOIN course AS c ON g.courseID=c.cID AND g.userID=" & strUserIDTmp


strSQL= "SELECT grade.gID,grade.userID,grade.courseID,course.cID,course.cTitle,course.cCredit,course.cQuiz FROM grade,course WHERE grade.courseID=course.cID AND grade.userID=" www.vzio.com
ASP WEB DEVELOPMENT

 

[cwong1]

I think both queries are the same...
But if you use the first query, you could do something more (that is if you think you'll need it, if not, you can just use the second query) like getting information from the grade table even if no identical course ID was found on course table, of course, there should be an additional condition in the SQL.