Adding Columns/Fields Dynamically to an Access Table

Discussion in 'C#' started by sunny99, May 18, 2009.

  1. #1
    I am trying to add columns dynamically to an access table.

    I am able to add one field at a time but am getting an error
    that says

    Syntax error in field definition. (in the for loop.)

    if i try to add more than 1 column dynamically.

    It works fine if i remove for loop and add just one column.

    
    <% 
    Dim myRS, objConn, connString
    connString = Server.MapPath("misc.mdb")
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & connString 
    
    for I=1 to 3
    Set myRS = objConn.Execute("ALTER TABLE LUReview ADD COLUMN notes(I) TEXT(50);")
    next
    
    If err.Number = 0 Then
    Response.Write "Column successfully added."
    Else
    Response.Write "Error Encountered<br>" & err.Number & "<br>" & err.Description
    End If
    %>
    
    Code (markup):

    I will appreciate if anyone can fix the code

    The result should add columns dynamically

    For Example

    if I = 1 to 4

    Then table should be altered to add have 4 columns/fields - notes1, notes2, notes3, notes4

    thanks
     
    sunny99, May 18, 2009 IP
  2. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #2
    try:
    
    for I=1 to 3
    Set myRS = objConn.Execute("ALTER TABLE LUReview ADD COLUMN notes[B]" & I & " [/B]TEXT(50);")
    next
    
    Code (markup):
     
    camjohnson95, May 19, 2009 IP