none
OleDbCommand: SQL syntax error RRS feed

  • Question

  • I am having trouble with a ALTER TABLE command that I am trying to use on a MS Access database in a C# project. I am trying to rename a column and change the type at the same time.

    Here is my command:
    string sqlCommand = "ALTER TABLE " + tableName + " CHANGE [" + oldColName + "] [" + newColName + "] " + colType;
    • The type and the names of the table, new column and old column are not the problem
    • The exception catched is : Syntax error in ALTER TABLE statement.
    • Connection provider: Microsoft.ACE.OLEDB.12.0
    • The final string looks like this: ALTER TABLE [Big List] CHANGE [num] [test] CHARACTER
    What is wrong in this command and what do I need to do to make this it work?


    I have tried doing something similar to this but it does not work either:
    sqlQuery = "ALTER TABLE " + tableName + " ADD COLUMN [" + newColName + "] " + colType;
    command = new OleDbCommand(sqlQuery, conn);
    command.ExecuteNonQuery();
    
    sqlQuery = "UPDATE TABLE " + tableName + " SET [" + newColName + "]=[" + oldColName  + "]";
    command = new OleDbCommand(sqlQuery, conn);
    command.ExecuteNonQuery();
    
    sqlQuery = "ALTER TABLE " + tableName + " DROP COLUMN [" + oldColName + "]";
    command = new OleDbCommand(sqlQuery, conn);
    command.ExecuteNonQuery();
    

    Thank you for any help!
    Tuesday, August 18, 2009 3:23 AM

Answers

  • If the suggestions from 1987Raymond don't work for you you may want to indicate the error you are encountering.

    Unfortunately there is no support for renaming a column in Jet SQL so you essentially need to do the ALTER ADD, UPDATE SET, ALTER DROP method.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by PartialAlert Tuesday, August 18, 2009 4:47 PM
    Tuesday, August 18, 2009 2:16 PM

All replies

  • Hello here is the syntax of Access alter table:

    ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL]     [CONSTRAINT index] |     ALTER COLUMN field type[(size)] |     CONSTRAINT multifieldindex} |     DROP {COLUMN field I CONSTRAINT indexname} }


    I Run the following succeesfully in Access

    Sub AlterTableX1()

        Dim dbs As Database
       
        Set dbs = Application.CurrentDb
        dbs.Execute "ALTER TABLE [Test] Add Column [MyColumn] MONEY;"
        dbs.Execute "ALTER TABLE [Test] ALTER Column [MyColumn] Text(20);"
        dbs.Close

    End Sub


    Wenn ich dich hab’,gibt es nichts, was unerträglich ist.坚持不懈!http://hi.baidu.com/1987raymond
    Tuesday, August 18, 2009 5:21 AM
  • If the suggestions from 1987Raymond don't work for you you may want to indicate the error you are encountering.

    Unfortunately there is no support for renaming a column in Jet SQL so you essentially need to do the ALTER ADD, UPDATE SET, ALTER DROP method.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by PartialAlert Tuesday, August 18, 2009 4:47 PM
    Tuesday, August 18, 2009 2:16 PM
  • Thank you for the help! I will use the ALTER ADD, UPDATE SET, ALTER DROP method. Also, I have found my mistake for that method... I was using "UPDATE TABLE name..." instead of only "UPDATE name..."
    Tuesday, August 18, 2009 4:47 PM