-
Sep 26th, 2017, 09:28 PM
#1
Thread Starter
New Member
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
-
Sep 26th, 2017, 09:50 PM
#2
Re: Need to Synchronize two combos...
Please use code formatting tags for readability:
Originally Posted by 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.
vb.net Code:
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:
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:
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
-
Sep 27th, 2017, 08:21 AM
#3
Thread Starter
New Member
Re: Need to Synchronize two combos...
Thank you. I will do better in the future.
-
Sep 27th, 2017, 10:52 AM
#4
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
-
Sep 27th, 2017, 12:01 PM
#5
Thread Starter
New Member
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
-
Sep 27th, 2017, 12:35 PM
#6
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
-
Sep 27th, 2017, 02:01 PM
#7
Thread Starter
New Member
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
-
Sep 27th, 2017, 02:13 PM
#8
Thread Starter
New Member
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
-
Sep 27th, 2017, 06:43 PM
#9
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:
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:
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.
-
Sep 28th, 2017, 08:27 AM
#10
Thread Starter
New Member
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.
-
Sep 28th, 2017, 08:32 AM
#11
Re: Need to Synchronize two combos...
Originally Posted by alorenzini
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.
-
Sep 28th, 2017, 01:59 PM
#12
Thread Starter
New Member
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
-
Sep 28th, 2017, 02:02 PM
#13
Thread Starter
New Member
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
-
Sep 28th, 2017, 02:36 PM
#14
Re: Need to Synchronize two combos...
Originally Posted by alorenzini
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
-
Sep 28th, 2017, 04:27 PM
#15
Thread Starter
New Member
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)
-
Sep 28th, 2017, 06:47 PM
#16
Re: Need to Synchronize two combos...
Here's what I said:
Originally Posted by jmcilhinney
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.
-
Sep 28th, 2017, 07:34 PM
#17
Thread Starter
New Member
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".
-
Sep 28th, 2017, 09:19 PM
#18
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.
-
Sep 28th, 2017, 09:33 PM
#19
Thread Starter
New Member
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)
-
Sep 28th, 2017, 10:53 PM
#20
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)'.
-
Sep 28th, 2017, 10:56 PM
#21
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|