PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Need to Synchronize two combos...-VBForums
Results 1 to 21 of 21

Thread: Need to Synchronize two combos...

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Need to Synchronize two combos...

    I have a from called frmMainMenu. On it there is a combo called cmbCurrentECabinet and cmbEFolder. Now by pieceing together a few things I found I have gotten this far.

    Private Sub frmMainMenu_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    filleCabinets()
    End Sub

    Sub filleCabinets()
    Try
    Dim CN As New SqlConnection(cs)
    CN.Open()
    adp = New SqlDataAdapter()
    adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ArchiveName) FROM Archive", CN)
    ds = New DataSet("ds")
    adp.Fill(ds)
    dtable = ds.Tables(0)
    cmbCurrentECabinet.Items.Clear()
    For Each drow As DataRow In dtable.Rows
    cmbCurrentECabinet.Items.Add(drow(0).ToString())
    Next

    Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
    End Sub

    Now this works and fills the cmbCurrentECabinet. Now where I am getting confused is trying to link it to cmbeFolder. Here is the query that needs to be in play:

    SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle
    FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey

    cmEfolder Needs to show the MainTitle.MainLevelTitle field. I tried this but recieved an error:

    Try
    Dim CN As New SqlConnection(cs)
    CN.Open()
    adp = New SqlDataAdapter()
    adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey where ArchiveKey='" + cmbCurrentECabinet.SelectedItem.ToString() + "'"
    con.Open()
    adp.Fill(ds)
    dtable = ds.Tables(0)
    cmbCurrentECabinet.Items.Clear()
    For Each drow As DataRow In dtable.Rows
    cmbeFolder.Items.Add(drow(0).ToString())
    Next
    Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

    Tried Running it and now I have the ArchiveKey dispalyed in the cmdCurrentECabinet instead of the archivename.

    So Confused,

    THank you

    Art Lorenzini
    Sioux Falls, SD

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    Please use code formatting tags for readability:
    Quote Originally Posted by alorenzini View Post
    I have a from called frmMainMenu. On it there is a combo called cmbCurrentECabinet and cmbEFolder. Now by pieceing together a few things I found I have gotten this far.

    vb.net Code:
    1. Private Sub frmMainMenu_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    2.         filleCabinets()
    3.     End Sub
    4.  
    5.  Sub filleCabinets()
    6.         Try
    7.             Dim CN As New SqlConnection(cs)
    8.             CN.Open()
    9.             adp = New SqlDataAdapter()
    10.             adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ArchiveName) FROM Archive", CN)
    11.             ds = New DataSet("ds")
    12.             adp.Fill(ds)
    13.             dtable = ds.Tables(0)
    14.             cmbCurrentECabinet.Items.Clear()
    15.             For Each drow As DataRow In dtable.Rows
    16.                 cmbCurrentECabinet.Items.Add(drow(0).ToString())
    17.             Next
    18.  
    19.         Catch ex As Exception
    20.             MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    21.         End Try
    22.     End Sub
    Now this works and fills the cmbCurrentECabinet. Now where I am getting confused is trying to link it to cmbeFolder. Here is the query that needs to be in play:

    Code:
    SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle
    FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey
    cmEfolder Needs to show the MainTitle.MainLevelTitle field. I tried this but recieved an error:

    vb.net Code:
    1. Try
    2.             Dim CN As New SqlConnection(cs)
    3.             CN.Open()
    4.             adp = New SqlDataAdapter()
    5.             adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" + cmbCurrentECabinet.SelectedItem.ToString() + "'"
    6.             con.Open()
    7.             adp.Fill(ds)
    8.             dtable = ds.Tables(0)
    9.             cmbCurrentECabinet.Items.Clear()
    10.             For Each drow As DataRow In dtable.Rows
    11.                 cmbeFolder.Items.Add(drow(0).ToString())
    12.             Next
    13.         Catch ex As Exception
    14.             MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    15.         End Try
    Tried Running it and now I have the ArchiveKey dispalyed in the cmdCurrentECabinet instead of the archivename.

    So Confused,

    THank you

    Art Lorenzini
    Sioux Falls, SD

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    Thank you. I will do better in the future.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,732

    Re: Need to Synchronize two combos...

    For what it's worth... there's a better way to get a list of data into a dropdown... especially if you have it in a datatable:
    Code:
        Sub filleCabinets()
            Try
                Dim CN As New SqlConnection(cs)
                CN.Open()
                adp = New SqlDataAdapter()
                adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ArchiveName) As ArchiveName FROM Archive", CN)
                dtable = new DataTable
                adp.Fill(dtable )
                cmbCurrentECabinet.DisplayMember = "ArchiveName"
                cmbCurrentECabient.ValueMember = "ArchiveName"
                cmbCurrentECabinet.DataSource = dtable 
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    Boom...No looping.

    Code:
    Try
                Dim CN As New SqlConnection(cs)
                CN.Open()
                adp = New SqlDataAdapter()
                adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" + cmbCurrentECabinet.Value + "'" '''<--- note the change here too!
                con.Open()
                dtable = new DataTable
                adp.Fill(dtable)
                cmbeFolder.DisplayMember = "MainLevelTitle"
                cmbeFolder.ValueMember = "MainTitleKey"
                cmbeFolder.DataSource = dtable
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    MEanwhile you had some issues in the code... first in loading the second combo, you completely cleared out the first combo... which could be an issue. Also on the second combo you were loading the first column of your results which was always your ArchiveKey.
    By using binding the way I've shown, you display one thing (ArchiveName) while using something else as the value (ArchiveKey)... same with the second combo box... it displays the MainLevelTitle, but will return the MainTitleKey as the .Value when requested. FYI - having ArchiveName and ArchiveKey are extraneous info and isn't necessary. IT isn't going to hurt either.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    It says "Value" is not a member of 'System.Windows.forms.Comboboxes'. when I entered the follwoing code:

    Code:
     adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" + cmbCurrentECabinet.Value + "'"
    '''<--- note the change here too

    I uses intellisense to look at the members and did not see one that made sense for what you are trying to do even though I understand it.

    Thanks,

    Art Lorenzini
    SD

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,732

    Re: Need to Synchronize two combos...

    Try .SelectedValue ... I was shooting from the hip with no IDE when I wrote that.,...(OR maybe it is .SelectedItem.... it's been a long day)_.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    So I tried both SelectedItem and SelectedValue and neither work. The cmbCurrenteCabinet combo loads fine. But it is generating the following errror:

    Operator '+' is not defined for string "SELECT Archive.ArchiveKey, Archi" and type 'DataRowView".

    al

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    Tried change th eoperator to a & and that did work either... It errored with

    Operator '&' is not defined for string "SELECT Archive.ArchiveKey, Archi" and type 'DataRowView".

    -al

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    In this case, Text or SelectedValue will work because the DisplayMember and ValueMember refer to the same column. Text is type String so it will work as is:
    vb.net Code:
    1. adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" & cmbCurrentECabinet.Text & "'"
    SelectedValue is type Object so it would require a cast:
    vb.net Code:
    1. adp.SelectCommand = "SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" & CStr(cmbCurrentECabinet.SelectedValue) & "'"
    You could use String.Format or string interpolation to clean that up a bit but it would be better to use a parameter. You can follow the Blog link in my signature below and check out my post on Parameters In ADO.NET to learn why and how.

  10. #10

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    I am not sure if it makes a difference but it should be coded as:
    Code:
     cmbCurrentECabinet.DisplayMember = "ArchiveName"
     cmbCurrentECabient.ValueMember = "ArchiveKey"
    Although they are both text fields.

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    Quote Originally Posted by alorenzini View Post
    I am not sure if it makes a difference but it should be coded as:
    Code:
     cmbCurrentECabinet.DisplayMember = "ArchiveName"
     cmbCurrentECabient.ValueMember = "ArchiveKey"
    Although they are both text fields.
    In that case, the Text property will return the value from the ArchiveName column of the selected record and the SelectedValue will return the value from the ArchiveKey column. Presumably you want the latter.

  12. #12

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    YEs I need the latter. I implemented your advice into the following code:

    Code:
    Private Sub cmbCurrentECabinet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbCurrentECabinet.SelectedIndexChanged
            Try
                Dim CN As New SqlConnection(cs)
                CN.Open()
                adp = New SqlDataAdapter("SELECT ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" & cmbCurrentECabinet.ValueMember & "'", CN)
                con.Open()
                dtable = New DataTable
                adp.Fill(dtable)
                cmbCurrentEFolder.DisplayMember = "MainLevelTitle"
                cmbCurrentEFolder.ValueMember = "ArchiveKey"
                cmbCurrentEFolder.DataSource = dtable
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    But it throws a ArchiveKey is a Ambigous column which I get because of the where statement WHERE Archivekey = so I tried MainTitle.ArchiveKey and Archive.ArchiveKey which cleared the error but brought back nothing when I ran it. Seems like I am going in circles...

    Thanks

    -adl

  13. #13

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    If I run this Query on my SQL Server It works:

    SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey where MainTitle.ArchiveKey='122909154635admindbaCAB'

    '122909154635admindbaCAB' is what the contents of ArchiveKey should be.

    Thanks
    -adl

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,732

    Re: Need to Synchronize two combos...

    Quote Originally Posted by alorenzini View Post
    YEs I need the latter. I implemented your advice into the following code:

    Code:
    Private Sub cmbCurrentECabinet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbCurrentECabinet.SelectedIndexChanged
            Try
                Dim CN As New SqlConnection(cs)
                CN.Open()
                adp = New SqlDataAdapter("SELECT ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" & cmbCurrentECabinet.ValueMember & "'", CN)
                con.Open()
                dtable = New DataTable
                adp.Fill(dtable)
                cmbCurrentEFolder.DisplayMember = "MainLevelTitle"
                cmbCurrentEFolder.ValueMember = "ArchiveKey"
                cmbCurrentEFolder.DataSource = dtable
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    But it throws a ArchiveKey is a Ambigous column which I get because of the where statement WHERE Archivekey = so I tried MainTitle.ArchiveKey and Archive.ArchiveKey which cleared the error but brought back nothing when I ran it. Seems like I am going in circles...

    Thanks

    -adl
    It's ambiguous because you didn't specify which table to get the ArchiveKey from .... you need to specify the table... both in the select and where clause...
    Code:
    SELECT ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FR
    But I see that in your next post you have that fixed:

    If I run this Query on my SQL Server It works:

    SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey where MainTitle.ArchiveKey='122909154635admindbaCAB'

    '122909154635admindbaCAB' is what the contents of ArchiveKey should be.
    So.... ??? what;s the problem now?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    RThe issue is it not populating the cmbeCurrenteFolder combo. I think its because I need qoutes arount the ArchiveKey. Like MainTitle.ArchiveKey='122909154635admindbaCAB'

    How can I display the contents of the SQL Adapter in the immediate Window so I can see theSelect string and how its being presented:

    Code:
    adp = New SqlDataAdapter("SELECT ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey  where ArchiveKey='" & cmbCurrentECabinet.ValueMember & "'", CN)

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    Here's what I said:
    Quote Originally Posted by jmcilhinney View Post
    In that case, the Text property will return the value from the ArchiveName column of the selected record and the SelectedValue will return the value from the ArchiveKey column. Presumably you want the latter.
    Now take another look at your code.

  17. #17

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    I changes it and I am back to Operator '&' is not defined for string "SELECT Archive.ArchiveKey, Archi" and type 'DataRowView".

  18. #18
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    Read what I said. Three times I have told you to use SelectedValue - I even bolded and underlined it on the third occasion - and you are still not using it. I know that SelectedValue and SelectedItem both start with "Selected" but the clue that they are not the same is that one ends with "Value" and one ends with "Item". If you're not going to bother reading what's in front of you then you're not going to get very far and, speaking for myself at least, you're going to put people off helping you again.

  19. #19

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    15

    Re: Need to Synchronize two combos...

    This is what I am using...

    adp = New SqlDataAdapter("SELECT Archive.ArchiveKey, Archive.ArchiveName, MainTitle.MainTitleKey, MainTitle.MainLevelTitle FROM Archive INNER JOIN MainTitle ON Archive.ArchiveKey = MainTitle.ArchiveKey where MainTitle.ArchiveKey='" & cmbCurrentECabinet.SelectedValue & "'", CN)

  20. #20
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    If SelectedValue is returning the item rather than a value from a column of the item, which is the case if it's referring to a DataRowView, then you must not have set the ValueMember, even though you previously told us that you had. If you understand what these properties mean then it's all fairly obvious.

    The DataSource is the list of items so SelectedItem will return the item from that list that is selected. When you bind a DataTable, the data actually comes from its DefaultView, which is a DataView, hence each item is a DataRowView.

    The DisplayMember is the name of the property or column from which to get the text to display in the control. That's why, if you set the DisplayMember to "ArchiveName", you see values from the ArchiveName column in the control.

    The ValueMember is the name of the property or column from which to get the value to return from the SelectedValue property. If you set the ValueMember to "ArchiveKey", as you said you did, then the SelectedValue will return the value from the ArchiveKey column of the selected record. If you don't set the ValueMember then SelectedValue will return the item itself, just like SelectedItem. That's why you're getting a message about a DataRowView.

    Also, I told you back in post #9 to use 'CStr(cmbCurrentECabinet.SelectedValue)'.

  21. #21
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,163

    Re: Need to Synchronize two combos...

    It's worth noting that you should always set the DisplayMember and ValueMember before setting the DataSource but that's especially critical if you're using the SelectedValue in the SelectedIndexChanged event handler. If you set the DataSource first then the SelectedIndex will change before the ValueMember has been set and you will then get the item from the SelectedValue instead of the value you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width