having trouble passing variables and cfquery with a variable table name

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>
LVL 1
larksysAsked:
Who is Participating?
 
kkhippleConnect With a Mentor Commented:
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
 
sigmaconCommented:
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
 
larksysAuthor Commented:
                                  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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
larksysAuthor Commented:
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
 
larksysAuthor Commented:
                                 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
 
larksysAuthor Commented:
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
 
sigmaconCommented:
I was pointing that out in my first response, why did you not accept that?
0
All Courses

From novice to tech pro — start learning today.