• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

CF Link / Access Database

Ok,

1st page is layout.cfm
It lists in tables, the names of people
The Access database sorts these people by name, lot, block and section.
There is one section, 40 blocks, 8 lots per block, 2 people per lot.
The fields are id1, name, lot, block

I can make the names appear in their proper order. On this page all I want are the names.

2nd page is lookup.cfm
The next page, (which each name above is linked to) should appear their individual information (section, lot, block, date, etc.)
How do I format the link to show the individuals information?
This is what I have tried so far...


layout.cfm
<cfoutput query="getinfoa"> <cfif name neq "">
<a href="lookup.cfm?name=#name#">#name#, #lot#</a>
<cfelse>
EMPTY LOT</cfif></cfoutput>
</td>
            </tr>
          </table>
        </div>
      </td>
      <td><div align="left">
          <table border="3" cellpadding="0" cellspacing="0">
            <tr>
              <td>2  <!--Block 1-->
<cfquery name="getinfoa" datasource="kidwell">
              select  name,lot,block
                  from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
                  WHERE division = 2
                   
            
            
      </cfquery>
                  
      <cfoutput query="getinfoa"> <cfif name neq "">
#name#
<cfelse>
EMPTY LOT</cfif></cfoutput>

lookup.cfm
<cfquery name="getinfoa" datasource="kidwell">
            Select id, name, lot, many, date, cb
            FROM from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
            WHERE name = #name#;
            
            
      </cfquery>
                  
      <cfoutput query="getinfoa">
<table border="1" width="600" style="border-collapse: collapse" bordercolor="111111" cellpadding="0" cellspacing="0"><TR>
  <td width="30">#ID#</td><TD width="250">
#name#</TD><TD width="250"> #lot#</TD><TD width="30">
#many#,#date#,#cb#</TD></TR></TABLE>
</cfoutput>

I keep on getting an error...can someone help me here?
thanks a lot!
michelle
0
mflagstad
Asked:
mflagstad
  • 10
  • 5
  • 4
  • +3
1 Solution
 
jyokumCommented:
what's the error?
0
 
anandkpCommented:
If everythings going fine ... then i suppose the reserved word "DATE" in ur query as one of ur column names - cld be the reason for error ...

just  a guess

let us know more abt it - so we can try & help u !

K'Rgds
Anand
0
 
Renante EnteraCommented:
Yah! I agree with anandkp.  The cause of error is your fieldname "date" which is a reserved word since you are using an Access for your database. Unless if you are using Sybase or SQL, it will not matter.

Better change it with another fieldname or you may try enclose it with a bracket.

So, instead of :

      Select id, name, lot, many, date, cb

It will be :

      Select id, name, lot, many, [date], cb

Goodluck!
eNTRANCE2002 :-)
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
Renante EnteraCommented:
Another thing is that, take note for the datatype of your field/column in your database.

Be sure that when you have a conditional statement such as :

  where name = '#name#'

Basing from your query, you have a line with the same concept as above but be sure that you have a single quote or quotation marks in between your cf variable if the column you are comparing to is a string not an integer.

Regards!
eNTRANCE2002 :-)
0
 
mflagstadAuthor Commented:
This is the error I am getting:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (12:1) to (12:46).


Date/Time: 11/18/03 07:11:38
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Remote Address: 216.176.166.6
HTTP Referrer: http://www.blah.com/layout1.cfm
Query String: name=George%20P.%20Harris

I posted the code earlier, so you should see what this is talking about. I am going to change the date field real quick and see what happens from there.
0
 
mflagstadAuthor Commented:
Ok, I changed the code to this on the lookup.cfm page:
<cfquery name="getinfoa" datasource="kidwell">
            Select name, lot, many, [date], cb
            FROM peopleA
            WHERE name = '#URL.name#'
            
            
      </cfquery>
                  
      <cfoutput query="getinfoa">
<table border="1" width="600" style="border-collapse: collapse" bordercolor="111111" cellpadding="0" cellspacing="0"><TR>
  <td width="30"></td><TD width="250">
#name#</TD><TD width="250"> #lot#</TD><TD width="30">
#many#,#date#,#cb#</TD></TR></TABLE>
</cfoutput>

But when I test, all I get is a blank page! Nothing is there...any suggestions?
0
 
anandkpCommented:
Does it return some records !

Chk what "getinfoa.recordcount" returns !
0
 
mflagstadAuthor Commented:
It doesn't return any records, it is just a blank page, Where do I put getinfoa.recordcount at in the code?
0
 
anandkpCommented:
<cfquery name="getinfoa" datasource="kidwell">
         Select name, lot, many, [date], cb
         FROM peopleA
         WHERE name = '#URL.name#'
</cfquery>

<cfoutput>
#getinfoa.recordcount#
</cfoutput>
0
 
mflagstadAuthor Commented:
Now it's only showing a zero '0'
It seems we're getting somewhere bc now it's showing at least something...how can i make it show all their info?
0
 
mflagstadAuthor Commented:
tried this in code:
<cfoutput>
#getinfoa.recordcount#
#name#,#lot#,#many#,#date#,#cb#.
</cfoutput>

and the name appears, but then i get an error like this below it:
Error Diagnostic Information

An error occurred while evaluating the expression:


#lot#



Error near line 37, column 9.
--------------------------------------------------------------------------------

Error resolving parameter LOT


ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.


The error occurred while processing an element with a general identifier of (#lot#), occupying document position (37:8) to (37:12).


Date/Time: 11/18/03 08:07:04
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Remote Address: 216.176.166.6
HTTP Referrer: http://www.blah.com/layout1.cfm
Query String: name='Thomas%20Harris'
0
 
James RodgersWeb Applications DeveloperCommented:
unles the output is in a cfloop/cfoutput with a specified query you need to be explicit in you query variable calls

<cfoutput>
#getinfoa.recordcount#
#getinfoa.name#,#getinfoa.lot#,#getinfoa.many#,#getinfoa.date#,#getinfoa.cb#.
</cfoutput>
0
 
cheekycjCommented:
Try this:

<cfquery name="getinfoa" datasource="kidwell">
          Select name, lot, many, [date], cb
          FROM peopleA
          WHERE name = '#URL.name#'
     </cfquery>
<cfif getinfoa.recordcount gt 0>              
<table border="1" width="600" style="border-collapse: collapse" bordercolor="111111" cellpadding="0" cellspacing="0">
     <cfoutput query="getinfoa">
<TR>
  <td width="30"></td><TD width="250">
#name#</TD><TD width="250"> #lot#</TD><TD width="30">
#many#,#date#,#cb#</TD></TR>
</cfoutput>
</TABLE>
</cfelse>
   Sorry your search for <cfoutput>#URL.name#</cfoutput> did not return any results.
</cfif>

Can you tell us what shows?

I think people are right that your query is not returning any results.  Your code is fine.

CJ
0
 
mflagstadAuthor Commented:
CJ, the code should look like this below, but it does not return any results at all...I have no idea what I am doing wrong here!! This is my query:
<cfquery name="getinfoa" datasource="kidwell">
            Select *
            FROM peopleA      
            WHERE name = '#URL.name#'
            
            
      </cfquery>

If I ask it to return the #name#, it will appear on the screen. However, if I ask any more info, like #lot#, it says the following error message:
ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.

But the 'lot' field definatley exists in the table, so what is the problem here! I am going insane with this:) Also, Jester's response doesn't work either, it just shows a blank screen.



<cfoutput query="getinfoa"> <cfif getinfoa.recordcount gt 0>  
<TR>
  <td width="30"></td><TD width="250">
#name#</TD><TD width="250"> #lot#</TD><TD width="30">
#many#,#date#,#cb#</TD></TR>
</TABLE>
<cfelse>
   Sorry your search for #URL.name# did not return any results.
</cfif></cfoutput>
0
 
mflagstadAuthor Commented:
Do you think the original query on the layout page is causing the problem?
<cfquery name="getinfoa" datasource="kidwell">
              select  name,lot,block
               from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
               WHERE division = 2          
     </cfquery>

Maybe the relationships are screwy? Any thoughts on this?
0
 
cheekycjCommented:
see #name# isn't referring to the query.  CF checks all scopes.  It isn't matching it to getinfoa.name but url.name since you have a var defined like that.  That is why you are seeing the name.  The query itself is not returning any rows.

I can't tell you if your relationships are screwy unless you post your full schema /db design.

are you trying to find an exact match of a name or searching on name or what?

CJ
0
 
mflagstadAuthor Commented:
In the peopleA table, there are several fields: name, division, description, lot, many, date, cb
I have one page that lists all the names which are linked. When you click on the name, it should take you to the lookup.cfm
page which would have all the rest of the info associated with the name. This is the problem I am having, I can get the name to show
up, but not the rest of the info...
0
 
cheekycjCommented:
is name your key don't you have like a id or user_id or account_id or emp_id associated with each name?

CJ
0
 
James RodgersWeb Applications DeveloperCommented:
<cfquery name="getinfoa" datasource="kidwell">
          Select id, name, lot, many, date, cb
          FROM from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
          WHERE name = #name#;
     </cfquery>

do you get anything returned from this query?
have you tried it with a set name like 'john smith'
what about quoting the name var it is a string

<cfquery name="getinfoa" datasource="kidwell">
          Select id, name, lot, many, date, cb
          FROM from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
          WHERE name = '#name#';
     </cfquery>

0
 
mflagstadAuthor Commented:
Table:PeopleA
fields are: name, division, description, id (autonumber), lot, many, date, cb
Table: SectionA
fields are: ID1, ID (PK), Block

Relationship is division to ID
0
 
cheekycjCommented:
You should do this then:

Change your query getinfoA in layout.cfm to get the peopleA.ID column and change lookup.cfm to retrieve based on that not name.

so your query becomes:
<cfquery name="getinfoa" datasource="kidwell">
              select  name,lot,block, peopleA.ID
               from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
               WHERE division = 2
     </cfquery>

Here is what it should loook like:

layout.cfm
<cfquery name="getinfoa" datasource="kidwell">
              select  name,lot,block, peopleA.ID
               from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
               WHERE division = 1
     </cfquery>
<cfoutput query="getinfoa"> <cfif name neq "">
<a href="lookup.cfm?id=#id#">#name#, #lot#</a>
<cfelse>
EMPTY LOT</cfif></cfoutput>
</td>
            </tr>
          </table>
        </div>
      </td>
      <td><div align="left">
          <table border="3" cellpadding="0" cellspacing="0">
            <tr>
              <td>2  <!--Block 1-->
<cfquery name="getinfoa" datasource="kidwell">
              select  name,lot,block, peopleA.ID
               from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
               WHERE division = 2
     </cfquery>
               
     <cfoutput query="getinfoa"> <cfif name neq "">
#name#
<cfelse>
EMPTY LOT</cfif></cfoutput>

lookup.cfm
<cfquery name="getinfoa" datasource="kidwell">
          Select id, name, lot, many, date, cb
          FROM from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
          WHERE id = #URL.ID#;        
     </cfquery>
               
     <cfoutput query="getinfoa">
<table border="1" width="600" style="border-collapse: collapse" bordercolor="111111" cellpadding="0" cellspacing="0"><TR>
  <td width="30">#ID#</td><TD width="250">
#name#</TD><TD width="250"> #lot#</TD><TD width="30">
#many#,#date#,#cb#</TD></TR></TABLE>
</cfoutput>
     <cfoutput query="getinfoa"> <cfif name neq "">
#name#
<cfelse>
EMPTY LOT</cfif></cfoutput>

See if that works.

CJ
0
 
anandkpCommented:
Michelle - if the recordcount is 0 - that means - u need to chk ur query & make sure u do necessary chnages to it - so that it returns the records [if they exists in the DB & match the "where" clause in ur query]

I am sure if u get ur query right ... rest all can be easily managed by U :)
0
 
mflagstadAuthor Commented:
Thank you all for helping me with this problem. CJ, your answer worked great! Thanks Again!
Michelle
0
 
cheekycjCommented:
Glad to help and Thnx for the "A"

CJ
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now