Listbox guidance 1

[Domino2]

I am scratching around with 2 problems. THe first - I am trying to fill a listbox dynamically as a textbox is filled, the result filling a listbox with the field having matches in view and the record ID in a column with width set to zero. Can someone guide me how to do it, or point me in the right direction.

The other is how do I cycle through a filled listbox against a users texbox entry until there is a complete match or nothing found. I tried some code but it did not do anything. I added an extra line in the code which set the listindex so I could see it running through the list. The list itself has a leading hidden column with an ID number which I use to select the record from the list. The second column on view is a barcode number. I tried entering both Barcode number and record number into the textbox but it just went passed the record in the listbox each time.

I tried an alternative method to retrieve the second item in the listbox so that I could see what it was looking for in search with:

Dim PJ As String = ListBox1.SelectedItem.ToString
MsgBox(PJ)but it did not work.

Private Sub TextBox11_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox11.TextChanged
Me.ListBox1.SelectedItems.Clear()

If Not TextBox11.Text.Trim = "" Then

For i As Integer = 0 To ListBox1.Items.Count - 1
ListBox1.SelectedIndex = i
If ListBox1.Items(i).ToString.StartsWith(TextBox11.Text.Trim) Then
ListBox1.SelectedItems.Add(ListBox1.Items(i))
Exit For
End If
Next
End If
End Sub

Any help very much appreciated

 

[softhemc]

I'm not sure I totally understand your question, but I think you mean something like this:

    Private ListBoxSource As DataTable = New DataTable
    Private LastIDNumber As Integer = -1

    'This prevents ListBox1.SelectedIndex changes from popping up unnecessary MessageBoxes
    Private ListBoxJustUpdated As Boolean = False

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Add the columns to the table
        ListBoxSource.Columns.Add(New DataColumn("ID", GetType(Integer)))
        ListBoxSource.Columns.Add(New DataColumn("Barcode", GetType(String)))

        'Set the ListBox datasource
        ListBox1.DataSource = ListBoxSource
        ListBox1.ValueMember = "ID"
        ListBox1.DisplayMember = "Barcode"
        ListBox1.SelectedIndex = -1

    End Sub

    Private Sub TextBox1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyUp

        If e.KeyCode = Keys.Enter Then
            Dim ListBoxSelectedIndex As Integer = -1
            e.Handled = True
            Dim Barcode As String = TextBox1.Text.Trim
            Dim dr As DataRow
            If Barcode <> "" Then
                ListBoxSelectedIndex = ListBox1.FindStringExact(Barcode)
                If ListBoxSelectedIndex = ListBox.NoMatches Then
                    dr = ListBoxSource.NewRow
                    LastIDNumber += 1
                    dr("ID") = LastIDNumber
                    dr("Barcode") = Barcode
                    ListBoxSource.Rows.Add(dr)
                    ListBoxSelectedIndex = ListBox1.FindStringExact(Barcode)
                End If
            End If
            ListBoxJustUpdated = True
            ListBox1.SelectedIndex = ListBoxSelectedIndex
            TextBox1.Clear()
        End If

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        If Not ListBoxJustUpdated Then
            If ListBox1.SelectedIndex <> -1 Then
                Dim dr As DataRow = ListBoxSource.Rows(ListBox1.SelectedIndex)
                MessageBox.Show("ID = " & dr("ID").ToString & ", Barcode = " & dr("Barcode"))
            End If
        Else
            ListBoxJustUpdated = False
        End If

    End Sub

 

[Domino2]

Many thanks. I have tried this part but the listbox does not fill?

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class Form1
Private ListBoxSource As DataTable = New DataTable
Private LastIDNumber As Integer = -1
Private ListBoxJustUpdated As Boolean = False

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=LOGGING;User ID=root;Password=mynewpassword;")

Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM TXMasters", con)

Dim ds As DataSet = New DataSet()
Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
DataAdapter1.SelectCommand = sql
DataAdapter1.Fill(ds, "TXMasters")


'Add the columns to the table
ListBoxSource.Columns.Add(New DataColumn("ID", GetType(Integer)))
ListBoxSource.Columns.Add(New DataColumn("Barcode", GetType(String)))

'Set the ListBox datasource
ListBox1.DataSource = ListBoxSource
ListBox1.ValueMember = "ID"
ListBox1.DisplayMember = "Barcode"
ListBox1.SelectedIndex = -1

End Sub

End Class

 

[SqueakinSweep]

You dont need the ListSource Table, you already have the data you need in TXMasters

ListBox1.DataSource = ds.Tables("TXMasters")
ListBox1.ValueMember = "ID"
ListBox1.DisplayMember = "Barcode"
ListBox1.SelectedIndex = -1



Sweep
...if it works, you know the rest..
Always remember that Google is your friend

 

[softhemc]

In my example I created the table in code as I don't have your database and included some code to demonstrate the process working.

To adjust for your requirements, you can remove all the Private declarations, you can also remove the two Columns.Add lines.

However as you've written your code, I think the DataSet will go out of scope after the Form Load Event, so I would add a couple of Private declarations, i.e:

[tt]
Private TXMastersDataset As DataSet
Private TXMastersDataAdapter As MySqlDataAdapter
[/tt]

Loading the data would then be:

[tt]
Dim con ... unchanged
Dim sql ... unchanged

TXMastersDataset = New DataSet()
TXMastersDataAdapter = New MySqlDataAdapter()
TXMastersDataAdapter .SelectCommand = sql
TXMastersDataAdapter .Fill(ds, "TXMasters")
[/tt]

Adjust the lines which set up the ListBox to:
[tt]
ListBox1.DataSource = ListBoxSource
ListBox1.ValueMember = "The name of your ID column (the hidden column)"
ListBox1.DisplayMember = "The name of the column you want to display"
ListBox1.SelectedIndex = -1
[/tt]


Also you would need to adjust the TextBox KeyUp event's code:

Remove this line: ListBoxJustUpdated = True
Change this line: dr = ListBoxSource.NewRow to reflect the actual table
Change the column names in dr("ID") and dr("Barcode") to reflect your actual column names.


I can't provide sample code because I don't have a suitable database, but I think this covers all the changes.

 

[Domino2]

Many thanks for your help. I have gone as far as trying to fill the listbox but get an exception:

Cannot bind to the new display member. Parameter name: newDisplayMember It halts on the line with this message:

ListBox1.ValueMember = "ID"


Code modified so far is:

Imports MySql.Data.MySqlClient
Imports System.Data
Public Class Form1
Private ListBoxSource As DataTable = New DataTable
Private TXMastersDataset As DataSet
Private TXMastersDataAdaptor As MySqlDataAdapter

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=LOGGING;User ID=root;Password=mynewpassword;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM TXMasters", con)
Dim Slug As Boolean
Dim ds As DataSet = New DataSet()

TXMastersDataset = New DataSet()
TXMastersDataAdaptor = New MySqlDataAdapter()
TXMastersDataAdaptor.SelectCommand = sql
TXMastersDataAdaptor.Fill(ds, "TXMasters")

Slug = True
'Set the ListBox datasource
ListBox1.DataSource = ListBoxSource
ListBox1.ValueMember = "ID"
ListBox1.DisplayMember = "Barcode"
ListBox1.SelectedIndex = -1
Slug = False


End Sub


Thanks again

 

[softhemc]

You don't need these lines:

[tt]
Private ListBoxSource As DataTable = New DataTable
Dim ds As DataSet = New DataSet()
[/tt]
Change this line:
[tt]TXMastersDataAdaptor.Fill(ds, "TXMasters")[/tt]
to:
[tt]TXMastersDataAdaptor.Fill(TXMastersDataset, "TXMasters")[/tt]

Change this line:
[tt]Box1.DataSource = ListBoxSource[/tt]
to:
[tt]Box1.DataSource = TXMastersDataset.Tables("TXMasters")[/tt]


and also make sure that:

ListBox1.ValueMember and ListBox1.DisplayMember are actual column names from your table.

 

[softhemc]

In these:

Box1.DataSource = ListBoxSource

Box1.DataSource = TXMastersDataset.Tables("TXMasters")

Box1 should be ListBox1

 

[Domino2]

Thanks the list now fills. I just added another part, and get an exception:

There is no row at position 0.

Code breaks on - Dim dr As DataRow = ListBoxSource.Rows(ListBox1.SelectedIndex)

Code was:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

If Not ListBoxJustUpdated Then
If ListBox1.SelectedIndex <> -1 Then
Dim dr As DataRow = ListBoxSource.Rows(ListBox1.SelectedIndex)
MessageBox.Show("ID = " & dr("ID").ToString & ", Barcode = " & dr("Barcode"))
End If

end sub

I added two declarations as ListboxSource was marked as well as ListBoxJustUpdated.

Full code:
Imports MySql.Data.MySqlClient
Imports System.Data

Public Class Form1
Private TXMastersDataset As DataSet
Private TXMastersDataAdaptor As MySqlDataAdapter
Private ListBoxJustUpdated As Boolean = False
Private Slug As Boolean
Private ListBoxSource As DataTable = New DataTable
Private LastIDNumber As Integer = -1

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=LOGGING;User ID=root;Password=mynewpassword;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM TXMasters", con)


TXMastersDataset = New DataSet()
TXMastersDataAdaptor = New MySqlDataAdapter()
TXMastersDataAdaptor.SelectCommand = sql
TXMastersDataAdaptor.Fill(TXMastersDataset, "TXMasters")

Slug = True
'Set the ListBox datasource
ListBox1.DataSource = TXMastersDataset.Tables("TXMasters")
ListBox1.ValueMember = "ID"
ListBox1.DisplayMember = "Barcode"
ListBox1.SelectedIndex = -1
Slug = False
End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

If Not ListBoxJustUpdated Then
If ListBox1.SelectedIndex <> -1 Then
Dim dr As DataRow = ListBoxSource.Rows(ListBox1.SelectedIndex)
MessageBox.Show("ID = " & dr("ID").ToString & ", Barcode = " & dr("Barcode"))
End If
Else
ListBoxJustUpdated = False
End If

End Sub

Else
ListBoxJustUpdated = False
End If

End Sub


Regards

 

[softhemc]

Get rid of the
Private ListBoxSource declaration

you are not using it anymore, this was just for the test table that I originally created.

Everywhere else that you are using ListBoxSource replace it with the name of the actual table that you are using ... this is TXMastersDataset.Tables("TXMasters")

For example, change:

Dim dr As DataRow = ListBoxSource.Rows(ListBox1.SelectedIndex)

to

Dim dr As DataRow = TXMastersDataset.Tables("TXMasters").Rows(ListBox1.SelectedIndex)

and follow that principle eveywhere that you are currently using ListBoxSource

 

[Domino2]

Many thanks progressing that through. It all works now and I can get my ID and Barcode numbers out. My next move is to autofill a listbox as a user types in a Barcode, but thats another project which hopefully being able to split the two items in the listbox is a clearer challenge. This is all a real culture shock from Access and VB6. Regards