Print Page | Close Window

Check if column/exists and add fields dynamically

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=27392
Printed Date: 29 March 2026 at 4:24am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Check if column/exists and add fields dynamically
Posted By: sunny99
Subject: Check if column/exists and add fields dynamically
Date Posted: 20 May 2009 at 4:00pm
I am trying to dynamically insert form field values into the database. The code has 3 parts to it
1) Check if field already exists
2) Alter table to add columns/fields
3) Insert the values in the column/fields of the table

Html form url is below

users1.jabry.com/sunny/test.html

I have 2nd (altering table) and 3 (inserting values) working.
I am unable to check if fields are existing in the table and keep getting error-

Field 'notes1' already exists in table 'LUReview'.

I will appreciate if you can look into code and let me know the fix. It works fine the first time when no fields are inserted but when i trying inserting again i keep getting field already exists which obviosuly shows my field check code is incorrect.
 
I have tried modifying the code but am still getting following error
------------------------
Item cannot be found in the collection corresponding to the requested name or ordinal.
/geo/Test/insert.asp, line 93
------------------------
Looks like columns/fields are not bring created hence they cannot be found. Can you please look into code for any possible fix.
 

<%
'**********************
'Variable Declarations
'Setting up Objects
'**********************
Dim myRS, objConn, connString, maxfields, strSQL, oField, nameExists, oRecordset

connString = Server.MapPath("misc.mdb")


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & connString
Set rsAdd = Server.CreateObject("ADODB.Recordset")


maxfields = Request.Form("NoOfPpl")
%>


<%
'**********************
'Check if form field exists in database table and alter table to add new fields (notes1, notes2, notes3 etc)
'**********************



    dbname = "misc.mdb"  
    tablename = "LUReview"  
 
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source="  
        ConnStr = ConnStr & server.mappath(dbname) 
 
    set adoxConn = CreateObject("ADOX.Catalog")  
    set adodbConn = CreateObject("ADODB.Connection")  
    adodbConn.open ConnStr  
    adoxConn.activeConnection = adodbConn  
    set table = adoxConn.Tables(tablename) 
    'nameExists = False  
 
Set oRecordset = adodbConn.Execute("SELECT TOP 1 * FROM LUReview") 
For Each oField In table.columns
 nameExists =  False
 if left(oField.Name,4) = "Name" Then
  for i = 1 to maxfields
   If oField.Name = Request.Form("Name" & i) Then    nameExists = True 
    Exit for 
   End If
   if nameExists = False Then
    adodbConn.Execute("ALTER TABLE LUReview ADD COLUMN notes" & i & " TEXT(50);")
   end if 
  next
 end if
next 


 
    set table = nothing  
    adodbConn.close: set adodbConn = nothing  
    set adoxConn = nothing  


%>


<% 
'**********************
'Insert form field values in the columns/fields of table
'**********************


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & connString


Set rsAdd = Server.CreateObject("ADODB.Recordset")


strSQL = "SELECT LUReview.* FROM LUReview;"


rsAdd.CursorType = 2
rsAdd.LockType = 3
rsAdd.Open strSQL, objConn
rsAdd.AddNew


for I = 1 to maxfields
rsAdd.Fields("notes" & i & "") = Request.Form("Name" & i & "")
next


rsAdd.Update
rsAdd.Close
Set rsAdd = Nothing
Set objConn = Nothing


Response.Write "Values successfully added."


%>


Replies:
Posted By: cmv
Date Posted: 22 May 2009 at 9:04pm
a few things i don't understand ...
- why are you opening 2 connections to the same database (finally three)
- since you already create a *table object* why don't you iterate over the Columns collection instead of comparing to a recordset
- or you maybe created oRecordset by error and mixed recordsets and collections in the code (use Option Explicit maybe to avoid creating variants you don#t use later)
 
consider the Request.Form("Name" & i) might return a collection instead of a single string (double check naming of input fields)
 
also i'd recommend to reconsider the database design - creating a seperate table with a foreign key might be better than simply adding columns to a table
 
hth, christian


-------------
and remember: a CRAY is the only computer that runs an endless loop in just four hours ...


Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net