Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Altering Column Name Using Coldfusion of Microsoft Access

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
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 11
  • 10
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
try this
 ALTER table #list_name#
        MODIFY  #ListGetAt(form.tcolumnName, idx)#
0
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
at the same time, i also trying to are you also trying to change the field length at the same time?  
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
duncancummingCommented:
Did you put this into your form:

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

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
and when i run the query, i got the error executing database query:
by the way, does access support RENAME command
0
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
big mess!
do i need to write three diffrent queries
0
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
"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
 
duncancummingCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Thanks Dude
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now