ListView and data

[abenitez77]

I want to populate the a listview with 3 columns with data from a table that I have in SQL Server. I seen the documentation you have on listview, but it does not show how to populate the list with 3 columns using a sql server table. Also, I'll need to know how to retrieve the values from the list in each column. Can you give me an example?

thanks!

[Aaron]

Hi,

The documentation contains an extensive example showing how to perform operations with a listview, including adding multiple columns, adding and deleting items, as well as handling events. The process for hooking it up to SQL Server is the same, except that the data comes from a database query, which you're already familiar with.

Here's a link to the listview documentation example (see "Example" about 1/3 the way down): http://www.kirix.com/docs/ListView.html

Also, for reference, here's the code from the example:

Code: Select all

class MyForm extends Form
{
    var m_list;
    var m_text;
   
    var m_last_sort_order;
    var m_last_sort_column;
   
    function MyForm()
    {
        super("ListView Test", 100, 100, 700, 520);
        setMinSize(500, 360);
       
        // create list view and add some columns       
        m_list = new ListView;
        m_list.setView(ListView.Details);
        m_list.addColumn("Test Column", 200, ListView.AlignLeft);
        m_list.addColumn("Test Column 2", 200, ListView.AlignLeft);
        m_list.addColumn("Test Column 3", 200, ListView.AlignLeft);
        this.add(m_list);
       
        // add some items
        var folder_bitmap = getFolderBitmap();
        m_list.addItem(new ListViewItem("Finch", folder_bitmap));
        m_list.addItem(new ListViewItem("Bluebird", folder_bitmap));
        m_list.addItem(new ListViewItem("Goose", folder_bitmap));
        m_list.addItem(new ListViewItem("Sparrow", folder_bitmap));
        m_list.addItem(new ListViewItem("Hummingbird", folder_bitmap));
        m_list.addItem(new ListViewItem("Duck", folder_bitmap));
       
        // connect events
        m_list.keyDown.connect(this, onKeyDown);
        m_list.itemSelect.connect(this, onItemSelect);
        m_list.itemActivate.connect(this, onItemActivate);
        m_list.itemRightClick.connect(this, onItemRightClick);
        m_list.itemBeginLabelEdit.connect(this, onItemBeginLabelEdit);
        m_list.itemEndLabelEdit.connect(this, onItemEndLabelEdit);
        m_list.columnClick.connect(this, onColumnClick);
        m_list.columnRightClick.connect(this, onColumnRightClick);
               
        // text box for event output
        m_text = new TextBox("", 0, 0, 140, 140);
        m_text.setMultiline();

        // -- some extra buttons --
       
        var additem_button = new Button("Add Item");
        additem_button.click.connect(this, onAddItem);
       
        var changeitem_button = new Button("Change Item");
        changeitem_button.click.connect(this, onChangeItem);
     
        var deleteitem_button = new Button("Delete Item");
        deleteitem_button.click.connect(this, onDeleteItem);
       
        var deleteall_button = new Button("Delete All");
        deleteall_button.click.connect(this, onDeleteAll);
       
        var exit_button = new Button("Exit");
        exit_button.click.connect(this, onExit);
       
        // these variables save the last sort order
        m_last_sort_order = false;
        m_last_sort_column = -1;
       
        // -- create button layout --
        var button_layout = new BoxLayout(Layout.Horizontal);
        button_layout.add(additem_button);
        button_layout.addSpacer(8);
        button_layout.add(changeitem_button);
        button_layout.addSpacer(8);
        button_layout.add(deleteitem_button);
        button_layout.addSpacer(8);
        button_layout.add(deleteall_button);
        button_layout.addStretchSpacer();
        button_layout.add(exit_button);
       
        // -- create main layout --
        var main_layout = new BoxLayout(Layout.Vertical);
        main_layout.addSpacer(8);
        main_layout.add(m_list, 1, Layout.Expand | Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
        main_layout.add(m_text, 0, Layout.Expand | Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
        main_layout.add(button_layout, 0, Layout.Expand | Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
       
        setLayout(main_layout);
    }
   
    function onColumnClick(sender, event_args)
    {
        var column_to_sort = event_args.index;
        var sort_order;
       
        if (column_to_sort == m_last_sort_column)
        {
            sort_order = m_last_sort_order ? false : true;
        }
         else
        {
            sort_order = true; // ascending
        }
       
        m_list.sortItems(column_to_sort, sort_order);
       
        m_last_sort_order = sort_order;
        m_last_sort_column = column_to_sort;
    }
   
    function onColumnRightClick(sender, event_args)
    {
        addLogLine("Column right clicked; index = " + event_args.index);
       
        var sortasc_item = new MenuItem("Sort Ascending");
        var sortdec_item = new MenuItem("Sort Descending");
        var deletecol_item = new MenuItem("Delete Column");
       
        sortasc_item.index = event_args.index;
        sortasc_item.sort_order = true;
        sortasc_item.click.connect(this, onDoColumnSort);
       
        sortasc_item.index = event_args.index;
        sortdec_item.sort_order = false;
        sortdec_item.click.connect(this, onDoColumnSort);
       
        deletecol_item.index = event_args.index;
        deletecol_item.click.connect(this, onDoColumnDelete);
       
        var menu = new Menu;
        menu.add(sortasc_item);
        menu.add(sortdec_item);
        menu.addSeparator();
        menu.add(deletecol_item);
       
        menu.popup(m_list);
    }
   
    function onDoColumnSort(sender, event_args)
    {
        m_list.sortItems(sender.index, sender.sort_order);
    }
   
    function onDoColumnDelete(sender, event_args)
    {
        m_list.deleteColumn(sender.index);
    }
   
    function onKeyDown(sender, event_args)
    {
        addLogLine("Key down; Char code = " + event_args.keyCode +
                    " shiftDown(" + event_args.shiftDown + ")" +
                    " controlDown(" + event_args.controlDown + ")" +
                    " altDown(" + event_args.altDown + ")");
       
        // handle user pressing Del key
        if (event_args.keyCode == 127)
        {
            m_list.deleteItem(event_args.index);
        }
    }
   
    function onItemSelect(sender, event_args)
    {
        addLogLine("Item selected; index = " + event_args.index);
    }
   
    function onItemActivate(sender, event_args)
    {
        addLogLine("Item activated; index = " + event_args.index);
    }
   
    function onItemRightClick(sender, event_args)
    {
        addLogLine("Item right clicked; index = " + event_args.index);
       
        var menu_item1 = new MenuItem("Test Menu Item 1");
        var menu_item2 = new MenuItem("Test Menu Item 2");
        var menu_item3 = new MenuItem("Delete Listview Item");
       
        menu_item3.item_number = event_args.index;
        menu_item3.click.connect(this, onRightClickDelete);
       
        var menu = new Menu;
        menu.add(menu_item1);
        menu.add(menu_item2);
        menu.addSeparator();
        menu.add(menu_item3);
       
        menu.popup(m_list);
    }
   
    function onRightClickDelete(sender, event_args)
    {
        m_list.deleteItem(sender.item_number);
    }
   
    function onItemBeginLabelEdit(sender, event_args)
    {
        addLogLine("Begin label edit; index = " + event_args.index);
    }
   
    function onItemEndLabelEdit(sender, event_args)
    {
        addLogLine("End label edit; index = " + event_args.index);
    }
   
    function onAddItem(sender, event_args)
    {
        var folder_bitmap = getFolderBitmap();
        m_list.addItem(new ListViewItem("Test Item " + (m_list.getItemCount()+1),
                                        folder_bitmap));
    }
   
    function onDeleteItem(sender, event_args)
    {
        var items = m_list.getSelectedItems();
        for (var i in items)
        {
            m_list.deleteItem(items[i]);
        }
    }
   
    function onChangeItem(sender, event_args)
    {
        var items = m_list.getSelectedItems();
        var counter = 0;
        for (var i in items)
        {
            counter++;
            items[i].setText(items[i].getText() + " Changed Text");
            items[i].setColumnText(1, "Hello " + counter);
            items[i].setColumnText(2, "Hello " + counter);
        }
    }
   
    function onDeleteAll(sender, event_args)
    {
        m_list.clear();
    }
   
    function addLogLine(s)
    {
        var date = new Date();
       
        var text = m_text.getText();
        text += date.toString() + " - " + s;
        text += "\n";
        m_text.setText(text);
    }

    function onExit()
    {
        Application.exit();
    }
};




var form = new MyForm;
form.show();
Application.run();




var g_folder_bitmap;

function getFolderBitmap()
{
    if (g_folder_bitmap)
        return g_folder_bitmap;
   
    g_folder_bitmap = new Bitmap('
        "16 16 6 1",
        "  c None",
        "! c #000000",
        "# c #808080",
        "$ c #C0C0C0",
        "% c #FFFF00",
        "& c #FFFFFF",
        "                ",
        "                ",
        "  #####         ",
        " #%$%$%#        ",
        "#%$%$%$%######  ",
        "#&&&&&&&&&&&&#! ",
        "#&%$%$%$%$%$%#! ",
        "#&$%$%$%$%$%$#! ",
        "#&%$%$%$%$%$%#! ",
        "#&$%$%$%$%$%$#! ",
        "#&%$%$%$%$%$%#! ",
        "#&$%$%$%$%$%$#! ",
        "#&%$%$%$%$%$%#! ",
        "##############! ",
        " !!!!!!!!!!!!!! ",
        "                "');
   
    return g_folder_bitmap;
}

Best,
Aaron

[abenitez77]

Aaron,
I am new to javascript and I do not know what the exact syntax is to populate the listview from a table. I think I can handle getting the values from the table, but how to insert the values into the listview and how to retrieve the values when I want to retrieve them is where I am struggling. I tried doing it the same way the example shows it but could not get it to work for me. I have been struggling with this for days to make it work. I tried using the SetColumnText as it is shown but without success. I've started to change the code around to make it work for me...can you please help with this?

class MyForm extends Form
{
var m_list;
var m_text;

var m_last_sort_order;
var m_last_sort_column;

function MyForm()
{
super("ListView Test", 100, 100, 500, 320);
setMinSize(300, 160);

// create list view and add some columns
m_list = new ListView;
m_list.setView(ListView.Details);
m_list.addColumn("SQL Server", 200, ListView.AlignLeft);
m_list.addColumn("DATABASE", 200, ListView.AlignLeft);
m_list.addColumn("TABLE", 200, ListView.AlignLeft);
this.add(m_list);

//Connection
var dbP;
dbP = new DbConnection("mssql://USATL02PRSQ70:1434/AS_PROJECTS");
var MyClients = dbP.execute("select Distinct ClientName from tblv_ProjectsDetail WHERE ToolName = 'STRATA'");
while (MyClients.next())
{
//choiceboxClients.addItem(MyClients.ClientName);
}


// add some items
//var folder_bitmap = getFolderBitmap();
//m_list.addItem(new ListViewItem("Finch", folder_bitmap));
//m_list.addItem(new ListViewItem("Bluebird", folder_bitmap));
//m_list.addItem(new ListViewItem("Goose", folder_bitmap));
//m_list.addItem(new ListViewItem("Sparrow", folder_bitmap));
//m_list.addItem(new ListViewItem("Hummingbird", folder_bitmap));
//m_list.addItem(new ListViewItem("Duck", folder_bitmap));

//m_list.addItem(new ListViewItem("Finch"));
m_list.addItem(new ListViewItem(""));

//m_list.setColumnText(0,"SQ01");
//m_list.setColumnText(1,"SQ02");
//m_list.setColumnText(2,"SQ03");

//m_list.insertItem("SQ02",1);
//m_list.insertItem("SQ03",2);
//m_list.setColumnText(0, "Server Name");
//m_list.setColumnText(1, "DB Name");
//m_list.setColumnText(2, "Table Name");


// connect events
m_list.keyDown.connect(this, onKeyDown);
m_list.itemSelect.connect(this, onItemSelect);
m_list.itemActivate.connect(this, onItemActivate);
m_list.itemRightClick.connect(this, onItemRightClick);
m_list.itemBeginLabelEdit.connect(this, onItemBeginLabelEdit);
m_list.itemEndLabelEdit.connect(this, onItemEndLabelEdit);
m_list.columnClick.connect(this, onColumnClick);
m_list.columnRightClick.connect(this, onColumnRightClick);

// text box for event output
m_text = new TextBox("", 0, 0, 140, 140);
m_text.setMultiline();

// -- some extra buttons --

var additem_button = new Button("Add Item");
additem_button.click.connect(this, onAddItem);

var changeitem_button = new Button("Change Item");
changeitem_button.click.connect(this, onChangeItem);

var deleteitem_button = new Button("Delete Item");
deleteitem_button.click.connect(this, onDeleteItem);

var deleteall_button = new Button("Delete All");
deleteall_button.click.connect(this, onDeleteAll);

var exit_button = new Button("Exit");
exit_button.click.connect(this, onExit);

// these variables save the last sort order
m_last_sort_order = false;
m_last_sort_column = -1;

// -- create button layout --
var button_layout = new BoxLayout(Layout.Horizontal);
button_layout.add(additem_button);
button_layout.addSpacer(8);
button_layout.add(changeitem_button);
button_layout.addSpacer(8);
button_layout.add(deleteitem_button);
button_layout.addSpacer(8);
button_layout.add(deleteall_button);
button_layout.addStretchSpacer();
button_layout.add(exit_button);

// -- create main layout --
var main_layout = new BoxLayout(Layout.Vertical);
main_layout.addSpacer(8);
main_layout.add(m_list, 1, Layout.Expand | Layout.Left | Layout.Right, ;
main_layout.addSpacer(8);
main_layout.add(m_text, 0, Layout.Expand | Layout.Left | Layout.Right, ;
main_layout.addSpacer(8);
main_layout.add(button_layout, 0, Layout.Expand | Layout.Left | Layout.Right, ;
main_layout.addSpacer(8);

setLayout(main_layout);
}

function onColumnClick(sender, event_args)
{
var column_to_sort = event_args.index;
var sort_order;

if (column_to_sort == m_last_sort_column)
{
sort_order = m_last_sort_order ? false : true;
}
else
{
sort_order = true; // ascending
}

m_list.sortItems(column_to_sort, sort_order);

m_last_sort_order = sort_order;
m_last_sort_column = column_to_sort;
}

function onColumnRightClick(sender, event_args)
{
addLogLine("Column right clicked; index = " + event_args.index);

var sortasc_item = new MenuItem("Sort Ascending");
var sortdec_item = new MenuItem("Sort Descending");
var deletecol_item = new MenuItem("Delete Column");

sortasc_item.index = event_args.index;
sortasc_item.sort_order = true;
sortasc_item.click.connect(this, onDoColumnSort);

sortasc_item.index = event_args.index;
sortdec_item.sort_order = false;
sortdec_item.click.connect(this, onDoColumnSort);

deletecol_item.index = event_args.index;
deletecol_item.click.connect(this, onDoColumnDelete);

var menu = new Menu;
menu.add(sortasc_item);
menu.add(sortdec_item);
menu.addSeparator();
menu.add(deletecol_item);

menu.popup(m_list);
}

function onDoColumnSort(sender, event_args)
{
m_list.sortItems(sender.index, sender.sort_order);
}

function onDoColumnDelete(sender, event_args)
{
m_list.deleteColumn(sender.index);
}

function onKeyDown(sender, event_args)
{
addLogLine("Key down; Char code = " + event_args.keyCode +
" shiftDown(" + event_args.shiftDown + ")" +
" controlDown(" + event_args.controlDown + ")" +
" altDown(" + event_args.altDown + ")");

// handle user pressing Del key
if (event_args.keyCode == 127)
{
m_list.deleteItem(event_args.index);
}
}

function onItemSelect(sender, event_args)
{
addLogLine("Item selected; index = " + event_args.index);
}

function onItemActivate(sender, event_args)
{
addLogLine("Item activated; index = " + event_args.index);
}

function onItemRightClick(sender, event_args)
{
addLogLine("Item right clicked; index = " + event_args.index);

var menu_item1 = new MenuItem("Test Menu Item 1");
var menu_item2 = new MenuItem("Test Menu Item 2");
var menu_item3 = new MenuItem("Delete Listview Item");

menu_item3.item_number = event_args.index;
menu_item3.click.connect(this, onRightClickDelete);

var menu = new Menu;
menu.add(menu_item1);
menu.add(menu_item2);
menu.addSeparator();
menu.add(menu_item3);

menu.popup(m_list);
}

function onRightClickDelete(sender, event_args)
{
m_list.deleteItem(sender.item_number);
}

function onItemBeginLabelEdit(sender, event_args)
{
addLogLine("Begin label edit; index = " + event_args.index);
}

function onItemEndLabelEdit(sender, event_args)
{
addLogLine("End label edit; index = " + event_args.index);
}

function onAddItem(sender, event_args)
{
var folder_bitmap = getFolderBitmap();
m_list.addItem(new ListViewItem("Test Item " + (m_list.getItemCount()+1),
folder_bitmap));
}

function onDeleteItem(sender, event_args)
{
var items = m_list.getSelectedItems();
for (var i in items)
{
m_list.deleteItem(items[i]);
}
}

function onChangeItem(sender, event_args)
{
var items = m_list.getSelectedItems();
var counter = 0;
for (var i in items)
{
counter++;
items[i].setText(items[i].getText() + " Changed Text");
items[i].setColumnText(1, "Hello " + counter);
items[i].setColumnText(2, "Hello " + counter);
}
}

function onDeleteAll(sender, event_args)
{
m_list.clear();
}

function addLogLine(s)
{
var date = new Date();

var text = m_text.getText();
text += date.toString() + " - " + s;
text += "\n";
m_text.setText(text);
}

function onExit()
{
Application.exit();
}
};




var form = new MyForm;
form.show();
Application.run();




var g_folder_bitmap;

function getFolderBitmap()
{
if (g_folder_bitmap)
return g_folder_bitmap;

g_folder_bitmap = new Bitmap('
"16 16 6 1",
" c None",
"! c #000000",
"# c #808080",
"$ c #C0C0C0",
"% c #FFFF00",
"& c #FFFFFF",
" ",
" ",
" ##### ",
" #%$%$%# ",
"#%$%$%$%###### ",
"#&&&&&&&&&&&&#! ",
"#&%$%$%$%$%$%#! ",
"#&$%$%$%$%$%$#! ",
"#&%$%$%$%$%$%#! ",
"#&$%$%$%$%$%$#! ",
"#&%$%$%$%$%$%#! ",
"#&$%$%$%$%$%$#! ",
"#&%$%$%$%$%$%#! ",
"##############! ",
" !!!!!!!!!!!!!! ",
" "');

return g_folder_bitmap;
}

[abenitez77]

I get this error when i try this below - "Compiler error (line 334): Missing close brace
Script Runtime Error: Term 'm_list.setColumnText' does not evaluate to a function."

var dbP;
dbP = new DbConnection("mssql://USATL02PRSQ70:1434/AS_PROJECTS");
var MyClients = dbP.execute("select Distinct SQLServer, SQLDBName, SQLTableName, ClientName from tblv_ProjectsDetail WHERE ToolName = 'STRATA'");
while (MyClients.next())

{
m_list.addItem(new ListViewItem(""));
m_list.setColumnText(0, MyClients.SQLServer);
m_list.setColumnText(1, MyClients.SQLDBName);
m_list.setColumnText(2, MyClients.SQLTableName);

}

[Aaron]

Hi,

Your problem is that you're calling setColumnText() on the ListView object, not on one of the columns. You need to get the row using getItem() before you can call setColumnText(): m_list.getItem(0).setColumnText(0, "New Text");

Here's a smaller example for you to work with:

Code: Select all

class MyForm extends Form
{
    var m_list;

    function MyForm()
    {
        super("ListView Test", 100, 100, 700, 520);
        setMinSize(500, 360);
       
        // create list view and add some columns       
        m_list = new ListView;
        m_list.setView(ListView.Details);
        m_list.addColumn("Test Column", 200, ListView.AlignLeft);
        m_list.addColumn("Test Column 2", 200, ListView.AlignLeft);
        m_list.addColumn("Test Column 3", 200, ListView.AlignLeft);
        this.add(m_list);
       
        // add some columns
        m_list.addItem(new ListViewItem("Item 1"));
        m_list.addItem(new ListViewItem("Item 2"));
        m_list.addItem(new ListViewItem("Item 3"));
       

        // change the column text
        m_list.getItem(0).setColumnText(1,"Added Text");
        m_list.getItem(1).setColumnText(2,"More Added Text");       


        var exit_button = new Button("Exit");
        exit_button.click.connect(this, onExit);


        // -- create button layout --
        var button_layout = new BoxLayout(Layout.Horizontal);
        button_layout.add(exit_button);
       
        // -- create main layout --
        var main_layout = new BoxLayout(Layout.Vertical);
        main_layout.addSpacer(8);
        main_layout.add(m_list, 1, Layout.Expand | Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
        main_layout.add(button_layout, 0, Layout.Expand | Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
       
        setLayout(main_layout);
    }

    function onExit()
    {
        Application.exit();
    }
};


var form = new MyForm;
form.show();
Application.run();


Best,
Aaron

[abenitez77]

Thanks! I am able to set the text of the listview for each column..but now how do I retrieve the values of the selected items from the list? I see you use "setColumnText" to set a value to a column, how do I retrieve from a specific column? I have been trying something like this...Is this correct?

var items = m_list.getSelectedItems();
var counter = 0;
for (var i in items)
{
counter++;
items[i].getSelectedItems(items[i].getItem(i).getColumnText(0));
items[i].getSelectedItems(items[i].getItem(i).getColumnText(0));
items[i].getSelectedItems(items[i].getItem(i).getColumnText(0));

}

[Aaron]

You can use one of several function: getItem(), getItems(), findItem(). Take a look at the ListView "Methods" section in the documentation: http://www.kirix.com/docs/ListView.html

[abenitez77]

That is what i tried above... getItem()... is my syntax not correct above?

[abenitez77]

I got the syntax below to display the selected items...but the list let's me select only the first column. And the code below will display only the values of the first column. I want to select a value from the first column and then retrieve the values of the 3 columns depending on the selected item in the first column for that row. How can i do this?

var items = m_list.getSelectedItems();
var counter = 0;
Console.clear();
for (var i in items)
{
counter++;

Console.writeLine(items[i].getText(items[i].getColumnText(0)));
Console.writeLine(items[i].getText(items[i].getColumnText(1)));
Console.writeLine(items[i].getText(items[i].getColumnText(2)));
//Console.writeLine(items[i].getText(items[i].getColumnText(2)));

}

[abenitez77]

Just figured it out!

var items = m_list.getSelectedItems();
var counter = 0;
Console.clear();
for (var i in items)
{
counter++;

Console.writeLine(items[i].getColumnText(0));
Console.writeLine(items[i].getColumnText(1));
Console.writeLine(items[i].getColumnText(2));

}