How to insert rows to Database?

Aug 19, 2013 at 8:49 AM
i tried to insert in database using :
ExecuteStatementAsync();

and i always get this error :
An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.ni.dll but was not handled in user code

is there any tutorial to how to insert?
Coordinator
Aug 19, 2013 at 9:07 AM

See my post http://andywigley.com/2013/06/06/sqlite-winrt-database-programming-on-windows-phone-and-windows-8/ which walks through all the CRUD operations. There is also a sample download that does inserts and all the other operations.

As general advice: if you get an exception on an operation and you can’t figure out what the problem is – and a call to GetSqliteErrorCode doesn’t tell you (see the section titled ‘Handling Errors’ in that blog post), a good thing to try is to copy the database from the phone/emulator onto your PC and then open it with a SQLite workbench tool (for example, SQLite Studio), and then try to execute your SQL statement directly – you will often find that you have a syntax error in your SQL, which is easier to diagnose in a SQLite desktop tool than from your code.

Andy

Aug 20, 2013 at 9:33 AM
Now I can figure errors.

This error is found:

Failed with error ReadOnlyConstraint

I searched a lot for it but I didn't find any solution or details.
Coordinator
Aug 20, 2013 at 9:44 AM

Where is your database? And how are you opening it?

Your database needs to be in the Local Folder to make it read/write. If you have created a database on the desktop and shipped it as content with the app, then opened it from the Install Folder then that will be read-only.

You can ship a database as content, but if you want to then write to it, you will have to include code to copy it the first time over to the Local Folder.

Aug 20, 2013 at 11:16 AM
Yes i ship my database as acontent and open it like this :
                await InitializeDatabaseAsync();

                // Get the file from the local folder

                var file = await ApplicationData.Current.LocalFolder.GetFileAsync("MyDB.sqlite");
                // Create a new SQLite instance for the file
                db = new Database(file);
        async Task<bool> DoesFileExistAsync(string file)
        {
            try
            {
                await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(file);
                return true;
            }
            catch
            {
                return false;
            }
        }

        async Task InitializeDatabaseAsync()
        {
            if (!(await DoesFileExistAsync("MyDB.sqlite")))
            {
                string databaseFile = Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, @"DataAccess\MyDB.sqlite");
                StorageFile file = await StorageFile.GetFileFromPathAsync(databaseFile);
                await file.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);
            }
        }
Coordinator
Aug 20, 2013 at 2:11 PM
Edited Aug 20, 2013 at 2:14 PM
I just modified my main sample to use the code you posted above and it seems to be working OK for me.
Take a look at http://sdrv.ms/19GyCP6 which uses your code to make the database copy.

Which makes me think it is possibly an erroneous exception message and has more to do with some problem with your INSERT statement or your database structure. What is the Table DDL and what INSERT statement are you using?
  • Andy
Aug 21, 2013 at 9:22 AM
Thanx Andy for your great help.

now insert is work with no errors . but no thing inserted in database.

after insert is completed i get rows from database and found them the same number before insert

this is my code for insert :
        public async Task AddFeeds(FeedItem item)
        {

            if (null == file)
            {
                await InitializeDatabaseAsync();

                // Get the file from the local folder

                file = await ApplicationData.Current.LocalFolder.GetFileAsync("MyDB.sqlite");
            }
            // Create a new SQLite instance for the file
            Database db = new Database(file);

            // Prepare a SQL statement to be executed
            try
            {

                using (var custstmt = await db.PrepareStatementAsync("INSERT INTO FeedItem(Title, Description, Image, PublishDate,URL,CategoryID)VALUES (@Title, @Description, @Image,@PublishDate,@URL,@CategoryID)"))
                {
                    // NOTE that named parameters have a leading "@",":" or "$".       
                    custstmt.BindTextParameterWithName("@Title", item.Title);
                    custstmt.BindTextParameterWithName("@Description", item.Description);
                    custstmt.BindTextParameterWithName("@Image", item.Image);
                    custstmt.BindTextParameterWithName("@PublishDate", item.PublishDate.ToString());
                    custstmt.BindTextParameterWithName("@URL", item.URl);
                    custstmt.BindIntParameterWithName("@CategoryID", item.CategoryID);
                    // Use StepAsync to execute a prepared statement 

                    await custstmt.StepAsync();
                }
            }

            catch (Exception ex)
            {
                var result =
                    SQLiteWinRTPhone.Database.GetSqliteErrorCode(ex.HResult);
                throw new ApplicationException("Failed with error " + result);
            }
        }
Coordinator
Aug 21, 2013 at 9:38 AM

Well there’s nothing I can see wrong with your code.

Though what is item.Image which you are storing as a string?

If there is no exception, then the Insert must be completing, so if you are not finding the rows after there must be some other reason. How are you getting the rows?

Aug 25, 2013 at 3:45 PM
Thanks andy

my mistake was that i opened database for read only and try to write to it.

another question can i do BulkInsert using the sanme way or i can do that by constructing statement by this way :
INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');
Coordinator
Aug 28, 2013 at 9:08 AM

The statement you have there should work fine. You can specify each of the values as separate named or anonymous parameters.

I haven’t done any benchmarking myself, but I would expect that you could also try simply having a prepared statement to insert a single row, execute BEGIN TRANSACTION at the start, then insert all your rows (remember to call Reset() and ClearBindings() on your statement between each insert) and then call COMMIT at the end.

Andy

Oct 20, 2013 at 5:27 AM
Andy, may I ask you to update your post http://andywigley.com/2013/06/06/sqlite-winrt-database-programming-on-windows-phone-and-windows-8/ with an example of using transaction? Suppose I need to remove some records, insert a couple of other records and update a lot of records - all that in one transaction.