C# 显示sql数据库中的字符串

Question

我正在尝试制作一个小型通讯录，它从 mssql 数据库中获取联系人详细信息。它有 3 个表:contacts、last_talk(上次我与联系人交谈+讨论的简短描述)和另一个表(即具有前 2 个表中的两个主键)

在我显示联系人的表单(选项卡控件的选项卡)上，我添加了 2 个列表框，一个加载并显示联系人姓名，第二个列表框为我选择的每个联系人加载“最后通话”列表，具体取决于有多少个联系人我与联系人“交谈”。

现在，当我在“对话列表”列表框中选择一个字段时，我尝试在数据库中的 label.Text 和 richTextBox.Text 内部显示。

我的数据库有 3 个表:1 个用于联系人，1 个用于讨论/谈话，1 个用于在前 2 个表之间创建关系。

这是部分代码:

private void PopulateSelectedTalk()
    {
        string query = "SELECT * FROM LastTalk WHERE Id = @ID";

        using (connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(query, connection))
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            command.Parameters.Add("@ID", SqlDbType.Int);
            command.Parameters["@ID"].Value = lstConversationList.SelectedValue;


            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                lblLastTalk.Text = rdr["LastTalkSubject"].ToString();
                rtxtLastTalkDescription.Text = rdr["LastTalkDescription"].ToString();

            }
            rdr.Close();
            connection.Close();

        }
    }
    private void lstConversationList_SelectedIndexChanged(object sender, EventArgs e)
    {
        PopulateSelectedTalk();
    }

我在这里发布了一个非常相似的问题:C# Displaying a sql database object's name in a label.Text property on "SelectedIndexChanged" Event

添加后

command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = lstConversationList.SelectedValue;

成功了。

但是，现在当我尝试使用 lstConversationList.SelectedValue; 时我收到以下异常

An exception of type 'System.InvalidCastException' occurred in System.Data.dll but was not handled in user code Additional information: Failed to convert parameter value from a DataRowView to a Int32.

此行抛出异常:SqlDataReader rdr = command.ExecuteReader();

当我在上一期的 Debug模式下将鼠标悬停在“Listbox.SelectedValue”上时，我得到的值是“1”，当我将鼠标悬停在上面发布的代码中的“Listbox.SelectedValue”上时，我得到的值是“1” “系统.DataRowView”

这是上一个问题的代码(有效):

private void PopulateContactLabels()
    {
        string query = "SELECT * FROM Contact WHERE Id = @ID";

        using (connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(query, connection))
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            command.Parameters.Add("@ID", SqlDbType.Int);
            command.Parameters["@ID"].Value = lstContactList.SelectedValue;

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                lblContactName.Text = rdr["Name"].ToString();
                lblCompany.Text = rdr["Company"].ToString();
                lblOccupation.Text = rdr["Occupation"].ToString();
                lblPhoneNumber.Text = rdr["PhoneNumber"].ToString();
                lblEmail.Text = rdr["Email"].ToString();
            }
            rdr.Close();
            connection.Close();

        }
    }

我错过了什么/做错了什么？请帮忙!

编辑:设置 lstConversationlist 中项目的代码

private void PopulateTalkList()
    {
        string query = "SELECT a.LastTalkSubject FROM LastTalk a " + "INNER JOIN ContactLastTalk b ON a.Id = b.LastTalkId " +
            "WHERE b.ContactId = @ContactId";

        using (connection = new SqlConnection(connectionString))
        using(SqlCommand command = new SqlCommand(query,connection))
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            command.Parameters.AddWithValue("@ContactId", lstContactList.SelectedValue);
            DataTable lastTalkTable = new DataTable();
            adapter.Fill(lastTalkTable);

            lstConversationList.DisplayMember = "LastTalkSubject";
            lstConversationList.ValueMember = "Id";
            lstConversationList.DataSource = lastTalkTable;
         }
    }

Answer 1

问题的根源在于，您将 lstConversationList 的 DataSource 设置为 DataTable，却忘记将其 ValueMember 属性设置为包含该列表的列的名称。从 SelectedValue 属性检索的值。

当您将 DataSource 属性设置为 DataTable 时，ListBox.Items 集合中的每个项目都是 DataRowView。如果您不设置 ValueMember 属性，则 SelectedValue 无法知道从哪一列读取其返回值。它只是返回整个 DataRowView 实例，当然，这不是您要搜索的整数。

所以当你填写lstConversationList时不要忘记写

private void PopulateTalkList()
{

    ' NOTE. you need to add also the ID field to the select query

    string query = @"SELECT a.Id, a.LastTalkSubject 
                     FROM LastTalk a INNER JOIN ContactLastTalk b 
                     ON a.Id = b.LastTalkId
                     WHERE b.ContactId = @ContactId";

    using (connection = new SqlConnection(connectionString))
    using(SqlCommand command = new SqlCommand(query,connection))
    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
    {
        command.Parameters.AddWithValue("@ContactId", lstContactList.SelectedValue);
        DataTable lastTalkTable = new DataTable();
        adapter.Fill(lastTalkTable);

        lstConversationList.DisplayMember = "LastTalkSubject";
        lstConversationList.ValueMember = "Id";
        lstConversationList.DataSource = lastTalkTable;
     }
}