Solved

Getting all record ID in a single table row instead of one record ID

Posted on 2011-02-18
15
351 Views
Last Modified: 2012-08-13
Okay, here's the problem.

I have a table that lists all the classes an employee needs to take.
The cfquery classes pulls out what classes are due for the employee  
and the table displays them.

Now I have an onclick event set to open a CFWindow when a person clicks on the row that works fine, however I get an error because the URL.ID that is sent is all of the class IDs and not just the ID of the class in that specific row.


Part of the error message that is relevant.

URL - struct
ID      2,19,18,9,10,12,6,8,11,17,7,13,14
_cf_clientid      FB7DCF3EB9543C2D3191FFD823A49DA8
_cf_containerId      cw2_body
_cf_nocache      true
_cf_nodebug      true
_cf_rc      4



Page code.

<link rel="stylesheet" type="text/css" href="../../css/main.css">
<script type="text/javascript" src="../../includes/sortable.js"> </script>
<cfinclude template="config.cfm">
<!--- query to get the training ID of the person logged in. --->
<cfquery name="getMyClasses" datasource="#DSN#">
SELECT *
FROM dbo.Contacts
WHERE EDIPI =  '123456789'<!---'#SESSION.UserID#'--->
</cfquery>
<!--- query to get uncompleted classes for the person logged in. --->
<CFQUERY NAME="classes" DATASOURCE="#DSN#" >
select sub1.personnel, sub1.conid, sub2.reqdate, sub1.course_name, sub1.id, sub1.description, sub1.date, sub1.attendees
from (SELECT c.personnel, c.id as conID,tc.*
FROM dbo.training_courses tc, dbo.contacts c
WHERE  tc.date > <!---getdate()---> '6/1/2009' ) as sub1
left outer join (select * from usertraining where completed = 'yes') as sub2
on sub1.conid = sub2.contactid and sub1.id = sub2.courseid
where (sub2.id is null or sub2.completed != 'yes') and sub1.conid in (202,18,155,1,253,254) and sub1.conid = '#trim(getMyClasses.ID)#'
 ORDER BY sub1.date
</CFQUERY>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
  <table width="700" border="0" cellspacing="2" cellpadding="2" style="margin-top:10px; margin-left:10px; margin-right:10px; margin-bottom:10px;" class="sortable" id="allclasses">
    <tr style="background-color:#F5F5F5; border:1px solid #666666;" height="20px">
      <td><span style="font-weight:bold; color:#000000;">Course Name</span></td>
      <td><span style="font-weight:bold; color:#000000;">Description</span></td>
      <td><span style="font-weight:bold; color:#000000;">Due Date</span></td>
      <td><span style="font-weight:bold; color:#000000;">Course Type</span></td>
    </tr>
    <cfoutput query="classes"> 
      <tr onmouseover="style.backgroundColor='##D5EDB3';" onmouseout="style.backgroundColor='##FFF'" onclick="javascript:ColdFusion.Window.show('cw2')" >
   <td style="display:none;"><input type="hidden" name="classID" value="#classes.ID#" /></td>
        <td height="18px"> #classes.course_name#</td>
        <td height="18px">#classes.description#</td>
        <td height="18px">#DateFormat(classes.date, "mm/dd/yyyy")#</td>
        <td height="18px"><cfif isDefined('classes.attendees') AND classes.attendees EQ '1'>All<cfelseif classes.attendees EQ '2'>All<cfelseif classes.attendees EQ '3'>Non-Supervisor<cfelseif classes.attendees EQ '4'>this<cfelseif classes.attendees EQ '5'>1102<cfelseif classes.attendees EQ '6'>110x</cfif></td>
      </tr>
	  </cfoutput>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
</fieldset>

<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />

Open in new window

0
Comment
Question by:rrattie
  • 9
  • 6
15 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 34927299
wow, that's a very interesting query using multiple subqueries instead of simple joins.

My first observation is on this inner query..

         SELECT c.personnel, c.id as conID,tc.*
         FROM dbo.training_courses tc, dbo.contacts c
         WHERE  tc.date > '6/1/2009'

There is nothing joining the training courses with the contacts so you are matching each training record with EVERY contact... you need a join

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34927356
Tracing back the #classes.ID# value that you're talking about, it seems to come from the CONTACTS table.   It is the ID field of the CONTACTS table; which I am guessing is actually the course ID because of the join.

BIG TIP:  You really need to name your IDs after the name of the table.  For example, the contact's table primary key would be CONTACT_ID, and for the table COURSES it would be COURSE_ID.  Then in EVERY TABLE they appear, keep the name the SAME.   So that you don't have...

on sub1.conid = sub2.contactid and sub1.id = sub2.courseid

But instead you have...

 on Sub1.ContactID = sub2.ContactID
and sub1.CourseID  = Sub2.CourseID

I think they query needs to be re-written, will try to give you an example..
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34927510
ok, here's a try at the query...  It's hard to follow

But my guess is that you don't need the "getMyClasses" query at all because you can just use that criteria in the main query (I added WHERE EDIPI =  '123456789' into this query)

If it's not too late, I would pause and give us the table structure.  I think you should rework it a bit.   Is a Contact the same as a User ?

select c.personnel
     , c.ID as contactID
     , tc.reqdate
     , tc.course_name
     , tc.description
     , tc.attendees
     , tc.date
From dbo.contacts c 
  left join usertraining ut on ut.contactid = c.ID
                        and completed = 'yes'
  left join dbo.training_courses tc on ut.courseID  = tc.ID
where tc.date > '6/1/2009'
and  (ut.id is null or ut.completed != 'yes') 
and   c.ID in (202,18,155,1,253,254) 
and   c.EDIPI =  '123456789'

Open in new window

0
 
LVL 2

Author Comment

by:rrattie
ID: 34928139
The contact table was created a while ago and is the core of the intranet website (for login, user info, etc..)

This piece I am working on with training courses was a recent request.

We are dealing with 3 tables in this query.
contacts (primary source for user details)
training_courses (source of course info)
usertraining (source of user training details, as in what classes they have taken and when)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34931566
Ok, so I guess the structure has to stay as it is..

So did the query work or do you have to tweak it?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34938376
did that query work?
0
 
LVL 2

Author Comment

by:rrattie
ID: 34951751
After I changed the query, I get the error:

Bind Failed, Element not found: classID
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 39

Expert Comment

by:gdemaria
ID: 34952078

This it the tag with the bind...

<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />
Toggle


The classID is looking for a field named classID.   It has nothing to do with the query.

Do you have a field (cfinput tag, cfselect tag) named classID?
0
 
LVL 2

Author Comment

by:rrattie
ID: 34953598
Yeah, I have a cfinput tag named classID..

Here is my updated code.
<cfquery name="classes" datasource="#DSN#">


select c.personnel
     , c.ID as contactID
     , tc.course_name
     , tc.description
     , tc.attendees
     , tc.date
From dbo.contacts c 
  left join usertraining ut on ut.contactid = c.ID
                        and completed = 'yes'
  left join dbo.training_courses tc on ut.courseID  = tc.ID
where tc.date > '6/1/2009'
and  (ut.id is null or ut.completed != 'yes') 
and   c.ID in (202,18,155,1,253,254) 
and   c.EDIPI =  '123456789'

</cfquery>
<cfajaximport tags="CFWINDOW, CFGRID, CFFORM">
</head>
<body>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
<table width="700" border="0" cellspacing="2" cellpadding="2" style="margin-top:10px; margin-left:10px; margin-right:10px; margin-bottom:10px;" class="sortable" id="allclasses" background="##FFFFFF">
  <tr style="background-color:#F5F5F5; border:1px solid #666666;" height="20px">
    <td><span style="font-weight:bold; color:#000000;">Course Name</span></td>
    <td><span style="font-weight:bold; color:#000000;">Description</span></td>
    <td><span style="font-weight:bold; color:#000000;">Due Date</span></td>
    <td><span style="font-weight:bold; color:#000000;">Course Type</span></td>
  </tr>
  <cfoutput query="classes">
    <tr onmouseover="style.backgroundColor='##D5EDB3';" onmouseout="style.backgroundColor='##FFF'" onclick="javascript:ColdFusion.Window.show('cw2')">
      <td height="18px"><cfform>
          <cfinput type="hidden" name="classID" value="#ID#">
        </cfform>
        #course_name#</td>
      <td height="18px">#description#</td>
      <td height="18px">#DateFormat(date, "mm/dd/yyyy")#</td>
      <td height="18px"><cfif isDefined('attendees') AND attendees EQ '1'>
          All
          <cfelseif attendees EQ '2'>
          All
          <cfelseif attendees EQ '3'>
          Non-Supervisor
          <cfelseif attendees EQ '4'>
          this
          <cfelseif attendees EQ '5'>
          1102
          <cfelseif attendees EQ '6'>
          110x
        </cfif></td>
    </tr>
  </cfoutput>
</table>
</fieldset>
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />
</body>
</html>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34953709

I'm not understanding how your form is working.   You have a form inside the loop, so your web page will have X number of forms in it, all with a hidden field classID.   Since the hidden field cannot change (because it's hidden), there is no sense in binding to it.   The CFWINDOW cannot find the classID field, because it is not unique, there are many of them, all in different forms.

Are you trying to make it so that if a person clicks one of the courses the cfwindow will change to show the detail of that class?
0
 
LVL 2

Author Comment

by:rrattie
ID: 34953736
Yes that is what I am trying to do.

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 34953987
You should use ColdFusion.navigate for this, not bind.

See the link around the class name, it will send the URL to the CFWINDOW whenever you click a different class

<cfquery name="classes" datasource="#DSN#">
select c.personnel
     , c.ID as contactID
     , tc.course_name
     , tc.description
     , tc.attendees
     , tc.date
From dbo.contacts c 
  left join usertraining ut on ut.contactid = c.ID
                        and completed = 'yes'
  left join dbo.training_courses tc on ut.courseID  = tc.ID
where tc.date > '6/1/2009'
and  (ut.id is null or ut.completed != 'yes') 
and   c.ID in (202,18,155,1,253,254) 
and   c.EDIPI =  '123456789'
</cfquery>
</head>
<body>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
<cfform>
<table width="700" border="0" cellspacing="2" cellpadding="2" style="margin-top:10px; margin-left:10px; margin-right:10px; margin-bottom:10px;" class="sortable" id="allclasses" background="##FFFFFF">
  <tr style="background-color:#F5F5F5; border:1px solid #666666;" height="20px">
    <td><span style="font-weight:bold; color:#000000;">Course Name</span></td>
    <td><span style="font-weight:bold; color:#000000;">Description</span></td>
    <td><span style="font-weight:bold; color:#000000;">Due Date</span></td>
    <td><span style="font-weight:bold; color:#000000;">Course Type</span></td>
  </tr>
  <cfoutput query="classes">
    <tr onmouseover="style.backgroundColor='##D5EDB3';" onmouseout="style.backgroundColor='##FFF'" onclick="javascript:ColdFusion.Window.show('cw2')">
      <td height="18px">
        <a href="##" onclick="ColdFusion.navigate('training/classes/classInfo.cfm?ID=#ID#','cw2');return false;">#course_name#</a>
      </td>
      <td height="18px">#description#</td>
      <td height="18px">#DateFormat(date, "mm/dd/yyyy")#</td>
      <td height="18px"><cfif isDefined('attendees') AND attendees EQ '1'>
          All
          <cfelseif attendees EQ '2'>
          All
          <cfelseif attendees EQ '3'>
          Non-Supervisor
          <cfelseif attendees EQ '4'>
          this
          <cfelseif attendees EQ '5'>
          1102
          <cfelseif attendees EQ '6'>
          110x
        </cfif></td>
    </tr>
  </cfoutput>
</table>
</cfform>
</fieldset>
<cfajaximport tags="CFWINDOW, CFGRID, CFFORM">
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="true" bodyStyle="background-color: white;">
</body>
</html>

Open in new window

0
 
LVL 2

Author Comment

by:rrattie
ID: 34954611
Now I get no errors and no table shows either. (when I put in a valid EDIPI #)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34954724
Then you need to play with your query.  As I said, based on your original query, it was very hard to determine what it was supposed to be.. so mine is a total guess.

If you can state in words what it should do, that may help.


select c.personnel
     , c.ID as contactID
     , tc.course_name
     , tc.description
     , tc.attendees
     , tc.date
From dbo.contacts c
  left join usertraining ut on ut.contactid = c.ID
                        and completed = 'yes'
  left join dbo.training_courses tc on ut.courseID  = tc.ID
where tc.date > '6/1/2009'
and  (ut.id is null or ut.completed != 'yes')
and   c.ID in (202,18,155,1,253,254)
and   c.EDIPI =  '123456789'

This query will return all contacts where CONACT.ID is one of these 202,18,155,1,253,254
and the CONTACT.EDIPI number is 123456789

The user must have a completed USERTRAINING entry associated with a TRAINING_SOURCE whose date is after June 1, 2009


What you can do is put that query directly against the database and try modifying it until it returns records and see what was needed to change.

Perhaps there are no recrods after 6/1/09, for example
0
 
LVL 2

Author Comment

by:rrattie
ID: 34961191
I put the 'old' set of queries back in with the rest of the new code and it works now.

The query was supposed to get all classes that a user hasn't taken yet that are due after a certain date.
When it goes live the 6/1/09 will just be the current date, but I'm working with sample data at the moment.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now