?
Solved

CF Link / Access Database

Posted on 2003-11-17
24
Medium Priority
?
471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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
 
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 800 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

765 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