How to Check if Dynamic Form field exists in access table

Discussion in 'ASP General' started by sushil54@gmail.com, May 19, 2009.

  1. Guest

    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.

    Code:
    [COLOR=Green]<%
    '**********************
    '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")
    %>[/COLOR]
    
    [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
    
    %>[/COLOR]
    
    [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
    %>[/COLOR]
    
    [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")
    
    %>[/COLOR]
    
    thanks a bunch
     
    , May 19, 2009
    #1
    1. Advertising

  2. Gazing into my crystal ball I observed writing in
    news::

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


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

    --
    Adrienne Boswell at Home
    Arbpen Web Site Design Services
    http://www.cavalcade-of-coding.info
    Please respond to the group so others can share
     
    Adrienne Boswell, May 20, 2009
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jason Stacy
    Replies:
    2
    Views:
    4,814
    Roedy Green
    Apr 27, 2008
  2. Ulf Meinhardt
    Replies:
    8
    Views:
    6,261
  3. Jonathan

    How can I check if a Database Table exists?

    Jonathan, Apr 4, 2004, in forum: ASP General
    Replies:
    2
    Views:
    446
    Bob Barrows
    Apr 4, 2004
  4. NotGiven
    Replies:
    3
    Views:
    376
    Michael D. Kersey
    May 13, 2004
  5. GavMc
    Replies:
    4
    Views:
    366
    Evertjan.
    Sep 22, 2005
Loading...

Share This Page