check dynamically if field exists and add/alter fields to table

[sunny9977]

How to Check if Dynamic Form field exists in Access Table

--------------------------------------------------------------------------------

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 and Alter table to add columns/fields
2) Insert the values in the column/fields of the table

Html form url is below

users1.jabry.com/sunny/test.html

I am unable to check if fields are existing in the table and keep getting error-

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

Item cannot be found in the collection corresponding to the requested name or ordinal.
/geo/Test/insert.asp, line 78

the line 78 corresponds to following code (inserting values)

Code:
rsAdd.Fields("notes" & i & "") = Request.Form("Name" & i & "")

which I believe is because fields have not been created hence not able to insert the values.

I will appreciate if you can look into code and let me know the fix.

---------------------------------------------------
html file is at: users1.jabry.com/sunny/test.html
---------------------------------------------------

and heres the complete ASP code again.

i will greately appreciate if you can fix the bug.

---------------------------------------------------

<%
'**********************
'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
'**********************


    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 oRecordset = adodbConn.Execute("SELECT TOP 1 * FROM LUReview") 
For Each oField In oRecordset.Fields
	nameExists =  False
	if left(oField.Name,5) = "notes" 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."

%>