Three tiers select date from two tables

Question

I have tried googling a lot of things, but couldn't find the answer - so I was hoping somebody could help me out!

What I'm trying to do: On my winform application I need to select the computername through a combobox, upon selection, my listbox will be populated with the some data about the computer (softwarename, version and stuff)

The combobox is working but I only get the id, not all the other fields.

My Listview, which is using the database call, listed bellow:

clSoftwarePerPC SF = new clSoftwarePerPC();
DataTable DT = SF.SelectSoftware(ZoekId);



// voor iedere rij een nieuw nummer geven (r)
for (int r = 0; r < DT.Rows.Count; r++)
{
    LVI = new ListViewItem();
    // cdnummer als titel
    //LVI.Text = (string)(DT.Rows[r]["idComputer"]);
    LVI.Text = ((string)(DT.Rows[r]["IDInstallatie"]).ToString());
    // titels toevoegen in deze kolom
    LVI.SubItems.Add((string)(DT.Rows[r]["SoftwareNaam"]));
    LVI.SubItems.Add((string)(DT.Rows[r]["Ontwikkelaar"]));
    LVI.SubItems.Add((string)(DT.Rows[r]["Omschrijving"]));
    LVI.SubItems.Add((string)(DT.Rows[r]["Versie"]));
    LVI.SubItems.Add(((string)(DT.Rows[r]["UpdateDatum"]).ToString()));
    LVI.Tag = (((string)(DT.Rows[r]["IDInstallatie"]).ToString()));
    // alle opgevraagde velden weergeven
    lv.Items.Add(LVI);
}
// wanneer er records zijn
if (DT.Rows.Count > 0)
{
    // eerste rij selecteren
    lv.Items[0].Selected = true;
    lv.Select();
}

My database call (working and tested) / clSoftwarePerPC:

public DataTable SelectSoftware(string ZoekId)
{
    // selecteren van alle inhoud van tabel Computers en orderen op Merk naam
    // string SQL = "select * from SoftwareOpComputer order by IDComputer where Model = '" + ZoekId + "'";
    string SQL = "select * from Software, SoftwareOpComputer where software.IDSoftware = SoftwareOpComputer.IDSoftware and SoftwareOpComputer.IDComputer =  '" + ZoekId + "'";
    // uitoveren van query
    return clDatabase.executeSelect(SQL);
}

* the select is now: select * from [table names] I've tried to use the full location like this one: Software.Version, but this didn't work either. The ZoekId is the value from the combobox where I selected my computer.

The database: my databse looks like this: pbs.twimg.com/media/BJw-wD9CMAACZiO.jpg:large I need fields like: SoftwareOpComputer.Versie, SoftwareOpComputer.UpdateDatum, Software.Softwarenaam, Software.Ontwikkelaar.

When I use this method and use only one table (the other pages of my application) it works, but when I use this screen and need 2 tables it doesn't work.

Answer 1

I will try to use a join instead.

 string SQL = "select c.Versie, c.UpdateDatum, s.Softwarenaam, s.Ontwikkelaarfrom " +
              "FROM Software s INNER JOIN SoftwareOpComputer c " + 
              "ON s.IDSoftware = c.IDSoftware " + 
              "WHERE c.IDComputer =  '" + ZoekId + "'";

This will produce a join between Software and SotwareOpComputer. It returns all the rows from Software and SoftwareOpComputer tables that have a matched ID excluding the rows that don't have a matching ID and then the WHERE condition limits the output.

Unfortunately, you use a method clDatabase.executeSelect that doesn't seem to allow to pass a parameter to avoid the string concatenation. I suggest you to search about Parametrized query and Sql Injection to realize how this code is weak

Here some documentation on JOIN

Another aspect that I wish to improve is the loop that add the rows to the ListView

foreach (DataRow row in DT.Rows)
{
    LVI = new ListViewItem();
    LVI.Text = row.Field<string>("IDInstallatie"));
    LVI.SubItems.Add(row.Field<string>("SoftwareNaam"));
    .... etc ... 
}