asked on

How i Dynamically Alter table in Access

I have one query results derived from my Coldfusion to dynamically alter the table which is in Access:

I have the following in Command:
<th>Is Nullable:</th>
<th>Length:</th>
<th>Column Name:</th>
<th>Data Type:</th>
<th>Primary Key:</th>
<th>Increment:</th>

<cfinclude template="sqlserertypes.cfm">
<cfset fieldTypeList = "">
                        <cfloop from="1" to="#ArrayLen(fieldTypes)#" index="i">
                              <cfset fieldTypeList = ListAppend( fieldTypeList, fieldTypes[i].fieldtype )>
                        </cfloop>
<tr><td colspan="1"><cfloop from="1" to="#arrayLen(tableData)#" index="x">
<cfloop collection="#tabledata[x]#" item="key">
      <tr><td>
<cfif key eq "ColumnName">
<cfinput type="text" name="tcolumnName" value="#tableData[x][key]#" readonly="yes"><br />
</cfif>
</td>
<td>
<cfif key eq "AllowNulls">
<input type="checkbox" name="tIS_NULLABLE" value="1" <cfif tableData[x][key] is true>checked="checked"</cfif>><br />
</cfif>
</td>
<td><cfif key eq "length">
<cfinput type="text" size="4" name="tlength" value="#tableData[x][key]#"><br />
</cfif>
</td><td>
<cfif key eq "CF_DataType">
<select name="fieldtype" id="fieldType">
<cfloop list="#fieldTypeList#" index="fieldType">
      <cfoutput><option value="#fieldType#" selected="selected">#fieldType#</option></cfoutput>
</cfloop>
</select>
<cfinput type="text" name="ttexttype" value="#tableData[x][key]#"><br />
</cfif>
</td>
<td>
<cfif key eq "PrimaryKey">
<cfinput type="text" name="isPrimaryKey" value="#tableData[x][key]#" readonly="yes"><br />
</cfif>
</td>
<td>
<cfif key eq "increment">
<cfinput type="text" name="tIncrement" value="#tableData[x][key]#" readonly="yes"><br />
</cfif>
</td>
</cfloop>
</cfloop></td></tr>

This File <cfinclude template="sqlserertypes.cfm"> is attached with this:

The above dynamicaly list the table contents in the text fields.

I am trying to alter the access database using the alter Command. But exactly i do not know how i do this.
Please help me:

untitled.GIF

azadisaryev

1)
<cfset fieldTypeList = "">
<cfloop from="1" to="#ArrayLen(fieldTypes)#" index="i">
<cfset fieldTypeList = ListAppend( fieldTypeList, fieldTypes[i].fieldtype )>
</cfloop>

is way too long - just do <cfset fieldTypeList = ArrayToList(fieldTypes)>

2) MS Access ALTER TABLE syntax run-down:
http://www.blueclaw-db.com/alter_table_ddl.htm

Azadi

azadisaryev

oops... disregard my point 1) regarding ArrayToList - looks like your array is not an array of simple values, so ArrayToList will not work in this case... sorry!

Coast Line

ASKER

Well does not show that we can alter more than 1 column at a time. and also is it possible to alter more than 1 column at a time in msaccess.

azadisaryev

just do it in a loop?

Coast Line

ASKER

Ok i tried doing in a loop but i am getting an error: and how i do is like this:

ALTER table #list_name#
<cfloop from="1" to="#listLen(form.tcolumnName)#" index="i">
      ALTER COLUMN (#ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)# (#ListGetAt(tlength, i)#) #ListGetAt(form.tIS_NULLABLE, i)#)
      </cfloop>

and but i get an error: like this:

Invalid list index 3.
In function ListGetAt(list, index [, delimiters]), the value of index, 3, is not a valid as the first argument (this list has 2 elements). Valid indexes are in the range 1 through the number of elements in the list.

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 44
Called from C:\Inetpub\wwwroot\\index.cfm: line 30
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

42 :       ALTER table #list_name#
43 : <cfloop from="1" to="#listLen(form.tcolumnName)#" index="i">
44 :       ALTER COLUMN (#ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)# (#ListGetAt(tlength, i)#) #ListGetAt(form.tIS_NULLABLE, i)#)
45 :       </cfloop>
46 :       </cfif>

azadisaryev

there is A LOT at play here, so let's start with the basics:
put <cfdump var="#form"> before your ALTER TABLE query and see what data you have in the form.

at least one of your form fields is a checkbox: those will ONLY be submitted with the form IF they are checked/selected.

you are using form values in your sql, but i am not at all sure those values correspond to expected sql syntax for ALTER TABLE statement:
i.e. - again on your checkbox field - it's value will be 1 if it is selected, so the part of sql statement that uses that value [#ListGetAt(form.tIS_NULLABLE, i)#] will evaluate to 1:
ALTER COLUMN {somename} {fieldtype} 1
which, iirc, is NOT correct syntax for this statement...

Azadi

Coast Line

ASKER

Okay I Cfdumped the form values and Got this Stuff:

CHANGEDATABASE  	 Change Database
FIELDNAMES 	ISPRIMARYKEY,TCOLUMNNAME,FIELDTYPE,TTEXTTYPE,TINCREMENT,TIS_NULLABLE,TLENGTH,TTABLENAME,CHANGEDATABASE
FIELDTYPE 	NUMBER,DATETIME,MEMO
ISPRIMARYKEY 	true,false,false
TCOLUMNNAME 	id,Class,Name
TINCREMENT 	true,false,false
TIS_NULLABLE 	1,1
TLENGTH 	255,250
TTABLENAME 	test
TTEXTTYPE 	CF_SQL_INTEGER,CF_SQL_VARCHAR,CF_SQL_VARCHAR

Open in new window

azadisaryev

as you can see, your TCOLUMNNAME field has 3 values, but fields TIS_NULLABLE and TLENGTH have only 2 values - that's what causing your error: there is no element in position 3 in both of those lists.

you need to have same number of values in each other field you use in your sql statement as in your TCOLUMNNAME field.

i am afraid you will have to re-code your form and have uniquely-named fields instead of having several fields with same name which results in comma-delimited lists as field values. for instance, right now it is not possible to say, based on the posted form data, which element in the TIS_NULLABLE and TLENGTH fields should be added - is it the first, second or third that is missing?

you can easily use your X list index to make your form fields unique - just add it to the NAME attribute of your fields, ie:
<cfif key eq "AllowNulls">
<input type="checkbox" name="tIS_NULLABLE_#x#" value="1" <cfif tableData[x][key] is true>checked="checked"</cfif>><br />
</cfif>

leave your TCOLUMNNAME field non-unique (do not add _#x# to it's name): listlen() of this field will give you the total number of loop iterations when you generating your ALTER TABLE query later (just like you have now).

what unique field name will give you is the ability to check if they are defined in the form scope and to alter individual values when needed.

so you ALTER TABLE code generation may look something like this:

[IMPORTANT: code not tested!]

[NOTE 1:
i am pretty sure that one can't have multiple ALTER COLUMN statements in an ALTER TABLE statement - if so, your whole cfquery shold be placed inside a cfloop instead on having a cfloop inside your query. my code sample below is based on this suspicion;
furthermore, it appears that defining a column as NOT NULL is only supported in ADD COLUMN statement, and is not supported in ALTER TABLE...]

[NOTE 2:
i am assuming you name form fields unique by adding _#x# to their names, which will result in your form having field names like filedtype_1, fieldtype_2, etc - for all fields except tcolumnname field!]

<cfloop from="1" to="#listLen(form.tcolumnName)#" index="idx">
<cfquery ...>
ALTER table #list_name#
ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) GT 0>#form['fieldtype_' & idx]# <cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#
</cfquery>
</cfloop>

see if that works out.... (fingers crossed).
don;t forget to backup your db before you try any changes!!!

Azadi

Coast Line

ASKER

Okay Let's Make it very simple:

1. The tables values are not appearing under the same column defined as header as:

Then What is let's make the Checkbox field as textbox and then we see what goes on with it.

I changed a code bit to make it simpler

then i run it again and got the following:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 48
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1
Called from C:\Inetpub\wwwroot\index.cfm: line 48
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

46 :       <cfquery datasource="#session.newdsn#" name="change">
47 :       ALTER table #list_name#
48 :       ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# #ListGetAt(form.fieldtype, idx)# (#ListGetAt(tlength, idx)#) #ListGetAt(form.tIS_NULLABLE, idx)#)
49 :       </cfquery>
50 :       </cfloop>

SQLSTATE        42000
SQL        ALTER table gradmin ALTER COLUMN user_id DATETIME (255) Not Null)
VENDORERRORCODE        -3554
DATASOURCE        testdatabase2
Resources:

<th>Is Nullable:</th>
                  <th>Length:</th>
                  <th>Column Name:</th>
                  <th>Data Type:</th>
                  <th>Primary Key:</th>
                  <th>Increment:</th>
 
and then the above code not appearing underneath the same heading:
 as:
 
<cfinclude template="sqlserertypes.cfm">
                <cfset fieldTypeList = "">
				<cfloop from="1" to="#ArrayLen(fieldTypes)#" index="i">
  					<cfset fieldTypeList = ListAppend( fieldTypeList, fieldTypes[i].fieldtype )>
				</cfloop>
                <tr><td colspan="1"><cfloop from="1" to="#arrayLen(tableData)#" index="x"> 
                <cfloop collection="#tabledata[x]#" item="key">
            	<tr><td>
                  <cfif key eq "ColumnName">
                      <cfinput type="text" name="tcolumnName" value="#tableData[x][key]#" readonly="yes"><br />
                    </cfif>
                </td>
                <td><cfif key eq "AllowNulls">
 						<input type="text" name="tIS_NULLABLE" <cfif tableData[x][key] is 'yes'>value="Null"<cfelse>value="Not Null"</cfif>>
                    </cfif>
                 </td>   
                 <td><cfif key eq "length">
                      <cfinput type="text" size="4" name="tlength" value="#tableData[x][key]#"><br />
                    </cfif>
                 </td><td>   
                  <cfif key eq "CF_DataType">
                      <select name="fieldtype" id="fieldType">
                        <cfloop list="#fieldTypeList#" index="fieldType">
                        	<cfoutput><option value="#fieldType#" selected="selected">#fieldType#</option></cfoutput>
                        </cfloop>
                        </select>
                      <cfinput type="text" name="ttexttype" value="#tableData[x][key]#"><br />
                    </cfif>
                  </td>
                  <td>  
                  <cfif key eq "PrimaryKey">
                      <cfinput type="text" name="isPrimaryKey" value="#tableData[x][key]#" readonly="yes"><br />
                    </cfif>
                  </td>
                  <td>  
                  <cfif key eq "increment">
                      <cfinput type="text" name="tIncrement" value="#tableData[x][key]#" readonly="yes"><br />
                    </cfif>
                  </td>   
                </cfloop>    
              </cfloop></td></tr>

Open in new window

azadisaryev

> The tables values are not appearing under the same column defined as header:

well... re-shuffle your output to make columns appear under their headings!
currently you output outputs columns in this order:
ColumnName - AllowNulls - length - CF_DataType - PrimaryKey - increment

re-organize those <tr><td><cfif ...> blocks in the order you want!

> then i run it again and got the following [error]:

you did not run the code i posted - you are still running your old ListGetAt() code...

did you read the notes in my post? your sql does not look syntactically correct for ALTER COLUMN statement.
ALTER table gradmin ALTER COLUMN user_id DATETIME (255) Not Null)

for starters, there is a ) at the end which should not be there. second, i do not believe DATETIME (255) is a valid column definition - DATETIME columns do not have a length, though it may not throw an error - i have not tested it.

man, and i thought i had one too many beers!
read my previous post carefully. copy-paste code carefully.

what you are dealing with here is a very complicated matter - you need to account for numerous possible combinations of your form values to create a syntactically-correct sql statement. trial and error all the way + lots and lots of nested <cfif>/<cflelse> blocks is the way...

good luck - will check back on you tomorrow (it's late now here in Laos and i had too many beers.... time to go home....)

Azadi

Coast Line

ASKER

Thanks Man,

What i did is make the checkbox to text field and tried simply theat value if it works or not:

2nd option:

I tried your code and iget error when i run the following command: as:

<td><cfif key eq "AllowNulls">
                                   <input type="checkbox" name="tIS_NULLABLE_#x#" value="1" <cfif tableData[x][key] is true>checked="checked"</cfif>>
</cfif>
</td>

Error i encountered:

Element fieldtype_1 is undefined in a Java object of type class coldfusion.filter.FormScope.

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 48
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

46 :       <cfquery datasource="#session.newdsn#" name="change">
47 :       ALTER table #list_name#
48 :       ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <cfif isdefined(form['fieldtype_' & idx])
49 :       AND len(trim(form['fieldtype_' & idx])) GT 0>#form['fieldtype_' & idx]#</cfif> <cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#</cfif>
50 :       </cfquery>

<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['fieldtype_' & idx]) 
	AND len(trim(form['fieldtype_' & idx])) GT 0>#form['fieldtype_' & idx]#</cfif> <cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#</cfif>
	</cfquery>
	</cfloop>
 
 
Here is the error i encounter:

Open in new window

azadisaryev

you should append _#x# to NAME attribure of ALL your fields EXCEPT tcolumnname field, not just to tIS_NULLABLE field like in my example.

looks like you did not add it to FIELDTYPE field...

(yes, i am still drinking beer....)

Azadi

Coast Line

ASKER

Ok Mate, Lets make it very very simple. I am stuck and i am running into lots and lots of messy code. We will remove the Null Attribute and how we do next

Please guide me in easy and simple ways

Coast Line

ASKER

Ok I made this very simple:

i tried removing the nullable from query and tried simple way:

then in my alter command:

i used the same listgetat() function to make sure it work.

it did worked the very first time but later on it generates this error:

my code was simple 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)# #ListGetAt(form.fieldtype, idx)# 
      </cfquery>
      </cfloop>

It executes and error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.

Table comes at:

ALTER table test ALTER COLUMN id AUTO NUMBER

well must i tell here that the above did worked the very first time but did not worked later.

_agx_

> ALTER table test ALTER COLUMN id AUTO NUMBER

IIRC, "AUTO NUMBER" is not a valid data type either. I believe the Access version is called COUNTER.

However, as Azadi said "what you are dealing with here is a very complicated matter". First you have to make sure you know the data types. After that, take it one step at a time ...

Coast Line

ASKER

Ok, I done it but removing the isnull aattribute, if i use the isnull textbox then i encounter error. I tried all ways to figure out what is going on with it with no help was worth it.

as i did stated before after using the azadi's methodology of

<cfif isdefined(form['fieldtype_' & idx])
AND len(trim(form['fieldtype_' & idx])) GT 0>#form['fieldtype_' & idx]#</cfif> <cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#</cfif>

i tried again with old listgetat method but remving isnull and it worked perfectly. Bur if i use isnull and azadi's method of dynamicaaly updating i encounter an java error of:

Error i encountered:

Element fieldtype_1 is undefined in a Java object of type class coldfusion.filter.FormScope.

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 48
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

46 : <cfquery datasource="#session.newdsn#" name="change">
47 : ALTER table #list_name#
48 : ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <cfif isdefined(form['fieldtype_' & idx])
49 : AND len(trim(form['fieldtype_' & idx])) GT 0>#form['fieldtype_' & idx]#</cfif> <cfif isdefined(form['tlength_' & idx]) AND len(trim(form['tlength_' & idx])) GT 0>#form['tlength_' & idx]#</cfif>
50 : </cfquery>

Part 2 of this:

as i try to list colums dynamicaaly within cfloop, it appears very wored, i tried changinging <tr><td> manyy times but fields does not appearing each under: as attahced in this post

untitled.GIF

ASKER CERTIFIED SOLUTION

azadisaryev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Coast Line

ASKER

Arrghh1 You solution seems preetty ZGood, but i am encountering an error which i am unable to locate:

in the code it shows this eror:

<cfloop from="1" to="#arrayLen(tableData)#" index="x">

<cfif isstruct(tabledata[x]) AND structkeyexists(tabledata[x], 'ColumnName')>
<cfoutput>
<tr>
<td>
<input type="text" name="tIS_NULLABLE_#x#" value="<cfif structkeyexists(tabledata[x], 'AllowNulls'><cfif tableData[x]['AllowNulls'] is 'yes'>Null<cfelse>Not Null</cfif></cfif>" />
</td>
<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" /></cfif>
</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">
<option value="#fieldType#" <cfif fieldtype eq tabledata[x]['CF_DataType']>selected="selected"</cfif>>#fieldType#</option>
</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>
</tr>
</cfoutput>
</cfif>
</cfloop>

I tried figuring out myself and checked every part of it but i did not seen any coide error but still it is not running

it says end </cfoutput> required a matching start tag.

Coast Line

ASKER

Haha Solved It Thanks

Coast Line

ASKER

Very Cool Thanks