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.

Is `where software.IDSoftware = SoftwareOpComputer.IDSoftware` supposed to be a join? Or is `SoftwareOpComputer.IDSoftware` set somewhere else? — devilfish17, May 08 '13 at 19:04
Hello, i have heard from joins, but don't know how to deal with that. — RunnicFusion, May 08 '13 at 19:04
my databse looks like this: https://pbs.twimg.com/media/BJw-wD9CMAACZiO.jpg:large I need fields like: SoftwareOpComputer.Versie, SoftwareOpComputer.UpdateDatum, Software.Softwarenaam, Software.Ontwikkelaar. — RunnicFusion, May 08 '13 at 19:06
@devilfish17: in my databse the tables are linked to eachother by: software.IDSoftware = SoftwareOpComputer.IDSoftware. So i could use the softwarename from software and the version from softwareopcomputer (other table) — RunnicFusion, May 08 '13 at 19:08

Steve · Accepted Answer · 2013-05-08T19:20:38.003

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

Thanks, but how to select the values? this doens't work: LVI.SubItems.Add((string)(DT.Rows[r]["s.SoftwareNaam"])); ?. my select: string SQL = "select * FROM Software s INNER JOIN SoftwareOpComputer c ON s.IDSoftware = c.IDSoftware " + "where c.IDComputer = '" + ZoekId + "'"; — RunnicFusion, May 09 '13 at 17:12
No need to prefix with `s.` when reading the value from the DataRow. Just `SoftwareNaam` is enough. When debugging try to look at the Columns collection of the DataTable object to check for the correct column names. Then, you can try the query using the appropriate interactive interface provided by your database (For example Sql Server Management Studio for Sql Server, or MS-Access for MDB files etc..) Also look at the changed code that loops over the table rows without using an indexer `(r)` — Steve, May 09 '13 at 18:02

Three tiers select date from two tables

1 Answers1