Check if column/exists and add fields dynamically

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   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."
%>