Check field exists and dynamically add column/field in Access Table

sunny99 · May 20th, 2009, 05:08 PM

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'.

OR

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.

---------------------------------------------------
I have attached access database, along with html and asp files
---------------------------------------------------

Code:

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