Search dataset and populate list box with results

[buratti]

Ok so first off I am completely new to VB.net. I do however have a slight background with VBA particularly with MS Access, so I am not completely lost. I am recreating my Access database application in VB.net This is my first post here, and I am sure there will be many more to come, but lets get to my particular question... (Oh yeah, BTW I am using Visual Basic 2008 Express Edition)

I created a new project and added an Access database through the wizard, which automatically created a dataset for the database. On one of my forms I have, esentially, a search form. What I want to happen is have a text box, and as I start typing display a search result from my customers table in the list box under it. So for example, I type "A" and all customer names that start with "A" are displayed in the list box. I continue typing with a "p" (full string is now "Ap"), then all customer names that start with "Ap" are displayed and so on...

I did search on my own first before posting this and found a few tips but cant really get it to work the way I want. This is what I have... I know I would put this code in the keypress event of my test box, but just until I get it working I attached it to a button to search. (I basically got this online and not even sure its the correct usage.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'Create a dataview
        Dim dv As New DataView

        'Associate the dataview to dataset(Dataset table)
        dv.Table = RGB_Tools_back_endDataSet.Tables("Customers")

        Dim drv As DataRowView 'Data Row View object to query DataView object

        'Filter based on a text box value selected
        dv.RowFilter = "[Last Name] = " & textBox1.text
        'Retrieve my values returned in the result
        For Each drv In dv
            ListBox1.Items.Add(drv("Last Name"))
        Next

The item in red is where i get an error. it sucessfully builds and i open the find form enter a last name that I know is in the customers table, but I get the error "Cannot find Column ["whatever name i typed inthe text box"]

So first of all is this the correct way of going about what I want to achieve? If so how can I get it to work? If not what other ways can someone suggest for me. BTW the next step for the user would be to double click the customer in the list box they are searching for to open the customer Details form for that customer, so I would need an ID or Index attached to that item to be able to open that record in another form.

[.paul.]

try this. string values must be surrounded by '

dv.RowFilter = "[Last Name] = '" & textBox1.text & "'"

edit: what i should've said is - string values in sql queries + filters must be surrounded by '

[buratti]

Wow, Quick response. Thanks that did it. I moved the code to the KeyUp event of the textbox and I got the listbox populating correctly. My problem now is that in the end of my original post I stated that I want the user to double click an item to open another form and populate that form with the selected customer. I need rather the Customer ID from the same table or the Index of the record to be "attached" to the list item.

In MS Access I would create a multi column listbox where column 1 would be the customer ID at a width of 0" and column 2 be the name, column 3 be address, etc. and the value of the selected item in the listbox would always be the Customer ID (or first column). I realized that in VB.net you cannot (at least not easily) have a multi column listbox, so this technique would not work for me. What would you suggest?

[.paul.]

instead of looping through the dv to populate your listbox, bind the dv to the listbox:

'Create a dataview
Dim dv As New DataView
 
'Associate the dataview to dataset(Dataset table)
dv.Table = RGB_Tools_back_endDataSet.Tables("Customers")
 
'Filter based on a text box value selected
dv.RowFilter = "[Last Name] = " & textBox1.text
 
ListBox1.displaymember = "Last Name"
ListBox1.valuemember = "id"
ListBox1.datasource = dv

then when you doubleclick an item, you can cast the selectedItem to a DataRowView, or you can use the selectedValue property which is your id field

[buratti]

Ok that works like a charm, but brings up a new problem. I guess I should have been more clear in my first post, but I would actually like to have more than 1 column in the listbox (if possible). Well actually I would want 4 columns, not including the hidden ID column. I would want as follows: Account Number, Last Name, First Name, and Address. Because when searching, If I have more than 1 customer with the same last name I dont know which one I should select. What about using a ListView instead??? I have never used them and only got the idea when searching on how to add columns to ListBoxes. Can this accomplish the same thing but with more columns?

[.paul.]

listviews can have multiple columns in details view + it would be the ideal control, but it doesn't support databinding.

use a datagridview + hide the id column instead

[buratti]

i did try the datagrid and it worked but I wasn't so happy with the appearance of it. I would need to hide all but 4 columns and if possible the record selectors on the left side. Also would it be possible to have the whole row highlighted when clicked anywhere in that row as opposed to just that field highlighted? So in short how do I hide fields and adjust the appearance of the datagrid in general?

[.paul.]

to hide the first column (index = 0):

DataGridView1.Columns(0).Visible = False

to remove the row headers:

in properties...

RowHeadersVisible = False

to highlight the full row:

in properties...

SelectionMode = FullRowSelect

[buratti]

Ok I worked with the visual appearance of it and I think I may be able to deal with it. Now I just need to know what procedure it takes to get the value of the "ID" field on double click of a row...

Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
dim temp as integer
temp = DataGridView1.????????
    End Sub

[.paul.]

the column still exists, even though it's invisible:

temp = cint(DataGridView1(columnIndex, rowIndex).Value)

[.paul.]

if you use the DataGridView1_CellDoubleClick event, it has a DataGridViewCellEventArgs parameter which contains e.rowIndex (the index of the current row)

[buratti]

Sorry for the delay in this response but one more question and I should have it...
DataGridView1(columnIndex, rowIndex).Value)... How do I get the selected row index? I understand that the Column Index would always be 0 since I need the ID value, but each time a different search is proformed, a different row may need to be selected, so I cannot hardcode a row value in it.

[buratti]

sorry I posted that last one before I saw your other post explaining how to get the current row index. Thanks for all your help onthis issue. I'm sure I'll be back soon with a new post about something different.