How to create edit function in a Sqlite database

Question

I have created an app where a company can add the contact details of its customers. The data is saved on Sqlite. It is very similar to a To Do app. On the ListTile you get a location icon (not functional yet), the name of the company, then a delete icon (which works fine).The add button works fine and all new companies are listed fine and the details of the company.

Once you click on a company name to open the tile, you see all the company details, BUT, at the bottom I have an Edit icon. I just cannot seem to find my edit functionality to work.

I have found some helping code, but it does not work and as I am fairly new to programming I just don't understand what I am doing wrong. When I click my edit icon, it opens the input page where one would normally insert new details (see below). What it is supposed to do is to open the input page, but with the existing company details so that it can be edited.

Here is my code:

I keep my Sqfilte code in my a Library page:

Library

import 'package:sqflite/sqflite.dart';
 import 'package:path/path.dart';

 //Customer

 class Todo {
 int? id;
 final String title;
 final String name;
 final String phone;
 final String fax;
 final String email;
 final String street;
 final String city;
 final String town;
 final String code;
 bool isExpanded;

 Todo({
 this.id,
  required this.title,
  required this.name,
  required this.phone,
  required this.fax,
  required this.email,
  required this.street,
  required this.city,
  required this.town,
  required this.code,
  this.isExpanded = false,
  });

 Map<String, dynamic> toMap() {
 return {
  'id': id,
  'title': title,
  'name': name,
  'phone': phone,
  'fax': fax,
  'email': email,
  'street': street,
  'city': city,
  'town': town,
  'code': code,
  'isExpanded': isExpanded ? 1 : 0,
   };
   }

   @override
   String toString() {
   return 'Todo(id : $id, title : $title, name : $name, phone : $phone, fax: $fax, email: 
   $email, street: $street, city: $city, town: $town, code: $code, isExpanded : 
   $isExpanded,)';
   }
   }




    class DatabaseConnect {
    Database? _database;  
    Future<Database> get database async {    
   final dbpath = await getDatabasesPath();   
   const dbname = 'todo.db';   
   final path = join(dbpath, dbname);   
   _database = await openDatabase(path, version: 1, onCreate: _createDB);

   return _database!;
    }


   Future<void> _createDB(Database db, int version) async {
   await db.execute('''
   CREATE TABLE todo(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    name TEXT,
    phone TEXT,
    fax TEXT,
    email TEXT,
    street TEXT,
    city TEXT,
    town TEXT,
    code TEXT,
    isExpanded INTEGER
  
      )
     ''');
   }


   Future<void> insertTodo(Todo todo) async {   
    final db = await database;   
    await db.insert(
    'todo',
    todo.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
    );
    }

  Future<void> deleteTodo(Todo todo) async {
   final db = await database;
   await db.delete(
  'todo',
  where: 'id == ?',
  whereArgs: [todo.id],
  );
   }

   Future<void> updateTodo(Todo todo) async {
   final db = await database;
   db.update('todo', todo.toMap(), where: 'id=?', whereArgs: [todo.id]);

  }



   Future<List<Todo>> getTodo() async {
   final db = await database;


   List<Map<String, dynamic>> items = await db.query(
  'todo',
  orderBy: 'title ASC',
   ); //this will order the list by id in descending order

  return List.generate(
  items.length,
  (i) => Todo(
    id: items[i]['id'],
    title: items[i]['title'],
    name: items[i]['name'],
    phone: items[i]['phone'],
    fax: items[i]['fax'],
    email: items[i]['email'],
    street: items[i]['street'],
    city: items[i]['city'],
    town: items[i]['town'],
    code: items[i]['code'],
    isExpanded: items[i]['isExpanded'] == 1 ? true : false,
    ),
   );
   }

  Future<List<Todo>> searchContacts(String keyword) async {
  final db = await database;
  List<Map<String, dynamic>> items =
    await db.query('todo', where: 'title LIKE ?', whereArgs: ['$keyword%']);

   return List.generate(
   items.length,
   (i) => Todo(
    id: items[i]['id'],
    title: items[i]['title'],
    name: items[i]['name'],
    phone: items[i]['phone'],
    fax: items[i]['fax'],
    email: items[i]['email'],
    street: items[i]['street'],
    city: items[i]['city'],
    town: items[i]['town'],
    code: items[i]['code'],
    isExpanded: items[i]['isExpanded'] == 1 ? true : false,
    ),
    );
    }
    }

Then, on the code below (customercard) is where the ListTile with all the new company data is converted into a card. The edit icon is also seen here which onpressed will go to Edit Page. I am now having a error here as I don't know what code to insert here with the 'updateFunction'.

customercard

 import 'package:flutter/material.dart';
 import 'library.dart';
 import 'package:test_sqlite/editpage.dart';

 class CustomerCard extends StatefulWidget {
 final int id;
 final String title;
 final String name;
 final String phone;
 final String fax;
 final String email;
 final String street;
 final String city;
  final String town;
  final String code;
  bool isExpanded;
  final Function insertFunction;
  final Function deleteFunction;
  final Function updateFunction;

  CustomerCard(
  {required this.id,
  required this.title,
  required this.name,
  required this.phone,
  required this.fax,
  required this.email,
  required this.street,
  required this.city,
  required this.town,
  required this.code,
  required this.isExpanded,
  required this.insertFunction,
  required this.deleteFunction,
   required this.updateFunction,
  Key? key})
  : super(key: key);

  @override
  _CustomerCardState createState() => _CustomerCardState();
   }

  class _CustomerCardState extends State<CustomerCard> {
  var db = DatabaseConnect();

  @override
  Widget build(BuildContext context) {
  var anotherTodo = Todo(
    id: widget.id,
    title: widget.title,
    name: widget.name,
    phone: widget.phone,
    fax: widget.fax,
    email: widget.email,
    street: widget.street,
    city: widget.city,
    town: widget.town,
    code: widget.code,
    isExpanded: widget.isExpanded);

   return Card(
    child: Column(
    crossAxisAlignment: CrossAxisAlignment.start,
    children: [
      Theme(
        data: Theme.of(context).copyWith(dividerColor: Colors.transparent),
        child: ExpansionTile(
          initiallyExpanded: false,
          title: Text(
            widget.title,
            style: const TextStyle(
              //fontWeight: FontWeight.bold,
              fontSize: 16,
            ),
          ),
          children: [
            ListTile(
              leading: const Icon(
                Icons.person,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -3),
              title: Text(
                widget.name,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.phone,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.phone,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.report,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.fax,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.email,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.email,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.place,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.street,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.place,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.city,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.place,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.town,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            ListTile(
              leading: const Icon(
                Icons.code,
                size: 20,
                color: Colors.teal,
              ),
              visualDensity: const VisualDensity(vertical: -4),
              title: Text(
                widget.code,
                style: const TextStyle(
                  fontSize: 16,
                  fontWeight: FontWeight.normal,
                  color: Colors.black,
                ),
              ),
            ),
            Row(
              mainAxisAlignment: MainAxisAlignment.end,
              children: [
                Padding(
                  padding: const EdgeInsets.all(8.0),
                  child: ElevatedButton(
                    onPressed: () {
                      showModalBottomSheet(
                        isScrollControlled: true,
                        context: context,
                        builder: (context) =>
                            EditPage(updateFunction: addItem),);
                    },
                    style: ElevatedButton.styleFrom(
                      shape: const StadiumBorder(),
                      primary: Colors.white,
                      elevation: 0,
                      padding: const EdgeInsets.symmetric(
                          horizontal: 2, vertical: 2),
                    ),
                    child: const Icon(
                      Icons.edit,
                      size: 20,
                      color: Colors.grey,
                    ),
                  ),
                ),
              ],
            ),
          ],
          leading: const IconButton(
            icon: Icon(
              Icons.place,
              color: Colors.blue,
              size: 20,
            ),
            onPressed: null,
            alignment: Alignment.center,
          ),
          trailing: IconButton(
            onPressed: () {
              widget.deleteFunction(anotherTodo);
            },
            icon: const Icon(
              Icons.delete,
              color: Colors.red,
              size: 20,
            ),

Then the editpage which is the input page where one goes to when pressing the edit button. On this page one should see all the existing company data so one can edit it, but it only opens a new input page with hint text.

editpage

import 'package:flutter/material.dart';
import 'library.dart';
import 'package:flutter/cupertino.dart';

class EditPage extends StatelessWidget {

final textController = TextEditingController();
final nameController = TextEditingController();
final phoneController = TextEditingController();
final faxController = TextEditingController();
final emailController = TextEditingController();
final streetController = TextEditingController();
final cityController = TextEditingController();
final townController = TextEditingController();
final codeController = TextEditingController();

final Function updateFunction;

 // DatabaseConnect updateFunction = DatabaseConnect();

 EditPage({required this.updateFunction, Key? key, todo}) : super(key: key);

 @override
 Widget build(BuildContext context) {
 return Container(
  padding: const EdgeInsets.all(30.0),
  decoration: const BoxDecoration(
    color: Colors.white,
    borderRadius: BorderRadius.only(
      topLeft: Radius.circular(30.0),
      topRight: Radius.circular(30.0),
    ),
    ),
    child: Scaffold(
     appBar: AppBar(
      backgroundColor: Colors.white,
      elevation: 0,
      title: const Padding(
        padding: EdgeInsets.all(15.0),
        child: Text(
          'Client Details',
          style: TextStyle(color: Colors.black, fontSize: 24),
        ),
      ),
      leading: GestureDetector(
        onTap: () {
          Navigator.of(context).pushReplacementNamed('/homePage');
        },
        child: const Icon(
          Icons.arrow_back,
          color: Colors.black,
        ),
        ),
      ),
      body: SingleChildScrollView(
       child: Container(
        color: Colors.white,
        child: Column(
          crossAxisAlignment: CrossAxisAlignment.stretch,
          children: [
            
              TextField(
              controller: textController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: ' Company Name',
                hintStyle: TextStyle(color: Colors.grey),
              ),
             ),
            TextField(
              controller: nameController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: ' Contact Name & Surname',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: phoneController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: ' Contact Number',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: faxController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: 'Fax Number',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: emailController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: 'Email Address',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: streetController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: 'Street Name',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: cityController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: ' City',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: townController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: 'Town',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            TextField(
              controller: codeController,
              autofocus: true,
              textAlign: TextAlign.left,
              decoration: const InputDecoration(
                hintText: ' Code',
                hintStyle: TextStyle(color: Colors.grey),
              ),
            ),
            GestureDetector(
              onTap: () {
                Navigator.pop(context);
                var myTodo = Todo(
                    title: textController.text,
                    name: nameController.text,
                    phone: phoneController.text,
                    fax: faxController.text,
                    email: emailController.text,
                    street: streetController.text,
                    city: cityController.text,
                    town: townController.text,
                    code: codeController.text,
                    isExpanded: false);
                updateFunction.updateTodo(myTodo);
              },
              child: Padding(
                padding: const EdgeInsets.fromLTRB(0, 10, 0, 0),
                child: Container(
                  decoration: BoxDecoration(
                    color: Theme.of(context).primaryColor,
                    borderRadius: BorderRadius.circular(15),
                  ),
                  padding: const EdgeInsets.symmetric(
                      horizontal: 25, vertical: 10),
                  child: const Text(
                    'Add',
                    textAlign: TextAlign.center,
                    style: TextStyle(
                      color: Colors.white,
                      fontWeight: FontWeight.bold,
                    ),
          

Then, I have a customer page where you will see the following functions: addItem, deleteItem and updateItem, which in essence is contained in 'insertFunction, deleteFunction and updateFunction.

customer

import 'package:flutter/material.dart';
import 'package:flutter/cupertino.dart';
import '../customerlist.dart';
import '../library.dart';
import '../user_input.dart';

class Customer extends StatefulWidget {
const Customer({Key? key}) : super(key: key);

 @override
  _CustomerState createState() => _CustomerState();
  }

  class _CustomerState extends State<Customer> {
   var db = DatabaseConnect();

  void addItem(Todo todo) async {
  await db.insertTodo(todo);
  setState(() {});
  }

   void deleteItem(Todo todo) async {
    await db.deleteTodo(todo);
    setState(() {});
    }

    void updateItem(Todo todo) async {
    await db.updateTodo(todo);
    setState(() {});
    }

    @override
     Widget build(BuildContext context) {
     return Scaffold(
      backgroundColor: Colors.white,
     appBar: AppBar(
      centerTitle: false,
      title: const Padding(
        padding: EdgeInsets.all(50.0),
        child: Text(
          'My Clients',
          style: TextStyle(
              fontSize: 24,
              fontWeight: FontWeight.w600,
              color: Colors.black),
        ),
      ),
      backgroundColor: Colors.white,
      elevation: 0,
      actions: [
        IconButton(
          onPressed: () {
            Navigator.of(context).pushReplacementNamed('/searchPage');
          },
          icon: const Icon(
            Icons.search,
            color: Colors.black,
          ),
        ),
        ]),
        body: Column(
        crossAxisAlignment: CrossAxisAlignment.start,
        children: [
       const Padding(
        padding: EdgeInsets.symmetric(horizontal: 16, vertical: 10),
        child: Text(
          'Company Name',
          style: TextStyle(fontSize: 14, fontWeight: FontWeight.w600),
        ),
        ),
        CustomerList(
         insertFunction: addItem,
          deleteFunction: deleteItem,
         updateFunction: updateItem,
        ),
        ],

    
        ),
       floatingActionButton: FloatingActionButton(
        backgroundColor: Colors.lightBlueAccent,
        child: const Icon(Icons.add),
        onPressed: () {
         showModalBottomSheet(
          isScrollControlled: true,
          context: context,
          builder: (context) => CustomerProfile(insertFunction: addItem),
          );
        }

PS: I really need help. I have been stucked here for more than a month now. I have watch youtube channels, read stack overflow and the document, but I simply can't figure this out. Any help will really be greatly appreciated..

Answer 1

I have managed to finally resolve the problem.

My updateFunction and updateItem function was actually working just fine all along. I realized that I never assigned an 'id' to my variable ' updateTodo'. I only had my TextEditingController. Now, in my Sql Database, the database is updated using 'Where: id', but since I did not assign an 'id' to my text, I effectively returned Null everytime I updated.

Here is the snippet of code for GetureDetector before the fix,

GestureDetector(
          onTap: () {
            Navigator.pop(context);
            var updateTodo = Todo(
                
                title: textController.text,
                name: nameController.text,
                phone: phoneController.text,
                fax: faxController.text,
                email: emailController.text,
                street: streetController.text,
                city: cityController.text,
                town: townController.text,
                code: codeController.text,
                isExpanded: false);
            nalaFunction(updateTodo);
          },

and here the same code after the fix:

GestureDetector(
          onTap: () {
            Navigator.pop(context);
            var updateTodo = Todo(
                 id: id,
                title: textController.text,
                name: nameController.text,
                phone: phoneController.text,
                fax: faxController.text,
                email: emailController.text,
                street: streetController.text,
                city: cityController.text,
                town: townController.text,
                code: codeController.text,
                isExpanded: false);
            nalaFunction(updateTodo);
          },

and the code for passing the 'id' in CustomerCard class through to EditPage:

class CustomerCard extends StatefulWidget {
 final int id;
 final String title;
 final String name;
 final String phone;
 final String fax;
 final String email;
 final String street;
 final String city;
 final String town;
 final String code;
 bool isExpanded;

final Function janFunction;
final Function simbaFunction;

CustomerCard(
 {required this.id,
 required this.title,
 required this.name,
 required this.phone,
 required this.fax,
 required this.email,
 required this.street,
 required this.city,
 required this.town,
 required this.code,
 required this.isExpanded,

and ..

 Row(mainAxisAlignment: MainAxisAlignment.end,
  children: [
  Padding(
  padding: const EdgeInsets.all(8.0),
   child: ElevatedButton(
     onPressed: () {
     showModalBottomSheet(
     isScrollControlled: true,
     context: context,
     builder: (context) => EditPage(
      nalaFunction: widget.simbaFunction,
      variable: anotherTodo,
      id: widget.id,
      ),

                    //updateFunction,
                    //),
                  );
                },

and passing the 'id' through to the EditPage so that it can be reached by the GestureDetector on the same page:

class EditPage extends StatelessWidget {
 final Todo variable;
 final Function nalaFunction;
 final int id;

 late final textController = TextEditingController(text: variable.title);
 late final nameController = TextEditingController(text: variable.name);
 late final phoneController = TextEditingController(text: variable.phone);
 late final faxController = TextEditingController(text: variable.fax);
 late final emailController = TextEditingController(text: variable.email);
 late final streetController = TextEditingController(text: variable.street);
 late final cityController = TextEditingController(text: variable.city);
 late final townController = TextEditingController(text: variable.town);
 late final codeController = TextEditingController(text: variable.code);

 EditPage({Key? key,
 required this.nalaFunction,
 required this.variable,
 required this.id,
 }) : super(key: key);

This resolved my problem and my app now update new customer data perfectly.