Pre-select the values in a multi-select listbox based on records in query results

Question

Imagine this table:

MatchID UserID  MatchField        MatchValue
7          5    MatchInterests        3
8          5    MatchInterests        7
9          5    MatchInterests       12
10         5    MatchInterests       20
11         5    MatchInterests       26
12         2    MatchInterests        7
13         2    MatchInterests       26
14         4    MatchInterests       26
15         5    MatchStates          12
16         5    MatchStates          11
17         2    MatchStates           1
18         2    MatchStates          17
19         4    MatchStates          10

What I want to do is pre-select the MatchValues values in a listbox for User 5 for the field MatchInterests. So the resulting dataset of what I want pre-selected would look like this:

MatchID UserID  MatchField        MatchValue
7          5    MatchInterests        3
8          5    MatchInterests        7
9          5    MatchInterests       12
10         5    MatchInterests       20
11         5    MatchInterests       26

What's the best way to do this?

What I've tried to do is this:

string strSQL2 = "SELECT MatchValue FROM tmpUsermatch WHERE MatchField = 'MatchInterests' AND UserID = '" + (DT2["UserID"].ToString()) + "'";
Interests.SelectionMode = ListSelectionMode.Multiple;
using (var con = new SqlConnection(strCon1))
using (var adapter2 = new SqlDataAdapter(strSQL2, con))
   {
     DataTable dt2 = new DataTable();
     adapter2.Fill(dt2);
     foreach (DataRow row in dt2.Rows)
     {
       Interests.SelectedValue = row["MatchValue"].ToString();
     }
   }

This works, but only causes the value in the last record of the dataset to be selected and I need the value in every record to be selected.

As Requested: markup of control name Interests.

<tr>
   <td style="width:160px">
      <asp:Label ID="Label26" runat="server" AssociatedControlID="Interests">Interests:</asp:Label>
   </td>
   <td style="width:300px">
      <div id="UCStyle1">
        <asp:ListBox ID="Interests" SelectionMode="Multiple" runat="server">
        </asp:ListBox>
      </div>
   </td>
</tr>

Answer 1

You are effectively overwriting SelectedValue on each iteration of the loop which is why you are only seeing the last one selected.

You need to set the Selected property on the items themselves or use the ListBox.SetSelected() method. There is an example on the method's documentation page.

So, instead of

Interests.SelectedValue = row["MatchValue"].ToString();

you would have

Interests.SetSelected(x, true);

where x is the index of the list item you want to select. You might need to work out the index by e.g. getting the item based on row["MatchValue"] if you don't have the indices available at hand.