Dynamic Number of Tables

[sysadmin07]

Hi,

I'm pulling data from an Access database, in which I'd like to display in tables on a CF page. The problem I'm having is that I need to generate the necessary amount of tables, based upon number of occurrences. I have a few instances where, for example, the Access table row named "test" exists twice with different data in each row (FYI, the primary key is autonumber). How can I design the code to output two tables, if two rows exist named "test" or more, if necessary?

Thanks.

 

[RobHat]

Hi not sure if I can help with this, but I will try. Do you want it to display in tables or rows in a table? ie. if test occurs twice in you access table do you want it to display it in 2 different tables on a webpage or just dynamicly display however many records in your access table have the name test???

 

[sysadmin07]

I'd like to display the data in separate tables. For example, if the primary key of the first "test" row is "1", I'd like to display that Access db row information in a table on a CF page. Ditto if there's another "test" row in the Access db with a different primary key (all on the same CF page).

Thank you

 

[RobHat]

Ok This should be pretty easy you need to use a simple query in in the top of the page:

<cfquery name="sample" datasource="YourDataSource">
SELECT *
FROM YourTable
WHERE ColumnName = Data
</cfquery>

In the body you need to create one table. Display this table however you want. and wrap it in <cfoutput> tags. This should then display that table with each record that is pulled up in your query like so:

<cfoutput query="sample">
<table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="##0066FF">
<tr>
<td width="100">#sample.ID#</td>
<td width="100">#sample.OtherInfo#</td>
</tr>
<tr>
<td width="100">#sample.OtherInfo#</td>
<td width="100">#sample.OtherInfo#</td>
</tr>
</table>
</cfoutput>

That is ok only if the data you are displaying is fixed. Ie. you are always going to be displaying items with test in the column. if you want to the data to display depending on the choice of a user then it is a bit different but not much.

Hope this helps.

Rob

 

[sysadmin07]

Thank you for the very nice write-up, Rob. I'm doing something similar to what you've listed, however, this is a page where the user edits the information contained in the database (submission takes them to a cfupdate action page). I've modified your code below to show an example of what I'm doing. If there are two or more "test" rows, no tables show. If there is one entry of "test," it presents the data correctly.

Thanks again.

<cfquery name="sample" datasource="YourDataSource">
SELECT *
FROM YourTable
WHERE ColumnName = Data
</cfquery>

<cfoutput query="sample">
<table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="##0066FF">
<tr>
<td width="100">input name="test" value="#test#"</td>
</tr>
</table>
</cfoutput>

 

[philhege]

This looks like a formatting/display problem. What happens if you do this:

<cfquery name="sample" datasource="YourDataSource">
SELECT *
FROM YourTable
WHERE ColumnName = Data
</cfquery>

<cfoutput query="sample">
<table width="200" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="##0066FF">
<tr>
<td width="100">input name="test" value="#test#"</td>
</tr>
</table>
<BR>
</cfoutput>

Also, do you intend for a textbox to render? If so, you don't have any tags around your "input" code.

Show a sample of the data returned from the query.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[sysadmin07]

Thanks! The lack of <BR> before the </cfoutput> tag was the cause of the tables not displaying properly. However, with more than one table, I am getting an error on my cfupdate page. My primary key is an autonumber and when I edit the tables and then click "Submit" (which goes to my cfupdate page), I receive an "Invalid data for CFSQLTYPE CF_SQL_INTEGER" error. I'm not even modifying the primary key, so does anyone know why this is occurring? For debugging purposes, I did check to see that each table has the correct primary key associated with it, so this is somewhat perplexing.

 

[sysadmin07]

Anyone know why I'm getting the primary key-related error in my post above? I still can't figure it out.

Thanks.

 

[RobHat]

Can you post the update code for us to see. Not fully understandingt the problem, but am maybe thinking that it is not finding a primary key???

If We could see the code maybe someone might know why you are getting the problem.

Rob

 

[sysadmin07]

Hi,

Here is the ColdFusion portion of the update page:

<cfupdate datasource="YourDataSource" tablename="YourTable">

It works with only one table, but when there are two or more tables present, I get the following error (where 1 and 2 are the primary keys of two different rows in the Access table):

Invalid data 1,2 for CFSQLTYPE CF_SQL_INTEGER

Is it because the cfupdate command is trying to update an individual primary key field with both values (1 and 2)?

Thank you.

 

[sysadmin07]

Does anyone have any ideas? I believe I know the cause for the "Invalid data 1,2 for CFSQLTYPE CF_SQL_INTEGER" error, I just can't figure out a workaround for it. When there is more than one table displayed (two in the following example), the table entries are modified, and the "submit" button is clicked (invoking my action page), the primary keys of the two rows in my Access db that the tables in my CF page are based off of trigger the error (1 is the primary key for the first table and 2 is the primary key for the second). Since the column names are identical in the Access db and the cfupdate page is attempting to update two records from two different tables, which have the same column name, is that the cause?

Thanks!