Using a dataset to fill listbox

Question

In my Windows Forms Application I'm trying to fill a listbox with values from a dataset, but it just stays empty and doesn't give any exceptions.

This is my code

        private void FormTeams_load(object sender, EventArgs e)
    {

        try
        {

            DBTeams = new DBConnection();
            conString = Properties.Settings.Default.UserConnectionString;
            DBTeams.connection_string = conString;

            DBTeams.Sql = Properties.Settings.Default.SQL_Teams;

            ds = DBTeams.GetConnection;

            MaxRows = ds.Tables[0].Rows.Count;

            lsb_TeamsTeams.DataSource = ds;
            lsb_TeamsTeams.DisplayMember = "team_name";
            lsb_TeamsTeams.ValueMember = "team_ID";

        }
        catch (Exception err)
        {

            MessageBox.Show(err.Message);

        }

    }

Listbox name is "lsb_TeamsTeams", dataset name is "ds". The table contains 3 columns. I want column "team_ID" and "team_name" to be shown in this ListBox.

Okay, just to try out if my Query would display in my first Form, Form1, I tried the following. Here I'm trying to fill a second dataset with my second table in the database and then trying to pass the data to a ListBox.

        private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            secondForm = new FormTeams();

            DBPlayers = new DBConnection();
            conString = Properties.Settings.Default.UserConnectionString;
            DBPlayers.connection_string = conString;

            DBPlayers.Sql = Properties.Settings.Default.SQL_Players;
            ds = DBPlayers.GetConnection;

            DBPlayers.Sql = Properties.Settings.Default.SQL_Teams;
            ds2 = DBPlayer.GetConnection;

            lsb_Teams.DisplayMember = "team_name";
            lsb_Teams.ValueMember = "team_ID";
            lsb_Teams.DataSource = ds2;


            MaxRows = ds.Tables[0].Rows.Count;

            NavigateRecords();

            btn_Save.Enabled = false;
            btn_New.Enabled = true;
            btn_Cancel.Enabled = false;

        }
        catch (Exception err)
        {

            MessageBox.Show(err.Message);

        }
    }

This unfortunately throws this error: Invalid object name 'tbl_Teams'.

I don't quite understand why it would raise this error, I tried searching the problem on Google, but I could only find some comments about refreshing intellisense cache. I tried to find this in Visual Studio 2015 Enterprise but wasn't able to.

Here is the database connection methods I use.

        public System.Data.DataSet GetConnection
    {

        get { return MyDataSet(); }

    }

    private System.Data.DataSet MyDataSet()
    {

        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
        con.Open();
        da_0 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
        System.Data.DataSet dat_set = new System.Data.DataSet();
        da_0.Fill(dat_set, "Table_Data_1");

        con.Close();

        return dat_set;

    }

So while trying to access the database via SQL Server Management Studio, I found out the table I created in Visual Studio, was not present in the database, which explains why I got the error Invalid object name 'table' How can this be? I believe it has something to do with the fact I created the second table through Visual Studio. Somehow it did add it into the Visual Studio server explorer but not into the actual database. When using SQL management studio I was able to create the second table and then my query in C# would work.

But now the next problem has arrived with filling the ListBox.

I cannot display any data in my ListBox, but I can display data in a DataGridView, but I'd like to display it in a ListBox.

My code:

        private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            secondForm = new FormTeams();

            DBPlayers = new DBConnection();
            conString = Properties.Settings.Default.UserConnectionString;
            DBPlayers.connection_string = conString;

            DBPlayers.Sql = Properties.Settings.Default.SQL_Players;
            ds = DBPlayers.GetConnection;

            DBPlayers.Sql = Properties.Settings.Default.SQL_Teams;
            ds2 = DBPlayers.GetConnection;

            //The following lines of code diplay: " System.Data.DataViewManagerList Item TypeDescriptor " in the ListBox.
            lsb_Teams.DisplayMember = "team_name";
            lsb_Teams.ValueMember = "team_ID";
            lsb_Teams.DataSource = ds2.Tables[0];

            //This works, it displays the data from the table.
            dataGridView1.DataSource = ds2.Tables[0];


            MaxRows = ds.Tables[0].Rows.Count;

            NavigateRecords();

            btn_Save.Enabled = false;
            btn_New.Enabled = true;
            btn_Cancel.Enabled = false;

        }
        catch (Exception err)
        {

            MessageBox.Show(err.Message);

        }
    }

I have tried multiple positions of the .DataSource line, but that didn't help. I also tried to put the data from my other dataset ds.tables[0] into the ListBox and then it displays data from the DisplayMember column, but throws an error when starting the application cannot bind to the new value member. Parameter name: value.

A Sal · Answer 1 · 2016-06-13T19:34:25.347

1

Check to see if your dataset's tables are populated (using code breaks). You need to use a DataAdapter to fill the dataset, and I don't think you are doing that. Also, please post your sql query if this doesn't help you.

edited Jun 13 '16 at 19:34

answered Jun 13 '16 at 18:01

A Sal

79
7

1

Thank you! That code break tip was very useful. I found out my query is not even running. In Form1, my main form, I use the same code and run a query `SELECT * FROM tbl_Players`. This works like a charm. But when I try to reuse this code, and try to fill a second dataset with this query `SELECT * FROM tbl_Teams` visual studio throws an error `Invalid object name 'tbl_Teams'. – Dennis VW Jun 14 '16 at 17:40
1

You can't load data from two datasets into a single listbox. Also, you won't be able to show more than two columns in a listbox (unless you use concatenation to merge two fields). I would suggest using a listview in a grid mode to be able to have multiple columns. Also, I would revise the SQL statement to do a JOIN on the two tables and use the resulting dataset to populate the listview. – A Sal Jun 14 '16 at 19:03
I'm not trying to do that. The first dataset I'm using for displaying single records in a textbox, and I only want 2 columns to be shown in my listbox, so that's no issue. If I do a Join statement, then how do I filter out the results I need for my ListBox? – Dennis VW Jun 15 '16 at 06:37
If you are using WinForms, I don't think you can display two columns in a listbox. – A Sal Jun 15 '16 at 21:50

Abdellah OUMGHAR · Answer 2 · 2016-06-14T18:04:03.627

0

You can use :

lsb_TeamsTeams.DataSource = ds.Tables[0];

and Check if ds.Tables[0] contains data.

you must also change second query DBPlayers.Sql to get data from tbl_Teams table :

DBPlayers.Sql = Properties.Settings.Default.SQL_Players;
ds2 = DBPlayer.GetConnection;

because, you use the same query of Players to fill seconde DataSet

edited Jun 14 '16 at 18:04

answered Jun 13 '16 at 16:43

Abdellah OUMGHAR

3,627
1
11
16

Forgot to mention I already tried that one. Stays empty still. But thank you. – Dennis VW Jun 13 '16 at 16:49
What is the value of `MaxRows`, are you sure you have data in `ds.Tables[0]` ? – Abdellah OUMGHAR Jun 13 '16 at 16:53
MaxRows is set to the row count of tables[0]. In this case 2. But that's not really important since I reused this code from another part in my application and right now I'm not even using MaxRows (yet). – Dennis VW Jun 13 '16 at 16:56
I'm not basing this on anything else but a feeling, but I'm now thinking it might have something to do with the fact this is a second form? Because trying this code ` if (ds.Tables[0].Rows.Count > 0) { MessageBox.Show("Error"); } ` doesn't actually pop up a MessageBox – Dennis VW Jun 13 '16 at 17:00
So the listbox is empty because `ds.Tables[0]` don't have data! – Abdellah OUMGHAR Jun 13 '16 at 17:04
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/12674590) – Tony Hinkle Jun 13 '16 at 17:05
@TonyHinkle Thank you for your remark, I agree with you but I've answered the question, believing it is the answer but the applicant has already signed test the answer, I asked for other information. – Abdellah OUMGHAR Jun 13 '16 at 17:12
@Dennis1679 you must change second query `DBPlayers.Sql = Properties.Settings.Default.SQL_Players;`, you use the same query SQL_Players to fill seconde DataSet, view answer. – Abdellah OUMGHAR Jun 14 '16 at 18:04
Oh I made a typo, sorry. Of course I knew that, and I did. Just copied the wrong part of my code. – Dennis VW Jun 14 '16 at 18:09
@Dennis1679 can you update your code, you have to put the code that you run to check the problem ? – Abdellah OUMGHAR Jun 14 '16 at 18:13
I did change it now, as it sits now, it throws the error I told you about. Invalid object name 'tbl_Teams' <- which is my second tables name in the database. – Dennis VW Jun 14 '16 at 18:15
So the actual problem was something completely different. But now a new problem has arrived. Do you know why my data would display in a dataGridView but not in a ListBox? See my complete error in the first post. – Dennis VW Jun 15 '16 at 09:15
Listbox display `System.Data.DataViewManagerList Item TypeDescriptor` because you use `lsb_Teams.DataSource = ds2`, you must be use `lsb_Teams.DataSource = ds2.Tables[0];` – Abdellah OUMGHAR Jun 15 '16 at 10:08

Using a dataset to fill listbox

2 Answers2