• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Multiple columns in ComboBox

Hi,

I know this question has been asked loads in EE, but I still haven't been able to find an answer for my situation.

I have a combo box whichis currently populated by a MSSQL table.  At present only one column of data is returned (company_name).  I'm trying to return both the company_name and the ID associated with the company so that i can use the ID in the rest of my code.  Showing the company name makes it easier for the user to select (rather than knowing an ID).

The ID column doesn't have to be visable in the combobox, but the ID needs to be the value which is used as the Bound Column (if i were in VBA).

My project is in MS Outlook VB6
Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset


'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"


SQLStatement = "SELECT * FROM tbl_contacts"

Dim I As Long

'Fill ComboBox with company names
I = 0
frmInputbox.ComboBoxValue.Clear
Set rs = oconn.Execute(SQLStatement)

'Replace this routine with your code
Do While Not rs.EOF
    I = I + 1
    frmInputbox.ComboBoxValue.AddItem rs.Fields("company_name").Value
    'frmInputbox.ComboBoxValue.ItemData(I - 1) = rs.Fields("ID").Value
    rs.MoveNext
Loop

 
End Function

Open in new window

0
d10u4v
Asked:
d10u4v
  • 19
  • 8
1 Solution
 
AshokCommented:
Dim dsCodes As New DataSet
Dim codesAdapter As New SqlDataAdapter("SELECT * FROM tbl_contacts", myConnection)
codesAdapter.Fill(dsCodes, "Codes")
cboDocType.DataSource = dsCodes.Tables("tbl_contacts")
cboDocType.DisplayMember = "company_name"
cboDocType.ValueMember = "ID"

HTH
Ashok
0
 
d10u4vAuthor Commented:
Thanks,
When i add the code the following is in red:

Dim codesAdapter As New SqlDataAdapter("SELECT * FROM tbl_contacts", myConnection)
codesAdapter.Fill(dsCodes, "Codes")

Where do i fit this into my current code?

Tom
0
 
AshokCommented:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

.....

    Dim conn As SqlConnection
    Dim daCustomers As New SqlDataAdapter("SELECT * FROM tbl_contacts", conn)
    Dim dsCustomers As New DataSet
    daCustomers.Fill(dsCustomers, "tbl_contacts")
    cBoxCustomers.DataSource = dsCustomers.Tables("tbl_contacts")
    cBoxCustomers.DisplayMember = "company_name"
    cBoxCustomers.ValueMember = "ID"

HTH
Ashok
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
d10u4vAuthor Commented:
Hi,

Still no joy :(
Tom
0
 
AshokCommented:
You will have to make sure Connection String
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

.....

    Dim conn As SqlConnection
    // instantiate the connection
    conn = new SqlConnection("Driver={SQL Server};" & _
               "Server=MYSERVERNAME;" & _
               "Database=MYDATABASE;" & _
               "Uid=USERID;" & _
               "Pwd=PASSWORD;"
    Dim daCustomers As New SqlDataAdapter("SELECT * FROM tbl_contacts", conn)
    Dim dsCustomers As New DataSet
    daCustomers.Fill(dsCustomers, "tbl_contacts")
    cBoxCustomers.DataSource = dsCustomers.Tables("tbl_contacts")
    cBoxCustomers.DisplayMember = "company_name"
    cBoxCustomers.ValueMember = "ID"

HTH
Ashok


0
 
AshokCommented:
OR

Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset


'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"


SQLStatement = "SELECT * FROM tbl_contacts"

Dim I As Long

'Fill ComboBox with company names
comboBox1.DataSource = oconn.Execute(SQLStatement)

'Replace this routine with your code
comboBox1.DisplayMember = "tbl_contacts.company_name";
comboBox1.ValueMember = "tbl_contacts.ID";

 
End Function

HTH
Ashok
0
 
AshokCommented:
Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset

'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"

SQLStatement = "SELECT * FROM tbl_contacts"

'Fill ComboBox with company names
comboBox1.DataSource = oconn.Execute(SQLStatement)

'Replace this routine with your code
comboBox1.DisplayMember = "tbl_contacts.company_name";
comboBox1.ValueMember = "tbl_contacts.ID";
nction

HTH
Ashok
0
 
d10u4vAuthor Commented:
I have carried this out and i get a compile error on the following line:

frmInputbox.ComboBoxValue.DataSource = oconn.Execute(SQLStatement)

It highlights the .DataSource

With "Method ot Data member not found"


Function GSheetName() As String
'Fill ComboBoxValue with Data

Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset

'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVER;" & _
"Database=DATABASE;" & _
"Uid=USER;" & _
"Pwd=PASSWORD;"

SQLStatement = "SELECT * FROM tbl_contacts"

'Fill ComboBox with company names
frmInputbox.ComboBoxValue.DataSource = oconn.Execute(SQLStatement)

'Replace this routine with your code
frmInputbox.ComboBoxValue.DisplayMember = "tbl_contacts.company_name"
frmInputbox.ComboBoxValue.ValueMember = "tbl_contacts.ID"

 
End Function

Open in new window

0
 
AshokCommented:
How about.....

  Dim conn As OleDbConnection = New OleDbConnection("Your connection String")    
  Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM tbl_contacts", conn)    
  Dim daContacts As OleDbDataAdapter = New OleDbDataAdapter(cmd)    
  Dim dsContacts As DataSet = New DataSet()    

  daContacts.Fill(dsContacts, "tbl_contacts")
  cBoxCustomers.DataSource = dsContacts.Tables("tbl_contacts")
  cBoxCustomers.DisplayMember = "company_name"
  cBoxCustomers.ValueMember = "ID"

HTH
Ashok
0
 
AshokCommented:
Imports System.Data.OleDb

above is required for OleDB.....
0
 
d10u4vAuthor Commented:
Where do I put the:

imports system.data.oleDB

tom
0
 
AshokCommented:
OR

Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset
Dim dsContacts As DataSet = New DataSet()    
Dim obAdapter As New System.Data.OleDb.OleDbDataAdapter

'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"

SQLStatement = "SELECT * FROM
Set rs = oconn.Execute(SQLStatement)

'Fill ComboBox with company names
obAdapter.Fill(dsContacts, rs, "tbl_contacts")

'Replace this routine with your code
cBoxCustomers.DataSource = dsCustomers.Tables("tbl_contacts")
cBoxCustomers.DisplayMember = "company_name"
cBoxCustomers.ValueMember = "ID"

HTH
Ashok

0
 
AshokCommented:
You could put it (imports system.data.oleDB) at very top of the file.

Try the last suggestion I posted (where you would not have to worry about adding above line).

Ashok
0
 
AshokCommented:
Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset
Dim dsContacts As DataSet = New DataSet()    
Dim obAdapter As New System.Data.OleDb.OleDbDataAdapter

'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"

SQLStatement = "SELECT * FROM
Set rs = oconn.Execute(SQLStatement)

obAdapter.Fill(dsContacts, rs, "tbl_contacts")

'Fill ComboBox with company names
cBoxCustomers.DataSource = dsCustomers.Tables("tbl_contacts")
cBoxCustomers.DisplayMember = "company_name"
cBoxCustomers.ValueMember = "ID"

HTH
Ashok
0
 
AshokCommented:
Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset
Dim dsContacts As DataSet = New DataSet()    
Dim obAdapter As New System.Data.OleDb.OleDbDataAdapter

'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=MYSERVERNAME;" & _
"Database=MYDATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"

SQLStatement = "SELECT * FROM tbl_contacts"
Set rs = oconn.Execute(SQLStatement)

obAdapter.Fill(dsContacts, rs, "tbl_contacts")

'Fill ComboBox with company names
cBoxCustomers.DataSource = dsContacts.Tables("tbl_contacts")
cBoxCustomers.DisplayMember = "company_name"
cBoxCustomers.ValueMember = "ID"

HTH
Ashok
0
 
Reza RadCommented:
Try this

Dim cn = New System.Data.SqlClient.SqlConnection
        cn.ConnectionString = "Driver={SQL Server};" & _
        "Server=MYSERVERNAME;" & _
        "Database=MYDATABASE;" & _
        "Uid=USERID;" & _
        "Pwd=PASSWORD;"
        cn.Open()

        Dim cmd = New System.Data.SqlClient.SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_contacts"
        cmd.CommandType = CommandType.Text
        Dim dta = New System.Data.SqlClient.SqlDataAdapter

        Dim ds = New DataSet
        dta.SelectCommand = cmd
        dta.Fill(ds, "tbl_contacts")

        ComboBox1.DataSource = ds.Tables("tbl_contacts")
        ComboBox1.DisplayMember = "company_name"
        ComboBox1.ValueMember = "ID"




        cn.Close()

Open in new window

0
 
d10u4vAuthor Commented:
Hi The following lines of code are red and i get compile errors on each one:

Dim cn = New System.Data.SqlClient.SqlConnection

cn.Open()

Dim cmd = New System.Data.SqlClient.SqlCommand

Dim dta = New System.Data.SqlClient.SqlDataAdapter

Dim ds = New DataSet

dta.Fill(ds, "tbl_contacts")

Its as if some reference is missing as Outlook does not seem to know what these lines are saying.

Rgds,
Tom
Function GSheetName() As String
'Fill ComboBoxValue with Data

Dim cn = New System.Data.SqlClient.SqlConnection
        cn.ConnectionString = "Driver={SQL Server};" & _
        "Server=SERVERNAME;" & _
        "Database=DATABASE;" & _
        "Uid=USER;" & _
        "Pwd=PASSWORD;"
        
        cn.Open()

 
        Dim cmd = New System.Data.SqlClient.SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_contacts"
        cmd.CommandType = CommandType.Text
        Dim dta = New System.Data.SqlClient.SqlDataAdapter
 
        Dim ds = New DataSet
        dta.SelectCommand = cmd
        dta.Fill(ds, "tbl_contacts")
 
        ComboBox1.DataSource = ds.Tables("tbl_contacts")
        ComboBox1.DisplayMember = "company_name"
        ComboBox1.ValueMember = "ID"
  
 
        cn.Close()
End Function

Open in new window

0
 
d10u4vAuthor Commented:
I have changed the code to:

Dim cn As New System.Data.SqlClient.SqlConnection
        cn.ConnectionString = "Driver={SQL Server};" & _
        "Server=SERVER;" & _
        "Database=DATABASE;" & _
        "Uid=USER;" & _
        "Pwd=PASSWORD;"
       
        cn.Open (cn)

 
        Dim cmd As New System.Data.SqlClient.SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_contacts"
        cmd.CommandType = CommandType.Text
        Dim dta As New System.Data.SqlClient.SqlDataAdapter
 
        Dim ds As New DataSet
        dta.SelectCommand = cmd
        dta.Fill(ds, "tbl_contacts")
 
        ComboBox1.DataSource = ds.Tables("tbl_contacts")
        ComboBox1.DisplayMember = "company_name"
        ComboBox1.ValueMember = "ID"
 
 
        cn.Close (cn)
End Function

Which has removed some of the Red lines, but the:

dta.Fill(ds, "tbl_contacts")

Is still red and when i compile the project i get a compile error on the first line:

Dim cmd As New System.Data.SqlClient.SqlCommand

Which says: Complie Error User-defined type not defined.  

Am i missing some references?

Tom
0
 
AshokCommented:
Tom,

I will get back to you after few minutes.

Ashok
0
 
d10u4vAuthor Commented:
Hi Ashok,

I have managed to figure out a way of doing it myself (with the help of some assistance).  I'll post my final code here shortly.

Thanks,

Tom
0
 
AshokCommented:
If you goto Menu option "Project" and Select LAST option "ProjectName Properties.
Then select "References" TAB.
Make sure you have "System.Data" .NET type reference (otherwise click Add button to add).
Make sure you have "System.Data.DataSetExtensions" .NET type reference (otherwise click Add button to add).

HTH
Ashok
0
 
AshokCommented:
reza_rad,

cn.Open()

and

cn.Close()

not needed in above.

Instead of cn.Close(), you would use cn.Dispose()

Ashok
0
 
AshokCommented:
With OleDB

Imports System.Data.OleDb

.....

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim dsUsers As DataSet = New DataSet()
        Dim myAdapter As New OleDbDataAdapter("SELECT fldCategoryID, fldCategoryDesc FROM tblCategories", _
                                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\ITP400_IP3_B.mdb")

        myAdapter.Fill(dsUsers, "tblCategories")

        'Fill ComboBox with names
        ComboBox2.DataSource = dsUsers.Tables("tblCategories")
        ComboBox2.DisplayMember = "fldCategoryDesc"
        ComboBox2.ValueMember = "fldCategoryID"
        ComboBox2.SelectedIndex = 0

        myAdapter.Dispose()
        dsUsers.Dispose()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        TextBox1.Text = CStr(ComboBox2.SelectedValue)
    End Sub

HTH
Ashok

0
 
AshokCommented:
With SqlConnection

Imports System.Data
Imports System.Data.SqlClient

.....

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As SqlConnection = New SqlConnection()
        Dim dsUsers As DataSet = New DataSet()
        Dim myAdapter As SqlDataAdapter

        conn.ConnectionString = "SQL connection string is needed"

        myAdapter = New SqlDataAdapter("SELECT fldCategoryID, fldCategoryDesc FROM tblCategories", conn)

        myAdapter.Fill(dsUsers, "tblCategories")

        'Fill ComboBox with names
        ComboBox2.DataSource = dsUsers.Tables("tblCategories")
        ComboBox2.DisplayMember = "fldCategoryDesc"
        ComboBox2.ValueMember = "fldCategoryID"

        myAdapter.Dispose()
        dsUsers.Dispose()
        conn.Dispose()
    End Sub

HTH
Ashok
0
 
AshokCommented:
With ADODB

Imports ADODB

.....

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim oconn As ADODB.Connection
        Dim SQLStatement As String
        Dim rs As ADODB.Recordset
        Dim dsContacts As DataSet = New DataSet()
        Dim obAdapter As New System.Data.OleDb.OleDbDataAdapter

        'ADODB Connection string
        oconn = New ADODB.Connection
        oconn.Open("Driver={SQL Server};" & _
        "Server=MYSERVERNAME;" & _
        "Database=MYDATABASE;" & _
        "Uid=USERID;" & _
        "Pwd=PASSWORD;")

        SQLStatement = "SELECT * FROM tbl_contacts"
        rs = oconn.Execute(SQLStatement)

        obAdapter.Fill(dsContacts, rs, "tbl_contacts")

        'Fill ComboBox with company names
        ComboBox3.DataSource = dsContacts.Tables("tbl_contacts")
        ComboBox3.DisplayMember = "company_name"
        ComboBox3.ValueMember = "ID"
    End Sub

HTH
Ashok
0
 
d10u4vAuthor Commented:
This is the code i'm using which is now working:  Thanks


 
Public Function CInputBox(Prompt As String, Optional Title As String, _
                          Optional DefaultValue As Variant, _
                          Optional Password As Boolean)
 
    With InputBoxParameters
        .Prompt = Prompt
        .Title = Title
        .DefaultValue = DefaultValue
        .Password = Password
    End With
      
    'DoCmd.OpenForm "frmInputbox", , , , , acDialog
    frmInputbox.Show
    CInputBox = InputBoxParameters.Value
      
End Function
 
 
Function GSheetName() As String
'Fill ComboBoxValue with Data
'Insert Here your code to fill
 
Dim oconn As ADODB.Connection
Dim SQLStatement As String
Dim rs As ADODB.Recordset


'ADODB Connection string
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=SERVERADDRESS;" & _
"Database=DATABASE;" & _
"Uid=USERID;" & _
"Pwd=PASSWORD;"


SQLStatement = "SELECT * FROM tbl_contacts ORDER BY company_name ASC"


Dim I As Long

'Fill ComboBox with Sheet Names
I = 0
frmInputbox.ComboBoxValue.Clear
Set rs = oconn.Execute(SQLStatement)

'Replace this routine with your code
Do While Not rs.EOF
    frmInputbox.ComboBoxValue.AddItem rs.Fields("company_name").Value & Space(256) & "|" & rs.Fields("ID").Value
    rs.MoveNext
Loop

 
End Function

Open in new window

0
 
AshokCommented:
In d10u4v's original question,
Showing the company name makes it easier for the user to select (rather than knowing an ID).

So, it is asking for showing only one field "Company Name".
And allowing the code to read "ID" which is exactly what I provided in my solution.

The solution he came up with is a workaround, not what he asked for originally.

Thanks,
Ashok
0
 
AshokCommented:
I presume because the question was (also) posted in vb.net zone.
so, while your suggestions are fine for vb.net, they are useless for the vba/vb6 world as such.

That is correct.  VB.NET zone was there so I provided a solution in VB.NET.

Thanks,
Ashok

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now