Solved

CF Link / Access Database

Posted on 2003-11-17
24
419 Views
Last Modified: 2013-12-24
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
Comment
Question by:mflagstad
  • 10
  • 5
  • 4
  • +3
24 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9768740
what's the error?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9768962
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9769265
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9769289
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9770611
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9770727
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
 
LVL 17

Expert Comment

by:anandkp
ID: 9770758
Does it return some records !

Chk what "getinfoa.recordcount" returns !
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9770785
It doesn't return any records, it is just a blank page, Where do I put getinfoa.recordcount at in the code?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9770839
<cfquery name="getinfoa" datasource="kidwell">
         Select name, lot, many, [date], cb
         FROM peopleA
         WHERE name = '#URL.name#'
</cfquery>

<cfoutput>
#getinfoa.recordcount#
</cfoutput>
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9770906
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9770924
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9771759
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
Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

 
LVL 19

Expert Comment

by:cheekycj
ID: 9772707
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9773951
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9773965
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 9773996
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9774160
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 9774176
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9774352
<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
 
LVL 1

Author Comment

by:mflagstad
ID: 9774364
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
 
LVL 19

Accepted Solution

by:
cheekycj earned 200 total points
ID: 9775343
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
 
LVL 17

Expert Comment

by:anandkp
ID: 9776350
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
 
LVL 1

Author Comment

by:mflagstad
ID: 9778666
Thank you all for helping me with this problem. CJ, your answer worked great! Thanks Again!
Michelle
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9778973
Glad to help and Thnx for the "A"

CJ
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 is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

706 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