Suppose you have a listing of movies and you have a combo box where you can choose the genre to which that movie belongs. If you decide to add a new genre you do not want to have to log into the database and add a new genre record into your genre database. Why - because you are a developer and you are lazy right? And if someone else is doing the administration you do not want them to be in a position of needing to contact you to do this. Unless you can charge them for it. If that is the case, do not read this article.:)
Almost anytime you have a combo box you are pointing to a reference table where you may want to add entries. The only exception to this would be a states database because it is unlikely we will add any new states. But for combo boxes that list reference information that is subject to change, it would be great to add the "genre", "category" or whatever on the fly.
How do you do this?
Well first, add a row to your table through your database editor. Here just insert the genre and do not define the value of the id. If it works great. If not, it is because the id is not defined as autoincrement. I am assuming every table you create has a column called id or something similar which is defined as integer so you can reference this table by that identifier or id. As a side note, I recommend that every table also include a created_timestamp, created_userid, modified_timestamp and modified_userid. This is just my recommendation after 20 plus years of experience. I will discuss why in a later blog post.
The goal is to browse through the list of entries in a combo box, have the first entry defined as new genre, new category, or new whatever you are listing in your combo box. When you select this new item, your code will see it is the new entry (from the hardcoded id for the new item) provide a message prompt to enter a new genre, category, etc., capture that message text, and fire off an ajax request to insert that value into your reference table, then reload your store so when you click on the combo again your entry is there. Life is Wonderful.
What steps are involved?
1. Your application should be using a remote store rather than a simple store so you are querying your database.
2. Your application must be set up to provide the message prompt and react to that prompt.
3. You must define a cfc to insert a record into your database. But first, you must determine whether you set your table to auto increment or not. If it is not defined to auto increment then you must first query your database, select max(id) from your table, save the max id and increment it by one so you can then insert this new id into the table along with your value.
If you have auto increment defined, then you can insert the value right into the table without getting the maxid first. In a busy environment, you always want auto increment turned on. Otherwise, someone else may be inserting at the same time you are, they have already derived the same max id as you, when you go to insert your row you may get a failure as that key already exists because the other transaction inserted the record before you did. So, I recommend turning auto increment on.
Auto increment is a great term but in SqlServer it is actually called the Identity Specification. If you go into SqlServer, check your index, verify Identity Specification is set to YES, IS IDENTIFY = YES, Identity Increment is set to 1 or whatever step you prefer and Identity Seed (starting value) is set to your desired number or 1.
Here is some great information on auto incrementing indexes and it discusses how this is done with SqlServer, Mysql, Access, Oracle, etc. www.w3schools.com/sql/sql_autoincrement.asp.
Let's take a look at the combo box defintion for the form. First we add a listener to the combo box. Then we check the index of the item selected is equal to the first item. Extjs starts at base 0 and the very first record we have stored in our database is defined as New Genre. If this is the item selected we provide a message prompt asking what new genre should be added.
If the ok button is selected, we fire off an Ext.Ajax.request to execute an cfc to insert the row. The new genre is entered as text in the message prompt, we set the data variable equal to text, and then pass the data variable as a param to the cfc. We then check the response and if we were successful we send a message that the insert was successful otherwise we send a message that the update failed.
{
xtype: 'combo',
name: 'genre',
fieldLabel: 'Genre',
mode: 'local',
store: genres,
emptyText: 'Select a genre...',
displayField:'GENER_NAME',
valueField: 'ID',
width: 120,
triggerAction: 'all',
listeners: {
select: function(f,r,i){
if (i == 0){
Ext.Msg.prompt('New Genre','Name', function(btn, text){
if (btn == 'ok'){
var data = text;
Ext.Msg.alert('you entered', data);
Ext.Ajax.request({
url: 'genres3.cfc?method=addGenres',
params: {genre: data},
success: function(resp,opt) {
genres.load();
Ext.Msg.alert( 'Category was added');
},
failure: function(resp,opt) {
Ext.Msg.alert('Category add failed');
}
//}
});
}
}, this, false, '');
;
}
}
}
},
Here is the addgenres function inside the cfc. Note that I did not need to select max id prior to the insert as the database has auto increment set to 1.
INSERT INTO genres (GENER_NAME) Values ('#arguments.genre#')
This whole process was made easier by searching through the ExtJS examples, examples shown in Learning ExtJS 3.0 and 3.2 by Shea Frederick and examples in the ExtJS Cookbook by Jorge Ramon. If you do not own them you should go to PacktPub and buy them. They will provide many examples that show you how to do almost anything you want to do with ExtJS 3 series.
No comments:
Post a Comment