• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Need help linking from static page to db record

Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

I'm having trouble linking from an ordinary <a href="description.cfm?recordid=A51">Select</a> link to an action page with an SQL query of

select * from classes where recordid="....." I don't know what to put in here.

I've tried '#rsClasses.recordid#' but that just gives me the first record in the database. What I want to bring in is the A25 that I've identified in the link. I have about 30 links I'd like to identify myself rather than do a database search for them. In an order version of Dreamweaver I was able to make that static link connection using a parameter like: <cfparam name="rsOperas_TM_progid" default="#progid#"> with a query of <cfquery name="rsOperas" datasource="#MM-cnOne_DSN#"> SELECT * from operas WHERE progid = #rsOperas_TM_progid# </cfquery> Everything worked fine then but I can't get it to work with my new page. What should I be putting in my original select statement? I can't just use URL.recordid because I don't want to do a search. I want to link directly.

Thanks.

Views

869

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

#url.recordID#

<cfqueryparam value="#url.recordID#" cfsqltype="cf_sql_intenger"> would be even better.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

I changed my setup to:

<cfquery name="rsClass2" datasource="Holly" >

SELECT recordid, sortorder, classname FROM classes

WHERE recordid = #url.recordID#

<cfqueryparam value="#url.recordID#">

</cfqueryparam>

</cfquery>

about a dozen different ways and NOW I get....

SELECT recordid, sortorder, classname FROM classes WHERE recordid = A51  (param 1)

as an error.

Any ideas?

Thanks.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

Also, the recordid field isn't an integer. It's a text (string) field. There is a different field for the actual id field for the table. Hope that helps.

Thanks again.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

Yes, I did get an error message that the <cfqueryparam> needed a closing tag. And it asked to be nestled within the <cfquery> tag. That's why after about 20 tries I set it up that way.

What I'm still having problems with is getting the correct record to show up. I've identified which recordid I want (a regular column) in my link , but I'm still not getting that specific record using this:

SELECT recordid, sortorder, classname FROM classes

WHERE recordid = #url.recordID#

<cfqueryparam value="#url.recordID#">

</cfqueryparam>

</cfquery>

Why would CF return "WHERE recordid = A51 (param 1)?

Is it expecting more params?

It has the param I want. Why won't it return that record?

Thanks


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

<cfqueryparam....> is not a block tag, it does not need a closing </cfqueryparam> tag.  I don't know if this would cause an error, I've never tried to do that.

Then just use the proper cf_sql... type.  I.E "cf_sql_char" or "cf_sql_varchar" or something.  See the documentation for the full list.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

Use the syntax Ian suggested in his first reply.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

OK, Dan. I did that. I used the simplest setup I could:

<cfparam name="#url.recordID#" default="1">

<cfquery name="rsClass2" datasource="Holly">

SELECT recordid, sortorder, classname

FROM classes

WHERE recordid = #url.recordID#</cfquery>

And the cold fusion response was:

Column not found: Unknown column 'A51' in 'where clause'

IT shows the query was:

SQL

   SELECT recordid, sortorder, classname FROM classes WHERE recordid = A51

Why is it saying the "column" is named A51? That's the recordid in the table classes.

Remember my link is: <a href="description.cfm?recordid=A51">Select this link</a>

Please help me.

Thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 04, 2010 May 04, 2010

Copy link to clipboard

Copied

I found the answer somewhere else. It would have been so easy for you to tell me to put single quote marks around  '#url.recordID#' in my select statement. That's all it needed. And there isn't any such thing as <cfqueryparam> tags, so that threw me too. It's either <cfparam> or <cfquery> I've only wasted about 5 hours on this.

Thanks anyway.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 05, 2010 May 05, 2010

Copy link to clipboard

Copied

The non-existant <cfqueryparam....> tag, found in about three seconds with this internet search: [http://www.google.com/search?q=cfqueryparam]

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html

cfqueryparam

Description

Verifies the data type of a query parameter and, for DBMSs that  support bind variables, enables ColdFusion to use bind variables in the  SQL statement. Bind variable usage enhances performance when executing a  cfquery statement multiple times.

This  tag is nested within a cfquery tag,  embedded in a query SQL statement. If you specify optional parameters,  this tag performs data validation.

Adobe recommends that you use  the cfqueryparam tag within every cfquery tag, to help secure your databases from  unauthorized users. For more information, see Security Bulletin  ASB99-04, "Multiple SQL Statements in Dynamic Queries," at www.adobe.com/devnet/security/security_zone/asb99-04.html,  and "Accessing  and Retrieving Data" in the ColdFusion Developer's Guide.

Category

Database  manipulation tags

Syntax

<cfquery 
    name = "query name"
    dataSource = "data source name"
    ...other attributes...
    SQL STATEMENT column_name =
    <cfqueryparam value = "parameter value"
        CFSQLType = "parameter type"
        list = "yes|no"
        maxLength = "maximum parameter length"
        null = "yes|no"
        scale = "number of decimal places"
        separator = "separator character">
    AND/OR ...additional criteria of the WHERE clause...>
</cfquery>

Note that there is no closing tag in the <cfquerparam...> syntax sample.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 05, 2010 May 05, 2010

Copy link to clipboard

Copied

LATEST

Additionally, if you use <cfqueryparam> (which you definitely should) I don't think you'd even need the single quotes.  The tag formats it for you.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation