Link to home
Start Free TrialLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

access AutoNumber field not picking up

Hi Experts, I list all my fields in the one file. From there i am calling them in a Page

Here below is the code:

<cfsetting enablecfoutputonly="Yes">

<cfset fieldTypes = ArrayNew(1)>

<!--- TEXT --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_VARCHAR">

<!--- AUTO NUMBER --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_AUTO">

<!--- MEMO --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_LONGVARCHAR">

<!--- NUMBER --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_INTEGER">
<!--- Big Number --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_DOUBLE">
<!--- CURRENCY --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_MONEY">
<!--- YES/NO --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_BIT">

<!--- DATETIME --->
<cfset insertPos = ArrayLen(fieldTypes) + 1>
<cfset fieldTypes[insertPos] = StructNew()>
<cfset fieldTypes[insertPos].fieldType = "CF_SQL_DATE">

<cfsetting enablecfoutputonly="No">
now i am using a select box to match the field exactly the same as it is in database so i can update it.
 
i have the following in my select box:
 
<cfif structkeyexists(tabledata[x], 'CF_DataType')>
                <cfif listfindnocase(fieldTypeList, tabledata[x]['CF_DataType'])>
                  <select name="fieldtype_#x#" id="fieldType_#x#">
                    <cfloop list="#fieldTypeList#" index="fieldType">
                    <cfoutput>
                      <option value="<cfif #fieldType# EQ 'CF_SQL_AUTO'>COUNTER
					  <cfelseif #fieldType# IS 'CF_SQL_VARCHAR'>TEXT
					  <cfelseif #fieldType# IS 'CF_SQL_LONGVARCHAR'>MEMO
					  <cfelseif #fieldType# IS 'CF_SQL_DATE'>DATETIME
					  <cfelseif #fieldType# IS 'CF_SQL_INTEGER'>NUMBER
					  <cfelseif #fieldType# IS 'CF_SQL_DOUBLE'>NUMBER
					  <cfELSEif #fieldType# IS 'CF_SQL_BIT'>YESNO
					  <cfelseif #fieldType# IS 'CF_SQL_MONEY'>CURRENCY</cfif>" 
					  <cfif fieldtype eq tabledata[x]['CF_DataType']>selected="selected"</cfif>>#fieldType#</option>
                     </cfoutput> 
                    </cfloop>
                  </select>
                  <cfelse>
                  <input type="text" name="fieldtype_#x#" value="#tabledata[x]['CF_DataType']#" />
                </cfif>
 
 
what it does when the field is AutoNumber in Access table. It picks up the cf_sql_interger rather than cf_sql_auto
 
i am confused what is happening here

Open in new window

Avatar of duncancumming
duncancumming
Flag of United Kingdom of Great Britain and Northern Ireland image

AFAIK, there is no CF_SQL_AUTO.  All types are here:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

Avatar of _agx_
> what it does when the field is AutoNumber in Access table. It picks up the cf_sql_interger
> rather than cf_sql_auto

AFAIK - There is no such data type _or_ cfsqltype as cf_sql_auto.   I believe the default data type of Access auto number columns is  "integer" and that is what is reported as the data type.  There is no special "autonumber" data type. The fact that the column is also an autonumber is a separate characteristic.  Unless you can find a way to retrieve that extra information, I am afraid you are out of luck.
 
Avatar of Coast Line

ASKER

yeah i know, But i created a default to match the records based on the criteria. i make the source code and see what i am getting here:

<option value="TEXT
					  ">CF_SQL_VARCHAR</option>
 
                      
                    
                    
                      <option value="COUNTER
					  ">CF_SQL_AUTO</option>
                      
                    
                    
                      <option value="MEMO
					  ">CF_SQL_LONGVARCHAR</option>
                      
                    
                    
                      <option value="NUMBER
					  " selected="selected">CF_SQL_INTEGER</option>
                      
                    
                    
                      <option value="NUMBER
					  ">CF_SQL_DOUBLE</option>
                      
                    
                    
                      <option value="CURRENCY">CF_SQL_MONEY</option>
                      
                    
                    
                      <option value="YESNO
					  ">CF_SQL_BIT</option>
 
                      
                    
                    
                      <option value="DATETIME
					  ">CF_SQL_DATE</option>
                      
                    
                  </select>

Open in new window

I think you have the wrong list "values".  Your one list value using "COUNTER"
         <option value="COUNTER">CF_SQL_AUTO</option>

But your other code is checking for "CF_SQL_AUTO", instead of "COUNTER"
        <option value="<cfif #fieldType# EQ 'CF_SQL_AUTO'>COUNTER                      

ok i will list here again what i am doing exactly:

This sqlserertypes.cfm file have contents specified on the very first post
<cfinclude template="sqlserertypes.cfm">
          <cfset fieldTypeList = "">
          <cfloop from="1" to="#ArrayLen(fieldTypes)#" index="i">
            <cfset fieldTypeList = ListAppend( fieldTypeList, fieldTypes[i].fieldtype )>
          </cfloop>
         <table align="center">
          <tr>
            <th>Length:</th>
            <th>Column Name:</th>
            <th>Data Type:</th>
            <th>Primary Key:</th>
            <th>Increment:</th>
          </tr>
         
          <cfloop from="1" to="#arrayLen(tableData)#" index="x">
           <cfif isstruct(tabledata[x]) AND structkeyexists(tabledata[x], 'ColumnName')>
           <cfoutput>
            <tr>
            <td><input type="text" size="4" name="tlength_#x#" value="<cfif structkeyexists(tableData[x], 'length')>#tableData[x]['length']#</cfif>" /></td>
            <td>
            <input type="text" name="tcolumnName" value="#tableData[x]['ColumnName']#" readonly="yes" />
          </td>
          <td><cfif structkeyexists(tabledata[x], 'CF_DataType')>
                <cfif listfindnocase(fieldTypeList, tabledata[x]['CF_DataType'])>
                  <select name="fieldtype_#x#" id="fieldType_#x#">
                    <cfloop list="#fieldTypeList#" index="fieldType">
                    <cfoutput>
                      <option value="<cfif #fieldType# EQ 'CF_SQL_AUTO'>COUNTER
                      <cfelseif #fieldType# IS 'CF_SQL_VARCHAR'>TEXT
                      <cfelseif #fieldType# IS 'CF_SQL_LONGVARCHAR'>MEMO
                      <cfelseif #fieldType# IS 'CF_SQL_DATE'>DATETIME
                      <cfelseif #fieldType# IS 'CF_SQL_INTEGER'>NUMBER
                      <cfelseif #fieldType# IS 'CF_SQL_DOUBLE'>NUMBER
                      <cfELSEif #fieldType# IS 'CF_SQL_BIT'>YESNO
                      <cfelseif #fieldType# IS 'CF_SQL_MONEY'>CURRENCY</cfif>"
                      <cfif fieldtype eq tabledata[x]['CF_DataType']>selected="selected"</cfif>>#fieldType#</option>
                     </cfoutput>
                    </cfloop>
                  </select>
                  <cfelse>
                  <input type="text" name="fieldtype_#x#" value="#tabledata[x]['CF_DataType']#" />
                </cfif>
                <cfelse>
                <input type="text" name="fieldtype_#x#" value="" />
              </cfif></td>
            <td><input type="text" name="isPrimaryKey_#x#" value="<cfif structkeyexists(tableData[x], 'PrimaryKey')>#tableData[x]['PrimaryKey']#</cfif>" readonly="yes" /></td>
            <td><input type="text" name="tIncrement_#x#" value="<cfif structkeyexists(tableData[x], 'increment')>#tableData[x]['increment']#</cfif>" readonly="yes" /></td>
            <td>
            <cfif structkeyexists(tableData[x], 'PrimaryKey') AND tableData[x]['PrimaryKey'] EQ TRUE><cfelse>
            <a href="home.cfm?action=show&table=#url.table#&access=#tableData[x]['ColumnName']#" title="Delete Column"><img title="Delete This Column" src="delete.png" /></a></cfif></td>
          </tr>
          </cfoutput>
        </cfif>
        </cfloop>
okay let change a bit i remove the cf_sql_auto from the list.

i want that it should automatically select to cf_sql_integer with the value of COUNTER in the select Box:

<option value="<cfif #fieldType# EQ 'CF_SQL_INTEREGR'>COUNTER
what constrainst i apply here to know if it is primary key, set it to COUNTER

I hope u guys understand what i am trying to say and do.

application is just not identifying the AutOnUmber.

It gets to NUMBER if it encounter NUMBER, AutoNumber in the table.


Perhaps with that big CFIF statement oddly wrapped inside of your value="" parameter, there are some spaces of extra characters getting added.  Look at your view-Source in your browser to see what values are getting into the value=""  it's probably not clean.

If that is part of the problem, you could do it this way...
The replaceList() function will swap the one value for the corresponding value in the next list, you need to fill in the reset of the values to swap...

It's odd to me why you're doing it this way
<cfif listfindnocase(fieldTypeList, tabledata[x]['CF_DataType'])>
  <select name="fieldtype_#x#" id="fieldType_#x#">
    <cfoutput>
    <cfloop list="#fieldTypeList#" index="fieldType">
      <cfset theValue = replaceList(fieldType,"CF_SQL_AUTO,CF_SQL_VARCHAR,CF_SQL_DATE","COUNTER,TEXT,DATETIME")>
      <option value="#theValue#" <cfif fieldtype eq tabledata[x]['CF_DataType']>selected="selected"</cfif>> #fieldType#
    </cfloop>
    </cfoutput> 
  </select>
  <cfelse>
  <input type="text" name="fieldtype_#x#" value="#tabledata[x]['CF_DataType']#" />
</cfif>

Open in new window

hI I DUMPED THE data returened by Access database and it came as following:

cf_sql_integer - for number, autonumber, double.

i removed cf_sql_auto to remove confusion from code:

now when i have three fields in my table like:

id   - Autonumber
age  - number (in details it is small integer)
speed - number (in detail, it is long integer)

now when i open the structure it comes as:

id - cf_sql_integer
age - cf_sql_integer
speed - cf_sql_double

now confusion is created between the above two. both have cf_sql_integer.

in my cfdump if the field is autonumber and primary key. the primary key is set to True.

how can i add the check in the above fields to see if one of the fields have the primary key and set its value in the select box as:

for field ID:
<select>
    <option value="counter" selected>cf_sql_integer</option>
   <option value="number">cf_sql_integer</option>
  <option value="number">cf_sql_double</option>
</select>

for field age:

<select>
    <option value="counter">cf_sql_integer</option>
   <option value="number" selected>cf_sql_integer</option>
  <option value="number">cf_sql_double</option>
</select>

for field speed

<select>
    <option value="counter">cf_sql_integer</option>
   <option value="number">cf_sql_integer</option>
  <option value="number" selected>cf_sql_double</option>
</select>

in my code you will see this line:

<td><input type="text" name="isPrimaryKey_#x#" value="<cfif structkeyexists(tableData[x], 'PrimaryKey')>#tableData[x]['PrimaryKey']#</cfif>" readonly="yes" /></td>
how should i fit it into the select box to select the fields according to the valid values.

I hope i tried my best to explain what the problem is

Which version of CF Server are you on?  If you're on 8, you could use the <cfdbinfo> tag to get more information about the table.  The information it should give you back includes and IS_PRIMARYKEY column.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_d-e_01.html

i am on coldfusion 7
How about a simple checkbox to check if the field is the primary key?


<input type="Radio" name="isPrimaryKey_#kk#" value="Yes"
<cfif structkeyexists(tableData[x], 'PrimaryKey') AND tableData[x]['PrimaryKey'] is true>checked="checked"</cfif>> Is a Primary Key

Open in new window

How about a simple checkbox to check if the field is the primary key?

 <!-- richText -->                                                        
1:

2:

Open in new window

                                 
<input type="Radio" name="isPrimaryKey_#kk#" value="Yes"

<
cfif
 
structkeyexists
(
tableData
[
x
], 
'PrimaryKey'
) 
AND
 
tableData
[
x
][
'PrimaryKey'
] 
is
 
true
>
checked="checked"
</
cfif
>
> Is a Primary Key

But will that not change the whole structure as i am only trying to figure that first two rows where
the 


for field ID:
<select>
 <option value="counter" selected>cf_sql_integer</option>

Open in new window

  <option value="number">cf_sql_integer</option>
  <option value="number">cf_sql_double</option>
</select>

for field age:

<select>
    <option value="counter">cf_sql_integer</option>
   <option value="number" selected>cf_sql_integer</option>
  <option value="number">cf_sql_double</option>
</select>


so problem is coming only on  COUNTER, NUMBER for field ID, age.

when i try to run the Alter Command, it  count all rows field as counter unless otherwise the field value is cf_sql_double.

Open in new window

I dont' understand what you're asking for.  

Is the database function identifying the primary key ?   Yes , No ?

What is your goal with the code above?    Your comments do not tell me enough of what you're trying to do.    If you're trying to save this to the database, what columns and values do the table have?  

If you read back this statement, you may see what I mean...

But will that not change the whole structure as i am only trying to figure that first two rows where
the 
 
for field ID:
<select>
<option value="counter" selected>cf_sql_integer</option>
</pre>
  <option value="number">cf_sql_integer</option>
 <option value="number">cf_sql_double</option>
</select>
 
 
Huh?

Open in new window

Ok i think i m making things Complex here:

my DB Code is like this:

id                  AutoNumber
age              number

let's consider the above 2 fields only.

now what my purpose ir:
  • I would be changing the datatypes of the above fields.
  • I would not be changing the Primary key datatype which is AutoNumber
  • the other colums datatypes can be changed.
what my code is doing:
  • if i encounter a datatype of COUNTER (Auto Number), it selects CF_SQL_INTERGER
  • now i encounter datatype o NUMBER, it selects CF_SQL_INTERGER
  • in both cases, it selects as COUNTER.
Now when i run my Alter Command, It pass the fields:

counter, counter

1: counter is primary key it is o
2. it should pass it as number, but it pass as counter.

that way alter command shows error.

parameter 1 of variable counter not defined. this will show .

why i considerred adding the primary key to option box to make it different from other CF_SQL_INTERGER.

u will see my option boxes have something like this:

<cfif #fieldType# EQ 'CF_SQL_AUTO'>COUNTER
                                          <cfelseif #fieldType# IS 'CF_SQL_VARCHAR'>TEXT
                                          <cfelseif #fieldType# IS 'CF_SQL_LONGVARCHAR'>MEMO
                                          <cfelseif #fieldType# IS 'CF_SQL_DATE'>DATETIME
                                          <cfelseif #fieldType# IS 'CF_SQL_INTEGER'>NUMBER
                                          <cfelseif #fieldType# IS 'CF_SQL_DOUBLE'>NUMBER
                                          <cfELSEif #fieldType# IS 'CF_SQL_BIT'>YESNO
                                          <cfelseif #fieldType# IS 'CF_SQL_MONEY'>CURRENCY</cfif>"
 
                                          
<
cfif fieldtype eq tabledata
[
x
][
'CF_DataType'
]>
selected
=
"selected"
<
/cfif>>#fieldType#</
option
>

Open in new window

in this:

<cfif #fieldType# EQ 'CF_SQL_AUTO'>COUNTER

Open in new window

i would like to add something like table[x]primary key eq true, it gets as counter, all other gets to number.

if you can purpose a better way to do this, please guide me
Thanks

I hope i have cleared all the stuff, if you are not understood.

Please ask me again.
>  why i considerred adding the primary key to option box to make it different from other CF_SQL_INTERGER

I agree with this approach.   If you need to identify the primary key differently from a regular integer, this makes sense.   BUT,  it will not work if you have any primary keys that are strings,  that could be a problem.

> i would like to add something like table[x]primary key eq true, it gets as counter, all other gets to number

Start your CFIF statement with the test for the primary key, then CFELSE the field type after that.  

<cfif table[x]primary  is true>  ---- then it's a primary key
<cfelseif fieldType is "CF_SQL_INTEGER"> ---- then its a normal number, not a key
<cfelseif fieldType is "CF_SQL_DOUBLT">
  etc...


>  Now when i run my Alter Command, It pass the fields

what is the "alter command" ?


sorry, after midnight here, have to get to bed.  Will check back in the morning.
Ok Mate!!

I tried this:

<cfif #fieldType# EQ 'CF_SQL_INTEGER' OR tabledata[x]['PrimaryKey'] is true>COUNTER

but it creates very difficul understanble attitute in coldfusion:

Here is DOM Source code i got

<table align="center">
          <tr>
            
            <th>Length:</th>
            <th>Column Name:</th>
            <th>Data Type:</th>
            <th>Primary Key:</th>
            <th>Increment:</th>
          </tr>
          
           
		   
            <tr>
            
            <td><input type="text" size="4" name="tlength_1" value="" /></td>
            <td>
            <input type="text" name="tcolumnName" value="listid" readonly="yes" />
          </td>
          <td>
                
                  <select name="fieldtype_1" id="fieldType_1">
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_VARCHAR</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_AUTO</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_LONGVARCHAR</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  selected="selected">CF_SQL_INTEGER</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_DOUBLE</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_MONEY</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_BIT</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_DATE</option>
                      
                    
                  </select>
                  
                </td>
            <td><input type="text" name="isPrimaryKey_1" value="true" readonly="yes" /></td>
            <td><input type="text" name="tIncrement_1" value="true" readonly="yes" /></td>
            <td>
			</td>
          </tr>
		  
        
        
           
		   
            <tr>
            
            <td><input type="text" size="4" name="tlength_2" value="" /></td>
            <td>
            <input type="text" name="tcolumnName" value="cid" readonly="yes" />
          </td>
          <td>
                
                  <select name="fieldtype_2" id="fieldType_2">
                    
                    
                      <option value="TEXT
					  " 
					  >CF_SQL_VARCHAR</option>
                      
                    
                    
                      <option value="" 
					  >CF_SQL_AUTO</option>
                      
                    
                    
                      <option value="MEMO
					  " 
					  >CF_SQL_LONGVARCHAR</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  selected="selected">CF_SQL_INTEGER</option>
                      
                    
                    
                      <option value="NUMBER
					  " 
					  >CF_SQL_DOUBLE</option>
                      
                    
                    
                      <option value="CURRENCY" 
					  >CF_SQL_MONEY</option>
                      
                    
                    
                      <option value="YESNO
					  " 
					  >CF_SQL_BIT</option>
                      
                    
                    
                      <option value="DATETIME
					  " 
					  >CF_SQL_DATE</option>
                      
                    
                  </select>
                  
                </td>
            <td><input type="text" name="isPrimaryKey_2" value="false" readonly="yes" /></td>
            <td><input type="text" name="tIncrement_2" value="false" readonly="yes" /></td>
            <td>
			
            <a href="home.cfm?action=show&table=grlist&access=cid" title="Delete Column"><img title="Delete This Column" src="delete.png" /></a></td>
          </tr>
		  
        
        
           
		   
            <tr>
            
            <td><input type="text" size="4" name="tlength_3" value="255" /></td>
            <td>
            <input type="text" name="tcolumnName" value="listname" readonly="yes" />
          </td>
          <td>
                
                  <select name="fieldtype_3" id="fieldType_3">
                    
                    
                      <option value="TEXT
					  " 
					  selected="selected">CF_SQL_VARCHAR</option>
                      
                    
                    
                      <option value="" 
					  >CF_SQL_AUTO</option>
                      
                    
                    
                      <option value="MEMO
					  " 
					  >CF_SQL_LONGVARCHAR</option>
                      
                    
                    
                      <option value="COUNTER
					  " 
					  >CF_SQL_INTEGER</option>
                      
                    
                    
                      <option value="NUMBER
					  " 
					  >CF_SQL_DOUBLE</option>
                      
                    
                    
                      <option value="CURRENCY" 
					  >CF_SQL_MONEY</option>
                      
                    
                    
                      <option value="YESNO
					  " 
					  >CF_SQL_BIT</option>
                      
                    
                    
                      <option value="DATETIME
					  " 
					  >CF_SQL_DATE</option>
                      
                    
                  </select>
                  
                </td>
            <td><input type="text" name="isPrimaryKey_3" value="false" readonly="yes" /></td>
            <td><input type="text" name="tIncrement_3" value="false" readonly="yes" /></td>
            <td>

Open in new window

"but it creates very difficul understanble attitute in coldfusion"

Not sure I understand the problem?
well sorry i created ambigious english words above trying to nullify my situtation.

Well If You see the Source code of my last post you will see that if i make change to the

<cfif #fieldType# EQ 'CF_SQL_INTEGER' OR tabledata[x]['PrimaryKey'] is true>COUNTER
 to identify that at least cf_sql_interger  is primary key , set it as counter and set all other other  to number, double etc.

but that does not seems to work either
can you give us a dump of tabledata ?

<cfdump var="#tableData#">

and a dump of fieldTypes ?

<cfdump var="#fieldTypes#">

yes of course. i will attachment here as an attachment.

and the dump of fieldtype is:

cf_sql_date, cf_sql_interger, cf_sql,varchar, etc

untitled.GIF
Ok, this code adds a new element to that table structure for  the column Type.   It creates this element based on the data type.

Please add this towards the top of your code (after table strucutre is created)
and see if the dump contains the right data for the new columnType


<cfloop from="1" to="#arrayLen(tableData)#" index="x">
   <cfset newType = "">
   <cfif tableData[x].primaryKey is true>
     <cfset newType = "COUNTER">
   <cfelse>
     <cfswitch expression="#tableData[x].CF_dataType#">
       <cfcase value="CF_SQL_VARCHAR">
          <cfset newType = "TEXT">
       </cfcase>
       <cfcase value="CF_SQL_VARCHAR">
          <cfset newType = "TEXT">
       </cfcase>
       <cfcase value="CF_SQL_LONGVARCHAR">
          <cfset newType = "MEMO">
       </cfcase>
       <cfcase value="CF_SQL_DATE">
          <cfset newType = "DATETIME">
       </cfcase>
       <cfcase value="CF_SQL_INTEGER">
          <cfset newType = "DATETIME">
       </cfcase>
       <cfcase value="CF_SQL_INTEGER,CF_SQL_DOUBLE">
          <cfset newType = "NUMBER">
       </cfcase>
       <cfcase value="CF_SQL_BIT">
          <cfset newType = "YESNO">
       </cfcase>
       <cfcase value="CF_SQL_MONEY">
          <cfset newType = "CURRENCY">
       </cfcase>
     </cfswitch>
   </cfif>
   <cfset tableData[x].columnType = newType>
</cfloop>
<cfdump var="#tableData#">

Open in new window

Your Code Works as i have not implemented it in my exact cod, i am attaching the file which i am getting by running your code.

I Placed your code exactly after my cfinvoke to get table structure to see what i get.


This stuff has eaten my brain so much that i am getting afraid to see where exactly i should put it to make it work for me.

Please

untitled.GIF
Now we have the right values.   What is your goal?
What are you trying to do with it?

Do you want to put it into a <SELECT> tag and ask the user to change it if he wants to ?   If yes, you need to build a select tag with it?

I really do not know what you're trying to accomplish with all this.  I am missing the "Big Picture"  ...  what is your task?
Here is the SELECT tag that you were working to build.

What else do you need?

<cfset fieldTypeOptions = "COUNTER,TEXT,MEMO,DATETIME,NUMBER,YESNO,CURRENCY">
<cfoutput>
 <select name="fieldtype_#x#" id="fieldType_#x#">
 <cfloop index="aVal" list="#fieldTypeOptions#">
 <option value="#aVal#" <cfif aVal is tableData[x].columnType>selected="selected"</cfif>> #aVal# </option>
 </cfloop>
</select> 
</cfoutput>

Open in new window

Thanks Mate, Ultimate goal is to alter the table structure.

  • Alter the length of fields only of those which are varchar, other it will ignore.
  • Alter the datatype of the fields.
  • till now, primary key datatype will not be changed.
  • all other field's datatype can be changed.
For a Some reason, i have my alter command like this:

<cfloop from="1" to="#listLen(form.tcolumnName)#" index="idx">
        <cfquery datasource="#session.newdsn#" name="change">
    ALTER table #list_name#
    ALTER COLUMN #ListGetAt(form.tcolumnName, idx)#  #form['fieldtype_' & idx]# <cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) EQ "TEXT">#form['tlength_' & idx]#</cfif>
    </cfquery>
      </cfloop>this is my BIG picture till yet,

But in case i also want to ask a question :

  1. If in future, if i need to change primary key data structure, what will be the problem and how can i be able to use that in alter command
Hi Experts, I am using This query to alter Command, but it not changing the length and the dtatype attribute of the table

<cfquery datasource="#session.newdsn#" name="change">
   ALTER table #list_name#
   ALTER COLUMN #ListGetAt(form.tcolumnName, idx)#  #form['fieldtype_' & idx]# <cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) EQ "TEXT">#form['tlength_' & idx]#</cfif>
   </cfquery>
     </cfloop>


ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, I wuld ask the related question here