I want to show all books from selected writers from checkedlistbox on chart. Problem is that I need to merge two columns and it throws exception on line da.Fill(dt1): Invalid column name ime_prezime

            conn.Open();

            //chart1.Series.Clear();
            //string predmeti = "";
            string result = "";
            string[] sAutori;
            foreach (object itemChecked in checkedListBox1.CheckedItems)
            {
                DataRowView castedItem = itemChecked as DataRowView;
                string companyName = (string)castedItem["ime_prezime"];
                //int? id = castedItem["autor_id"];
                result  = $@"'({companyName})'"   ",";
            }
            
            if (checkedListBox1.CheckedItems.Count < 2)
            {
                MessageBox.Show("Morate izabrati bar dva pisca!");
            }
            else
            {
                result = result.Substring(0, result.Length - 1);
                //string result = predmeti.Substring(0, predmeti.Length - 1);
                cmd1.CommandText = $@"SELECT a.ime   ' '   a.prezime AS ime_prezime, COUNT(b.knjiga_id) FROM AUTORI AS a JOIN autori_izdanja AS b ON a.autor_id = b.autor_id WHERE ime_prezime IN ({result}) GROUP BY a.ime";
                da.SelectCommand = cmd1;
                da.Fill(dt1);
                try {
                    foreach (DataRow row in dt1.Rows)
                    {
                        chart1.Series[0].Points.AddXY(row[0], row[1]);
                    }
                    label2.Text = result;
                    button1.Enabled = false;
                }
                catch (Exception g) {

                    MessageBox.Show(g.Message);
                }
                
            }
            conn.Close();

Can someone solve this please...

CodePudding user response：

           conn.Open();

            //chart1.Series.Clear();
            //string predmeti = "";
            string result = "";
            string[] sAutori;
            foreach (object itemChecked in checkedListBox1.CheckedItems)
            {
                DataRowView castedItem = itemChecked as DataRowView;
                string companyName = (string)castedItem["ime_prezime"];
                //int? id = castedItem["autor_id"];
                result  = "'" companyName "'"   ",";
            }
            
            if (checkedListBox1.CheckedItems.Count < 2)
            {
                MessageBox.Show("Morate izabrati bar dva pisca!");
            }
            else
            {
                if(result != string.Empty ){
                    result = result.Substring(0, result.Length - 1);
                }
                //string result = predmeti.Substring(0, predmeti.Length - 1);
                cmd1.CommandText = $@"SELECT a.ime   ' '   a.prezime AS ime_prezime, COUNT(b.knjiga_id) FROM AUTORI AS a JOIN autori_izdanja AS b ON a.autor_id = b.autor_id WHERE a.ime   ' '   a.prezime IN ({result}) GROUP BY a.ime    ' '   a.prezime";
                da.SelectCommand = cmd1;
                da.Fill(dt1);
                try {
                    foreach (DataRow row in dt1.Rows)
                    {
                        chart1.Series[0].Points.AddXY(row[0], row[1]);
                    }
                    label2.Text = result;
                    button1.Enabled = false;
                }
                catch (Exception g) {

                    MessageBox.Show(g.Message);
                }
                
            }

CodePudding user response：

        SqlCommand cmd;
        SqlCommand cmd1;
        SqlDataAdapter da;
        DataTable dt;
        DataTable dt1;

        void Connection()
        {
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=DESKTOP-QEDL8JA\SQLEXPRESS;Initial Catalog=EvidencijaKnjiga;Integrated Security=True;";
            cmd = new SqlCommand();
            cmd1 = new SqlCommand();
            cmd.Connection = conn;
            cmd1.Connection = conn;
            dt = new DataTable();
            dt1 = new DataTable();
            da = new SqlDataAdapter();
        }

        void stats()
        {
            Connection();
            //string select = "SELECT";
        }
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            Connection();
            cmd.CommandText = "SELECT autor_id, ime   ' '   prezime AS ime_prezime FROM autori ORDER BY ime_prezime";
            da.SelectCommand = cmd;
            da.Fill(dt);
            checkedListBox1.DataSource = dt;
            checkedListBox1.DisplayMember = "ime_prezime";
            checkedListBox1.ValueMember = "autor_id";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            chart1.Series[0].Points.Clear();
            button1.Enabled = true;
            //chart1.Series.Clear();
            //chart1.ChartAreas.Clear();
            checkedListBox1.ClearSelected();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Connection();
            conn.Open();
            /*cmd.CommandText = "SELECT autor_id, ime   ' '   prezime AS ime_prezime FROM autori ORDER BY ime_prezime";
            da.SelectCommand = cmd;
            da.Fill(dt);*/
            //chart1.Series.Clear();
            //string predmeti = "";
            string result = "";
            //string sAutori;
            foreach (object itemChecked in checkedListBox1.CheckedItems)
            {
                DataRowView castedItem = itemChecked as DataRowView;
                string companyName = (string)castedItem["ime_prezime"];
                //int? id = castedItem["autor_id"];
                result  = "'"   companyName   "'"   ",";
            }

            if (checkedListBox1.CheckedItems.Count < 2)
            {
                MessageBox.Show("Morate izabrati bar dva pisca!");
            }
            else
            {
                if (result != string.Empty)
                {
                    result = result.Substring(0, result.Length - 1);
                }
                //string result = predmeti.Substring(0, predmeti.Length - 1);
                cmd1.CommandText = $@"SELECT a.ime   ' '   a.prezime AS ime_prezime, COUNT(b.knjiga_id) FROM AUTORI AS a JOIN autori_izdanja AS b ON a.autor_id = b.autor_id WHERE a.ime   ' '   a.prezime IN ({result}) GROUP BY a.ime   ' '   a.prezime";
                da.SelectCommand = cmd1;
                da.Fill(dt1);
                //dataGridView1.DataSource = dt1;
                try
                {
                    foreach (DataRow row in dt1.Rows)
                    {
                        chart1.Series[0].Points.AddXY(row[0], row[1]);
                    }
                    label2.Text = result;
                    button1.Enabled = false;
                }
                catch (Exception g)
                {
                    MessageBox.Show(g.Message);
                }

            }
            conn.Close();
        }```
This is the final code. I just ujusted some things and it worked. Thank you so much on help :D