Need to Synchronize two combos...

[alorenzini]

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

[jmcilhinney]

Please use code formatting tags for readability:

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

[alorenzini]

Thank you. I will do better in the future.

[techgnome]

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:

    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.

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

[alorenzini]

It says "Value" is not a member of 'System.Windows.forms.Comboboxes'. when I entered the follwoing 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

[techgnome]

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

[alorenzini]

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

[alorenzini]

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

[jmcilhinney]

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:

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:

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.

[alorenzini]

I am not sure if it makes a difference but it should be coded as:

 cmbCurrentECabinet.DisplayMember = "ArchiveName"
 cmbCurrentECabient.ValueMember = "ArchiveKey"

Although they are both text fields.

[jmcilhinney]

I am not sure if it makes a difference but it should be coded as:

 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.

[alorenzini]

YEs I need the latter. I implemented your advice into the following 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

[alorenzini]

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

[techgnome]

YEs I need the latter. I implemented your advice into the following 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...

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

[alorenzini]

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:

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)

[jmcilhinney]

Here's what I said:

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.

[alorenzini]

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

[jmcilhinney]

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.

[alorenzini]

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)

[jmcilhinney]

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)'.

[jmcilhinney]

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.