Problem with multipe update in coldfusion

>  SELECT * FROM #url.tableName#

Putting aside your question for a moment .. do you realize that code is dangerous? Even if you are using Access.  It allows anyone to pass in whatever table name they want in the url. Then your code would not only give them access to the information, but allow them to modify it too.  For example, someone could pass in the name of your "user" table and proceed to steal everyone's password.

I understand Dude, Well this is to be modified later if it needs to be

Thanks Dude, But how we do multiple update with first 50 rows and then 50 rows. i know it can done by a loop but exactly do know how as column fields could vary

well, you will need to set up a few more variables on your page to keep track of which block of 50 rows is being edited and which block of 50 rows to display next, plus limit the query output to the needed block of 50 records.

since you are already using a form in your page, setting up and keeping track of these variables in the form scope makes sense and is easy.

something like this should do the trick:

1) add these 2 lines to the top of your page to set up initial values for the form variables:
<cfparam name="form.startrow" default="1">
<cfparam name="form.maxrows" default="50">

2) add 2 hidden fields to your form to store the startrow & maxrows variables:
<input type="hidden" name="startrow" value="#form.startrow#" />
<input type="hidden" name="maxrows" value="#form.maxrows#" />

3) add STARTROW and MAXROWS attributes to the query output:
<cfoutput query="getData" startrow="#form.startrow#" maxrows="#form.maxrows#">

4) in your form validation/processing code, after all the validation and processing, add:
<cfset form.startrow = form.startrow + form.maxrows>
this will increment the form.startrow variable by 50, thus the query output will show next 50 rows.

all the above assumes that your form posts to itself instead of a separate action page. if you use a separate action page for form processing, you will need to probably use URL variables instead of FORM variables, and pass the STARTROW variable from your action page to the form page via URL.

hth

Azadi

Thanks for a Quick Guidence, I just worked with your solutiona bit and i just edited a countried table, fortunately the countries table has only two colums and for editing first fifty rows, i clicked the update command as u specified.

i go list as :

struct                                                                                                                                                                              COUNTRY                              1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50                                                                                                                                                                                                                                                COUNTRY_NAME                              Afghanistan,Albania,Algeria,AmericanSamoa,Andorra,Angola,Anguilla,Antarctica,Antigua andBarbuda,Argentina,Armenia,Aruba,Australia,Austria,Azerbaidjan,Bahamas,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin,Bermuda,Bhutan,Bolivia,Bosnia-Herzegovina,Botswana,BouvetIsland,Brazil,British Indian Ocean Territory,BruneiDarussalam,Bulgaria,Burkina Faso,Burundi,Cambodia,Cameroon,Canada,CapeVerde,Cayman Islands,Central AfricanRepublic,Chad,Chile,China,Christmas Island,Cocos (Keeling)Islands,Colombia,Comoros,Congo,Cook Islands                                                                                                                                                                                                                                                 FIELDNAMES                                                                                                                                                                                                            COUNTRY,COUNTRY_NAME,TABLENAME,UPDATEROWS                                                                                                                                                                                                                                                                                                                                                                                                            TABLENAME                                                                                                                                                                                                            grcountry                                                                                                                                                                                                                                                                                                                                                                                                            UPDATEROWS                                                                                                                                                                                                            Update
Now in this: colums fields are two, what if the colums fields are going to more than 2 how,, i know what i will fields.

do i need to loop through the form variables or something else

here goes another lengthy reply...

obviously, since you are creating up to 50 rows of identically named form fields, you will only have the fields in your posted form as the columns you have in your table, with up to 50 values in each field.

may i inquire what kind of interface you are trying to create and for what purpose? 'cos it looks to me that you did not think it through enough... i.e. a proper db will NOT let you update a primary key field in a row to the same value as primary key field in another row - which your interface seems to allow to do.  furthermore, some databases will just not let you manually change an autonumber/autoincrement column value.

all this boils down to one thing: you MUST know the name of your PK column in your table. without it you won;t be able to update column values - you need to know which row in your db the column is in to be able to update it properly.

i suggest that you start by laying down formal naming conventions for your table columns, at least for the primary key columns - that way you can:
a) NOT output the PK column as an editable form field to avoid inadvertent db corruption by your users.
b) make your form fields UNIQUE for each row of table data by appending the PK value to the name of each form field: this way you will have individual form fields for each column and row value in your posted form instead of just the fields corresponding to your db table columns with 50 values in each.

the simplest way of going about it would be to set up your PK column names as, for example, [tablename]_ID for numeric PKs and [tablename] for text-based PKs, where [tablename] is the name of your db table.

you can then:
1) check if your getData.columnlist list contains a column named #url.tableName#_ID
2) if it does then you know the name of the PK column
3) if it does not, then your PK column is named #url.tableName#
4) now in your output you can use the value of PK column to make your fields unique
5) now in your form processing code you can identify which row in your db table the form field contains data for.

see sample code attached.

hth

Azadi

<cfparam name="FORM.startrow" default="1">
<cfset FORM.maxrows = 50>
<cfquery name="getData" datasource="#session.newdsn#">
SELECT * FROM #url.tableName#
</cfquery>
<cfparam name="FORM.columsList" default="#getData.columnList#">
<cfparam name="FORM.PKcolumn" default="">
<cfif FORM.PKcolumn is "">
  <cfif listcontains(FORM.columnlist, "#url.tableName#_ID")>
    <cfset FORM.PKcolumn = url.tableName & "_ID">
  <cfelse>
    <cfset FORM.PKcolumn = url.tableName>
  </cfif>
</cfif>
 
  <form ...>
  <cfoutput>
  <input type="hidden" name="columnList" value="#FORM.columnList#" />
  <input type="hidden" name="PKcolumn" value="#FORM.PKcolumn#" />
  <input type="hidden" name="startrow" value="#FORM.startrow#" />
  <input type="hidden" name="maxrows" value="#FORM.maxrows#" />
  </cfoutput>
    <table align="center" width="100%">
    <tr align="left">
      <cfloop list="#FORM.columsList#" index="col">
      <cfoutput><th class="bgcolor">#col#</th></cfoutput>
      </cfloop>
    </tr>
    <cfoutput query="getData">
      <tr>
      <cfloop list="#FORM.columnList#" index="col">
        <td>
          <input type="text" name="#col#_#getData[PKcolumn][currentrow]" value="#getData[col][currentrow]#" <cfif col eq FORM.PKcolumn>disabled="disabled"</cfif> />
        </td>    
      </cfloop>
      </tr>
    </cfoutput>
    ...
    </form>

Select allOpen in new window

I also do not want that someone should should edit/delete primary key as thanks for this. You keep an eye on that. besides i ran you code exactly and got this error when i am modifying to make it work, Although somethingwent wrong.


Okay, I tried your code and i get an error:

[Table (rows 238 columns COUNTRY, COUNTRY_NAME): [COUNTRY: coldfusion.sql.QueryColumn@1159873] [COUNTRY_NAME: coldfusion.sql.QueryColumn@cbc660] ] is not indexable by grcountry
 
The error occurred in C:\Inetpub\wwwroot\Classic\listrows.cfm: line 53

51 :       <cfloop list="#FORM.columnList#" index="col">
52 :         <td>
53 :           <input type="text" name="#col#_#getData[PKcolumn][currentrow]#" value="#getData[col][currentrow]#" <cfif col eq FORM.PKcolumn>disabled="disabled"</cfif>>
54 :         </td>    
55 :       </cfloop>



below is the complete code i am working on:

I want to edit the rows value [except primary key], to mnake it smaller, i will edit first 50 and then next 50 without knowing the exact columns a table contains.

Access is the database.

<cfparam name="FORM.startrow" default="1">
<cfset FORM.maxrows = 50>
 
<!---<cfif isdefined('form.updaterows')>
<cfdump var="#form#"><cfabort>
<cfquery name="updateData" datasource="#session.newdsn#">
<!--- do whatever your update is --->
</cfquery>
</cfif>--->
<cfif isdefined('url.mode')>
  <cfif url.mode eq 'rows'>
    <cfquery name="getData" datasource="#session.newdsn#">
	SELECT * FROM #url.tableName#
	</cfquery>
	<cfparam name="FORM.columnlist" default="#getData.columnList#">
	<cfparam name="FORM.PKcolumn" default="">
		<cfif FORM.PKcolumn is "">
  			<cfif listcontains(FORM.columnlist, "#url.tableName#_ID")>
    	<cfset FORM.PKcolumn = url.tableName & "_ID">
  			<cfelse>
    	<cfset FORM.PKcolumn = url.tableName>
  		</cfif>
	</cfif>
    <cfform method="post" action="#CGI.SCRIPT_NAME#?#CGI.QUERY_STRING#">
    <table align="center" width="100%">
      <tr align="left">
        <cfoutput>
  <input type="hidden" name="columnList" value="#FORM.columnList#" />
  <input type="hidden" name="PKcolumn" value="#FORM.PKcolumn#" />
  <input type="hidden" name="startrow" value="#FORM.startrow#" />
  <input type="hidden" name="maxrows" value="#FORM.maxrows#" />
  </cfoutput>
    <table align="center" width="100%">
    <tr align="left">
      <cfloop list="#FORM.columnlist#" index="col">
      <cfoutput><th class="bgcolor">#col#</th></cfoutput>
      </cfloop>
    </tr>
    <cfoutput query="getData">
      <tr>
      <cfloop list="#FORM.columnList#" index="col">
        <td>
          <input type="text" name="#col#_#getData[PKcolumn][currentrow]#" value="#getData[col][currentrow]#" <cfif col eq FORM.PKcolumn>disabled="disabled"</cfif>>
        </td>    
      </cfloop>
      </tr>
    </cfoutput>
 
    <tr>
    	<td align="center"><cfoutput><input type="hidden" name="tableName" value="#url.tablename#" /></cfoutput><input type="submit" name="updaterows" value="Update" /></td>
    </tr>    
    </table>
    </cfform>
  </cfif>
</cfif>

Select allOpen in new window

it looks like your table name is GRCOUNTRY? for my code to work you need to make sure your PK column in this table is named either GRCOUTRY or GRCOUNTRY_ID - i do not see any such column in your table...

i'll be back in 2 hours if you still need help...

Azadi

TableName cannot be a Primary key, This Completely going other way..

Disbale the primarykey by simple way and edit the rows.

all my tablesname's primarykeys are different and are not like tableName

then you are gonna have a really hard time creating this update table interface, without proper rigid naming conventions for at least PK columns in your tables...

as i said before, you MUST know the name of the PK column in the table being processed. without knowing it you CAN'T update that table.

for instance, what does your UPDATE query look like? (post its sql if you can). how do you let the update query know which data row to update? without knowing the name of PK column and the value of currently processed PK, or some other UNIQUE identifier of the table row, you can't update that table row.

[Note: the name of your PK column in all your tables does not have to be the same as name of the table it is in, but you must know what it is and use it in your code. if all your PK columns are named ID, or PK, or something else, it'll work, too, as long as ALL of them have SAME name. i just think a naming convention for PK columns based on table name is a better approach.]

i hope you can understand your issue here - you MUST know the name of PK column in the table being processed for your code to work and do what you want it to do.

Azadi

Well Thanks Mate,

Well i adress the issue properly, i have not written the update sql yet as i was confused on this modal, so i did not went for sql yet?

1. I don't want to edit Primary Key
2. Column are not known
3. Wanna Pass Primary key as disbaled item.
4. how update will work

Please help me out of this, i am very far with it now and it is getting very heavy for me.

working till the morning and experimenting a lot but no luck yet

@myselfrandhawa,

 The question you need to answer is what does your where clause look like?

 When you do an update, you need a where clause that uniquely identifies the record.   If you were to make this update by hand, what would your where clause look like?

 
  Update  #myTableName#
      set  col = value
          ,  etc...
  where  XXXX =  theValue   <=====   what do you put here?

  Perhaps you can make a list of every table and identify it's primary key...
  Do you know the primary key for every table?


 

well the primary key will ne listed with other fields, Yje code Azadi provided me have some issue that checks the Primary key is start by the name of tablename.

wel i am abt confused on this, how i do all this:

You are building a list of columns to update from a list of ALL columns of the table, true?   All columns includes the primary key.   But the primary key should not be updated, it should be in the where clause, right?

A typical update looks like this..

  update mytable
    set  aColumn = 1
       , anotherColumn = 2
 where thePrimaryKey = 123

 Right, because you cannot identify your primary key you will do this...

  update mytable
    set  aColumn = 1
        ,  anotherColumn = 2
        ,  thePrimaryKey = 123


 Since there is no where clause you will update every row in the datbase, and you will also (attempt) to update the primary key.


!!!!  Please give us the name of a table and tell us what the primary key field is for that table.....

 

Okay, I explain again:

I click the link LIST ROWS of any table say [GRCOUNTRY].  Now Grcountry has two Colums, Grcountry IS Primary KEY.

another country_name is Column and that needs to be updated.

so in the above only two colums are now:

i will attach the file what it going as:

this is just for grcountry table, i have another table which have much rows.

Here is the Source Code what is it:
 
 
 
  
    
 
    <form name="CFForm_1" id="CFForm_1" action="rows.cfm?mode=rows&tableName=grcountry" method="post" onsubmit="return _CF_checkCFForm_1(this)">    
    
    <table align="center" width="100%">
      <tr align="left">
        
          
            <th class="bgcolor">COUNTRY</th>
          
        
          
            <th class="bgcolor">COUNTRY_NAME</th>
          
        
      </tr>
      
      <tr>
 
      
          <td><input type="text" name="COUNTRY" value="1" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Afghanistan" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="2" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Albania" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="3" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Algeria" /></td>    
      
      </tr>
    
      <tr>
 
      
          <td><input type="text" name="COUNTRY" value="4" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="American Samoa" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="5" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Andorra" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="6" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Angola" /></td>    
      
      </tr>
    
      <tr>
 
      
          <td><input type="text" name="COUNTRY" value="7" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Anguilla" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="8" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Antarctica" /></td>    
      
      </tr>
    
      <tr>
      
          <td><input type="text" name="COUNTRY" value="9" /></td>    
      
          <td><input type="text" name="COUNTRY_NAME" value="Antigua and Barbuda" /></td>    
      
      </tr>
</cfform>

Select allOpen in new window

1.GIF
2.GIF

Please put your session.newdsn   value in place of my  request.datasource in the query and the update.

(By the way,  since the datasource name is the same for every user, you could use the application scope instead of the session scope.  That way you don't need to cflock the value again and again (which you're not doing but should be when using session scope))

application.newdsn  

or  

request.newdsn

Thanks, The Code Your Proivided me is understandable to me but little partial:

Can U explain this Chunk of Code in more detail:

 <!---For Every Table where the primary key is not the same as the table name
      add to this structure with table and key name.  The format is...--->
  <cfset variables.primaryKeys["theTable"] = "PRIMARY_KEY_FIELD">
  <cfset variables.primaryKeys["theTable"] = "theTable_ID">
       -
<!---<cfset variables.primaryKeys = structNew()>
<cfset variables.primaryKeys["GRCOUNTRY"] = "GRCOUNTRY">--->
 
<cfif structKeyExists(variables.primaryKeys,theTable)>
   <cfset primaryKey = variables.primaryKeys[theTable]> <!--- the name of the primary key column --->
<cfelse>
   <cfset primaryKey = theTable> <!--- assume primary key has the same name as the table --->
</cfif>
<cfset dataColumns = getData.columnList> <!--- a list of all data columns ---->
<cfset pos = listFindNoCase(dataColumns,primaryKey)>
<cfif pos eq 0>
  <cfoutput>
  <h1>Warning Primary Key [#primaryKey#] is not in the table [#theTable#]</h1>
  <p>IF the primary key is not called [#primaryKey#] then enter it in the structure
     called variables.primaryKeys so we know the name of the key for this table</p>
  </cfoutput>
  <cfexit>
<cfelse>
  <cfset dataColumns = listDeleteAt(dataColumns,pos)>
</cfif>
 I expected that all my tables do not have same primary key as of tables.

Well with this query,

<cfquery name="getData" datasource="#session.newdsn#"> <!--- #session.newdsn# ---->
 SELECT * FROM #theTable#
</cfquery>

can i get the list of Colums and use them as primary key in :

<cfset variables.primaryKeys["theTable"] = "PRIMARY_KEY_FIELD">
  <cfset variables.primaryKeys["theTable"] = "theTable_ID">
but how??????

> Can U explain this Chunk of Code in more detail

That code tries to figure out what is your primary key for the table.  This is important to know.   The code will not work if we do not know the primary key column for the table.   You said that sometimes the primary key is the same as the table name.  When it is the same, this code will work.   When the primary key column name is not the same as the table, you need to tell the code the name of the primary key column.

To tell the code the name of the primary key column, add it here..
<cfset variables.primaryKeys["tableName"] = "PkColumnName">

For example, if you have a table called CONTACTS and the primary key column nane is CONTACT_ID you would add this line to the code...

<cfset variables.primaryKeys["CONTACTS"] = "CONTACT_ID">

That will tell the code that CONTACTS table has a primary key of CONTACT_ID


>  can i get the list of Colums and use them as primary key in

I do not understand this statement.  The primary key is ONE column name, not a list of columns.    


This is what you need to do.   Make a list of your tables and write down the name of the primary key column for each one.    If the primary key column is not the same as the table name, add a line of code like this...

<cfset variables.primaryKeys["CONTACTS"] = "CONTACT_ID">

ok, here's a working code that will, IF YOU ARE ON CF8, programmatically get the name of Primary Key column (uses <cfdbinfo> tag available in CF8) of the table being edited.

mind you, this still does not cover all possible table structures, especially the updating table code, but should work in most cases...

replace my #application.dsn# in <cfquery> tags with a variable that holds your DSN name.

suggestion:
you may want to limit the rows returned by your getData query to only the sub-set of 50 rows required for editing, especially if the number of total rows in a table is large. this can significantly speed-up query execution and page-load time. the sql code to do this is db-specific. let me know which db you are using and i will post updated getData query code for you.
alternatively, you can cache the getData query in session scope, so that you do not have to hit the db every time the form is submitted to get next 50 rows...

Azadi

<cfparam name="url.maxrows" default="50">
<cfparam name="url.startrow" default="1">
<cfparam name="FORM.startrow" default="#url.startrow#">
<!--- process form data if form has been submitted --->
<cfif isDefined("form.saveData")>
	<!--- you might want to put the update query below in a cftry/cfcatch block to trap any errors and output them nicely formatted instead of as default cf error message--->
  <cfloop index="recNum" from="1" to="#FORM.recordCount#">
    <cfquery name="updateTable" datasource="#application.dsn#">
    UPDATE #FORM.tableName#
    SET
		<cfloop list="#FORM.dataColumns#" index="col">
			<cfset theValue = form[col & '_' & recNum]>
			 #col# = <cfif len(trim(theValue)) eq 0>NULL
							 <cfelseif isNumeric(theValue)>#theValue#
							 <cfelseif isValid('date', theValue) OR (listLen(theValue,"/") gt 2 AND isDate(theValue))>#createODBCDateTime(theValue)#
							 <cfelse>'#theValue#'
							 </cfif>
							 <cfif col neq listlast(FORM.dataColumns)>,</cfif>
		</cfloop>
    WHERE #FORM.PKcolumn# = <cfif isNumeric(form[FORM.PKcolumn & '_' & recNum])><cfqueryparam cfsqltype="cf_sql_numeric" value="#form[FORM.PKcolumn & '_' & recNum]#"><cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#form[FORM.PKcolumn & '_' & recNum]#"></cfif>
    </cfquery>
  </cfloop>
	<!--- increment FORM.startrow var to show next block of 50 rows --->
	<cfset FORM.startrow = FORM.startrow + url.maxrows>
<cfelse>
	<!--- 
	set required FORM variables if this is the first call to this page [form has never been submitted yet].
	(only need to set these once on first call to this page - after this all these vars are available in the FORM scope
	since they are set as hidden form fields in the form.)
	--->
	<!--- set and check FORM.mode var--->
	<cfparam name="url.mode"  default="">
	<cfparam name="form.mode" default="#trim(url.mode)#">
	<cfif form.mode is not "Rows">
		<h1>Mode is not set to rows</h1>
		<cfabort>
	</cfif>
	<!--- set and check FORM.tableName var--->
	<cfparam name="url.tableName" default="">
	<cfparam name="FORM.tableName" default="#trim(url.tableName)#">
	<cfif len(trim(FORM.tableName)) is 0>
		<h1>No table selected</h1>
		<cfabort>
	</cfif>
	<!--- get db table metadata to extract PK column name --->
	<cfdbinfo name="getDataInfo" datasource="#application.dsn#" table="#FORM.tableName#" type="columns">
	<!--- extract PK column name from table metadata and set form variable --->
	<cfparam name="FORM.PKcolumn" default="">
	<cfloop query="getDataInfo">
		<cfif getDataInfo.IS_PRIMARYKEY>
			<cfset FORM.PKcolumn = getDataInfo.COLUMN_NAME>
			<cfbreak>
		</cfif>
	</cfloop>
	<cfif len(trim(FORM.PKcolumn)) is 0>
		<h1>No Primary Key defined in table</h1>
		<cfabort>
	</cfif>
</cfif>
<!--- get table data --->
<cfquery name="getData" datasource="#application.dsn#">
 SELECT * FROM #FORM.tableName#
</cfquery>
<cfif NOT structkeyexists(FORM, "dataColumns")>
	<!--- set FORM.dataColumn to list of table columns without PK column --->
	<!--- only executes if FORM.dataColumns is not defined yet (i.e. on first call to this page) --->
	<cfset PKColPosition = listFindNoCase(getData.columnList, FORM.PKcolumn)>
	<cfset FORM.dataColumns = listDeleteAt(getData.columnList, PKColPosition)>
</cfif>
<!--- 
show table edit form. 
only output form if not all table rows have been processed/updated yet 
--->
<cfif FORM.startrow lte getData.recordcount>
	<cfform name="form1" action="#getfilefrompath(cgi.script_name)#" method="post">
	<cfinput type="hidden" name="mode" value="#FORM.mode#" />
	<cfinput type="hidden" name="tableName" value="#FORM.tableName#" />
	<cfinput type="hidden" name="PKcolumn" value="#FORM.PKcolumn#" />
	<cfinput type="hidden" name="dataColumns" value="#FORM.dataColumns#" />
	<cfinput type="hidden" name="startrow" value="#FORM.startrow#" />
	<table align="center" width="100%" border="1" cellpadding="3">
		<tr>
			<th class="bgcolor"><cfoutput>#ucase(FORM.PKcolumn)#</cfoutput></th>
			<cfloop list="#FORM.dataColumns#" index="col">
				<th class="bgcolor"><cfoutput>#col#</cfoutput></th>
			</cfloop>
		</tr>
		<cfoutput query="getData" startrow="#FORM.startrow#" maxrows="#url.maxrows#">
			<cfset curRow = getData.currentrow>
			<tr>
				<td><input type="text" name="#FORM.PKcolumn & '_' & curRow#" value="#getData[FORM.PKcolumn][curRow]#" readonly="readonly" style="background-color:##e9e9e9;" size="6" /></td>
				<cfloop list="#FORM.dataColumns#" index="col">
					<td><input type="text" name="#col & '_' & curRow#" value="#getData[col][curRow]#" /></td>    
				</cfloop>
			</tr>
		</cfoutput>
	</table>
	<cfinput type="hidden" name="recordcount" value="#curRow#" />
	<p style="text-align:center;"><input type="submit" name="saveData" value="Update" /></p>
	</cfform>
<cfelse>
	<!--- 
	all rows in the table have been updated already (FORM.startrow > getData.recordcount)
	do want you need to do, i.e. cflocate to a page listing all tables 
	--->
	<h3>Good job! You have edited all rows in [<cfoutput>#FORM.tableName#</cfoutput>] table!</h3>
</cfif>

Select allOpen in new window

> (uses <cfdbinfo> tag available in CF8)

Does it work for regular Access datasources, or just unicode?

Nice find Azadi !
 I have incorporated the cfdbinfo function into my code version below.

But I have to disagree with caching a query into a session variable.  You do not want to do that.   Session variables are over used by many people.  They use your server memory, slow processing, cause bugs and make it difficult to debug/follow code.

It's much better to just call the query again.  Typically, the database will return results within 30-40 milliseconds, that's really nothing.  If you want to save that time, then simply use the cache attributes of the cfquery variable.

<cfquery       ...         cachedwithin="#CreateTimeSpan(0,0,10,0)#">

That will cache your query without using the session variable.

@myself, my sample does not restrict the number of records, hopefully you can add that, if you need help, just ask.

<cfparam name="FORM.startrow" default="1">
<cfset FORM.maxrows = 50>
 
<cfparam name="url.mode"  default="">
<cfif url.mode is not "Rows">
  <h1>Mode is not set to rows</h1>
  <cfexit>
</cfif>
 
<cfparam name="url.tableName" default="">
<cfset theTable = url.tableName>
<cfif len(theTable) eq 0>
  <h1>Table name is required on URL</h1>
  <cfexit>
</cfif>
 
<cfquery name="getData" datasource="#request.datasource#"> <!--- #session.newdsn# ---->
 SELECT * FROM #theTable#
</cfquery>
<cfset dataColumns = getData.columnList> <!--- a list of all data columns ---->
 
<!--- find the primary key column automatically ----->
<cfdbinfo name="getInfo" datasource="#request.datasource#" table="#theTable#" type="columns">
<cfquery name="getKey" dbtype="query">
  select * from getInfo where IS_PRIMARYKEY = 'YES'
</cfquery>
<cfset primaryKey = getKey.COLUMN_NAME>
 
<!---- remove the primary key from the list of columns, so we don't update it ---->
<cfset pos = listFindNoCase(dataColumns,primaryKey)>
<cfif pos eq 0>
  <cfoutput>
  <h1>Warning Primary Key [#primaryKey#] is not in the table [#theTable#]</h1>
  <p>IF the primary key is not called [#primaryKey#] then enter it in the structure
     called variables.primaryKeys so we know the name of the key for this table</p>
  </cfoutput>
  <cfexit>
<cfelse>
  <cfset dataColumns = listDeleteAt(dataColumns,pos)>
</cfif> 
 
  
<cfif isDefined("form.saveData")>
  <cfloop index="recNum" from="1" to="#form.recordCount#">
    <cfset theFirst = true> <!--- flag to track the first record, for comma placement ---->
    <cfquery name="updateTable" datasource="#request.datasource#">
     update #theTable#
       set 
      <cfloop list="#dataColumns#" index="col">
        <cfif NOT theFirst>,<cfelse><cfset theFirst=false></cfif>
        <cfset theValue = form[col & '_' & recNum]>
         #col# = <cfif len(trim(theValue)) eq 0>NULL
                 <cfelseif listLen(theValue,"/") gt 2 and isDate(theValue)>#createODBCdate(theValue)#
                 <cfelseif isNumeric(theValue)>#theValue#
                 <cfelse>'#theValue#'
                 </cfif>
      </cfloop>
     where #primaryKey# = #form[primaryKey & '_' & recNum]#
    </cfquery>
  </cfloop>
</cfif>
 
 
<cfset numCols = listLen(dataColumns)>
<cfform>
 <table align="center" width="100%" border="1" cellpadding="3">
  <tr align="left">
  <tr align="left">
  <th><cfoutput>#primaryKey#</cfoutput></th>
  <cfloop list="#dataColumns#" index="col">
    <cfoutput><th class="bgcolor">#col#</th></cfoutput>
  </cfloop>
  </tr>
  <cfoutput query="getData">
  <tr>
    <td><input type="text" name="#primaryKey#_#getData.currentrow#" value="#getData[primaryKey][getData.currentrow]#" readonly="readonly" style="background-color:##e9e9e9;"></td>
    <cfloop list="#dataColumns#" index="col">
    <td>
      <input type="text" name="#col#_#getData.currentrow#" value="#getData[col][getData.currentrow]#">
    </td>    
    </cfloop>
  </tr>
  </cfoutput> <!--- loop each record ---->
  <tr>
    <cfoutput>
	<td align="left" colspan="#numCols+1#">
    <input type="hidden" name="recordCount" value="#getData.recordCount#">
    </cfoutput>
    <input type="submit" name="saveData" value="Update" />
    </td>
  </tr>    
</table>
</cfform>

Select allOpen in new window

ah, good catch agx.  I didn't remember MS access was being used.  
It may not work.

> ah, good catch agx.  I didn't remember MS access was being used.  
> It may not work.

Yes, I do not know what kind of datasource they are using.  So just thought I would mention it.

ah, yes, MS Access...

see amended code for <cfdbinfo ...> part attached. it accounts for MS Access (non-unicode) db now by using cftry/cfcatch around <cfdbinfo ...> tag and using GetMetaData() function in cfcatch part to get query's meta data and looping through returned array of column structures to find 'COUNTER' type column.

Azadi

	<!--- get db table metadata to extract PK column name --->
	<cfparam name="FORM.PKcolumn" default="">
	<cftry>
		<cfdbinfo name="getDataInfo" datasource="#application.dsn#" table="#FORM.tableName#" type="columns">
		<!--- extract PK column name from table metadata and set form variable --->
		<cfloop query="getDataInfo">
			<cfif getDataInfo.IS_PRIMARYKEY>
				<cfset FORM.PKcolumn = getDataInfo.COLUMN_NAME>
				<cfbreak>
			</cfif>
		</cfloop>
		<cfcatch>
			<cfloop array="#getmetadata(getData)#" index="colStruct">
				<cfif colStruct['TypeName'] is 'COUNTER'>
					<cfset FORM.PKcolumn = colStruct['Name']>
					<cfbreak>
				</cfif>
			</cfloop>
		</cfcatch>
	</cftry>
	<cfif len(trim(FORM.PKcolumn)) is 0>
		<h1>No Primary Key defined in table</h1>
		<cfabort>
	</cfif>

Select allOpen in new window

hmm... it appears my amended code only work with MS Access tables that have Autonumber column... and assumes that column is the PK column...

there seems to be no way to get a PK column name from MS Access db if that column is NOT set as AUTONUMBER... is that right? another good reason not to use Access...

Azadi

... or another good reason to employ and use naming conventions for column names instead of trying to programmatically derive PK column name, as I have suggested before...

Azadi

Thanks Guys, I checked all your Codes you Provided. Thanks

Well it seems Access database is behaving very wired with it.

I get this error:

Now what i was trying to search google and other stuff around. I cam Across  the CFC AccessDBUtils.cfc. The details are here:

If CFDBInfo does not work with Access, then can i use the below CFC to make it work with Access and get the Primary Key of table where primary key of table is not known.

<cfcomponent>

    <cffunction name="init" returntype="AccessDBUtil" access="public" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset setDatasourceName(arguments.jdbcDatasourceName)  />
        <cfreturn this  />
    </cffunction>
    
    <cffunction name="setDatasourceName" returntype="void" access="private" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset variables.instance.datasource = arguments.jdbcDatasourceName  />
    </cffunction>
    <cffunction name="getDatasourceName" returntype="string" access="public" >
        <cfreturn variables.instance.datasource  />
    </cffunction>
    
    <cffunction name="getCatologs" returntype="query" access="public" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getCatalogs()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getSchemas" returntype="query" access="public" output="true" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getSchemas()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getTableTypes" returntype="query" access="public" >
        <cfset var Local = structNew() />
        
        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getTableTypes()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />
        
        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getProcedures" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="procedurePattern" type="string" required="false" default="%" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.procedurePattern );
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            javacast("null", ""),
                            arguments.procedurePattern );
            }        

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    
    <cffunction name="getTables" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="tablePattern" type="string" required="false" default="%" />
        <cfargument name="typeList" type="string" required="false" default="" hint="Comma delimited list of table types to include. An empty string means retrieve all types" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isTypeNonNull =  len(trim(arguments.typeList));
            Local.typeArray        = listToArray(arguments.typeList);
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", ""));
            }        
            else if ( Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray); 
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", "")); 
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getColumns" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. If not specified, the schema is not used to narrow the search"  />
        <cfargument name="tablePattern" type="string" required="false" default="%" hint="Table name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfargument name="columnPattern" type="string" required="false" default="%" hint="Column name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            //Local.query2 = createObject("java", "coldfusion.sql.QueryTable").init(Local.resultset);
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getVersionInfo" returntype="query" access="public" >
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.query = queryNew( "DATABASE_VERSION,DATABASE_PRODUCTNAME" & 
                                ",DATABASE_MAJOR_VERSION,DATABASE_MINOR_VERSION," & 
                                ", DRIVER_VERSION,DRIVER_NAME,JDBC_MAJOR_VERSION," &
                                "JDBC_MINOR_VERSION");
                            
            Local.connection = getConnection();
            Local.meta = getConnection().getMetaData();
        
            Local.row = queryAddRow( Local.query, 1);
            Local.query["DATABASE_PRODUCTNAME"][Local.row]         = Local.meta.getDatabaseProductName();
            Local.query["DATABASE_VERSION"][Local.row]             = Local.meta.getDatabaseProductVersion();
            Local.query["DATABASE_MAJOR_VERSION"][Local.row]     = Local.meta.getDatabaseMajorVersion();
            Local.query["DATABASE_MINOR_VERSION"][Local.row]     = Local.meta.getDatabaseMinorVersion();
            Local.query["DRIVER_VERSION"][Local.row]             = Local.meta.getDriverVersion();
            Local.query["DRIVER_NAME"][Local.row]                 = Local.meta.getDriverName();
            Local.query["JDBC_MAJOR_VERSION"][Local.row]         = Local.meta.getDriverMajorVersion();
            Local.query["JDBC_MINOR_VERSION"][Local.row]         = Local.meta.getDriverMinorVersion();
    
            Local.connection.close();
        </cfscript>
        
        <cfreturn Local.query  />
    </cffunction>


    <cffunction name="getConnection" returntype="any" >
        <cfset var dsService = createObject("java", "coldfusion.server.ServiceFactory").getDataSourceService()  />
        <cfset var connection = dsService.getDataSource(getDatasourceName()).getConnection()  />
        <cfreturn connection  />
    </cffunction>
    
    <cffunction name="getResultSetColumnNames" returntype="array" access="private" hint="Returns an array of column names from the resultset metadata" >
        <cfargument name="resultsetMetadata" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <cfscript>
            Local.columnArray = arrayNew(1);
            //get number of columns in the resulset
            Local.maxColumn = arguments.resultsetMetadata.getColumnCount();
            //get the name of each column in the query and append it to the array
            for (Local.index = 1; Local.index LTE Local.MaxColumn; Local.index = Local.index + 1) {
                arrayAppend( Local.columnArray, arguments.resultsetMetadata.getColumnName( javacast("int", Local.index)) );        
            }
        </cfscript>
        
        <cfreturn Local.columnArray  />
    </cffunction>
    
    <cffunction name="resultsetToQuery" returntype="query" access="private" hint="Converts a resulset to a query object" >
        <cfargument name="resultset" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <!--- could also use "coldfusion.sql.QueryTable" to create a resulset 
        <cfset Local.query = createObject("java", "coldfusion.sql.QueryTable").init(arguments.resultset) />
        --->
        
        <cfscript>
            Local.columnNames     = getResultSetColumnNames( arguments.resultset.getMetaData() );
            Local.maxColumn     = arrayLen( Local.columnNames );
            Local.query         = queryNew( arrayToList(Local.columnNames) );
            
            while ( arguments.resultset.next() ) {
                //add one row to the query for each row in the resultset
                Local.rowIndex = queryAddRow(Local.query , 1);
                 for ( Local.colIndex = 1; Local.colIndex LTE Local.maxColumn; Local.colIndex = Local.colIndex + 1) {
                    Local.colName = Local.columnNames[Local.colIndex];
                    Local.value = arguments.resultset.getObject( Local.colName );
                    // if the returned value is not NULL
                    if ( structKeyExists(Local, "value") ) {
                        Local.query[Local.colName][Local.rowIndex] = Local.value;
                    }
                }
            }
        </cfscript>
                
        <cfreturn Local.query  />
    </cffunction>
    
</cfcomponent>

Select allOpen in new window

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented
 
The error occurred in C:\Inetpub\wwwroot\Classic\listrows.cfm: line 224
 
222 : 	</cfif>
223 : 	<!--- get db table metadata to extract PK column name --->
224 : 	<cfdbinfo name="getDataInfo" datasource="#session.newdsn#" table="#FORM.tableName#" type="columns">
225 : 	<!--- extract PK column name from table metadata and set form variable --->
226 : 	<cfparam name="FORM.PKcolumn" default="">

Select allOpen in new window

> there seems to be no way to get a PK column name from MS Access db if that column is NOT set as
> AUTONUMBER... is that right?

Not AFAIK.  Maybe newer versions have changed, but the older versions did not expose that level of metadata via jdbc.  Unlike ms sql, mysql, etc... the best hope may be to use cfdbinfo with a unicode dsn. But even with that, it seems like they are pushing the limits of access..

> another good reason not to use Access...

+1 .  This would be a breeze with a more robust database like ms sql or mysql

** Try the suggestions from Azadi and gdemaria first, before searching for alternatives **

> If CFDBInfo does not work with Access,

It is not that cfdbinfo does _not_ work with Access.  It does work. But only with certain datasource types.  There are two types of datasources for Access.

- Microsoft Access
- Microsoft Access with Unicode

CFDBINFO works only with the type "Microsoft Access with Unicode".  But bear in mind, Access itself is very limited.  IT may not support certain features.  BUT, don't worry about that until after you try the code Azadi and gdemaria suggested.  See if their suggestions work for you first.  

All fields set as primary key have the value of AUTONUMBER,


I need to make it work with Access. I know Access is not a great database but still something will need to get workaround. The above cfc  will not help in any way, if yes how can i use that cfc to get the database results:


and check for a primary key.


Please experts.

@ _agx_: some solutions for getting MS Access PK column i have just found use COM object - so there must be a way to do it using <cfobject> in CF... will have to explore that more...

@ myselfrandhawa:
1) that error is because <cfdbinfo> tag does NOT work with MS Access, as _agx_ has duly noted.
2) that cfc looks interesting... i will see if it can be used in your case... where did you find it?
3) another solution for you may be to create another table in your db which will hold all table names and names of their PK columns... then you could query that table to get current table's PK column name....

Azadi

well, if ALL your PK columns are set as AUTONUMBER, then my amended code should work for you.

here's complete code to try (see attached).

Azadi

<cfparam name="url.maxrows" default="50">
<cfparam name="url.startrow" default="1">
<cfparam name="FORM.startrow" default="#url.startrow#">
<!--- process form data if form has been submitted --->
<cfif isDefined("form.saveData")>
	<!--- you might want to put the update query below in a cftry/cfcatch block to trap any errors and output them nicely formatted instead of as default cf error message--->
  <cfloop index="recNum" from="1" to="#FORM.recordCount#">
    <cfquery name="updateTable" datasource="#application.dsn#">
    UPDATE #FORM.tableName#
    SET
		<cfloop list="#FORM.dataColumns#" index="col">
			<cfset theValue = form[col & '_' & recNum]>
			 #col# = <cfif len(trim(theValue)) eq 0>NULL
							 <cfelseif isNumeric(theValue)>#theValue#
							 <cfelseif isValid('date', theValue) OR (listLen(theValue,"/") gt 2 AND isDate(theValue))>#createODBCDateTime(theValue)#
							 <cfelse>'#theValue#'
							 </cfif>
							 <cfif col neq listlast(FORM.dataColumns)>,</cfif>
		</cfloop>
    WHERE #FORM.PKcolumn# = <cfif isNumeric(form[FORM.PKcolumn & '_' & recNum])><cfqueryparam cfsqltype="cf_sql_numeric" value="#form[FORM.PKcolumn & '_' & recNum]#"><cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#form[FORM.PKcolumn & '_' & recNum]#"></cfif>
    </cfquery>
  </cfloop>
	<!--- increment FORM.startrow var to show next block of 50 rows --->
	<cfset FORM.startrow = FORM.startrow + url.maxrows>
<cfelse>
	<!--- 
	set required FORM variables if this is the first call to this page [form has never been submitted yet].
	(only need to set these once on first call to this page - after this all these vars are available in the FORM scope
	since they are set as hidden form fields in the form.)
	--->
	<!--- set and check FORM.mode var--->
	<cfparam name="url.mode"  default="">
	<cfparam name="form.mode" default="#trim(url.mode)#">
	<cfif form.mode is not "Rows">
		<h1>Mode is not set to rows</h1>
		<cfabort>
	</cfif>
	<!--- set and check FORM.tableName var--->
	<cfparam name="url.tableName" default="">
	<cfparam name="FORM.tableName" default="#trim(url.tableName)#">
	<cfif len(trim(FORM.tableName)) is 0>
		<h1>No table selected</h1>
		<cfabort>
	</cfif>
	<!--- get db table metadata to extract PK column name --->
	<cfparam name="FORM.PKcolumn" default="">
	<cftry>
		<cfdbinfo name="getDataInfo" datasource="#application.dsn#" table="#FORM.tableName#" type="columns">
		<!--- extract PK column name from table metadata and set form variable --->
		<cfloop query="getDataInfo">
			<cfif getDataInfo.IS_PRIMARYKEY>
				<cfset FORM.PKcolumn = getDataInfo.COLUMN_NAME>
				<cfbreak>
			</cfif>
		</cfloop>
		<cfcatch>
			<cfloop array="#getmetadata(getData)#" index="colStruct">
				<cfif colStruct['TypeName'] is 'COUNTER'>
					<cfset FORM.PKcolumn = colStruct['Name']>
					<cfbreak>
				</cfif>
			</cfloop>
		</cfcatch>
	</cftry>
	<cfif len(trim(FORM.PKcolumn)) is 0>
		<h1>No Primary Key defined in table</h1>
		<cfabort>
	</cfif>
</cfif>
<!--- get table data --->
<cfquery name="getData" datasource="#application.dsn#">
 SELECT * FROM #FORM.tableName#
</cfquery>
<cfif NOT structkeyexists(FORM, "dataColumns")>
	<!--- set FORM.dataColumn to list of table columns without PK column --->
	<!--- only executes if FORM.dataColumns is not defined yet (i.e. on first call to this page) --->
	<cfset PKColPosition = listFindNoCase(getData.columnList, FORM.PKcolumn)>
	<cfset FORM.dataColumns = listDeleteAt(getData.columnList, PKColPosition)>
</cfif>
<!--- 
show table edit form. 
only output form if not all table rows have been processed/updated yet 
--->
<cfif FORM.startrow lte getData.recordcount>
	<cfform name="form1" action="#getfilefrompath(cgi.script_name)#" method="post">
	<cfinput type="hidden" name="mode" value="#FORM.mode#" />
	<cfinput type="hidden" name="tableName" value="#FORM.tableName#" />
	<cfinput type="hidden" name="PKcolumn" value="#FORM.PKcolumn#" />
	<cfinput type="hidden" name="dataColumns" value="#FORM.dataColumns#" />
	<cfinput type="hidden" name="startrow" value="#FORM.startrow#" />
	<table align="center" width="100%" border="1" cellpadding="3">
		<tr>
			<th class="bgcolor"><cfoutput>#ucase(FORM.PKcolumn)#</cfoutput></th>
			<cfloop list="#FORM.dataColumns#" index="col">
				<th class="bgcolor"><cfoutput>#col#</cfoutput></th>
			</cfloop>
		</tr>
		<cfoutput query="getData" startrow="#FORM.startrow#" maxrows="#url.maxrows#">
			<cfset curRow = getData.currentrow>
			<tr>
				<td><input type="text" name="#FORM.PKcolumn & '_' & curRow#" value="#getData[FORM.PKcolumn][curRow]#" readonly="readonly" style="background-color:##e9e9e9;" size="6" /></td>
				<cfloop list="#FORM.dataColumns#" index="col">
					<td><input type="text" name="#col & '_' & curRow#" value="#getData[col][curRow]#" /></td>    
				</cfloop>
			</tr>
		</cfoutput>
	</table>
	<cfinput type="hidden" name="recordcount" value="#curRow#" />
	<p style="text-align:center;"><input type="submit" name="saveData" value="Update" /></p>
	</cfform>
<cfelse>
	<!--- 
	all rows in the table have been updated already (FORM.startrow > getData.recordcount)
	do want you need to do, i.e. cflocate to a page listing all tables 
	--->
	<h3>Good job! You have edited all rows in [<cfoutput>#FORM.tableName#</cfoutput>] table!</h3>
</cfif>

Select allOpen in new window

@azadisaryev - Using COM, definitely.  Using the built in JDBC drivers, maybe.. maybe not. Besides, the jdbc method probably requires the unicode dsn type (which the OP is obvioiusly NOT using).  Anyway, I am going to stay back off for a bit, so you guys can focus on helping the OP with your examples.

@_agx_:
yep, you are right, JDBC driver methods do not work with non-unicode MS Access DSN... implementing COM object solutions proves troublesome so far...but *should* work in theory...

@myselfrandhawa:
the CFC you found does not work with non-unicode MS Access dsn... fails with same error as <cfdbinfo> - i guess it is using same underlying java/jdbc methods as <cfdbinfo> tag...

did you try the latest code i posted? did it work?

Azadi

Thanks Azadi..

Your code Seems work..

but when i run the code first time, it gets updated, then it moves to the next 50 rows and when i click the update button:

I get Coldfusion Error:

 Element COUNTRY_NAME_1 is undefined in a Java object of type class coldfusion.filter.FormScope.
 
The error occurred in C:\Inetpub\wwwroot\Classic\listrows.cfm: line 301
 
299 :     SET
300 : 		<cfloop list="#FORM.dataColumns#" index="col">
301 : 			<cfset theValue = form[col & '_' & recNum]>
302 : 			 #col# = <cfif len(trim(theValue)) eq 0>NULL
303 : 							 <cfelseif isNumeric(theValue)>#theValue#

Select allOpen in new window

oh, right...

change this line:
<cfloop index="recNum" from="1" to="#FORM.recordCount#">

to this:
<cfloop index="recNum" from="#FORM.startrow#" to="#FORM.recordCount#">

Azadi

Hi Guys, Please explain a little bit thins: can i make a link near the UPDATe button to skip first 50 rows and edit next row, how.

and will the same solution works with MYSQl, MSSQl wth little modification. I hope so.

Regards

oops... found another mistake in my code.

i have attached full corrected code for you.

Azadi

<cfset application.dsn = "lao_ngo0">
<cfparam name="url.maxrows" default="50">
<cfparam name="url.startrow" default="1">
<cfparam name="FORM.startrow" default="#url.startrow#">
<!--- 
set required FORM variables if this is the first call to this page [form has never been submitted yet].
(only need to set these once on first call to this page - after this all these vars are available in the FORM scope
since they are set as hidden form fields in the form.)
--->
<!--- set and check FORM.mode var--->
<cfparam name="url.mode"  default="">
<cfparam name="FORM.mode" default="#trim(url.mode)#">
<cfif FORM.mode is not "Rows">
	<h1>Mode is not set to rows</h1>
	<cfabort>
</cfif>
<!--- set and check FORM.tableName var--->
<cfparam name="url.tableName" default="">
<cfparam name="FORM.tableName" default="#trim(url.tableName)#">
<cfif len(trim(FORM.tableName)) is 0>
	<h1>No table selected</h1>
	<cfabort>
</cfif>
<!--- process form data if form has been submitted --->
<cfif structkeyexists(FORM, 'saveData')>
	<!--- you might want to put the update query below in a cftry/cfcatch block to trap any errors and output them nicely formatted instead of as default cf error message--->
  <cfloop index="recNum" from="#FORM.startrow#" to="#FORM.recordCount#">
    <cfquery name="updateTable" datasource="#application.dsn#">
    UPDATE #FORM.tableName#
    SET
		<cfloop list="#FORM.dataColumns#" index="col">
			<cfset theValue = form[col & '_' & recNum]>
			 #col# = <cfif len(trim(theValue)) eq 0>NULL
							 <cfelseif isNumeric(theValue)>#theValue#
							 <cfelseif isValid('date', theValue) OR (listLen(theValue,"/") gt 2 AND isDate(theValue))>#createODBCDateTime(theValue)#
							 <cfelse>'#theValue#'
							 </cfif>
							 <cfif col neq listlast(FORM.dataColumns)>,</cfif>
		</cfloop>
    WHERE #FORM.PKcolumn# = <cfif isNumeric(form[FORM.PKcolumn & '_' & recNum])><cfqueryparam cfsqltype="cf_sql_numeric" value="#form[FORM.PKcolumn & '_' & recNum]#"><cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#form[FORM.PKcolumn & '_' & recNum]#"></cfif>
    </cfquery>
  </cfloop>
	<!--- increment FORM.startrow var to show next block of 50 rows --->
	<cfset FORM.startrow = FORM.startrow + url.maxrows>
</cfif>
<!--- get table data --->
<cfquery name="getData" datasource="#application.dsn#" cachedwithin="#createtimespan(0,0,10,0)#">
SELECT * FROM #FORM.tableName#
</cfquery>
<cfif NOT structkeyexists(FORM, 'saveData')>
	<!--- only executes if FORM.dataColumns is not defined yet (i.e. on first call to this page) --->
	<!--- get db table metadata to extract PK column name --->
	<cfparam name="FORM.PKcolumn" default="">
	<cftry>
		<cfdbinfo name="getDataInfo" datasource="#application.dsn#" table="#FORM.tableName#" type="columns">
		<!--- extract PK column name from table metadata and set form variable --->
		<cfloop query="getDataInfo">
			<cfif getDataInfo.IS_PRIMARYKEY>
				<cfset FORM.PKcolumn = getDataInfo.COLUMN_NAME>
				<cfbreak>
			</cfif>
		</cfloop>
		<cfcatch>
			<cfloop array="#getmetadata(getData)#" index="colStruct">
				<cfif colStruct['TypeName'] is 'COUNTER'>
					<cfset FORM.PKcolumn = colStruct['Name']>
					<cfbreak>
				</cfif>
			</cfloop>
		</cfcatch>
	</cftry>
	<cfif len(trim(FORM.PKcolumn)) is 0>
		<h1>No Primary Key defined in table</h1>
		<cfabort>
	</cfif>
	<!--- set FORM.dataColumn to list of table columns without PK column --->
	<cfset PKColPosition = listFindNoCase(getData.columnList, FORM.PKcolumn)>
	<cfset FORM.dataColumns = listDeleteAt(getData.columnList, PKColPosition)>
</cfif>
<!--- 
show table edit form. 
only output form if not all table rows have been processed/updated yet 
--->
<cfif FORM.startrow lte getData.recordcount>
	<cfform name="form1" action="#getfilefrompath(cgi.script_name)#" method="post">
	<cfinput type="hidden" name="mode" value="#FORM.mode#" />
	<cfinput type="hidden" name="tableName" value="#FORM.tableName#" />
	<cfinput type="hidden" name="PKcolumn" value="#FORM.PKcolumn#" />
	<cfinput type="hidden" name="dataColumns" value="#FORM.dataColumns#" />
	<cfinput type="hidden" name="startrow" value="#FORM.startrow#" />
	<table align="center" width="100%" border="1" cellpadding="3">
		<tr>
			<th class="bgcolor"><cfoutput>#ucase(FORM.PKcolumn)#</cfoutput></th>
			<cfloop list="#FORM.dataColumns#" index="col">
				<th class="bgcolor"><cfoutput>#col#</cfoutput></th>
			</cfloop>
		</tr>
		<cfoutput query="getData" startrow="#FORM.startrow#" maxrows="#url.maxrows#">
			<cfset curRow = getData.currentrow>
			<tr>
				<td><input type="text" name="#FORM.PKcolumn & '_' & curRow#" value="#getData[FORM.PKcolumn][curRow]#" readonly="readonly" style="background-color:##e9e9e9;" size="6" /></td>
				<cfloop list="#FORM.dataColumns#" index="col">
					<td><input type="text" name="#col & '_' & curRow#" value="#getData[col][curRow]#" /></td>    
				</cfloop>
			</tr>
		</cfoutput>
	</table>
	<cfinput type="hidden" name="recordcount" value="#curRow#" />
	<p style="text-align:center;"><input type="submit" name="saveData" value="Update" /></p>
	</cfform>
<cfelse>
	<!--- 
	all rows in the table have been updated already (FORM.startrow > getData.recordcount)
	do want you need to do, i.e. cflocate to a page listing all tables 
	--->
	<h3>Good job! You have edited all rows in [<cfoutput>#FORM.tableName#</cfoutput>] table!</h3>
</cfif>

Select allOpen in new window

you can put this link next to your UPDATE button to go to next 50 rows without updating currently displayed rows:

<cfoutput><a href="[YOU_PAGE_NAME_HERE]?mode=rows&tableName=#FORM.tableName#&startrow=#FORM.startrow+url.maxrows#">Skip to next 50 rows</a></cfoutput>

replace [YOU_PAGE_NAME_HERE] with actual name of your page.

the whole code DOES work with MySQL db and *should* work with MSSQL, but i have not tested it on MSSQL...

Azadi