0

my first question on stackoverflow since I didn't find any solution yet:

I'm working on a simple password display tool which takes data from an MS Access DB (no proper DB available...)

The Access DB has two tables. A PC table and a passwords child table, linked via MAC Adress.

The Program displays a key/value list in a listbox with all the PC names retrieved via oledb.

key is the PC name, value is the MAC adress. This works fine.

        void cmdGetPCs()
    {
        OleDbDataAdapter daPCs = new OleDbDataAdapter();
        OleDbConnection vcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source=H:\XXX\XXX\MYACCESSFILE.accdb;Jet OLEDB:Database Password=[REDACTED]");
        const string q = "SELECT SISSI & ' (' & IP & ') - ' & DESCRIPTION as LONGDESCR, MAC from PC_LIST WHERE active = true order by sissi, ip"; //use & instead of + to have blankspace instead of null values for displaying PCs without Sissi because CONCAT doesn't work (MS Access....)
        vcon.Open();
            daPCs.SelectCommand = new OleDbCommand(q, vcon);
        vcon.Close();

        DataSet dsPC = new DataSet("PCs");

        daPCs.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        daPCs.Fill(dsPC, "tblPCs");
        DataTable dtPC = dsPC.Tables["tblPCs"];

        var PCList = new List<PCInfo>();
        foreach (DataRow dtRow in dtPC.Rows)
        {
            PCList.Add(new PCInfo() { LONGDESCR = dtRow["LONGDESCR"].ToString(), MAC = dtRow["MAC"].ToString()  });
        }
        lstPCs.DisplayMember = "LONGDESCR";
        lstPCs.ValueMember = "MAC";
        lstPCs.DataSource = PCList;
    }

So ok, I have a list filled with PC descriptions. Now when I select an item from the Listbox I have a datagridview element in the same window that should be filled with the password info:

    void ListBox1SelectedIndexChanged(object sender, EventArgs e)
    {
        groupBox1.Text = lstPCs.GetItemText(lstPCs.SelectedItem);
        string x = lstPCs.GetItemText(lstPCs.SelectedValue);
        //Dataset_get(x);
    }

This still works fine as long as Dataset_get is commented out. First line displays the visible key of the item box as a header of a groupbox and string x is the MAC adress (=ItemList Value) of the selected Item.

As soon as I activate Dataset_get(x) and launch the program the ListBox is populated, but all items get replaced by item.toString() placeholders it seems.

Listbox just looks like:

  • myprogramname.MainForm+PCInfo
  • myprogramname.MainForm+PCInfo
  • myprogramname.MainForm+PCInfo
  • myprogramname.MainForm+PCInfo
  • myprogramname.MainForm+PCInfo
  • etc..

The first two lines (groupbox and string x) of the indexchange still work fine and display the right values. Also Dataset_get itself is working fine and populating the Datagridview. So I can either have a user readable List box without filled datagrid view or a broken listbox with a filled datagrid view... Obviously I need a readable list box with a filled datagrid view ;)

I narrowed down the issue in Dataset_get to the point that it starts to get broken as soon as the Fill line of the datagrid view data retrieving is called:

    private void Dataset_get(string mymac)
    {
        OleDbDataAdapter daPass = new OleDbDataAdapter();
        OleDbConnection vconp = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source=H:\XXX\XXX\MYACCESSFILE.accdb;Jet OLEDB:Database Password=[REDACTED]");
        string qp = "";
        switch (mypermissions)
        {
            case "ADMIN":
                qp = "SELECT USER_TYPE, HAS_ADMIN, USER_NAME, PASSWORD, ID FROM PASSWORDS WHERE ID = '" + mymac + "' ORDER BY user_type";break;
            case "USER":
                qp = "SELECT p.USER_TYPE, p.HAS_ADMIN, p.USER_NAME, p.PASSWORD, p.ID FROM PASSWORDS p, PC_LIST pc WHERE p.ID = '" + mymac + "' and p.ID = pc.MAC and (pc.x_plant like '%USER%' or (ucase(p.user_type) not like '%ADMIN%')) ORDER BY p.user_type";break;
            default: break;
        }
        vconp.Open();
            daPass.SelectCommand =  new OleDbCommand(qp, vconp);
        vconp.Close();

        DataSet dsPass = new DataSet("Passwords");
        daPass.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        daPass.Fill(dsPass,"tblPass"); //REPLACEMENT OF LIST ITEMS IS TRIGGERED BY THIS LINE

        DataTable dtPass = dsPass.Tables["tblPass"];
        dataGridView1.DataSource = dtPass;
    }

Please help me... Thanks in advance!

PS: The datagridview is in a group box while the listbox isn't, but that doesn't make any difference I guess.

doomi
  • 53
  • 7
  • I seem to have it working with having the listbox populated with a list instead of a datasource: List> test = new List>(); foreach (DataRow dtRow in dtPC.Rows) { test.Add(new KeyValuePair(dtRow["LONGDESCR"].ToString(), dtRow["MAC"].ToString())); } lstPCs.DataSource = test; however (beware: noob question): how can I only display the key in the listbox instead of [key, value]? – doomi Oct 11 '16 at 14:20

1 Answers1

0

Solved it with the help of Reza Aghaeis answer at Get the value for a listbox item by index

    public static class ListControlExtensions
    {
        public static object GetItemValue(this ListControl list, object item)
        {
            if (item == null)
                throw new ArgumentNullException("item");

            if (string.IsNullOrEmpty(list.ValueMember))
                return item;

            var property = TypeDescriptor.GetProperties(item)[list.ValueMember];
            if (property == null)
                throw new ArgumentException(
                    string.Format("item doesn't contain '{0}' property or column.",
                    list.ValueMember));
            return property.GetValue(item);
        }
    }

//.........................................

    void cmdGetPCs()
    {
        const string q = "SELECT SISSI & ' (' & IP & ') - ' & DESCRIPTION as LONGDESCR, MAC from PC_LIST WHERE active = true order by sissi, ip"; //use & instead of + to have blankspace instead of null values for displaying PCs without Sissi because CONCAT doesn't work (MS Access....)

        OleDbDataAdapter da = new OleDbDataAdapter();
        DataSet ds = new DataSet();

        vcon.Open();
            da.SelectCommand = new OleDbCommand(q, vcon);
        vcon.Close();

        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        da.Fill(ds, "tblPCs");
        DataTable dtPC = ds.Tables["tblPCs"];

        foreach (DataRow dtRow in dtPC.Rows)
        {
            lstPCs.Items.Add(new KeyValuePair<String, String>(dtRow["LONGDESCR"].ToString(), dtRow["MAC"].ToString()));
        }
        lstPCs.DisplayMember = "Key";
        lstPCs.ValueMember = "Value";
    }

//.........................................
    private void Dataset_get(string mymac)
    {
        string qp = "";
        switch (mypermissions)
        {
            case "ADMIN":
                qp = "SELECT USER_TYPE, HAS_ADMIN, USER_NAME, PASSWORD FROM PASSWORDS WHERE ID = '" + mymac + "' ORDER BY user_type";break;
            case "USER":
                qp = "SELECT p.USER_TYPE, p.HAS_ADMIN, p.USER_NAME, p.PASSWORD FROM PASSWORDS p, PC_LIST pc WHERE p.ID = '" + mymac + "' and p.ID = pc.MAC and (pc.x_plant like '%USER%' or (ucase(p.user_type) not like '%ADMIN%')) ORDER BY p.user_type";break;
            default: break;
        }

        OleDbDataAdapter dapass = new OleDbDataAdapter();
        DataSet dspass = new DataSet();
        vcon.Open();
            dapass.SelectCommand =  new OleDbCommand(qp, vcon);
        vcon.Close();

        dapass.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        dapass.Fill(dspass,"tblPass");
        DataTable dtPass = new DataTable();
        dtPass = dspass.Tables["tblPass"];
        dataGridView1.DataSource = dtPass;
    }
//.........................................
    void ListBox1SelectedIndexChanged(object sender, EventArgs e)
    {
        groupBox1.Text = lstPCs.GetItemText(lstPCs.SelectedItem);
        string x = lstPCs.GetItemValue(lstPCs.SelectedItem).ToString();
        Dataset_get(x);
    }

OleDbConnection vcon is shared.

Community
  • 1
  • 1
doomi
  • 53
  • 7