mercredi 3 juin 2020

Select columns based on text displayed on multiple TextBoxes

I am trying to create a user report generator feature.

I have a list of CheckBoxes that when IsChecked will show the SQL Server Column name from a table in from the database. I am then trying to use those names in my SQL Statement to generate the query results.

The issue I am facing is he user cannot select various column names because a , is needed in the SQL Statement to separate the column names. If the CheckBox is not checked, I do not want the column to display.

Here is my code that will execute the query results and populate the DataGrid where I am getting an exception error if both textboxes are filled with text. It works if only one is filled.

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);
    }
}

Here is the code to handles when the CheckBoxes are checked and 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 = " ";
}



Aucun commentaire:

Enregistrer un commentaire