Android Question ALTER TABLE ADD COLUMN not working

[GEoffT]

Could someone please explain to me why the following code does not add a column?

Been staring at it for ages .... Its probably something simple that I am not seeing.
BTW the column is not appearing in the second log message loop.

Thanks in advance Geoff

Sub addBowTypeColumn
    Dim query As String
    Dim cursorAdd As Cursor
    '    See if Bowtype is a field   if not add it
    query = "ALTER TABLE SightMarks ADD COLUMN BowType text"
    Main.SQL1.BeginTransaction
    Try
        Main.SQL1.ExecNonQuery(query)
    Catch
        Log("failed to Add Bowtype Field")
        
    End Try
    Main.SQL1.EndTransaction
    query = "SELECT * FROM SightMarks" 'get all columns
    cursorAdd = Main.SQL1.ExecQuery(query)
    For l=0 To cursorAdd.ColumnCount-1
        Log(cursorAdd.GetColumnName(l))
    
    Next


End Sub

 

[Computersmith64]

Could someone please explain to me why the following code does not add a column?

Been staring at it for ages .... Its probably something simple that I am not seeing.
BTW the column is not appearing in the second log message loop.

Thanks in advance Geoff

Sub addBowTypeColumn
    Dim query As String
    Dim cursorAdd As Cursor
    '    See if Bowtype is a field   if not add it
    query = "ALTER TABLE SightMarks ADD COLUMN BowType text"
    Main.SQL1.BeginTransaction
    Try
        Main.SQL1.ExecNonQuery(query)
    Catch
        Log("failed to Add Bowtype Field")
      
    End Try
    Main.SQL1.EndTransaction
    query = "SELECT * FROM SightMarks" 'get all columns
    cursorAdd = Main.SQL1.ExecQuery(query)
    For l=0 To cursorAdd.ColumnCount-1
        Log(cursorAdd.GetColumnName(l))
  
    Next


End Sub

You need to call TransactionSuccessful otherwise no changes are made.

Sub addBowTypeColumn
    Dim query As String
    Dim cursorAdd As Cursor
    '    See if Bowtype is a field   if not add it
    query = "ALTER TABLE SightMarks ADD COLUMN BowType text"
    Main.SQL1.BeginTransaction
    Try
        Main.SQL1.ExecNonQuery(query)
        Main.SQL1.TransactionSuccessful   '*****************************************************
    Catch
        Log("failed to Add Bowtype Field")
     
    End Try
    Main.SQL1.EndTransaction
    query = "SELECT * FROM SightMarks" 'get all columns
    cursorAdd = Main.SQL1.ExecQuery(query)
    For l=0 To cursorAdd.ColumnCount-1
        Log(cursorAdd.GetColumnName(l))
  
    Next


End Sub

- Colin.

 

[Computersmith64]

Fyi - you probably don't need to wrap the table mod in a transaction anyway. When you're running a single update, insert, etc... I don't think there's an advantage to putting it in a transaction. If you're running multiple queries a transaction will make execution faster (eg: if you're inserting 1,000 records in a loop), or if you're performing a cascading delete on multiple tables & you need to back out the entire process if it fails at any stage a transaction will do that.

- Colin.