Multiple columns in a combobox - again

[briancps]

I have read the posts re this subject and all I want to achieve is the equivalent of an Access Lookup i.e. a 2 column combo, the ID is hidden and the user sees the Description. Select the description and the update routine stores the ID.

I find it hard to believe this functionality is not simply available in VB.NET. I have gone thru Edneeis's Class (really great stuff but an overkill for what I want).

I decided on the folowing approach. Im just padding the description to 60 spaces and then adding the ID at the end - the user never sees it - I can access it at update time. Can anyone see any inherent problems with this - i know it's not pretty but with something like 60 Lookups on 20 Forms - it works. Is there a simple way to reproduce the Access Lookups ? - it appears not.

Open to comments - I have shown my ComboBuild code below for interest-

Public Function ComboBuild(ByVal sTable As String, ByVal cboCombo As ComboBox, ByVal nID As Long)
'fill the combo with the required details from the table specified
Dim dbcConnection As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\Safework\SkeletonVBNet\Safework.MDB;"

Dim sSql As String
Dim sDesc As String
Dim nSelected As Integer

Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()
cboCombo.Items.Clear()

nSelected = 0

sSql = "SELECT * FROM " & sTable & " ORDER BY Description"

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'read the datareader and fill the Combo
Do While drd.Read
sDesc = drd!Description
cboCombo.Items.Add(sDesc.PadRight(60) & CStr(drd!ID))
If nID = drd!ID Then
nSelected = cboCombo.Items.Count - 1
End If
Loop
drd.Close()

cboCombo.SelectedIndex = nSelected

End Function

[Edneeis]

I'd say go with whatever works. Although if you are only reading the ID and Description from the table then you should have the query only return those fields. That will speed things up and cut down on the traffic.

[briancps]

Thanks for the reply - I have a consistent table structure for my Lookups -they only have 2 fields - an ID and a Description.
regards
BH

[Athley]

If you use a disconnected state with a dataset instead you can put the Description in the DisplayMember property and the ID in the ValuMember property.

If you want to keep with the open connection create an own Item class with a ItemDisplay and a ItemData property.

[Corinne]

Hi,

Can anyone expand on this subject. I'm facing a similar problem & need some help. I am using a stored procedure and trying to pass a parameter to it. Where I am having a problem is passing the stored value rather that the displayed value. The combobox the user selects from contains the complete word let's say Chair. The value that is tied to that is C which is stored in the table. I need to know how to pass the parameter C to the stored procedure. I think I have the stored procedure ok but I think that I'm having a problem with defining the value of the parameter.

I have this in the selected index changed for the combobox:

cmModel.Parameters.add("@ModelType", SqlDbType.NVarChar, 10) = Trim(cboType.ValueMember)
cmModel.parameters("@ModelType").value = Trim(cboType.Text)

My Stored procedure:
CREATE PROCEDURE cusSP_Model

@ModelType as nvarchar(10)

AS

SELECT imUphCase.Type, imProduct.ExtendedCore
FROM imUphCase INNER JOIN
imProduct ON imUphCase.ProductID = imProduct.ProductID LEFT OUTER JOIN
CusType ON imUphCase.Type = CusType.TypeCode
WHERE (imUphCase.Type = @ModelType)


Can anyone see what I'm missing??

Thanks,
Corinne

[Edneeis]

This line:
cmModel.parameters("@ModelType").value = Trim(cboType.Text)

Sets the parameter value to the 'TEXT' of the combo is that what you are trying to do? For the sounds of it it isn't.

[Corinne]

Edneeis,

You're right, What I'm trying to do is to place the value of the Type into the parameter:

In my table Chair is the Type and C is the TypeCode. I want the user to be able to select Chair and place C into the parameter.


Thanks,
Corinne

[Edneeis]

Well if the combo has the valuemember set to the field that has the type code then you can use this:
cmModel.parameters("@ModelType").value = cboType.SelectedValue

[Corinne]

OK, this how I set the value member of the combo:

cboType.ValueMember = dsCushions.Tables("cusType").Columns("TypeCode").ToString

And I received this error:

An unhandled exception of type 'System.NullReferenceException' occurred in Mock.exe

Additional information: Object reference not set to an instance of an object.


???

[Edneeis]

Post your code for filling the combo or setting its DataSource.

The ValueMember and DisplayMember should be strings that are the property/field name to use.

cboType.ValueMember = "TypeCode"

[Corinne]

Edneeis,

Here's my code so far:

Dim cmModel = New SqlCommand("cusSP_Model", cnnCushions)
cmModel.CommandType = CommandType.StoredProcedure
Dim daModel As New SqlDataAdapter(cmModel)
daModel.SelectCommand = cmModel

cboType.ValueMember = dsCushions.Tables("cusType").Columns("TypeCode").ToString
cmModel.parameters("@ModelType").value = cboType.SelectedValue

cmModel.Parameters.add("@ModelType", SqlDbType.NVarChar, 10) = Trim(cboType.SelectedValue)
cmModel.parameters("@ModelType").value = Trim(cboType.ValueMember)

'fill DataSet
daModel.Fill(dsCushions, "ExtendedCore")

'adding items with DataRow from DataSet
For Each drCushions In dsCushions.Tables("ExtendedCore").Rows
cboModel.Items.Add(drCushions("ExtendedCore"))
Next

dsCushions.Clear()

Thanks,
Corinne

[Corinne]

Edneeis,

When I add this line:
cboType.ValueMember = "TypeCode"

I receive this message when I run:
An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Could not bind to the new value member.

I know that I'm missing something pretty easy but I just can't see it yet.

Corinne

[Edneeis]

Well one thing I see is that TypeCode comes from the cusType table while you are filling the items from the ExtendedCore table. Also since you aren't using it as a datasource but are instead just filling it with the actual data then it it will only have that said actual data and not link to anything else.

Explain the relation of the data in the cusType and ExtendedCore tables. List their fields or keys to show their relationship.

[Corinne]

"Explain the relation of the data in the cusType and ExtendedCore tables. List their fields or keys to show their relationship."

I have 3 tables: cusType, imProduct and imUphCase.

imUphCase is linked to imProduct by ProductID. My stored procedure joins them together and also specifies a where clause to retreive just a subset from the imUphCase table based on TypeCode (TypeCode will be the parameter @ModelType).

Stored Procedure:

CREATE PROCEDURE cusSP_Model

@ModelType as nvarchar(10)

AS

SELECT imUphCase.Type, imProduct.ExtendedCore
FROM imUphCase INNER JOIN
imProduct ON imUphCase.ProductID = imProduct.ProductID LEFT OUTER JOIN
CusType ON imUphCase.Type = CusType.TypeCode
WHERE (imUphCase.Type = @ModelType)

imProduct contains all products by ProductID
imUphCase contains subset of products by ProductID
imUphCase contains Type field (SS)
cusType contains Type field (Slip Seat)
cusType contains TypeCode field (SS)

cusType is a lookup table

cboType is filled with the Type field so the user will select "Slip Seat as a choice and then I want a second combo box to fill with all of the imProduct.ExtendedCore data that coorespond by ProductID that have SS as a value in imUphCase.Type.

Am I making this too hard by wanting to use a lookup table? I thought that was the correct thing to do when desgining the tables - to store as little data as possible.

Any help is appreciated.

Thanks,
Corinne

[Edneeis]

Well I'm still not exactly clear on how you have the data laid out, it seems overly complex to me but I have some suggestions.

The valuemember for cboType should be set when it is filled (this is good practice but not a requirement). Also you don't show the filling of cboType which must have the correct information in order for the rest to work right. I think that is the main problem, that the cboType valuemember is incorrect so the sp doesn't find what it is looking for.

Show the code for filling cboType also switch from this:

Dim cmModel = New SqlCommand("cusSP_Model", cnnCushions)
cmModel.CommandType = CommandType.StoredProcedure
Dim daModel As New SqlDataAdapter(cmModel)
daModel.SelectCommand = cmModel
 
cboType.ValueMember = dsCushions.Tables("cusType").Columns("TypeCode").ToString
cmModel.parameters("@ModelType").value = cboType.SelectedValue
 
cmModel.Parameters.add("@ModelType", SqlDbType.NVarChar, 10) = Trim(cboType.SelectedValue)
cmModel.parameters("@ModelType").value = Trim(cboType.ValueMember)
 
'fill DataSet
daModel.Fill(dsCushions, "ExtendedCore")
 
'adding items with DataRow from DataSet
For Each drCushions In dsCushions.Tables("ExtendedCore").Rows
cboModel.Items.Add(drCushions("ExtendedCore"))
Next
 
dsCushions.Clear()

to this:

Dim cmModel As New SqlCommand("cusSP_Model", cnnCushions)
cmModel.CommandType = CommandType.StoredProcedure
cmModel.Parameters.add("@ModelType", cboType.ValueMember)
Dim daModel As New SqlDataAdapter(cmModel)
 
'fill DataSet
daModel.Fill(dsCushions, "ExtendedCore")
 
'set combo datasource to newly filled table
cboModel.DataSource=dsCushions.Tables("ExtendedCore")
cboModel.DislayMember="ExtendedCore"

[Corinne]

'==> Fill cboType
Dim cmType = New SqlCommand("cusSP_Type", cnnCushions)
cmType.CommandType = CommandType.StoredProcedure
Dim daCushions As New SqlDataAdapter(cmType)
daCushions.SelectCommand = cmType

'fill DataSet
daCushions.Fill(dsCushions, "Type")

'adding items with DataRow from DataSet
For Each drCushions In dsCushions.Tables("Type").Rows
cboType.Items.Add(drCushions("Type"))
Next

dsCushions.Clear()


Stored Procedure:

CREATE PROCEDURE cusSP_Type
AS
SELECT Type FROM cusType
GO

I'll try the code change you suggest

[Edneeis]

Now is the field 'Type' contain the 'C' that needs to be passed to the sp to get the model or does it contain 'Chair', the description?

[Corinne]

When filling cboType, Type contains "Chair". The field in cusType TypeCode contains C.

[Edneeis]

Ok so make sure that 'cusSP_Type' returns both the 'Type' and 'TypeCode' fields. Then switch this code:

'==> Fill cboType
Dim cmType = New SqlCommand("cusSP_Type", cnnCushions)
cmType.CommandType = CommandType.StoredProcedure
Dim daCushions As New SqlDataAdapter(cmType)
daCushions.SelectCommand = cmType
 
'fill DataSet
daCushions.Fill(dsCushions, "Type")
 
'adding items with DataRow from DataSet
For Each drCushions In dsCushions.Tables("Type").Rows
cboType.Items.Add(drCushions("Type"))
Next
 
dsCushions.Clear()

to this:

'==> Fill cboType
Dim cmType As New SqlCommand("cusSP_Type", cnnCushions)
cmType.CommandType = CommandType.StoredProcedure
Dim daCushions As New SqlDataAdapter(cmType)
 
'fill DataSet
daCushions.Fill(dsCushions, "Type")
cboType.DataSource=dsCushions.Tables("Type")
cboType.DisplayMember="Type"
cboType.ValueMember="TypeCode"

Then test it and it should work, as long as these changes and the other ones I posted have been made and the sp returns both fields.

[Corinne]

Edneeis,

Thanks you for helping me through this, I finally have the combo boxes working correctly. I finally found the value of the selected item and placed it in a string & then passed the string to the parameter:

Dim ID As String
ID = Trim(dsType.Tables("cusType").Rows(cboType.SelectedIndex).Item("TypeCode"))

Again, thanks so much for helping.

Corinne

[Edneeis]

Does that mean you didn't get the ValueMember working right? Although if it works to your satisfaction then I guess it doesn't matter. Another thing to remember is that the Display and Value Member properties are case sensitive.

[Corinne]

Heres what I ended up with:

'==> Fill cboType
Dim cmType = New SqlCommand("cusSP_Type", cnnCushions)
cmType.CommandType = CommandType.StoredProcedure
Dim daType As New SqlDataAdapter(cmType)
daType.SelectCommand = cmType

'fill DataSet
daType.Fill(dsType, "CusType")
cboType.DisplayMember = "Type"
cboType.ValueMember = "TypeCode"

'adding items with DataRow from DataSet
For Each drType In dsType.Tables("CusType").Rows
cboType.Items.Add(drType("Type"))
Next

'==> Fills cboModel on selected item change of cboType
Private Sub cboType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboType.SelectedIndexChanged

cboModel.Items.Clear()

Dim ID As String
ID = Trim(dsType.Tables("cusType").Rows(cboType.SelectedIndex).Item("TypeCode"))

Dim cmModel As New SqlCommand("cusSP_Model", cnnCushions)
cmModel.CommandType = CommandType.StoredProcedure
cmModel.Parameters.Add("@ModelType", ID)
Dim daModel As New SqlDataAdapter(cmModel)

'fill DataSet
daModel.Fill(dsCushions, "imProduct")

cboModel.DisplayMember = "ExtendedCore"

'adding items with DataRow from DataSet
For Each drCushions In dsCushions.Tables("imProduct").Rows
cboModel.Items.Add(drCushions("ExtendedCore"))
Next

dsCushions.Tables.Clear()

End Sub


Stored procedure to fill parameter:

CREATE PROCEDURE cusSP_Model

@ModelType as nvarchar(10)

AS

SELECT imUphCase.TypeCode, imProduct.ExtendedCore, CusType.TypeCode, CusType.Type
FROM imUphCase INNER JOIN
imProduct ON imUphCase.ProductID = imProduct.ProductID LEFT OUTER JOIN
CusType ON imUphCase.TypeCode = CusType.TypeCode
WHERE (imUphCase.TypeCode = @ModelType)
ORDER BY imProduct.ExtendedCore

This works for me so far ........... as I go further with the project I may come up against something I'm not seeing right now. Again, thanks for your help.

Corinne