Solved

Altering Column Name Using Coldfusion of Microsoft Access

Posted on 2009-03-30
22
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
22 Comments
 
LVL 19

Expert Comment

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

Expert Comment

by:duncancumming
ID: 24021686
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24021744
at the same time, i also trying to are you also trying to change the field length at the same time?  
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 16

Expert Comment

by:duncancumming
ID: 24021795
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24021872
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
ID: 24021980
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24022045
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
ID: 24022226
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24022758
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
ID: 24022802
Did you put this into your form:

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

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24023018
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
 
LVL 16

Expert Comment

by:duncancumming
ID: 24023066
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24023227
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
ID: 24023273
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24048234
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24048238
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
ID: 24051118
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24051158
big mess!
do i need to write three diffrent queries
0
 
LVL 16

Accepted Solution

by:
duncancumming earned 500 total points
ID: 24051274
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 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24052017
"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
ID: 24052077
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 16

Author Closing Comment

by:Gurpreet Singh Randhawa
ID: 31564457
Thanks Dude
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

732 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