From my SQLite database with a table and a virtual table which links to that table I'm getting an error that tables and/or variables have different types. I have checked my typing and nothing appears to be wrong.

using Microsoft.Data.Sqlite;

namespace SqliteExample
{
    struct Track
    {
        public string FilePath;
        public string Artist;
        public string Title;

        public Track(string file_path, string artist, string title)
        {
            FilePath = file_path;
            Artist = artist;
            Title = title;
        }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            // Get database connection
            var connection = await Init();

            await InsertTracks(connection, [new Track("test_path", "test_artist", "test_title")]);
        }

        static async Task<SqliteConnection> Init()
        {
            // Make a connection to our database and open it
            var connection = new SqliteConnection("Data Source=meta.db");
            await connection.OpenAsync();

            // Create tables if they don't already exist
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nCreating tables...");
                // Create metadata table
                command.CommandText = """
                CREATE TABLE IF NOT EXISTS meta (
                    file TEXT PRIMARY KEY,
                    artist TEXT,
                    title TEXT
                )
                """;

                await command.ExecuteNonQueryAsync();

                // Create FTS5 virtual table
                command.CommandText = """
                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    artist,
                    title,
                    content='meta',
                    content_rowid='file'
                );
                """;

                await command.ExecuteNonQueryAsync();

                // Push triggers
                command.CommandText = """
                CREATE TRIGGER IF NOT EXISTS insert_search AFTER INSERT ON meta BEGIN
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS update_search AFTER UPDATE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS delete_search AFTER DELETE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                END;
                """;

                await command.ExecuteNonQueryAsync();
            }

            return connection;
        }

        static async Task InsertTracks(SqliteConnection connection, Track[] tracks)
        {
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nInserting entries...");
                command.CommandText = "INSERT INTO meta (file, artist, title) VALUES ($path, $artist, $title)";

                foreach (Track track in tracks)
                {
                    command.Parameters.Add("$path", SqliteType.Text).Value = track.FilePath;
                    command.Parameters.Add("$artist", SqliteType.Text).Value = track.Artist;
                    command.Parameters.Add("$title", SqliteType.Text).Value = track.Title;

                    await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}

Full error message:

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x8000
4005): SQLite Error 20: 'datatype mismatch'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(In
t32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBeh
avior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(Cancellati
onToken cancellationToken)
--- End of stack trace from previous location ---
   at SqliteExample.Program.InsertTracks(SqliteConnection connecti
on, Track[] tracks) in E:\Documents\projects\sqlite-in-csharp\Sqli
teExample\Program.cs:line 201
   at SqliteExample.Program.Main(String[] args) in E:\Documents\pr
ojects\sqlite-in-csharp\SqliteExample\Program.cs:line 39
   at SqliteExample.Program.<Main>(String[] args)

From my SQLite database with a table and a virtual table which links to that table I'm getting an error that tables and/or variables have different types. I have checked my typing and nothing appears to be wrong.

using Microsoft.Data.Sqlite;

namespace SqliteExample
{
    struct Track
    {
        public string FilePath;
        public string Artist;
        public string Title;

        public Track(string file_path, string artist, string title)
        {
            FilePath = file_path;
            Artist = artist;
            Title = title;
        }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            // Get database connection
            var connection = await Init();

            await InsertTracks(connection, [new Track("test_path", "test_artist", "test_title")]);
        }

        static async Task<SqliteConnection> Init()
        {
            // Make a connection to our database and open it
            var connection = new SqliteConnection("Data Source=meta.db");
            await connection.OpenAsync();

            // Create tables if they don't already exist
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nCreating tables...");
                // Create metadata table
                command.CommandText = """
                CREATE TABLE IF NOT EXISTS meta (
                    file TEXT PRIMARY KEY,
                    artist TEXT,
                    title TEXT
                )
                """;

                await command.ExecuteNonQueryAsync();

                // Create FTS5 virtual table
                command.CommandText = """
                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    artist,
                    title,
                    content='meta',
                    content_rowid='file'
                );
                """;

                await command.ExecuteNonQueryAsync();

                // Push triggers
                command.CommandText = """
                CREATE TRIGGER IF NOT EXISTS insert_search AFTER INSERT ON meta BEGIN
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS update_search AFTER UPDATE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS delete_search AFTER DELETE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                END;
                """;

                await command.ExecuteNonQueryAsync();
            }

            return connection;
        }

        static async Task InsertTracks(SqliteConnection connection, Track[] tracks)
        {
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nInserting entries...");
                command.CommandText = "INSERT INTO meta (file, artist, title) VALUES ($path, $artist, $title)";

                foreach (Track track in tracks)
                {
                    command.Parameters.Add("$path", SqliteType.Text).Value = track.FilePath;
                    command.Parameters.Add("$artist", SqliteType.Text).Value = track.Artist;
                    command.Parameters.Add("$title", SqliteType.Text).Value = track.Title;

                    await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}

Full error message:

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x8000
4005): SQLite Error 20: 'datatype mismatch'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(In
t32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBeh
avior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(Cancellati
onToken cancellationToken)
--- End of stack trace from previous location ---
   at SqliteExample.Program.InsertTracks(SqliteConnection connecti
on, Track[] tracks) in E:\Documents\projects\sqlite-in-csharp\Sqli
teExample\Program.cs:line 201
   at SqliteExample.Program.Main(String[] args) in E:\Documents\pr
ojects\sqlite-in-csharp\SqliteExample\Program.cs:line 39
   at SqliteExample.Program.<Main>(String[] args)

• c#
• sqlite

Share Improve this question Follow edited Nov 18, 2024 at 0:05 user4157124 2,9961414 gold badges3131 silver badges4646 bronze badges asked Nov 17, 2024 at 5:02 ThreeRoundedSquaresThreeRoundedSquares 111 silver badge22 bronze badges 3

• Does the code work without the triggers created? – flackoverstow Commented Nov 17, 2024 at 7:10
• 1 Error message indicates problem is on line 39. Please indicate which line is 39 in your code as you see it; when I paste your code line 39 is a comment – flackoverstow Commented Nov 17, 2024 at 7:15
• The code does work without the triggers, and line 39 is just the InsertTracks call. – ThreeRoundedSquares Commented Nov 17, 2024 at 16:56

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

When calling InsertTracks make sure that you are passing the array correctly:

await InsertTracks(connection, new Track[] { new Track("test_path", "test_artist", "test_title") });

When adding parameters in the query string you are adding the same parameters with different value, which leads to an error. Make sure that you are doing it outside of a loop. Later you can assign values inside the loop.

var pathParam = command.Parameters.Add("$path", SqliteType.Text);
var artistParam = command.Parameters.Add("$artist", SqliteType.Text);
var titleParam = command.Parameters.Add("$title", SqliteType.Text);

// Now iterate over the tracks and assign values
foreach (Track track in tracks)
{
    pathParam.Value = track.FilePath;
    artistParam.Value = track.Artist;
    titleParam.Value = track.Title;

    await command.ExecuteNonQueryAsync();
}

Assuming that the rowid column is not a user defined column named rowid (which the error indicates that it is not) then rowid, MUST be an integer value, that is the only cause of the datatype mismatch error.

• new.file/old.file is likely not an integer value (if you believe it is then try CAST(new.file AS INTEGER) and for old.file.

• an alternative would be to either set roiwd to be null, or to not specify the rowid column; in either case the value would then be generated and typically be 1 greater than the highest existing rowid (a monotonically increasing rowid value is not guaranteed, see the link for rowid in the linked data mismatch for more info on the rowid column)

  • noting that if the value is not an integer then you will probably get unanticipated results, perhaps even a unique conflict error as the triggered INSERTs use the default CONFLICT handling which is ABORT (perhaps consider using INSERT OR IGNORE which would just skip a unique conflict, this obviously depends upon the functionality you want).

However, you may(should) really be wanting to store the file in the content_rowid column, in which case rather than assigning the values in the INSERTs to rowid you should be assigning/using content_rowid in the SQL.

• you may wish to have a closer look at https://sqlite./fts5.html#external_content_tables and also the following section (or even the entire FTS5 section) and especially the examples (which actually utilise the content_rowid column).

(20) SQLITE_MISMATCH The SQLITE_MISMATCH error code indicates a datatype mismatch.

SQLite is normally very fiving about mismatches between the type of a value and the declared type of the container in which that value is to be stored. For example, SQLite allows the application to store a large BLOB in a column with a declared type of BOOLEAN. But in a few cases, SQLite is strict about types. The SQLITE_MISMATCH error is returned in those few cases when the types do not match.

The rowid of a table must be an integer. Attempt to set the rowid to anything other than an integer (or a NULL which will be automatically converted into the next available integer rowid) results in an SQLITE_MISMATCH error.

• https://www.sqlite./rescode.html#mismatch