Hi all.

SLX LAN v7.2.1

We have a listbox (myListbox) that contains item numbers and it varies for each ACCOUNT. So one account may have 5 items in myListBox and another customer may have 6 items. I want to be able display the itemnumbers and their description in a datagrid. I have a table (myItems) that has the item number and the description.

So when the end user clicks a button it will populate the datagrid with the itemnumber (retreived from the myListBox) and descriptions (retrieved from myItems table). Some items will not appear in the myItems table so for that case then the datagrid should only display the item number and the description will be blank.

Any idea how I can read the data in the list box? I already know how to dynamically create a datagrid using a sql statement (I used Ryan's article on here) but I don't know how to do it with a listbox.

Thank you in advance!

' To get a Count of Items on a Listbox:

Dim iCount

Dim i

iCount = listBox.Items.Count

' And to get the Items (this code will display a msgbox for each item it find on the List Box

  i = 0

Do While i < iCount

  msgbox listBox.Items.Item(i)

  i = i + 1

Loop

You may need to review my syntax as I just wrote the sample directly into this reply!

Thanks for the quick reply Raul. How do I populate the data in the listbox into my datagrid with the description from the table myItems?

This is how I would do it:

a) Create the Grid based on your myItems table (maybe add a condition so that it displays no data).

b) Upon load of your view, build an SQL Statement based on the SQL for the Grid (you could read the one that the view created - e.g. msgbox grid.SQL.Text)

c) Apply your SQL statement to the grid (since yours would have the necessary WHERE clause) and refresh it

e.g.

  strSQL = grid1.SQL.Text

  strSQL = Replace(strSQL, "myItemsID = "xxxx", "")  'Sample of how I would remove my grid condition to keep it blank until I load the correct data

  strSQL = strSQL & " WHERE ITEMNBRS IN ('123', '234', '3343', '334') "  ' These would be the IDs just retreived from the List Box

  myGrid.SQL.Text = strSQL   'Replace the SQL Statement on the Grid with the one I created

  myGrid.Refresh    ' Refresh the grid, it should now show myItems data based on the Where clause I added

My dilema is in the retreiving of the items from the list box so would I do something like this?

strSQL = grid1.SQL.Text

iCount = listBox1.Items.Count

 j = 0

  strSQL = Replace(strSQL, "myItemsID = "xxxx", "")  'Sample of how I would remove my grid condition to keep it blank until I load the correct data

Do While j < iCount

  strSQL = strSQL & " WHERE ITEMNBRS IN (listBox1.Items.Item(j)) "  ' These would be the IDs just retreived from the List Box

  myGrid.SQL.Text = strSQL   'Replace the SQL Statement on the Grid with the one I created

j = j + 1

Loop

myGrid.Refresh

Here's what I'm working on.

Dim strSQL
Dim col
Dim i
Dim iCount
Dim j
 
iCount = listBox1.Items.Count
With Datagrid1

  j = 0
      Do While j < iCount
strSQL = "SELECT     ITEMNUMBER, 'CASE' AS [CASE],ITEMDESCRIPTION, UNITSPERCASE, UNITSPERPACK FROM    sysdba.C_ITEMS WHERE     (ITEMNUMBER = '" & listBox1.Items.Item(j) & "')"
.SQL.Text = strSQL
        With .Columns
            If (.Count > 0) Then
                For i = 0 To .Count - 1
                    .Item(0).Delete
                Next
            End If

 

             Set col = .Add(0)

  col.FieldName="ItemNumber"
             col.Caption = "Item Number"
             col.Readonly = True
             col.Width = 90
             col.Alignment = 2
             col.Alignment = 2
             col.HeaderAlignment = 2

             Set col = .Add(0)
             col.FieldName="CASE"
             col.Caption = "UOM"
             col.Readonly = True
             col.Width = 90
             col.Alignment = 2
             col.Alignment = 2
             col.HeaderAlignment = 2

             Set col = .Add(0)
             col.FieldName="ITEMDESCRIPTION"
             col.Caption = "Description"
             col.Readonly = True
             col.Width = 200
             col.Alignment = 2
             col.Alignment = 2
             col.HeaderAlignment = 2

        End With

j = j + 1

        Loop

.Refresh

     End With

You should first loop and get all items from the list box, then build an SQL statement.

On the SQL statement Where clause, rather than using "=" us "IN" and provide a list of one or more items to retreive.

Since the layout of the grid is based on the myItems table, you should be able to build the layout using the Architect, thus avoiding having to create the columns on your own

(this is why I showed you how to retreive the base SQL statement generated by the Grid, and why I mention to putting a condition on the grid to filter out all records by default)

That said, seems like you are pretty close to doing what you need to via code.

Ok. So first I would loop and get all the items from the listbox. Where do I put them until I get to the Where clause in the SQL statement?