Navigate records via ListBox

Discussion in 'Scripting & Programming' started by ThomasMc, Sep 5, 2007.

  1. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Afternoon all, Got a little problem thats messing with my head .

    Form w/ 1x ListBox and 1x TextBox

    When the form loads, it fills the textbox with the TableAdapter.Fill method
    Code:
      
    [COLOR="Blue"]Me[/COLOR].TblMainTableAdapter.Fill([COLOR="blue"]Me[/COLOR].Contact_ManagerDataSet.tblMain)
    
    and my listbox is populated with this code
    Code:
            [COLOR="Blue"]Try[/COLOR]
                [COLOR="seagreen"]'Connect the db[/COLOR]
                [COLOR="blue"]Dim[/COLOR] conn [COLOR="blue"]As[/COLOR] Common.DbConnection = [COLOR="blue"]New[/COLOR] SqlClient.SqlConnection
                conn.ConnectionString = _
                    [COLOR="red"]"Server=SERVER;Database=DATABASE;Trusted_Connection=true;"[/COLOR]
                conn.Open()
                [COLOR="seagreen"]'Get the data[/COLOR]
                [COLOR="blue"]Dim[/COLOR] cmd [COLOR="blue"]As New[/COLOR] SqlClient.SqlCommand
                cmd.Connection = [COLOR="blue"]CType[/COLOR](conn, SqlClient.SqlConnection)
                cmd.CommandText = [COLOR="Red"]"SELECT * FROM tblMain"[/COLOR]
                cmd.CommandType = CommandType.Text
                [COLOR="blue"]Dim [/COLOR]dr [COLOR="blue"]As[/COLOR] Common.DbDataReader = cmd.ExecuteReader
                [COLOR="seagreen"]'Do some work with the data[/COLOR]
                [COLOR="blue"]While[/COLOR] (dr.Read())
                    ListBox1.Items.Add([COLOR="blue"]CStr[/COLOR](dr.Item(1)) & " " & [COLOR="blue"]CStr[/COLOR](dr.Item(2)))
                [COLOR="blue"]End While[/COLOR]
            [COLOR="blue"]Catch[/COLOR] ex [COLOR="blue"]As[/COLOR] SqlClient.SqlException
                MsgBox(ex.Message)
                [COLOR="blue"]Exit Sub[/COLOR]
            [COLOR="Blue"]End Try[/COLOR]
    
    Now what i want to do is when i click on the listbox value i want the textbox to be updated with data from that row, I think i should be using a select case but for the life of me i cant get it to work, anyone care to share there wisdom with me :D
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  2. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    What you need to do is go into the sql builder for the table adapter and set it with a parameter on the field you want to filter it by.

    Then, when you are populating the textbox content, you need to use:

    Code:
    'Clear the Datasets current data
    Me.Contact_ManagerDataSet.tblMain.clear
    
    'apply filter
    Me.TblMainTableAdapter.Selectcommand.parameters("@myparam1").Value = listbox1.selectedvalue
    
    'fill dataset from adapter
    Me.TblMainTableAdapter.Fill(Me.Contact_ManagerDataSet.tblMain)
    
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  3. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    this maybe a silly question so don't laugh big yin, but would the filter work ok considering the listbox is 2 columns that are concatenated(My PK would be on the dr.Item(0)) to display the full name eg. dr.Item(1) & " " & dr.Item(2) would be something like Thomas McConnell
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  4. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    no i wouldnt expect it to be an issue.... i think.

    im finding it a little hard to visualise it in my head though (im still learning these things myself). give it a shot and see what error message (if any) you get, then post back with the details.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  5. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Did i miss an import Fergal, i get Error "SelectCommand is not a member of myTableAdapter"

    pic below
     

    Attached Files:

    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  6. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    no you shouldnt need an import for it to work.

    Im not sure why that wouldnt work. Although the second error would be because you havent set up the parameter yet.

    i think it might be because you are using a tableadapter, and what i stated was for a dataadapter. give me a few mins to look this over.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  7. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Hey Fergal i was having a little mess about and this seems to produce a result i added the PK to the ListBox so it now looks something like

    1 Thomas McConnell

    and used this
    Code:
            [COLOR="blue"]Dim[/COLOR] myparm [COLOR="blue"]As String[/COLOR]
            [COLOR="Olive"]'Clear the Datasets current data[/COLOR]        
            [COLOR="blue"]Me[/COLOR].Contact_ManagerDataSet.tblMain.Clear()
            MessageBox.Show([COLOR="blue"]CStr[/COLOR](ListBox1.SelectedItem))
    
            myparm = [COLOR="blue"]CChar[/COLOR](ListBox1.SelectedItem.ToString())
    
           [COLOR="olive"] 'fill dataset from adapter[/COLOR]
            [COLOR="blue"]Me[/COLOR].TblMainTableAdapter.Fill([COLOR="blue"]Me[/COLOR].Contact_ManagerDataSet.tblMain, [COLOR="blue"]CInt[/COLOR](myparm))
    
    Edit: just thought the above code through a little more and its no good
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  8. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    ok. i 'think' you need to be using the bindingsource filter method. On your form you should have something like tblMainBindingSource or the like. so for the above code, substitute this for the selectcommand line:

    Code:
    tblmainbindingsource.filter = " <fieldtofilter> = " & listbox1.selectedvalue
    
    Remember to substitute <fieldtofilter> with the column in the table you are wanting to filter by.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  9. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    looks like we are on the right path with the bindingsource.filter only problem I'm having now is that there is no Value only Items and can prove this by

    Code:
    MessageBox.Show(CStr(ListBox1.SelectedValue))
    
    it just shows a blank box

    when i try

    Code:
    Me.TblMainBindingSource.Filter = " firstName = " & CStr(ListBox1.SelectedItem)
    
    i get a syntax error: Missing operand after 'McConnell' operator
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  10. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    you need to encase the value in apostrophes. try this:

    Code:
    Me.TblMainBindingSource.Filter = " firstName = '" & CStr(ListBox1.SelectedItem) & "'"
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  11. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Hi, sorry about this Fergal, that took away the error but no data is being returned i tryed LIKE instead of = and still got nothing back
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  12. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    check what the value is compared to what is in the field in question.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  13. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    i tryed this query on the sql server itself and got no results, seems its not liking the space and surname

    Code:
    SELECT     ID, firstName, surName, phoneNumber, ext, mobileNumber, emailAddress
    FROM         tblMain
    WHERE     (firstName LIKE '&#37;Thomas McConnell%')
    
    returns Nothing but

    Code:
    SELECT     ID, firstName, surName, phoneNumber, ext, mobileNumber, emailAddress
    FROM         tblMain
    WHERE     (firstName LIKE '%Thoma%')
    
    returns the correct record, seems my concatenated value(firstName & " " & surName) is scewing the filter up
     

    Attached Files:

    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  14. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    yeah that would be screwing it up. the filter, in the example you are using is looking for any field where the FIRSTNAME is equal to the selected value of the listbox (ie 'Thomas McConnell') because the Firstname field is only 'Thomas' its a non-match.

    You will need to do some manipulation of the data from the listbox before filtering it. something like:

    Code:
    Dim myName() As String = ListBox1.SelectedItem.ToString.Split(" ")
    Dim myFirstname As String = myName(0)
    
    You can then use myFirstname in the filter.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  15. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Nice one bud here you deserve this :morebeer, kudos added
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  16. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    I was having a little read at my VS 2005:Language book and managed to come up with this, it will return a value for each item in a Listbox(or even a combo) via the ToString Override. I have added a couple of loops on the for so you can see how the value can be dynamically assigned so it would be perfect to put a PK in
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  17. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Forgot to add the file :soz
     

    Attached Files:

    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  18. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    172
    211
    That looks good, but...

    You should consider the scope of your variables. For instance, Counter and Value dont need to be global variables. Define them in the load event, since you arent using them outside that function.

    Similarly, i wouldnt define i as global, I would actually define it as part of the for loop:

    Code:
    For I as Integer = 0 to 22
    
    Next
    
    Defining your variables appropriately will allow you to handle memory, etc a lot better. Theres no point holding a variable in memory if its not needed.

    I also wouldnt bother using the With method in the example you provided. Its only useful if you have multiple actions to take with an object. If you are only performing one action with it, you dont need to bother with it. It just clutters up the code a little bit more.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  19. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    duly noted :D , updated file
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  20. drum_dude

    drum_dude Gigabyte Poster

    1,664
    92
    135
    Crikey Fergal, I need to take a leaf out of your book and start expanding my knowledge! That's some impressive posts there mate!
     
    Certifications: MCP, MCSA 2000 , N+, A+ ,ITIL V2, MCTS, MCITP Lync 2010 & MCSA 2008, Sonus SATP SBC 1k/2k
    WIP: Hopefully Skype for Business and some Exchange stuff...

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.