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.
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.