C# Using value from Listbox1 for SQL Query to filter Listbox2

Question

I have three SQL Tables - Team (Id, Name), Player (Id, Name) and TeamPlayer (Id, TeamID, PlayerID). I also have two ListBoxes on my Form and want to filter the 2nd ListBox when an Item is selected on the 1st ListBox.

This is mostly setup but I'm having issues where the code is not liking the value being passed to it in the LstTeams_SelectedIndexChanged method at the commented lines.

private void Form1_Load(object sender, System.EventArgs e)
    {
        showTeams();
    }

    private void showTeams()
    {
        // Create the SQL Query, and an SqlDataAdapter using this query and sqlConnection declared earlier
        string query = "select * from Team";
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);

        // Get the 'Team' Table and Fill it
        DataTable teamTable = new DataTable();
        sqlDataAdapter.Fill(teamTable);

        // Populate the 'Team' ListBox with the 'Team' Table
        lstTeams.DataSource = teamTable.DefaultView;
        lstTeams.DisplayMember = "Name";
        lstTeams.ValueMember = "Id";
    }

    private void LstTeams_SelectedIndexChanged(object sender, System.EventArgs e)
    {
        string TeamID = lstTeams.GetItemText(lstTeams.SelectedValue);
        MessageBox.Show("TeamID: " + TeamID);

        // Create the SQL Query, and an SqlDataAdapter using this query and sqlConnection declared earlier
        string query = "select * from Player p inner join TeamPlayer tp on p.Id = tp.PlayerID where tp.TeamID = @TeamID";

        SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
        sqlCommand.Parameters.AddWithValue("@TeamID", lstTeams.SelectedValue); // this
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand); // causes the dataadapter to error

        // Get the 'Team' Table and Fill it
        DataTable teamTable = new DataTable();
        sqlDataAdapter.Fill(teamTable);

        // Populate the 'Team' ListBox with the 'Team' Table
        lstPlayers.DataSource = teamTable.DefaultView;
        lstPlayers.DisplayMember = "Name";
    }

My Error is:

System.ArgumentException: 'No mapping exists from object type System.Data.DataRowView to a known managed provider native type.'

I've looked up some solutions but have been unable to get a working result.

Thank you for any help provided :).

You mapped a DataSource so the SelectedValue is going to be a DataRowView object. You need to cast it to a number. And dont use AddWithValue — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Aug 12, 2020 at 16:39
Use your variable TeamID for your param value, but you need to cast it as an int first if the column is an int. Also specify the datatype for your param type. — Trevor
– Trevor, Commented Aug 12, 2020 at 16:42

Connor Osborne · Accepted Answer · 2020-08-12 22:15:23Z

1

After further research and some direction from comments made to my question, I have found success with the following:

DataRowView data = lstTeams.SelectedItem as DataRowView;
int TeamID = int.Parse(data["Id"].ToString());

string query = "select * from Player p inner join TeamPlayer tp on p.Id = tp.PlayerID where tp.TeamID = " + TeamID;

Inside the ListBox1.SelectedIndexChanged method.

answered Aug 12, 2020 at 22:15

Connor Osborne

212 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Using value from Listbox1 for SQL Query to filter Listbox2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related