locked
getting error "Unable to cast object of type 'System.Data.DataRowView' to type 'System.String'." RRS feed

  • Question

  • Hi

    I have created windows application in c#.inside datagridview i have combo box.

    if i click combo box  selectedindexchanged event is fired and i want to get selected value of combobox.

      i click combobox first time and  i get  selected value. if i click combobox second time , i get error saying

    "Unable to cast object of type 'System.Data.DataRowView' to type 'System.String'."

    Please help me to fix the error.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApplication2
    {
        public partial class Form1 : Form
        {
    
            SqlConnection sqlCon = new SqlConnection("connection");
            SqlCommandBuilder sqlCommand = null;
            SqlDataAdapter sqlAdapter = null;
            DataSet dataset = null;
    
    
            public Form1()
            {
                InitializeComponent();
            }
            private void LoadData()
            {
                try
                {
                    sqlAdapter = new SqlDataAdapter("SELECT *, 'Delete' AS [Delete] FROM Employees", sqlCon);
                    sqlCommand = new SqlCommandBuilder(sqlAdapter);
    
                    sqlAdapter.InsertCommand = sqlCommand.GetInsertCommand();
                    sqlAdapter.UpdateCommand = sqlCommand.GetUpdateCommand();
                    sqlAdapter.DeleteCommand = sqlCommand.GetDeleteCommand();
    
                    dataset = new DataSet();
                    sqlAdapter.Fill(dataset, "Employees");
                    dgvEmployee.DataSource = null;
                    dgvEmployee.DataSource = dataset.Tables["Employees"];
    
                    for (int i = 0; i < dgvEmployee.Rows.Count; i++)
                    {
                        DataGridViewLinkCell linkCell = new DataGridViewLinkCell();
                        dgvEmployee[6, i] = linkCell;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
    
    
    
    
            private void Form1_Load(object sender, EventArgs e)
            {
                LoadData();
                DataTable data = new DataTable();
    
                data.Columns.Add(new DataColumn("Value", typeof(string)));
                data.Columns.Add(new DataColumn("Description", typeof(string)));
    
                data.Rows.Add("item1", "123");
                data.Rows.Add("item2", "234");
                data.Rows.Add("item3", "245");
                var column = new DataGridViewComboBoxColumn();
                column.DataSource = data;
                column.ValueMember = "Value";
                column.DisplayMember = "Description";
    
    
                dgvEmployee.Columns.Add(column);
    
    
            }
    
            private void dgvEmployee_UserAddedRow_1(object sender, DataGridViewRowEventArgs e)
            {
                try
                {
                    int lastRow = dgvEmployee.Rows.Count - 2;
                    DataGridViewRow nRow = dgvEmployee.Rows[lastRow];
                    DataGridViewLinkCell linkCell = new DataGridViewLinkCell();
                    dgvEmployee[6, lastRow] = linkCell;
                    nRow.Cells["Delete"].Value = "Insert";
                }
                catch (Exception ex) { MessageBox.Show(ex.Message); }
            }
    
            private void dgvEmployee_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
            {
    
                try
                {
                    if (e.ColumnIndex == 6)
                    {
                        string Task = dgvEmployee.Rows[e.RowIndex].Cells[6].Value.ToString();
                        if (Task == "Delete")
                        {
                            if (MessageBox.Show("Are you sure to delete?", "Deleting...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                            {
                                int rowIndex = e.RowIndex;
                                dgvEmployee.Rows.RemoveAt(rowIndex);
                                dataset.Tables["Employees"].Rows[rowIndex].Delete();
                                sqlAdapter.Update(dataset, "Employees");
                            }
                        }
                        else if (Task == "Insert")
                        {
                            int row = dgvEmployee.Rows.Count - 2;
                            DataRow dr = dataset.Tables["Employees"].NewRow();
                            dr["LastName"] = dgvEmployee.Rows[row].Cells["LastName"].Value;
                            dr["FirstName"] = dgvEmployee.Rows[row].Cells["FirstName"].Value;
                            dr["Title"] = dgvEmployee.Rows[row].Cells["Title"].Value;
                            dr["HireDate"] = dgvEmployee.Rows[row].Cells["HireDate"].Value;
                            dr["PostalCode"] = dgvEmployee.Rows[row].Cells["PostalCode"].Value;
    
                            dataset.Tables["Employees"].Rows.Add(dr);
                            dataset.Tables["Employees"].Rows.RemoveAt(dataset.Tables["Employees"].Rows.Count - 1);
                            dgvEmployee.Rows.RemoveAt(dgvEmployee.Rows.Count - 2);
                            dgvEmployee.Rows[e.RowIndex].Cells[6].Value = "Delete";
                            sqlAdapter.Update(dataset, "Employees");
                        }
                        else if (Task == "Update")
                        {
                            int r = e.RowIndex;
                            dataset.Tables["Employees"].Rows[r]["LastName"] = dgvEmployee.Rows[r].Cells["LastName"].Value;
                            dataset.Tables["Employees"].Rows[r]["FirstName"] = dgvEmployee.Rows[r].Cells["FirstName"].Value;
                            dataset.Tables["Employees"].Rows[r]["Title"] = dgvEmployee.Rows[r].Cells["Title"].Value;
                            dataset.Tables["Employees"].Rows[r]["HireDate"] = dgvEmployee.Rows[r].Cells["HireDate"].Value;
                            dataset.Tables["Employees"].Rows[r]["PostalCode"] = dgvEmployee.Rows[r].Cells["PostalCode"].Value;
                            sqlAdapter.Update(dataset, "Employees");
                            dgvEmployee.Rows[e.RowIndex].Cells[6].Value = "Delete";
                        }
                    }
                }
                catch (Exception ex) { }
    
            }
    
    
            private void dgvEmployee_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
            {
                ComboBox combo = e.Control as
    ComboBox;
    
                if
               (combo != null)
                {
    
    
    
                    combo.SelectedIndexChanged -=
    
                      new
                 EventHandler(ComboBox_SelectedIndexChanged);
    
    
    
                  
    
    
                    combo.SelectedIndexChanged +=
    
                      new
                 EventHandler(ComboBox_SelectedIndexChanged);
    
                }
            }
    
    
    
            private
             void
             ComboBox_SelectedIndexChanged(object
             sender, EventArgs e)
            {
    
                try
                {
                    label1.Text = (string)((ComboBox)sender).SelectedValue;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
    
            }
    
    
        }
    }
    

    Thanks and Regards

    Chandran

    Sunday, July 13, 2014 3:30 PM

Answers

  • You are making it too complicated. You are manipulating both the grid rows and the data table - you should do one or the other. Almost never should you manipulate the grid rows, if you correctly bind the data to the grid then you can let the grid handle the data changes for you.

    Below is some sample code where I demonstrate using a BindingSource in between the grid and the data. It does not show using a combobox column, but again, if you configure that correctly you do not need to handle the combobox changed event (for exactly the same reason you do not handle the textboxchanged event in a regular cell). It will automatically keep the bound data in sync. All you need is a way to call Update on the SqlDataAdapter when you want to save the changes, whether they be inserts, updates or deletes. You will see this simple call in button2_Click below.

    using System;
    using System.Data;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace dgv
    {
      public partial class Form1 : Form
      {
        SqlDataAdapter da = new SqlDataAdapter();
        BindingSource bind = new BindingSource();
        DataTable datatab = new DataTable();
        SqlConnection connect = new SqlConnection();
    
        public Form1()
        {
          InitializeComponent();
        }
    
        /// <summary>
        /// Loads the data into the datagridview
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
          try
          {
            SqlConnectionStringBuilder csb = 
              new SqlConnectionStringBuilder();
    
            csb.DataSource = ".\\BOB2008R2";
            csb.InitialCatalog = "ContrivedExample";
            csb.IntegratedSecurity = true;
    
            connect.ConnectionString = csb.ConnectionString;
    
            //SElect command loads the data
            SqlCommand selectCmd = new SqlCommand(
                @"SELECT [id], [CompanyName], 
                [regionid] FROM [Companies]", connect);
    
            //Update command is run when there are rows to 
            //update - ie, you modified data
            SqlCommand updateCmd = new SqlCommand(
                @"UPDATE [Companies] SET [CompanyName] = @CompanyName, 
                [regionid] = @regionid WHERE [id] = @id", connect);
    
            updateCmd.Parameters.Add("@id", SqlDbType.Int, 4, "id");
            updateCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar,
              30, "CompanyName");
            updateCmd.Parameters.Add("@regionid", SqlDbType.Int, 4, 
              "regionid");
    
            //Runs when you add a row to the datatable
            SqlCommand insertCmd = new SqlCommand(
                @"INSERT INTO [Companies] ([CompanyName], [regionid]) 
                  VALUES(@CompanyName, @regionid)", connect);
            insertCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar,
              30, "CompanyName");
            insertCmd.Parameters.Add("@regionid", SqlDbType.Int, 4,
              "regionid");
    
            //deletes...
            SqlCommand deleteCmd = new SqlCommand(
                "DELETE FROM [Companies] WHERE [id] = @id", connect);
            deleteCmd.Parameters.Add("@id", SqlDbType.Int, 4, "id");
    
            connect.Open();
    
            da.SelectCommand = selectCmd;
            da.UpdateCommand = updateCmd;
            da.InsertCommand = insertCmd;
            da.DeleteCommand = deleteCmd;
    
            da.Fill(datatab);
    
            bind.DataSource = datatab;
            dataGridView1.DataSource = bind;
          }
          catch (SqlException sqlex)
          {
            MessageBox.Show(sqlex.Message);
          }
        }
    
        /// <summary>
        /// Updates the data. The SqlDataAdapter will 
        ///call all the 
        /// appropriate commands for any modified
        /// data it has.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, 
            EventArgs e)
        {
          da.Update(datatab);
        }
      }
    }
    


    Bob - www.ContrivedExample.com

    Sunday, July 13, 2014 6:45 PM
  • Hello,

    Check out my MSDN article on working with DataGridView ComboBox. There are two solutions, one in C# while the other is VB.NET. Both show how to retrieve the current item via SelectionChangeCommitted event.

    Key parts


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, July 13, 2014 7:56 PM

All replies

  • You are making it too complicated. You are manipulating both the grid rows and the data table - you should do one or the other. Almost never should you manipulate the grid rows, if you correctly bind the data to the grid then you can let the grid handle the data changes for you.

    Below is some sample code where I demonstrate using a BindingSource in between the grid and the data. It does not show using a combobox column, but again, if you configure that correctly you do not need to handle the combobox changed event (for exactly the same reason you do not handle the textboxchanged event in a regular cell). It will automatically keep the bound data in sync. All you need is a way to call Update on the SqlDataAdapter when you want to save the changes, whether they be inserts, updates or deletes. You will see this simple call in button2_Click below.

    using System;
    using System.Data;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace dgv
    {
      public partial class Form1 : Form
      {
        SqlDataAdapter da = new SqlDataAdapter();
        BindingSource bind = new BindingSource();
        DataTable datatab = new DataTable();
        SqlConnection connect = new SqlConnection();
    
        public Form1()
        {
          InitializeComponent();
        }
    
        /// <summary>
        /// Loads the data into the datagridview
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
          try
          {
            SqlConnectionStringBuilder csb = 
              new SqlConnectionStringBuilder();
    
            csb.DataSource = ".\\BOB2008R2";
            csb.InitialCatalog = "ContrivedExample";
            csb.IntegratedSecurity = true;
    
            connect.ConnectionString = csb.ConnectionString;
    
            //SElect command loads the data
            SqlCommand selectCmd = new SqlCommand(
                @"SELECT [id], [CompanyName], 
                [regionid] FROM [Companies]", connect);
    
            //Update command is run when there are rows to 
            //update - ie, you modified data
            SqlCommand updateCmd = new SqlCommand(
                @"UPDATE [Companies] SET [CompanyName] = @CompanyName, 
                [regionid] = @regionid WHERE [id] = @id", connect);
    
            updateCmd.Parameters.Add("@id", SqlDbType.Int, 4, "id");
            updateCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar,
              30, "CompanyName");
            updateCmd.Parameters.Add("@regionid", SqlDbType.Int, 4, 
              "regionid");
    
            //Runs when you add a row to the datatable
            SqlCommand insertCmd = new SqlCommand(
                @"INSERT INTO [Companies] ([CompanyName], [regionid]) 
                  VALUES(@CompanyName, @regionid)", connect);
            insertCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar,
              30, "CompanyName");
            insertCmd.Parameters.Add("@regionid", SqlDbType.Int, 4,
              "regionid");
    
            //deletes...
            SqlCommand deleteCmd = new SqlCommand(
                "DELETE FROM [Companies] WHERE [id] = @id", connect);
            deleteCmd.Parameters.Add("@id", SqlDbType.Int, 4, "id");
    
            connect.Open();
    
            da.SelectCommand = selectCmd;
            da.UpdateCommand = updateCmd;
            da.InsertCommand = insertCmd;
            da.DeleteCommand = deleteCmd;
    
            da.Fill(datatab);
    
            bind.DataSource = datatab;
            dataGridView1.DataSource = bind;
          }
          catch (SqlException sqlex)
          {
            MessageBox.Show(sqlex.Message);
          }
        }
    
        /// <summary>
        /// Updates the data. The SqlDataAdapter will 
        ///call all the 
        /// appropriate commands for any modified
        /// data it has.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, 
            EventArgs e)
        {
          da.Update(datatab);
        }
      }
    }
    


    Bob - www.ContrivedExample.com

    Sunday, July 13, 2014 6:45 PM
  • Hello,

    Check out my MSDN article on working with DataGridView ComboBox. There are two solutions, one in C# while the other is VB.NET. Both show how to retrieve the current item via SelectionChangeCommitted event.

    Key parts


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, July 13, 2014 7:56 PM