Flutter SQLite no such column

Question

I am developing a Flutter app that uses SQLite database using Flutter SQFlite plugin. Issue is that when I try to insert some test data, app prints following in the console:

Unhandled Exception: DatabaseException(no such column: dummy_value (code 1): , while compiling: INSERT INTO DemoTable (name) VALUES(dummy_value)) sql 'INSERT INTO DemoTable (name) VALUES(dummy_value)' args []}

As far as I can understand from the log, issue is with the column 'dummy_value' but here lies the real problem, my database has column 'id' and 'name' whereas the 'dummy_value' is the value, sent to be inserted in column 'name'.

Following is my code for creating database:

class DatabaseCreator {
static const table = 'DemoTable';
static const id = 'id';
static const name = 'name';
static Database db;

initDatabase() async {
Directory documentsDirectory = await 
getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, 'Demo.db');
db = await openDatabase(path, version: 1, onCreate: _onCreate);
return db;
}

Future<void> _onCreate(Database db, int version) async {
await db.execute('''
      CREATE TABLE $table (
        $id INTEGER PRIMARY KEY AUTOINCREMENT,
        $name TEXT
      )
      ''');
  }
}

And following code for inserting data into database:

class DBOP {
static insertName(String name) async {
Database db = await DatabaseCreator().initDatabase();
final result = await db.rawInsert(
    'INSERT INTO ${DatabaseCreator.table} (${DatabaseCreator.name}) 
VALUES($name)');
print(result);
 }
}

And at last, following is my Stateful widget:

class StFull extends StatefulWidget {
@override
_StFullState createState() => _StFullState();
}

class _StFullState extends State<StFull> {
@override
Widget build(BuildContext context) {
return Scaffold(
  appBar: AppBar(
    title: Text('DB Demo'),
  ),
  body: Container(
    child: RaisedButton(
      onPressed: _insertData,
      child: Text('INSERT DATA'),
    ),
    ),
  );
}

_insertData() {
DBOP.insertName('dummy_value');
 }
}

Solution

You should use paramaters for values (especially strings)

Instead of:

await db.rawInsert(
    'INSERT INTO ${DatabaseCreator.table} (${DatabaseCreator.name}) VALUES($name)');

Do something like:

await db.rawInsert(
    'INSERT INTO ${DatabaseCreator.table} (${DatabaseCreator.name}) VALUES(?)', [name]);

or even simpler:

await db.insert(DatabaseCreator.table, <String, dynamic>{DatabaseCreator.name: name});

Comments

[mlewis54]

I believe that you need single quote marks around $name in the insert statement such as "values('$name')"

[Muhammad Faizan]

Thanks @mlewis54, will try and let you know

[Muhammad Faizan]

Not working, even syntax error is being thrown :'(

[mlewis54]

That's probably because you are using single quotes for the string for the INSERT statement. Change those to double quotes: "INSERT ...values('$name')"

[Muhammad Faizan]

@mlewis54..you are a life savor, thanks

[John Melody Me]

+1 ! This works for me ! The part await db.rawInsert( 'INSERT INTO ${DatabaseCreator.table} (${DatabaseCreator.name}) VALUES(?)', [name]);