时间戳

首页 > 解决方案 > 根据多个文本框上显示的文本选择列

sql-server - 根据多个文本框上显示的文本选择列

问题描述

我正在尝试创建用户报告生成器功能。

我有一个列表，CheckBoxes什么时候IsChecked会显示SQL Server数据库中表中的列名。然后我试图在我的中使用这些名称SQL Statement来生成查询结果。

我面临的问题是他用户无法选择各种列名，因为,SQL 语句中需要 a 来分隔列名。如果CheckBox未选中，我不希望显示该列。

这是我的代码，它将执行查询结果并填充DataGrid我收到异常错误的位置，如果两者textboxes都填充了文本。如果只有一个被填满，它就可以工作。

private void butn_ExecuteQuery_Click(object sender, RoutedEventArgs e)
{
    try
    {
        string connectionString = ("Data Source=WINDOWS-B1AT5HC\\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");
        SqlConnection connection = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand("SELECT " + txtAccount.Text + txtAssignedTo.Text + " FROM [hb_Disputes] CROSS JOIN hb_FinAdj INNER JOIN Users ON hb_Disputes.ASSGNTO = Users.KY_USER_ID WHERE (hb_Disputes.OPENED >=@OPENED AND hb_Disputes.OPENED < @CLOSED) AND (Users.TX_EMPLOYEE =@query)", connection);
        //EMP Name
        cmd.Parameters.AddWithValue("@query", txt_Query.Text);

        cmd.Parameters.Add("@OPENED", SqlDbType.DateTime).Value = dtepicker_Open.Text;
        cmd.Parameters.Add("@CLOSED", SqlDbType.DateTime).Value = dtepicker_DateResolved.Text;

        connection.Open();
        DataTable dt = new DataTable();

        dt.Load(cmd.ExecuteReader());
        connection.Close();

        dt_ReportList.DataContext = dt;
        dt_ReportList.SelectedIndex = 0;

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

这是处理时的CheckBoxes代码checked和unchecked

private void chckAccount_Checked(object sender, RoutedEventArgs e)
{
    txtAccount.Text = "[hb_Disputes].[ACCOUNT]";
}

private void chckAccount_Unchecked(object sender, RoutedEventArgs e)
{
    txtAccount.Text = " ";
}

private void chckAssignedTo_Checked(object sender, RoutedEventArgs e)
{
    txtAssignedTo.Text = "[hb_Disputes].[ASSGNTO]";
}

private void chckAssignedTo_Unchecked(object sender, RoutedEventArgs e)
{
    txtAssignedTo.Text = " ";
}

标签： sql-serverwpfselectcheckbox

解决方案

您可以检查两个文本框是否有文本，如果有则添加逗号：

private void butn_ExecuteQuery_Click(object sender, RoutedEventArgs e)
{
    try
    {
        string connectionString = ("Data Source=WINDOWS-B1AT5HC\\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");
        SqlConnection connection = new SqlConnection(connectionString);

        string account = txtAccount.Text;
        string assignedTo = txtAssignedTo.Text;
        if (account.Length > 1 && assignedTo.Length > 1)
            account += ",";
        SqlCommand cmd = new SqlCommand("SELECT " + account + assignedTo + " FROM [hb_Disputes] CROSS JOIN hb_FinAdj INNER JOIN Users ON hb_Disputes.ASSGNTO = Users.KY_USER_ID WHERE (hb_Disputes.OPENED >=@OPENED AND hb_Disputes.OPENED < @CLOSED) AND (Users.TX_EMPLOYEE =@query)", connection);

        ...

    }
    catch (Exception ex)
    {
        ...
    }
}

推荐阅读