[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

CF Link / Access Database

Posted on 2003-11-17
24
Medium Priority
?
479 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
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

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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