Android Question After modified Structure Table of DB ,I can't see the new column

[Theera]

Hi all ,
Assume I have myDB.db which has 1 table (myTable) as below

CREATE TABLE "myTable" (
    "ID"    INTEGER,
    "A"    TEXT,
    "B"    TEXT,
    "C"    INTEGER,
      PRIMARY KEY("ID" AUTOINCREMENT)
)

and then I have modify my table (add "D") is

CREATE TABLE "myTable" (
    "ID"    INTEGER,
    "A"    TEXT,
    "B"    TEXT,
    "C"    INTEGER,
    "D"    INTEGER,
    PRIMARY KEY("ID" AUTOINCREMENT)
)

In starter module,I have code as belows

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public SQL1 As SQL
    Public DBFileName As String                    : DBFileName =  "myDB.db"
   
End Sub

Sub Service_Create
    'Use the database in the writable  location
    DBDir =DBUtils.CopyDBFromAssets(DBFileName)
    SQL1.Initialize(DBDir,DBFileName, True)

End Sub

and In another module is

Sub AddEntry
 
    Dim m As Map
    m.Initialize
    m.Put("A",Edtext1.Text)
    m.Put("B",Edtext2.Text)
    m.Put("C",Edtext3.Text)
    m.Put("D",Paid)  'Paid is from calculated

    Starter.RowIDList.Add(m)

    DBUtils.InsertMaps(Starter.SQL1,"myTable", Starter.RowIDList)
  

End Sub

My problem is : After I modified table , my project is not work,it can't see [D] in the code as belows

Sub Button1_Click
    DBUtils.DeleteRecord(SQL1,"myTable", CreateMap("ID":Csr.GetInt("ID") ))
    WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1,"SELECT [A] As "A",[B] as "B" ,[C] as "C",[D] as "D"  From myTable",Null,0,True))
    FWin.Close
End Sub

I think it still remember the old structure of table,doesn't it?

 

[DonManfred]

If you updated the db in assets then you need to do the changes on an existing DB also. OR replace the old db with the new one (loosing data maybe).

You need to implement a versioning in your database. Search the forum. this is asked a few times now.

 

[Theera]

I have ever changed a new one db(mydb.db=>newdb.db) I notice that it can't do in AddEntry when button1_click isClicked.(can't show[D]). I'm sorry that Ican't expain in English well.

 

[Mahares]

I have ever changed a new one db(mydb.db=>newdb.db) I notice that it can't do in AddEntry when button1_click isClicked.(can't show[D])

It is difficult to umderstand the question. your best approach is to create a small project with what you want to do and export it here. Some members will detect your problem and try to help you.
In the meantime here are a few tips:
1. You do not have to drop (delete) the table to add a new column to it. You can check if the column you want to add exists; and if not add it. This way, you will not lose all the data you already accumulated in the original table.
2. In your 1st post you have this line: DBDir =DBUtils.CopyDBFromAssets(DBFileName)
You are not checking if the file exists in DBDir first before you copy it from assets to DBDir. It seems like you are overwriting the file in DBDir every time you run the app with the file from assets . If that is the case, then the new data you insert gets wiped out the next time you enter the program.
These are the bits and pieces I gathered from the scattered code you have in post #1.

 

[Theera]

I will try to do following your advise.

 

[aeric]

Can check this:

SQlite: Backup database and alter table

Here's a little sub to backup and alter a table (assuming you have all the SQlite parts in the starter service). The sub checks if the column is already defined. Of course you can leave the loop at once with a return. For debug reasons I've used a Boolean here. Sub AddColumnToTable (TableName...

www.b4x.com

 

[Theera]

I have decided to create a new DB(RentTable.db) and a new Table(RTable) in my project. There is error dbutils_executehtml( java line:187). I don't know the error code from my project or from dbUtils library. Please see the log file.

P.S. "first query out of 1" What does it mean,please?

Logger connected to: HUAWEI LDN-LX2
--------- beginning of system
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (business) Create (first time) **
111
112
113
** Activity (business) Resume **
** Activity (business) Pause, UserClosed = false **
** Activity (chkinguest) Create (first time) **
(ParseException) java.text.ParseException: Unparseable date: ""
** Activity (chkinguest) Resume **
InsertMaps (first query out of 1): INSERT INTO [RTable] ([RoomNo], [Guest], [Phone], [CitizenID], [Cost], [DateChkIn], [TimeChkIn], [DateChkOut], [StayDays], [CostPaid]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
** Activity (chkinguest) Pause, UserClosed = false **
** Activity (printout) Create (first time) **
** Activity (printout) Resume **
** Activity (printout) Pause, UserClosed = false **
** Service (starter) Destroy (ignored)**
Logger connected to: HUAWEI LDN-LX2
--------- beginning of system
--------- beginning of main
** Service (starter) Destroy (ignored)**
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (business) Create (first time) **
111
112
113
211
** Activity (business) Resume **
** Activity (business) Pause, UserClosed = false **
** Activity (timelinerents) Create (first time) **
dbutils_executehtml (java line: 187)
android.database.sqlite.SQLiteException: no such table: RTable (Sqlite code 1): , while compiling: SELECT [RoomNo] As ห้อง,[Guest] as ผู้เข้าพัก ,[Phone] as โทรศัพท์,[StayDays] as เข้าพัก,[CostPaid] as ยอดเงิน From RTable, (OS error - 2:No such file or directory)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:925)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:536)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1397)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1331)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.npsoft.millionaireresort.dbutils._executehtml(dbutils.java:187)
at b4a.npsoft.millionaireresort.timelinerents._showtableinwebview(timelinerents.java:471)
at b4a.npsoft.millionaireresort.timelinerents._activity_create(timelinerents.java:378)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:221)
at b4a.npsoft.millionaireresort.timelinerents.afterFirstLayout(timelinerents.java:105)
at b4a.npsoft.millionaireresort.timelinerents.access$000(timelinerents.java:17)
at b4a.npsoft.millionaireresort.timelinerents$WaitForLayout.run(timelinerents.java:83)
at android.os.Handler.handleCallback(Handler.java:808)
at android.os.Handler.dispatchMessage(Handler.java:101)
at android.os.Looper.loop(Looper.java:166)
at android.app.ActivityThread.main(ActivityThread.java:7529)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:245)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:921)
android.database.sqlite.SQLiteException: no such table: RTable (Sqlite code 1): , while compiling: SELECT [RoomNo] As ห้อง,[Guest] as ผู้เข้าพัก ,[Phone] as โทรศัพท์,[StayDays] as เข้าพัก,[CostPaid] as ยอดเงิน From RTable, (OS error - 2:No such file or directory)

 

[Mahares]

There is error dbutils_executehtml( java line:187).

1. The first thing that caught my eye is this line:

(ParseException) java.text.ParseException: Unparseable date: ""

You have an unparseable date. You need to show us how the dates DateChkIn and DateChkOut are defined in your schema structure of Rtable. Also, you need to show an example of what those dates look like when you insert them in the table.
2. You also need to show the full syntax you have for this line: dbutils_executehtml
3. But if you can reproduce the problems in a small project you can export, it is always easier to help you and members will get back to you faster.

 

[Theera]

I found my problem, I have managed. already. I created 10 fields in db, but I entried only 9 fileds. There is 1field be absented.

Thank you for kind of all of you.