How to Check if Dynamic Form field exists in access table

[sushil54]

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.

[QUOTE]<%
'**********************
'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")
%>[/QUOTE]

[COLOR=Red]<%
'**********************
' Error is due to bug in this section of the code
'Check if form field exists in database table
'**********************
nameExists = false

Set oRecordset = objConn.Execute("SELECT * FROM LUReview")

For Each oField In oRecordset.Fields
for I = 1 to maxfields
If oField.Name = Request.Form("Name" & i & "") Then
nameExists = True
Exit for
End If
next
next

Set objConn = Nothing
Set oRecordset = Nothing

%>[/QUOTE]

[COLOR=Brown]<%
'**********************
'Alter table to add columns/fields in the table
'**********************
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
connString

if nameExists = False then
for I = 1 to maxfields
Set myRS = objConn.Execute("ALTER TABLE LUReview ADD COLUMN notes" &
i & " TEXT(50);")
next
end if

Set objConn = Nothing
%>[/QUOTE]

[COLOR=RoyalBlue]<%
'**********************
'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.Redirect("confirm.asp")

%>[/QUOTE]

thanks a bunch

 

[Adrienne Boswell]

Gazing into my crystal ball I observed (e-mail address removed) writing in

If oField.Name = Request.Form("Name" & i & "") Then

You might want to TRIM both, as = "field " is not the same as "field".