How to get back a datasource for a list when an item is not equal value from database

Question

I have monthcalendar with a button, when a button is clicked and a selected date in monthcalendar is equal to a value from database column then Iam usin a listbox.datasource from the database, but after that when I select a new date in monthcalendar and selected date is not equal to the column value i want back the first datasource for the list I used. Note that I use just: if(date.Equals("20160322")) without the foreach it works fine but not with the the foreach to loop throught the column

private void button1_Click(object sender, EventArgs e)
    {
        for (int i = 0; i < defaultList.Length; i++)
        {
            listBox1.Items.Add(defaultList[i]);
        }
        string date = monthCalendar1.SelectionStart.Date.ToString("yyyyMMdd");
        string connetionString = null;
        MySqlConnection connection;
        MySqlCommand command;
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        DataSet ds = new DataSet();
        int i = 0;
        string sql = null;
        connetionString = "datasource=localhost; database=bokning;port=3306;username=root;password=666666";
        sql = "select date,dayTime from newsystem where date='" + date + "'";
        connection = new MySqlConnection(connetionString);
        try
        {
            connection.Open();
            command = new MySqlCommand(sql, connection);
            adapter.SelectCommand = command;
            adapter.Fill(ds);
            adapter.Dispose();
            command.Dispose();
            connection.Close();

            dtDatetime = ds.Tables[0];
            foreach (DataRow dr in dtDatetime.Rows)
            {

                if (date.Equals(dr["date"]))
                {

                    listBox1.DataSource = ds.Tables[0];
                    listBox1.ValueMember = "date";
                    listBox1.DisplayMember = "dayTime";
                }
                else
                {
                    //listBox1.Items.Clear();
                    listBox1.DataSource = defaultList;
                }                                                          
          }

        }
        catch (Exception ex)
        {
            MessageBox.Show("Cannot open connection ! ");
        }
    }

what does stepping through the loop give you? what does it show for date and dr["date"] — BugFinder, Mar 01 '16 at 14:34
Console.WriteLine(dr["date"]); inside foreach gives me 20160322 which is the same is the selected date in monthcalendar (date). It Changes the datasource, but after a new date selected which not matching dr[date] the old datasource is not getting back, still the new one from the column is in listbox — Sivar, Mar 01 '16 at 14:50
I must admit, the cursory glance says to me, all lines should match - because thats what your SQL code asks for.. because you have a foreach, if you had 4 items and 3rd one didnt match, listbox1 would the data source from the sql - if there were no matches... you wouldnt have entered the foreach - as there are no rows, so Im guessing that datasource=defaultlist is never reached because it always matches — BugFinder, Mar 01 '16 at 15:01
the datasource=defaultlist is reached if I just have it like; if (date.Equals("20160322")) { listBox1.DataSource = ds.Tables[0]; listBox1.ValueMember = "date"; listBox1.DisplayMember = "dayTime"; } else { listBox1.DataSource = defaultList; } — Sivar, Mar 01 '16 at 15:45
exactly - the current if is inside a for loop of items you retrieved, if there are no items it cant do the else. — BugFinder, Mar 01 '16 at 15:46
so I want to grab the string: "20160322" , which is the column date in my db — Sivar, Mar 01 '16 at 15:48
so if this string is not exist in db then use DataSource = defaultList; — Sivar, Mar 01 '16 at 15:50

score 0 · Accepted Answer · answered Mar 01 '16 at 15:49

0

Your code:

foreach (DataRow dr in dtDatetime.Rows) { if (date.Equals(dr["date"])) { listBox1.DataSource = ds.Tables[0]; listBox1.ValueMember = "date"; listBox1.DisplayMember = "dayTime"; } else { listBox1.DataSource = defaultList; } }

Cannot reach the else statement if there are no items - and all items would match date if it did.. this code is where your logic error lies

you need something like

pseudo code: if rows.count>0 set datasource to ds.tables[0] else datasource=defaultlist

no for loop no point setting it 100 times if there were 100 items

answered Mar 01 '16 at 15:49

BugFinder

17,474
4
36
51

mm I see, I will try this tomorrow at work, you somethin like this; if dtDatetime.Rows.count >0 set datasource to ds.tables[0] else datasource=defaultlist without any loop? – Sivar Mar 01 '16 at 15:57
yes, for if there are rows you only need to know that there are some, you wouldnt need to set it many times to the same thing.. only the once. – BugFinder Mar 01 '16 at 16:10

How to get back a datasource for a list when an item is not equal value from database

1 Answers1