Monday, May 9, 2011

Creating an AIR application with an SQLite database: Part 4

Today we add functionality to the edit and delete buttons of our AIR application.

We need to display the data about a contact when it is selected in the list. Add a listener for the list, that calls a function called onChange. The onChange function will turn the two buttons (edit and delete) available for the user, and set the text fields values to the data from the selected item's data property.

itemList.addEventListener(Event.CHANGE, onChange);

function onChange(evt:Event):void
{
b_save.enabled = true;
b_delete.enabled = true;
t_fname.text = evt.target.selectedItem.data.fname;
t_lname.text = evt.target.selectedItem.data.lname;
t_phone.text = evt.target.selectedItem.data.phone;
}

We now need to update the selectItems() function and add two lines that turn the edit and delete buttons unavailable for the user when the function is called.

function selectItems(SQLEvent):void
{
b_save.enabled = false;
b_delete.enabled = false;
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone FROM contacts ORDER BY id";
stat.execute(-1, new Responder(onSelected));
}

Now, we add functionality to the save button.

Add a listener for the button, then in the function we create an SQLStatement object and send an UPDATE command, taking the id value from the data object of the item list we've clicked.

b_save.addEventListener(MouseEvent.CLICK, saveThis);

function saveThis(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "UPDATE contacts SET fname=@fname, lname=@lname, phone=@phone WHERE id=" + itemList.selectedItem.data.id;
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.execute(-1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = "";
}


Now, the delete button. Pretty much the same, except that we use a different command string.

b_delete.addEventListener(MouseEvent.CLICK, deleteThis);

function deleteThis(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts WHERE id=" + itemList.selectedItem.data.id;
stat.execute(-1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = "";
}

And here's the full code:

import flash.filesystem.File;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.net.Responder;
import flash.data.SQLResult;
import fl.data.DataProvider;
import flash.data.SQLMode;
import flash.errors.SQLError;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;

var connection:SQLConnection;

openDatabase();
t_phone.restrict = "0-9";

function openDatabase():void
{
var dbFile:File = File.applicationStorageDirectory.resolvePath("database.db");
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, onOpen);
connection.openAsync(dbFile, SQLMode.CREATE);
}

function onOpen(SQLEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, phone INTEGER)";
stat.execute(-1, new Responder(selectItems));
}

function selectItems(SQLEvent):void
{
b_save.enabled = false;
b_delete.enabled = false;
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone FROM contacts ORDER BY id";
stat.execute(-1, new Responder(onSelected));
}

function onSelected(evt:SQLResult):void
{
if (evt.data != null)
{
itemList.dataProvider = new DataProvider();
for (var i:int=0; i<evt.data.length; i++)
{
itemList.addItem({label:(evt.data[i].fname + " " + evt.data[i].lname), data:evt.data[i]});
}
}
}

b_new.addEventListener(MouseEvent.CLICK, createNew);

function createNew(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone) VALUES (@fname, @lname, @phone)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.execute(-1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = "";
}

itemList.addEventListener(Event.CHANGE, onChange);

function onChange(evt:Event):void
{
b_save.enabled = true;
b_delete.enabled = true;
t_fname.text = evt.target.selectedItem.data.fname;
t_lname.text = evt.target.selectedItem.data.lname;
t_phone.text = evt.target.selectedItem.data.phone;
}

b_save.addEventListener(MouseEvent.CLICK, saveThis);

function saveThis(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "UPDATE contacts SET fname=@fname, lname=@lname, phone=@phone WHERE id=" + itemList.selectedItem.data.id;
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.execute(-1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = "";
}

b_delete.addEventListener(MouseEvent.CLICK, deleteThis);

function deleteThis(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts WHERE id=" + itemList.selectedItem.data.id;
stat.execute(-1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = "";
}

Now we have a working simple AIR application that works with an SQLite database.

Thanks for reading!

Related:

Creating an AIR application with an SQLite database: Part 1
Creating an AIR application with an SQLite database: Part 2
Creating an AIR application with an SQLite database: Part 3

15 comments:

Anonymous said...

After copy and paste your code in Flash Builder 4.5 I get an error
import fl.data.DataProvider; I am not that great of programmer so is there a workaround?

Vivek said...

ITS REALLY REALLY HELPED ME A LOT....

THANKS THANKS

Anonymous said...

This is one amazing tutorial. It helped me to develop a neat application for my father. Thank you very much.

Josh said...

Thanks so much for posting this, it was a great help to me!!

Anonymous said...

Great tutorial

Anonymous said...

An excellent tutorial. Just note that you must publish in AIR and that one statement:

itemList.dataProvider = new DataProvider();

Should be adjusted (for it to work) to:

var itemList:DataProvider = new DataProvider();

Other than that a good tutorial.
Thank you

ganesh das said...

awesome, tried many tutorial but all of them are partial and did not work , but your tutorial is amazing,
I never faced any problem, I used flash develop
I am planning to make a database driven OOP, is this term correct?
my client need an application to perform student registration, admission, fees, invoice print out etc. Is it also possible in air desktop?

Kirill Poletaev said...

Sure.

mike said...

OMG......after searching for days I finally found such a simple nd sweet sqlite tut......yeah!

ganesh das said...

Hey Kiril thanks, I forgot that I have been to this site in past, I surprised to see my own comment and your sweet reply. I have already done my database plannings and now just need to create the tables and use flex to talk to my database. Can you suggest that should I create different class files to handle queries or they can be in a single .as files, Over all application is small in size.

ACC Member said...

Hi, thank you for this very detailed and useful tutorial. However I am having this issue:

Scene 1, Layer 'Layer 1', Frame 1, Line 36 1046: Type was not found or was not a compile-time constant: SQLResult.

I already have imported the necessary component:
import flash.filesystem.File;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.net.Responder;
import flash.data.SQLResult;
import fl.data.DataProvider;

ACC Member said...

I got it... this is supposed to work on Adobe Air..., not Flash Player... Sorry!

Dreammate said...

Wow very nice tutorial, really helped me lot :)

Yƶnetici said...

Thank you very much.I can't find form this format which contains

Ardy S said...

by the way., that not working in sqlite, i hava a "null"

Post a Comment