Xamarin Forms `SQLiteAsyncConnection` throws error when trying to add data with new columns in existing table

Question

Xamarin Forms `SQLiteAsyncConnection` throws error when trying to add data with new columns in existing table

Gayatri Gokhale 1

I am using SQLiteAsyncConnection to perform CRUD operation in my xamarin.forms app.

My problem is. I have existing table with data in it. My requirement changed and I needed to add few more columns into that table. because I have data in that table, I can't drop it but I want to update existing one with newly added columns. but It's failing and even it doesn't give proper error message.

please find below screenshot that summarizes my problem

also I am not able to use using keyword to control scope of connection object.

Any help would be appreciated.

Thanks

JarvanZhang 23,971 Reputation points

2020-12-02T14:47:02.303+00:00

@Gayatri Gokhale Hi, is the column added to the table successfully but it is not displayed? Or the addition operation is failed? To use database in Xamarin.Forms, you could refer to this tutorial: https://learn.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/databases And you could check the official sample for a direction.
Joe Manke 1,091 Reputation points

2020-12-02T15:56:51.85+00:00

What is the exception you're seeing?
Gayatri Gokhale 1 Reputation point

2020-12-03T12:37:45.487+00:00

@Joe Manke I actually see null in the ex variable and by hovering on Exception It shows some other data but not related to errors.

Am I missing something ?
Gayatri Gokhale 1 Reputation point

2020-12-03T12:56:49.177+00:00

Is it problem because I am using InsertWithChildrenAsync because I have foreign key constraint in my model.

because I run above sample and simple InsertAsync is working with new column added in TodoItem Model.

please find attachment of my model class
Joe Manke 1,091 Reputation points

2020-12-03T14:37:59.55+00:00

Try adding Debug.WriteLine(ex) to your catch block and see what that outputs. Debugging exceptions thrown by async methods can be tricky.
Gayatri Gokhale 1 Reputation point

2020-12-05T07:10:21.227+00:00

@Joe Manke It is showing SQLite.SQLiteException: table Item has no column named TaxId

I searched on google about this error but everybody suggests to delete database or change db name.
But what if my db changes after app is live ? then I can't delete or rename database ?

JarvanZhang 23,971

Sorry for the late reply. The two tables have the relationship, make sure both the two table is available. Try the following code:

var db = Utils.CreateConnection();
db.CreateTable<TblUnit>();
db.CreateTable<Item>();

var tblUnit = new TblUnit()
{
    ...
};
db.Insert(tblUnit);   // Insert the object in the database

var item = new Item()
{
    ...
};
db.Insert(item);   // Insert the object in the database
tblUnit.Items = new List<Item> { item };

db.UpdateWithChildren(tblUnit);

Joe Manke 1,091 Reputation points

2020-12-07T15:27:13.14+00:00

Calling CreateTable functions as a "create or update" statement in reality. I would move calling CreateTable for every table in your database to where you open the connection, and remove the (if !TableExists()) block in this method.
Gayatri Gokhale 1 Reputation point

2020-12-08T05:02:03.713+00:00

@JarvanZhang Thanks for your reply. I will try and let you know
Gayatri Gokhale 1 Reputation point

2020-12-08T05:04:39.127+00:00

@Joe Manke Thanks for your reply. I will update code accordingly and let you know

Your answer

JarvanZhang 23,971 Reputation points

2020-12-02T14:47:02.303+00:00

@Gayatri Gokhale Hi, is the column added to the table successfully but it is not displayed? Or the addition operation is failed? To use database in Xamarin.Forms, you could refer to this tutorial: https://learn.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/databases And you could check the official sample for a direction.
Joe Manke 1,091 Reputation points

2020-12-02T15:56:51.85+00:00

What is the exception you're seeing?
Gayatri Gokhale 1 Reputation point

2020-12-03T12:37:45.487+00:00

@Joe Manke I actually see null in the ex variable and by hovering on Exception It shows some other data but not related to errors.

Am I missing something ?
Gayatri Gokhale 1 Reputation point

2020-12-03T12:56:49.177+00:00

Is it problem because I am using InsertWithChildrenAsync because I have foreign key constraint in my model.

because I run above sample and simple InsertAsync is working with new column added in TodoItem Model.

please find attachment of my model class
Joe Manke 1,091 Reputation points

2020-12-03T14:37:59.55+00:00

Try adding Debug.WriteLine(ex) to your catch block and see what that outputs. Debugging exceptions thrown by async methods can be tricky.
Gayatri Gokhale 1 Reputation point

2020-12-05T07:10:21.227+00:00

@Joe Manke It is showing SQLite.SQLiteException: table Item has no column named TaxId

I searched on google about this error but everybody suggests to delete database or change db name.
But what if my db changes after app is live ? then I can't delete or rename database ?
JarvanZhang 23,971 Reputation points

2020-12-07T11:22:03.087+00:00

Sorry for the late reply. The two tables have the relationship, make sure both the two table is available. Try the following code:

var db = Utils.CreateConnection(); db.CreateTable<TblUnit>(); db.CreateTable<Item>(); var tblUnit = new TblUnit() { ... }; db.Insert(tblUnit); // Insert the object in the database var item = new Item() { ... }; db.Insert(item); // Insert the object in the database tblUnit.Items = new List<Item> { item }; db.UpdateWithChildren(tblUnit);
Joe Manke 1,091 Reputation points

2020-12-07T15:27:13.14+00:00

Calling CreateTable functions as a "create or update" statement in reality. I would move calling CreateTable for every table in your database to where you open the connection, and remove the (if !TableExists()) block in this method.
Gayatri Gokhale 1 Reputation point

2020-12-08T05:02:03.713+00:00

@JarvanZhang Thanks for your reply. I will try and let you know
Gayatri Gokhale 1 Reputation point

2020-12-08T05:04:39.127+00:00

@Joe Manke Thanks for your reply. I will update code accordingly and let you know

Share via

Xamarin Forms `SQLiteAsyncConnection` throws error when trying to add data with new columns in existing table

Your answer