Trouble populating a combobox - VB & SQL

[rickjamesb]

In VB i have ONE drop down combobox that will display employee FirstName's, LastName's, & EmployeeID's from an Employee table.

To get all three attributes to fit into one combo box i used "AS EmployeeName" and i had to convert EmployeeID from an INT to NVarchar for it to run. The following procedure works fine:

CREATE PROCEDURE up_Display_Employees
AS
SELECT LastName + ', ' + FirstName + ' - ' + CAST (EmployeeID AS nvarchar(10))
AS EmployeeName
FROM Employees
Order by LastName

This SQL produces outputs like:
Dodsworth, Anne - 9
Fuller, Andrew - 2
Which is exactly the way i want it to show in the combo box.

The problem is with my VB code:
' Set SQL command specifics to fill the products grid
sqlCmd.CommandText = "up_Display_Employees"
sqlCmd.CommandType = CommandType.StoredProcedure

' Configure data adaptor and dill data set
sqlDatAdapNorthWinds.SelectCommand = sqlCmd
sqlDatAdapNorthWinds.Fill(datSetNorthWinds, "Employees")

cboEmployees.DataSource = datSetNorthWinds.Tables("Employees")
cboEmployees.DisplayMember = "LastName"
cboEmployees.ValueMember = "EmployeeID"

I'm not sure what to put as the DisplayMember and ValueMember and thats where the program keeps on getting errors. Any tips?

 

[jbenson001]

You are only selecting one column, the EmployeeName that you create. If you want to set your DisplayMemeber to LastName and your ValueMemever to EmployeeID, you will have to also specify those columns in the select statement. Otherwise, you will have to set both properties to EmployeeName. I think what you want is to set the DisplayMember to EmployeeName, and ValueMember to EmployeeID.

Jim

 

[rickjamesb]

I'm still getting errors on the ValueMember line: An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

I beleive i tried that earlier. I've tried several different variations, and all have failed

 

[jbenson001]

What did you just try? Can you show me the sql you have and the vb code?

Jim

 

[rickjamesb]

All i did was change this in VB:
cboEmployees.DisplayMember = "EmployeeName"
cboEmployees.ValueMember = "EmployeeID"

I guess im starting to understand what you said. "SELECT LastName + ', ' + FirstName + ' - ' + CAST (EmployeeID AS nvarchar(10))" Is basically only one statement, when before i thought it was three.

So to fix this i tried changing the SQL to:
SELECT LastName + ', ' + FirstName + ' - ' + CAST (EmployeeID AS nvarchar(10)), EmployeeID

Same error on the ValueMember

Then i tried:
SELECT LastName + ', ' + FirstName + ' - ' + CAST (EmployeeID AS nvarchar(10)), EmployeeID, LastName

And the combo box works, but only shows the last name - not the lastname/firstname/employeeid.

Its my first week with SQL programming, as you can see im not to good

 

[jbenson001]

You need to fix the SQL, try:

SELECT [b]EmployeeID,[/b] LastName + ', ' + FirstName + ' - ' + CAST (EmployeeID AS nvarchar(10))
AS EmployeeName 
FROM Employees
Order by LastName

Then:

cboEmployees.DisplayMember = "EmployeeName"
cboEmployees.ValueMember = "EmployeeID"

Jim

 

[rickjamesb]

Works like a charm!!! Thank you very much!

I was getting close, i put EmployeeID at the end (which wasnt very smart since it destroyed the purpose of the AS EmployeeName) instead of the beginning.

 

[jbenson001]

It doesn't matter where in the select you put the EmployeeID. Your error was that you removed the AS EmployeeName part.

Glad it is working for you...

Jim