Solved

having trouble passing variables and cfquery with a variable table name

Posted on 2004-10-28
229 Views
Last Modified: 2013-12-24
I list all the tables in a database and allow the user to select one. The next page should open that table and display the data. The variable is not passed from page 1 and page 2 was working with a hard coded table name but not a variable even if I hard code the value of the variable.

Page 1;

<form action="as400interface_CallListMaint2.html" method="get" name="pageone">
<p align="center">
Select a call list to work with<br>
<table>
<iSQLtables DBNAME="emailerdb"  Qualifier="emailers" types="TABLE" LOGIN="tax-talentdata/tony12">
<isqlfetch>
      <iwhile notalias=i_sqlempty>
            <tr><td><input type="radio" name="SelectIt" value=":3"> :3</td>
            <isqlfetch>            
            <td><input type="radio" name="SelectIt" value=":3"> :3</td>
            <isqlfetch>
            <td><input type="radio" name="SelectIt" value=":3"> :3</td>
            <isqlfetch>
            <td><input type="radio" name="SelectIt" value=":3"> :3</td></tr>
            <isqlfetch>
      </iwhile alias=i_sqlempty>
</isql>

</table>
<input type="submit" value="next">
</p>
</form>



Page 2;

<cfset CallListName = #SelectIt#>
CallListName
<cfquery name="emtable" datasource="emailerdb" dbtype="ODBC" username="tax-talentdata" password="tony12">
      SELECT *
      FROM CallListName
</cfquery>
<br>
<form action="As400interface_CallListMaint5.cfm" method="post">
<table width="100%">
      <tr>
            <td></td>
            <td><strong>Comp#</strong></td>
            <td><strong>Comp Name</strong></td>
            <td><strong>Home Email</strong></td>
            <td><strong>Work Email</strong></td>
            <td><strong>Frist Name</strong></td>
      </tr>
<cfloop query="emtable" startrow="1" endrow="#emtable.recordcount#">
      
      <tr>
            <td><input type="checkbox" name="addtocart" value="<cfoutput>#Use_Flag#</cfoutput>" checked="checked"></td>
            <td><cfoutput>#Company_Number#</td>
            <td>#Company_Name#</td>
            <td>#Home_Email#</td>
            <td>#Work_Email#</td>
            <td>#First_Name#</cfoutput></td>
      </tr>      

      
</cfloop>
</table>
<br>
<table width="300">
      <tr>
            <td><input type="submit" name="Submit" value="Send Mailer"></td>
            <td><input type="submit" name="Submit" value="Save As Call List"></td>
      </tr>

</table>
</form>
0
Question by:larksys
    7 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    Hi larksys,

    It would be very helpful if you would post the error message(s) that you are getting! Also, you are submitting the form to an HTML file - I am assuming that is mapped to ColdFusion Server or it's not going to do anything!

    First, just to make sure that the variable passing is working, do a <cfdump var="#SelectIt#"> on top of page 2. If it prints the table name you expect, the next thing to fix is you query itself

    <cfdump var="#SelectIt#"> <!--- this is for debugging only and can be deleted later --->
    <cfset CallListName = #SelectIt#>
    ...
    <cfquery name="emtable" datasource="emailerdb" dbtype="ODBC" username="tax-talentdata" password="tony12">
         SELECT *
         FROM #CallListName#
    </cfquery>

    One thing that is very dangerous in the way you are doing this is that someone could put arbitrary SQL in this query simply by doing page2.cfm?SelectIt=sysindexes;drop database master (this example is contrived). If you know that your table names only contain letter, for example, you may want to play it save with something like this:

    <cfquery name="emtable" datasource="emailerdb" dbtype="ODBC" username="tax-talentdata" password="tony12">
         SELECT *
         FROM #reReplace(CallListName, '[^a-zA-Z]+', '', 'all')#
    </cfquery>


    Good luck!
    0
     
    LVL 1

    Author Comment

    by:larksys
                                      Call List Maintenance

                      CallListName SELECT * FROM CallListName

                Comp#                Comp Name        Home Email      Work Email      Frist Name
     #Company_Number# #Company_Name# #Home_Email# #Work_Email# #First_Name#


    This is the results from page 2 after putting in the cfdump code (plus a couple of buttons at the bottum). You'll notice that there is no error code and the variable CallListName comes out as a literal and the SQL select statement prints as a literal.

     
    0
     
    LVL 1

    Author Comment

    by:larksys
    You'll notice that this code is a cludge of html, iHtml, cold fusion, and SQL. Below is an iHtml heapdump of the page and SelectIt=conspec3 which is correct.

    Thread Local Heap Dump


    Inline Internet Systems iHTML/2.20.500 Enterprise
    i_priv_srv : SBLOCK
    i_path : D:\usersites\taxtalent2\htdocs\admin\as400interface_CallListMaint2.html
    i_query_string : SelectIt=conspec3
    i_priv_ka : true
    i_http_HTTP_CONTENT_LENGTH : 0
    i_http_HTTP_ACCEPT : */*
    i_http_HTTP_ACCEPT_ENCODING : gzip, deflate
    i_http_HTTP_ACCEPT_LANGUAGE : en-us
    taxtalentv : 510161019892523-V2.0-A07470-L220
    i_hostname : www.taxtalent.com
    i_http_HTTP_REFERER : http://www.taxtalent.com/admin/as400interface_calllistmaint1.html
    i_http_HTTP_USER_AGENT : Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
    i_priv_post :
    i_priv_tcall : 197
    i_key_expiry : Never
    SelectIt : conspec3
    IHTML_PRIV_RECURSE : true
    0
     
    LVL 5

    Accepted Solution

    by:
    i think the problem might be as simple as you not having CFOUTPUT statements and #'s around your variables...OR maybe its just too late for me. :P

    try the following:


    <CFOUTPUT>
    <cfset CallListName = #SelectIt#>
    #CallListName#
    <cfquery name="emtable" datasource="emailerdb" dbtype="ODBC" username="tax-talentdata" password="tony12">
         SELECT *
         FROM #CallListName#
    </cfquery>
    <br>
    <form action="As400interface_CallListMaint5.cfm" method="post">
    <table width="100%">
         <tr>
              <td></td>
              <td><strong>Comp Num</strong></td>
              <td><strong>Comp Name</strong></td>
              <td><strong>Home Email</strong></td>
              <td><strong>Work Email</strong></td>
              <td><strong>Frist Name</strong></td>
         </tr>
    <cfloop query="emtable" startrow="1" endrow="#emtable.recordcount#">
         
         <tr>
              <td><input type="checkbox" name="addtocart" value="#Use_Flag#" checked="checked"></td>
              <td>#Company_Number#</td>
              <td>#Company_Name#</td>
              <td>#Home_Email#</td>
              <td>#Work_Email#</td>
              <td>#First_Name#</cfoutput></td>
         </tr>    

         
    </cfloop>
    </table>
    <br>
    <table width="300">
         <tr>
              <td><input type="submit" name="Submit" value="Send Mailer"></td>
              <td><input type="submit" name="Submit" value="Save As Call List"></td>
         </tr>

    </table>
    </form>
    </CFOUTPUT>
    0
     
    LVL 1

    Author Comment

    by:larksys
                                     Call List Maintenance

                 #CallListName# SELECT * FROM #CallListName#

         Comp#                    Comp Name         Home Email       Work Email        Frist Name
     #Company_Number# #Company_Name# #Home_Email# #Work_Email# #First_Name#


    No such luck (not that it doesn't usually comes down to something simple). But I do think it might be late. I'm going to play with a little more then hit the rack.
    0
     
    LVL 1

    Author Comment

    by:larksys
    Ok, I guess I need to limit my work to daylight hours. The page name was .html and should have been .cfm and I should have caught it a lot sooner.

    Thanks for making me look closer.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    I was pointing that out in my first response, why did you not accept that?
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    913 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

    13 Experts available now in Live!

    Get 1:1 Help Now