Matt E Matt E - 5 months ago 25
Vb.net Question

Populating a Combo Box with multiple fields of data from a database

I am trying to populate a combobox with data from a database - using sql. I am trying to use information from multiple fields (First Name, Last Name). I need the program to concatenate the fields and add the 'FullName' into the combobox. Here is the code that I am using - im getting an error as where the names should appear instead appears "System.Data.DataRowView":

Private Sub frmViewEmployee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
con.Open()
sqlstatement = "SELECT [First Name], [Last Name] FROM [EmployeeAccounts]"
da = New OleDb.OleDbDataAdapter(sqlstatement, con)
da.Fill(ds, "EmployeeName")
Dim x As Short = 0
Dim FirstName As String
Dim LastName As String
Dim MaxRows As Short = ds.Tables("EmployeeName").Rows.Count
Dim FullName(MaxRows) As String
x = 0
While x < MaxRows
FirstName = ds.Tables("EmployeeName").Rows(x).Item(0)
LastName = ds.Tables("EmployeeName").Rows(x).Item(1)
FullName(x) = FirstName & " " & LastName
x = x + 1
End While
comEmployeeName.DataSource = ds.Tables(CStr("EmployeeName"))
comEmployeeName.DisplayMember = CStr(FullName(x))
comEmployeeName.ValueMember = CStr(FullName(x))
End Sub


Any answers, within reason, are appreciated - whether that be corrections or new code entirely.

ehh ehh
Answer

[Not tested] Depending on DBMS you are working with, you could add a new field in your sql sentence that will be a concatenation of first name and last name. Something like:

sqlstatement = "SELECT [First Name] + ' ' + [Last Name] as FullName, [First Name], [Last Name] FROM [EmployeeAccounts]"

' It could be another syntax, it depends on your DBMS
sqlstatement = "SELECT [First Name] || ' ' || [Last Name] as FullName, [First Name], [Last Name] FROM [EmployeeAccounts]"

And then just set the ValueMember and DisplayMember to FullName:

comEmployeeName.DisplayMember ="FullName"
comEmployeeName.ValueMember="FullName"

No need for this part of code:

While x < MaxRows
    FirstName = ds.Tables("EmployeeName").Rows(x).Item(0)
    LastName = ds.Tables("EmployeeName").Rows(x).Item(1)
    FullName(x) = FirstName & " " & LastName
    x = x + 1
End While