[RESOLVED] Loading a listbox

[gwboolean]

I want to load a listbox with data that is provided as a single string. The listbox should display a two column list with a 10character ID number as column 1 and a variable character length name as Column 2.

The string of data would appear as ";VNXXXXXXXXVendorName;VNXXXXXXXXVendorName;...;"

I suppose that I could turn the string into an array and work it like that (I have not yet tried to do that), but I thinking that it would be better if I could work the listbox with the data as it already exists.

I have been looking for a means to do this but have, so far, come up with Butkus. Can anyone provide an assist or reference for how this would be done?

[Shaggy Hiker]

First off, you probably want to find something other than a listbox. While you CAN put two columns into a listbox, it usually looks terrible to do so. A better option would be a ListView in Details mode, which handles multiple columns much nicer. Another alternative is the DataGridView. Those take a bit of tweaking to make them look like anything other than a grid, but you have lots of layout options. The DGV tends to be a little easier to use than the ListView, but can be a bit harder to make look nice, unless you want the grid appearance.

As for dealing with the string, I'd certainly be breaking it out into an array if I were doing it, unless the string comes from some place that gives me options before it ever became a string.

[jmcilhinney]

Your actual question is how to split a String based on a specific format. You can't add that String to the ListBox as it is because that would require the ListBox to know how to split it out into separate items. You could create a custom ListBox that could do that, but you'd still need to provide the implementation of that split. A ListBox requires separate items so you need to do the split one way or another. You can split the String and add the items as you get them or you can populate an array or collection and then add or bind that but you cannot avoid the split.

As suggested, a ListBox is a bad idea if you want multiple columns too. You can have a multicolumn ListBox but it doesn't work the way you want. Rather than values in the same row being related, the list simply wraps to a new column rather than scrolling vertically. You can add whitespace to a single item to make it look like multiple columns but that's a bit of a hack. Given that there are multiple controls that actually do provide multiple columns, you should use one of them rather than a ListBox.

As for how to do the split, it looks like the first step would be to call String.Split and split on the semicolons. That would produce an array where each element contained the two sub-values. You can then simply use String.Substring twice to get each of those two values. Where you put them is up to you. You could create an array of Tuple(Of String, String) or a generic List of instances of your own dedicated class or you could add the data directly to a control as you read it. There are numerous specific solutions.

[.paul.]

@jm it is possible to set tabstops in a listbox (with an API method), to give it the appearance of multiple columns

[Shaggy Hiker]

The problem I have seen with making things appear to be multiple columns, is that most fonts are not fixed width fonts, in which case the spacing of even white space is not sufficiently consistent to make the columns look good. I haven't tried it with tabs rather than simple spaces, but I wouldn't expect it to act any differently. I suppose I haven't looked for a fixed position tab stop (as opposed to just inserting tabs), but why would that even exist? This listbox is a rather simple beast.

[gwboolean]

Yes Jim, that was my actual question. Glad you noticed that. The fact is that the string can be setup anyway one wants. That just happens to be the way this one was setup. Personally, I would use a comma delimiter, but that is just a preference that I am used to.

Thanks for the suggestions. I continued doing some additional searching and also came to the same conclusion that a list box isn't very good for what I am wanting to do.

I am completely unfamiliar with String.Split (never heard of it). I just looked it up and it appears that it is exactly what I need to array out the information.

The data actually resides in a single column of a table. and can be setup with any kind of delimiter and in any fasion the user wants. The table holds chemical parts information and the string contains a list of the of the part vendors ID numbers and their names. I used this because I did not want a column for every single vendor for the part and was looking at just putting them into a single column as a string then deconstructing the string to display it on the form as a list of IDs and names.

I am very comfortable with DGVs and if you can provide me with some of the tweaking that you mentioned, I would appreciate that. I believe I need a control for this because I want the user to be able to click on a row of the displayed data to open and review the vendor record (another table). This really does sound like a job for a DGV.

[.paul.]

First off, you probably want to find something other than a listbox. While you CAN put two columns into a listbox, it usually looks terrible to do so. A better option would be a ListView in Details mode, which handles multiple columns much nicer. Another alternative is the DataGridView. Those take a bit of tweaking to make them look like anything other than a grid, but you have lots of layout options. The DGV tends to be a little easier to use than the ListView, but can be a bit harder to make look nice, unless you want the grid appearance.

As for dealing with the string, I'd certainly be breaking it out into an array if I were doing it, unless the string comes from some place that gives me options before it ever became a string.

Here's what i meant. Works with any font...

Imports System.Runtime.InteropServices

Public Class Form1

    <DllImport("user32.dll")> _
  Private Shared Function SendMessage( _
    ByVal hWnd As IntPtr, _
    ByVal wMsg As Integer, _
    ByVal wParam As IntPtr, _
    ByVal lParam As IntPtr) As Integer
    End Function

    Private Sub SetTabStops(ByVal listBox As ListBox, ByVal tabStops() As Integer)
        Const LB_SETTABSTOPS As Integer = &H192
        Dim pinnedValues As GCHandle
        pinnedValues = GCHandle.Alloc(tabStops, GCHandleType.Pinned)
        Dim ptr As IntPtr = pinnedValues.AddrOfPinnedObject()
        SendMessage(listBox.Handle, LB_SETTABSTOPS, New IntPtr(tabStops.Length), ptr)
        pinnedValues.Free()
        listBox.Refresh()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ListBox1.Items.Add("Product" & vbTab & "Description" & vbTab & "Price")
        ListBox1.Items.Add("BLT" & vbTab & "Sandwich with bacon, lettuce and tomatoes" & vbTab & "7")
        ListBox1.Items.Add("PZVES" & vbTab & "Pizza with tomato sauce, cheese and ham" & vbTab & "12")
        ListBox1.Items.Add("BRGCH" & vbTab & "Cheese Burger" & vbTab & "9")
        ListBox2.Items.AddRange(ListBox1.Items)
        SetTabStops(ListBox2, New Integer() {70, 240})
    End Sub

End Class

[jmcilhinney]

The data actually resides in a single column of a table. and can be setup with any kind of delimiter and in any fasion the user wants. The table holds chemical parts information and the string contains a list of the of the part vendors ID numbers and their names. I used this because I did not want a column for every single vendor for the part and was looking at just putting them into a single column as a string then deconstructing the string to display it on the form as a list of IDs and names.

That all sounds like a very bad idea. Firstly, you don't need multiple columns. You just need one column in another table. The whole point of a relational database is the relations. You can have a parent record with a parent ID and then you can have zero, one or many child records that are related to that parent record via that parent ID. Rather than having multiple values joined together that you then have to separate, just store them in separate records in another table. You can then query that table and filter by the parent ID if necessary and then you get the data in a form that you can simply bind directly to a DataGridView. You have actually made your life much harder by trying to avoid complexity. Do it properly in the first place and it will just work.

If you insist on storing the data that way then you cannot allow the user to pack it in an arbitrary way. If you have delimited data then your code needs to know what that delimiter is in order to be able to split the data on it. If the data contains fixed-width values then your code has to know what those widths are in order to split on the data on them. Code is not magic. If you want the user to be able to use any delimiter and/or any width then you need to store the delimiter and/or the width with the data, so that your code can read them first and then split on the values it reads.

[gwboolean]

That sounds like a very bad idea.

I am not so sure. Let's consider what is needed. I have a parts table and I have a vendors table. Any part can have a variable number of sources (vendors), or can even be self made. Additionally, any vendor can be related to a variable number of parts.

I actually considered your approach, but that just seemed to me to be too complex and I questioned whether it was an effective approach. However, after spending most of a day trying to accomplish what I believed would be a rather easy solution, and finding it not to be easy at all (I still haven't gotten my string into a DGV yet), the, "in between table" is really starting to become very attractive to me.

You cannot allow the user to pack the data in an arbitrary way.

That is true. In fact, you cannot allow the user to pack the data at all. The user merely needs to request a vendor from the vendor table and the packing of the data is done entirely within the internal process of adding a vendor and packing the code.

That turned out to actually be the easy part of this process I was attempting. Additionally, deconstructing the string was not difficult either, thanks to your previous suggestion. The only hard part, for me, was getting the deconstructed string properly bound to the control I wanted to use, in this case a DGV.

While I will indeed go the, "in between table" route you suggest, I really don't see creating a string in a single column that is deconstructed and put into DGV is a bad idea. Difficult yes. I would still like to know how to take the split data and display it with a DGV.

[gwboolean]

I caved in. I did go with your suggestion, Jim, of just creating/using the extra table. It does work well, but it truly lacks the elegance of what I wanted to do.

[jmcilhinney]

it truly lacks the elegance of what I wanted to do.

I doubt that you'll find a DBA on the planet to agree with you, nor many application developers.

[gwboolean]

I doubt that you'll find...

That is probably true. But then I am (was) an engineer, not a DBA or an application developer.

[jmcilhinney]

If your aim is efficient storage of data then your original idea would seem great. Why should that be your aim though? Do you not have much storage space available? Efficient processing of data should generally be of greater concern, as should the most readable code possible. There's little elegance in your proposed solution on either of those counts. There are always multiple ways to skin a cat but some ways are undoubtedly better than others. Some ways make better use of the tools available than others as well. Given that a relational database is optimised for relations between data, it seems logical to make use of that. We'll make an application developer of you yet.

FYI, I was almost an engineer (about 90% of a ChemEng degree) before switching to CompSci, so I can appreciate both perspectives. I actually did a half-credit subject on Fortran as part of engineering that got me interested in programming to begin with.

[gwboolean]

Why should efficient storage of data be your aim?

It is not my aim, although however much memory one has, it should be. The fact is, that accumulating a lot of unused/poorly used, redundant data can pose numerous problems with both your system, all of your associated processes, and even the accuracy of your data, while significantly reducing the efficiency of your system and its associated processes. The Oracle Enterprise system comes to mind as an excellent example of how bad this can be.

I was almost a Chemical Engineer

That is great! That was my own area of past expertise. For me, I find that programming things is an interesting hobby. Programming is nothing but a (logic) math based language (actually, all language is logic based if one studies them a little).

For me the bottom line is that programming offers a rather cheap/sessile way to do interesting projects. I cannot afford to continue being an engineer without actually working for/with someone and all of the things one would do incurs a lot of expense. Not something attractive to a retired engineer on a fixed (but generous) income.

I think I will take a pass at becoming a developer. But thanks for the offer.

[jmcilhinney]

The fact is, that accumulating a lot of unused/poorly used, redundant data can pose numerous problems with both your system, all of your associated processes, and even the accuracy of your data, while significantly reducing the efficiency of your system and its associated processes.

Sure, but that's not what is being suggested.

[gwboolean]

Sure, but that's not what is being suggested.

That may not be what is suggested, but that is almost surely the outcome, unless one is very, very, very careful about how they setup and deal with this type of data.

The fact is that this approach is a potentially data integrity nightmare. I used to work in a regulated world under the FDA and I can see all sorts of issues around Part 11 doing this. Although I can also see how to minimize the issues.

There are many considerations that must be considered beyond the ease/efficiency of handling the data.