Solved

Altering Column Name Using Coldfusion of Microsoft Access

Posted on 2009-03-30
22
439 Views
Last Modified: 2013-12-24
Hello Experts, i am trying to convert the name of the column to some other using coldfusion 7. everything is generated, only need advice with the alter table command.

I have something i am on it. let not go too far, i will keep it simple:

<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']#" />
          </td>
</tr>
</cfloop><closing all tags>
<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)# 

	<cfif isdefined("form.tlength_#idx#") AND form['tlength_' & idx] GT 0 AND form['tlength_' & idx] LTE 255>

	(#form['tlength_' & idx]#)<cfelse>(255)</cfif>

</cfquery>

</cfloop>
 

so when i try to rename my colum i get this error:
 

Error! Could not find field 'CountryClassic'. Error Executing Database Query. 
 

i named the orginal field CountryName to CountryClassic and got this error.
 

what i am missing in my database i really do not know.
 

The altter command is something like this:
 

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |

ALTER COLUMN field type[(size)] |

CONSTRAINT multifieldindex} |

DROP {COLUMN field I CONSTRAINT indexname} }
 

but i think i am doing right, so what i am doing wrong

Open in new window

0
Comment
Question by:myselfrandhawa
  • 11
  • 10
22 Comments
 
LVL 19

Expert Comment

by:erikTsomik
Comment Utility
try this
 ALTER table #list_name#
        MODIFY  #ListGetAt(form.tcolumnName, idx)#
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
I believe the syntax is:
ALTER TABLE tblName
RENAME COLUMN oldName TO newName

If all you're wanting to do is rename a column, I'm not sure why you're also trying to specify the varchar length, you should be able to just ignore that.  Or are you also trying to change the field length at the same time?  
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
at the same time, i also trying to are you also trying to change the field length at the same time?  
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
Do it as two separate things.   First rename it.  Then resize it.  Or vice-versa.  

To resize, I think it's just like this (e.g. to resize it to varchar 100)

    ALTER TABLE tblName
    ALTER COLUMN columnName varchar(100)


0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
but my structure is complex and i will run into big mess, here is the whole code what i have:

<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)# <!---#ListGetAt(form.fieldtype, idx)#---> 
    #form['fieldtype_' & idx]# 
 <cfif form['fieldtype_' & idx] EQ "TEXT">
 <cfif isdefined("form.tlength_#idx#") AND form['tlength_' & idx] GT 0 AND form['tlength_' & idx] LTE 255>
 (#form['tlength_' & idx]#)<cfelse>(255)</cfif>
 </cfif>
 <!---<cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) EQ "TEXT">
    #form['tlength_' & idx]#
    <cfelse>
    </cfif>--->
 <!---<cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#</cfif>--->
 </cfquery>
      </cfloop>


0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
It's certainly complex and a mess.   Can you give more information about what is in all your form fields (or just post up the basic code from your form)?
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
ok here is the complete:
Access only:
<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']#" />
          </td>
          <td>
              <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>
              </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>
            </cfif></td>
          </tr>
         </cfoutput>
        </cfif>
        </cfloop>
        <tr><td colspan="4"></td></tr>
      </table>



the alter command is above
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
Right, there's no way this will work to do both resize and rename in the one operation.  You'd have to have some way of saying what the current column name is and what the new column name is.  It looks like you're only asking for the new name.  

I'd suggest make a read-only column on your form for the current name, and keep the text field for an optional new name (or leave blank for no change).

Your query I would still do as two operations.  KISS !

I'd also say you should wrap all the updates in a cftry, as there's much scope for errors, e.g. what happens when someone tries to rename a column to a reserved word?  And use cfqueryparam where possible.  Although I guess if you're giving users write-access to rename your table columns you've already got security covered elsewhere?  



Stick this before your loop:

<input type="hidden" name="rows" value="#arrayLen(tableData)#">
 

Gone with the same _#x# naming format as all your other formfields:

<input type="text" name="tcolumnName_new_#x#" value="" />

<input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
 
 

<cfloop from="1" to="#form.rows#" index="idx">

	<cfif Len(form['tcolumnName_new_' & idx])>

	<!--- rename column --->

		<cfquery datasource="#session.newdsn#" name="rename">

			ALTER table #list_name#

			RENAME COLUMN #form['tcolumnName_old_' & idx]#

			TO #form['tcolumnName_new_' & idx]#

		</cfquery>

		

		<cfset form['tcolumnName_old_' & idx] = form['tcolumnName_new_' & idx]>

	</cfif>

	

	<!--- change column size/type --->

	<cfquery datasource="#session.newdsn#" name="change">

		ALTER table #list_name#

		ALTER COLUMN #form['tcolumnName_old_' & idx]# 

		#form['fieldtype_' & idx]# 

		<cfif form['fieldtype_' & idx] EQ "TEXT">

			<cfif 	isdefined("form.tlength_#idx#") 

					AND form['tlength_' & idx] GT 0 

					AND form['tlength_' & idx] LTE 255>

				(#form['tlength_' & idx]#)

			<cfelse>

			(255)

			</cfif>

		</cfif>

	</cfquery>

</cfloop>

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
it errors out this:
 Element ROWS is undefined in FORM.
 
The error occurred in C:\Inetpub\wwwroot\Classic\home.cfm: line 90
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 58
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 1

88 :       <input type="text" name="tcolumnName_new_#x#" value="" />
89 :         <input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
90 :       <cfloop from="1" to="#form.rows#" index="idx">
91 :         <cfif Len(form['tcolumnName_new_' & idx])>
92 :         <!--- rename column --->

while rows is already defined above

0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
Did you put this into your form:

<input type="hidden" name="rows" value="#arrayLen(tableData)#">

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
ok i tried something like this:
<tr>
            <th>Length:</th>
            <th>Column Name:</th>
            <th>Data Type:</th>
            <th>Primary Key:</th>
            <th>Increment:</th>
          </tr>
          <input type="hidden" name="rows" value="#arrayLen(tableData)#">
          <input type="text" name="tcolumnName_new_#x#" value="" />
               <input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
          <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']#" />
          </td>
[Content goes down]

Now i click the change submit button and run the alter command i get this below error:
 The value #arrayLen(tableData)# cannot be converted to a number.
 
The error occurred in C:\Inetpub\wwwroot\Classic\home.cfm: line 88
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 58
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 1

86 :       </cfoutput><cfabort>--->
87 :       
88 :       <cfloop from="1" to="#form.rows#" index="idx">
89 :         <cfif Len(form['tcolumnName_new_' & idx])>
90 :         <!--- rename column --->



0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
You need to wrap <cfoutput> </cfoutput> around these lines:

<input type="hidden" name="rows" value="#arrayLen(tableData
)#">
          <input type="text" name="tcolumnName_new_#x#" value="" />
               <input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
Well it did not changed the ColumnName. i checked access after completing the operation, the message comes and say ok, done. but column name remains as it is:


note that
<input type="text" name="tcolumnName_new_#x#" value="" />
              <input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
i have put uder cfloop otherwise it was showing error:
error executing database query:
i tried something like this:
<cfoutput>
          <input type="hidden" name="rows" value="#arrayLen(tableData)#">
          
          </cfoutput>  
          <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_new_#x#" value="" />
               <input type="hidden" name="tcolumnName_old_#x#" value="#tableData[x]['ColumnName']#" />
            <input type="text" name="tcolumnName" value="#tableData[x]['ColumnName']#" />
          </td>


it also shows empty field against primary key field, can that be the cause of its error
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
Do you know if the RENAME query actually executed?  i.e. did this statement equal true?
<cfif Len(form['tcolumnName_new_' & idx])>

Try putting in some tracers inside that if statement to check when it executes.

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
Well i tried to outpur the contents of the query what you written here, so to see something like this:


ALTER table Table1 ALTER COLUMN countryID COUNTER ALTER table Table1 RENAME COLUMN CountryName TO counytu ALTER table Table1 ALTER COLUMN counytu TEXT (255)

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
and when i run the query, i got the error executing database query:
by the way, does access support RENAME command
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
What error do you get?  

I thought RENAME worked in Access, but I could be wrong. If not you could add a new column, update it to the contents of the old column, then delete the old column:

1)  Adding a column:
ALTER TABLE yourTableName ADD COLUMN NewName datatype(size)

2)  Run an update statement to copy the data from the old column to the new one:
UPDATE yourTableName SET NewName = OldName

3)  Then drop the old column:
ALTER TABLE yourTableName DROP COLUMN OldName
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
big mess!
do i need to write three diffrent queries
0
 
LVL 16

Accepted Solution

by:
duncancumming earned 500 total points
Comment Utility
Here's a variation on the code I originally posted, to take into account these extra queries.  The only problem might be if you change the datatype and size, then this code will create a new column of the new size, and when it does the copy of data from old to new, it might cause a problem if they're of incompatible types or sizes.

However that wouldn't be that hard for you to work out how to fix...

<cfloop from="1" to="#form.rows#" index="idx">

	<cfif Len(form['tcolumnName_new_' & idx])>

	<!--- rename column --->

		<cfquery datasource="#session.newdsn#" name="addColumn">

			ALTER table #list_name#

			ADD COLUMN #form['tcolumnName_new_' & idx]#

				#form['fieldtype_' & idx]# 

				<cfif form['fieldtype_' & idx] EQ "TEXT">

					<cfif   isdefined("form.tlength_#idx#") 

							AND form['tlength_' & idx] GT 0 

							AND form['tlength_' & idx] LTE 255>

						(#form['tlength_' & idx]#)

					<cfelse>

					(255)

					</cfif>

				</cfif>

		</cfquery>

		

		<cfquery datasource="#session.newdsn#" name="copyData">

			UPDATE #list_name#

			SET #form['tcolumnName_new_' & idx]# = #form['tcolumnName_old_' & idx]#

		</cfquery>

		

		<cfquery datasource="#session.newdsn#" name="dropColumn">

			ALTER table #list_name#

			DROP COLUMN #form['tcolumnName_old_' & idx]#

		</cfquery>

		

		<cfset form['tcolumnName_old_' & idx] = form['tcolumnName_new_' & idx]>

	</cfif>
 

	<!--- change column size/type --->

	<cfquery datasource="#session.newdsn#" name="change">

		ALTER table #list_name#

		ALTER COLUMN #form['tcolumnName_old_' & idx]# 

		#form['fieldtype_' & idx]# 

		<cfif form['fieldtype_' & idx] EQ "TEXT">

			<cfif   isdefined("form.tlength_#idx#") 

					AND form['tlength_' & idx] GT 0 

					AND form['tlength_' & idx] LTE 255>

				(#form['tlength_' & idx]#)

			<cfelse>

			(255)

			</cfif>

		</cfif>

	</cfquery>

</cfloop>

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
"The only problem might be if you change the datatype and size, then this code will create a new column of the new size, and when it does the copy of data from old to new, it might cause a problem if they're of incompatible types or sizes."
u mean if i change the datatype and the size, then this code will fail right?
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
Possibly.  For instance, if the column is originally varchar(100) and you change it to varchar(255), then no problem.  However if you change it to varchar(50) AND if you have values in that column > 50 chars long, then it may not work.

Or if you have a field that is of type memo and you change it to type date, then yes it'll fail.  

But then these things would fail ANYWAY using your system.  I expect you've already thought of that and have worked out how to get around it.


0
 
LVL 15

Author Closing Comment

by:myselfrandhawa
Comment Utility
Thanks Dude
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now