Аргумент 1: невозможно преобразовать из System.Data.SqlClient.SqlDataAdapter в System.Data.DataSet и не распознает имя столбца
Я получаю следующее сообщение об ошибке:
Argument 1: cannot convert from 'System.Data.SqlClient.SqlDataAdapter' to 'System.Data.DataSet' and doesn't recognize the column name
Я не совсем понимаю, что делаю неправильно. Кроме того, я продолжал получать сообщение об ошибке, говоря, что он не распознает имена столбцов (для операторов else if). Это сделано в Visal Studio 2017. Мы будем благодарны за любую помощь.
using System.Windows.Forms;
using System.Data.SqlClient;
пространство имен WindowsFormsApp2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
if (comboBox1.Text == "Plan_NAME")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [Plan_NAME] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "Admin_Name")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [Admin_Name] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "EIN_Number")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [EIN_Number] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "Number_Participants")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and ([Number_Participants] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "City")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [City] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "State")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [State] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "ZipCode")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [ZipCode] LIKE '%" + textBox1.Text + "%'", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "Total_Assets")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and ([Total_Assets] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
else if (comboBox1.Text == "Net_Assets")
{
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2)) ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and ([Net_Assets] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void label4_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged_1(object sender, EventArgs e)
{
}
private void textBox2_TextChanged_1(object sender, EventArgs e)
{
}
private void textBox3_TextChanged_1(object sender, EventArgs e)
{
}
}
}
Почему в Python есть оператор "pass"?
Оператор pass в Python - это простая концепция, которую могут быстро освоить даже новички без опыта программирования.
Некоторые методы, о которых вы не знали, что они существуют в Python
Python - самый известный и самый простой в изучении язык в наши дни. Имея широкий спектр применения в области машинного обучения, Data Science,...
Основы Python Часть I
Вы когда-нибудь задумывались, почему в программах на Python вы видите приведенный ниже код?
LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа
Алиса и Боб имеют неориентированный граф из n узлов и трех типов ребер:
Оптимизация кода с помощью тернарного оператора Python
И последнее, что мы хотели бы показать вам, прежде чем двигаться дальше, это
Советы по эффективной веб-разработке с помощью Python
Как веб-разработчик, Python может стать мощным инструментом для создания эффективных и масштабируемых веб-приложений.
0
0
78
1
Ответы 1
Более чистый и лаконичный код, вероятно, получил бы более быстрый ответ.
Одна проблема, которую я вижу, касается трех ситуаций, когда вы добавляете термин where на основе числа (Number_Participants, Total_Assets, Net_Assets), вы включаете '%" ... "%' с любой стороны, что неприменимо при использовании BETWEEN. Я переписал ваш базовый код ниже, чтобы сжать его, применить некоторый общий стиль SQL и, надеюсь, сделать его более читабельным, но пока пропустил параметризацию.
Посмотрите, решит ли это вашу проблему.
string selectTerm = @"SELECT CONCAT(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) [Plan_NAME]
, CONCAT(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) [Admin_Name]
, CONCAT(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) [EIN_Number]
, CONCAT(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) [Plan_Number]
, CONCAT(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) [Number_Participants]
, CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1, dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2, dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1], dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) [Address]
, CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_CITY, dbo.[5500_SF_NEW].[SF_SPONS_US_CITY]) [City]
, CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE]) [State]
, CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) [ZipCode]
, (ISNULL(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0) + ISNULL(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0)) [Total_Assets]
, (ISNULL(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0) + ISNULL(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) [Net_Assets]
, (ISNULL(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0) + ISNULL(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) [Total_Liabilities]
FROM dbo.[5500]
FULL JOIN dbo.sch_h ON dbo.[5500].ack_id = dbo.sch_h.ack_id
FULL JOIN dbo.SCH_I ON dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID
FULL JOIN dbo.[5500_SF_NEW] ON dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID
WHERE dbo.[5500_SF_NEW].SF_PLAN_NAME IN (SELECT Sf_Plan_name FROM Temp_Targets_SF)
OR dbo.[5500].PLAN_NAME IN (SELECT Plan_Name FROM Temp_Targets)";
string whereTerm;
if (comboBox1.Text == "Plan_NAME") whereTerm = " AND [Plan_NAME] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Admin_Name") whereTerm = " AND [Admin_Name] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "EIN_Number") whereTerm = " AND [EIN_Number] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Number_Participants") whereTerm = " AND [Number_Participants] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";
else if (comboBox1.Text == "City") whereTerm = " AND [City] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "State") whereTerm = " AND [State] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "ZipCode") whereTerm = "AND [ZipCode] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Total_Assets") whereTerm = " AND [Total_Assets] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";
else if (comboBox1.Text == "Net_Assets") whereTerm = " AND [Net_Assets] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";
SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter(selectTerm + whereTerm, con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
Другие вопросы по теме
Извлечение байта из SQL Python, возвращающий массив байтов
VBA Sub, похоже, пропускает объявления подключения
Объединение отсутствующих дат из календарной таблицы
SQLite3-запрос медленный в Swift
Создание sql-запроса, который ссылается на мета-значение
Как заполнить значения столбца
T-SQL преобразование строки XML в данные таблицы
Заменить часть строки, которая содержит любые результаты из столбца в таблице
Почему group_concat возвращает тоже запись для первой строки?
Как повторить эхо, если в PHP не найдены данные
Похожие вопросы
Извлечение байта из SQL Python, возвращающий массив байтов
Можно ли передать find_packages () несколько исходных директорий с помощью setuptools?
Разделите каждый элемент во вложенном массиве Python на целое число
Кластеризация K-средних между столбцами в наборе данных
Добавление разрыва строки в метки осей трехмерного графика - plotly
Автозаполнение пути к каталогу / папке в Atom (для python)
Python datetime и pandas to_datetime имеют разные выходные данные при покрытии одной и той же метки времени
Ошибка с numpy при использовании opencv с python с использованием некоторых методов хеширования для сравнения подписей
Как разобрать отрицательный индекс для объекта среза в argparse.ArgumentParser
Как избежать предупреждения от GLib с помощью GLib.source_remove ()