Cannot create a table without columns error Xamarin SQLite

Question

I am trying to use a local SQLite database in my Xamarin application, I've been following this tutorial: https://docs.microsoft.com/en-us/xamarin/get-started/tutorials/local-database/?tabs=vswin

When loading the ListView page that I'm testing it on I always get the error: System.AggregateException: 'One or more errors occurred. (Cannot create a table without columns (does 'TestApp1.Piece' have public properties?))'

Here is the applicable code:

Piece.cs

namespace TestApp1
{
    public class Piece
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public int PartNum { get; set; }
        public string Category { get; set; }
        public string Url { get; set; }
    }
}

PieceDatabase.cs

namespace TestApp1
{
    public class PieceDatabase
    {
        readonly SQLiteAsyncConnection _database;

        public PieceDatabase(string dbPath)
        {
            _database = new SQLiteAsyncConnection(dbPath);
            _database.CreateTableAsync<Piece>().Wait();
        }

        public Task<List<Piece>> GetPieceAsync()
        {
            return _database.Table<Piece>().ToListAsync();
        }

        public Task<int> SavePieceAsync(Piece temp)
        {
            return _database.InsertAsync(temp);
        }
    }
}

App.xaml.cs

namespace TestApp1
{
    public partial class App : Application
    {

        static PieceDatabase database;

        public static PieceDatabase PieceDatabase
        {
            get
            {
                if (database == null)
                {
                    database = new PieceDatabase(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "pieces.db3"));
                }
                return database;
            }
        }

        public App()
        {
            InitializeComponent();
            MainPage = new NavigationPage(new Page1());
        }

        protected override void OnStart()
        {
            // Handle when your app starts
        }

        protected override void OnSleep()
        {
            // Handle when your app sleeps
        }

        protected override void OnResume()
        {
            // Handle when your app resumes
        }
    }
}

DatabaseTest.xaml.cs

namespace TestApp1
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class DatabaseTest : ContentPage
    {

        protected override async void OnAppearing()
        {
            base.OnAppearing();

            /** The error does not occur if I add a piece here, but I'm trying to figure out why that is
            await App.PieceDatabase.SavePieceAsync(new Piece
            {
                Category = "Beams",
                PartNum = 1,
                Url = "whatever.com"
            }); */

            List<Piece> test = await App.PieceDatabase.GetPieceAsync();
            listView.ItemsSource = test;
        }

        public DatabaseTest()
        {
            InitializeComponent();

        }

        async void Handle_ItemTapped(object sender, ItemTappedEventArgs e)
        {
            if (e.Item == null)
                return;

            await DisplayAlert("Item Tapped", "An item was tapped.", "OK");

            //Deselect Item
            ((ListView)sender).SelectedItem = null;
        }
    }
}

DatabaseTest.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             x:Class="TestApp1.DatabaseTest">
    <StackLayout Margin="20,35,20,20">
        <ListView x:Name="listView">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <TextCell Text="{Binding PartNum}"
                              Detail="{Binding Url}"
                              />
                </DataTemplate>
            </ListView.ItemTemplate>

        </ListView>
    </StackLayout>
</ContentPage>

This is the first time I've worked with Xamarin so any help or explanation is appreciated.

Your app is setting MainPage = new NavigationPage(new Page1()); This code path appears to completely bypass any db creation or initialization — Jason
Even when initailizing the db in the App.xaml.cs constructor the error still occurs while initializing it. — No place like 127.0.0.1
Now I'm getting the error: SQLite.SQLiteException: 'near ")": syntax error' when trying to add to the database and this error: SQLite.SQLiteException: 'no such table: Piece' when trying to just read from it. — No place like 127.0.0.1

Cherry Bu - MSFT Cherry Bu - MSFT · Accepted Answer · 2019-11-11T04:44:57

I do one sample about loading data from sqlite database, you download it from github, I have no issue, I use sqlite-net-pcl 1.6.258-beta version and xamarin.forms 3.4 version.

https://github.com/CherryBu/Sqlite-Listview

You cam also take a look the following article about detailed info:

https://www.c-sharpcorner.com/article/xamarin-forms-sqlite-database-crud-operations2/

Cannot create a table without columns error Xamarin SQLite

1 Answers