How to show multiple Access database columns in a WinForms ListBox?

Question

I have a simple access database that has an "ITEM ID" and an "ITEM NAME". Below is an example.

1 Sword

2 Axe

3 Cat

When trying to get this into my listBox using a query it displays "System.Data.DataRow". I have found ways to show the "ITEM ID" or "ITEM NAME" separately but not together as I desire.

If anyone could tell me how to make it show up "1 Sword" that would be great as I have been searching for answers for hours now.

private void RunQuery()
{
    errorMsg = "";
    q = SearchBoxItemsAll.Text;
    try
    {
        OleDbCommand cmd = new OleDbCommand(q, conn);
        OleDbDataAdapter a = new OleDbDataAdapter(cmd);

        DataTable dt = new DataTable();

        a.SelectCommand = cmd;
        a.Fill(dt);
        a.Dispose();

        listBox1.DataSource = dt;
    catch (Exception ex)
    {}
}

Right now I am using a query box to call the above hoping to make it display the results onto the listBox.

Also I am using WinForms in visual studio 2015 (required). I also don't want to use anything other than a listBox. Apart from this issue it has the look I want and does everything I need.

what is the column name for "ITEM ID" and "ITEM NAME"? do this in your query ITEMID(columnname) + ' ' + ITEMNAME(columnname) AS ITEM — Dr. Stitch
– Dr. Stitch, Commented Jun 2, 2016 at 5:51
right now they are named ITEM ID and ITEM NAME in the database — Yuri Suzumiya
– Yuri Suzumiya, Commented Jun 2, 2016 at 5:54

Manu Varghese · Accepted Answer · 2016-06-02 06:08:48Z

2

Try this:

private void RunQuery()
{
    errorMsg = "";
    q = SearchBoxItemsAll.Text;
    try
    {
        OleDbCommand cmd = new OleDbCommand(q, conn);
        OleDbDataAdapter a = new OleDbDataAdapter(cmd);

        DataTable dt = new DataTable();

        a.SelectCommand = cmd;
        a.Fill(dt);
        a.Dispose();
       foreach(var v in dt.Rows)
       {                        
         listBox1.Items.Add(v[0].ToString()+" "+v[1].ToString());
       }
    }
    catch (Exception ex)
    {}
}

The better way to do it is by specifying DisplayMember and ValueMember and changing your SQL:

private void RunQuery()
{
    errorMsg = "";
    //q = SearchBoxItemsAll.Text;
    //Concatenate the ID and the Name in the SQL Query
    q = "SELECT [ITEM ID] , ([ITEM ID] +' '+ [ITEM NAME]) AS [ITEM NAME] FROM ITEMS";
    try
    {
        OleDbCommand cmd = new OleDbCommand(q, conn);
        OleDbDataAdapter a = new OleDbDataAdapter(cmd);

        DataTable dt = new DataTable();

        a.SelectCommand = cmd;
        a.Fill(dt);
        a.Dispose();
        listBox1.DisplayMember = "ITEM_NAME";
        listBox1.ValueMember = "ITEM_ID";
        listBox1.DataSource = dt;
    }
    catch (Exception ex)
    {}
}

edited Jun 2, 2016 at 6:08

answered Jun 2, 2016 at 5:57

Manu Varghese

8111 gold badge9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Yuri Suzumiya Over a year ago

I did try that, however it doesn't show the ITEM ID still when using SELECT * FROM ITEMS as the query only shows the ITEM NAME

Manu Varghese Over a year ago

change sql Code to SELECT [ITEM ID] , ([ITEM ID] +' '+ [ITEM NAME]) AS [ITEM NAME] FROM ITEMS

Yuri Suzumiya Over a year ago

Got an error 'The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available and the consumer had not yet set a new Double value.' Hang on ill look at your changes and see if it changes. EDIT: yeah still the same message

Jeremy Thompson Over a year ago

@YuriSuzumiya can I suggest you DONT use spaces in column names.And when you say The provider could not determine the Double value - why are you using a Double datatype, just use an Integer datatype for the Item_ID column

Yuri Suzumiya Over a year ago

I am using an integer, i have no idea why it is complaining about a double

|

Amir Shrestha · Accepted Answer · 2016-06-02 08:05:39Z

0

ListBox control does not support multi column display. Try ListView Control instead and add subitems

answered Jun 2, 2016 at 8:05

Amir Shrestha

4515 silver badges11 bronze badges

1 Comment

Yuri Suzumiya Over a year ago

I never wanted multi column display I wanted to show both in the 1 column which is now working.

Collectives™ on Stack Overflow

How to show multiple Access database columns in a WinForms ListBox?

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related