how to update multiple rows in one column using C#

Question

i am having a Datagrid that loads data from database and each time each row is selected all row shows up in one textbox. with the exception of the id column and name column like this;

i would like to update each row as selected with the changed values other than with its original values from that one textbox to their respective rows; for instance;
Row Dennis Math original value is 67 but will be update with the changed value 1st likewise other Rows.

(am using c# in wpf)(the original values will be clear like this; so it won't be a problem) i just want to update each row as selected with the changed values; 1st, 2nd etc;

txtresults.Clear();  
            txtresults.Text += Environment.NewLine;  
            txtresults.Text += string.Join(" ", parts);  

here is my selection changed event of datagrid

DataGrid dg = sender as DataGrid;  
            DataRowView dr = dg.SelectedItem as DataRowView;  
            if (dr != null)  
            {  
                if (dgvGrade.SelectedItems.Count > 0)  
                {  
                    foreach (var obj in dgvGrade.SelectedItems)  
                    {  
    str += dr["MATH"] +"\n ";  
                    }  
                }  
                else  
                {  
                    //DO NOTHING  
                }  
               textbox1.Text = str;  

please can some one teach me how to do the right thing. (thanks to all of you prodigies)

this is what i have tried;

        private void btnupdategraderecord_Click(object sender, RoutedEventArgs e)  
        {  
            SqlConnection Conn = new SqlConnection(shoolmanangmentconn);  
            Conn.Open();  
            SqlCommand cmd = new SqlCommand("UPDATE tbl_TestingTheApplicationSubject SET MATH=@MATH WHERE IDNO=@IDNO", Conn);  
            cmd.CommandType = CommandType.Text;  
            string[] arryval = txtresults.Text.Split(new[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries);  
            int i = 0;  
            cmd.Parameters.Clear();  
            cmd.Parameters.AddWithValue("@IDNO", txtids.Text);  
            cmd.Parameters.AddWithValue("@MATH", arryval[i++]);  
            cmd.ExecuteNonQuery();  
        }  

while having a txtids textbox that would hold IDNO numbers but i could not update each row according to the entered IDNO numbers in the txtids textbox like this;
1,2,3,4 etc. please can some one help me do the right thing please

Answer 1

Please check this code:

public partial class MainWindow : Window  
{  
    private DataTable dataTable;  
    private DataTable resDataTable;  
    public MainWindow()  
    {  
        InitializeComponent();  
         dataTable = GetDataTable();  
        dataGrid.ItemsSource = dataTable.DefaultView;  
    }  
    public DataTable GetDataTable()   
    {  
        string connString = @"";  
        using (SqlConnection sqlConnection = new SqlConnection(connString))  
        {  
            sqlConnection.Open();  
            using (SqlCommand command = new SqlCommand("select * from scoretable",sqlConnection))  
            {  
                DataTable dataTable = new DataTable();  
                dataTable.Load(command.ExecuteReader());  
                return dataTable;  
            }  
        }  
    }  

    private void button_Click(object sender, RoutedEventArgs e)  
    {  
        resDataTable = new DataTable();  
        resDataTable.Columns.Add("id", typeof(string));  
        for (int i = 1; i < dataTable.Columns.Count; i++)  
        {  
            resDataTable.Columns.Add(dataTable.Columns[i].ColumnName, typeof(string));  
        }  
        List<string> mathRank = getRank("Math");  
        List<string> scienceRank = getRank("Science");  
        List<string> englishRank = getRank("English");  
        List<string> historyRank = getRank("History");  

        for (int i = 0; i < mathRank.Count; i++)  
        {  
            resDataTable.Rows.Add(dataTable.Rows[i]["id"], dataTable.Rows[i]["name"], mathRank[i], scienceRank[i], englishRank[i], historyRank[i]);  
        }  
        dataGrid1.ItemsSource = resDataTable.DefaultView;  
        
        
    }  

    public void insertIntoDB(DataTable dataTable)   
    {  
        string connString = @"";  
        using (SqlConnection sqlConnection = new SqlConnection(connString))  
        {  
            sqlConnection.Open();  
            string createTableSql = "if not exists (select * from sysobjects where name='scoreRankTable' and xtype='U')" +  
                                       "CREATE TABLE  scoreRankTable(" +  
                                       " Id int," +  
                                        "Name varchar(50)," +  
                                        "Math varchar(10)," +  
                                        "Science varchar(10)," +  
                                        "English varchar(10)," +  
                                        "History varchar(10)," +  
                                    ");";  
            using (SqlCommand command = new SqlCommand(createTableSql, sqlConnection))  
            {  
                command.ExecuteNonQuery();  
                command.CommandText = "delete from scoreRankTable";  
                command.ExecuteNonQuery();  
            }  
            using (SqlBulkCopy bulkCopy =  
                        new SqlBulkCopy(sqlConnection))  
            {  
                bulkCopy.DestinationTableName =  
                    "dbo.scoreRankTable";  

                try  
                {  
                    bulkCopy.WriteToServer(dataTable);  
                }  
                catch (Exception ex)  
                {  
                    Console.WriteLine(ex.Message);  
                }  
            }  
        }  
    }  
    public List<string> getRank(string fieldName)   
    {  
         
        List<string> positions = new List<string>();  
        List<double> values = dataTable.AsEnumerable().Select(s => s.Field<double>(fieldName)).OrderByDescending(d=>d).ToList();  
        for (int i = 0; i < dataTable.Rows.Count; i++)  
        {  
           int index = values.IndexOf(dataTable.Rows[i].Field<double>(fieldName));  
            positions.Add(AddOrdinal(index + 1));  
        }  
        return positions;  
    }  
    public  string AddOrdinal(int num)  
    {  
        if (num <= 0) return num.ToString();  

        switch (num % 100)  
        {  
            case 11:  
            case 12:  
            case 13:  
                return num + "th";  
        }  
        switch (num % 10)  
        {  
            case 1:  
                return num + "st";  
            case 2:  
                return num + "nd";  
            case 3:  
                return num + "rd";  
            default:  
                return num + "th";  
        }  
    }  

    private void button1_Click(object sender, RoutedEventArgs e)  
    {  
        insertIntoDB(resDataTable);  
        MessageBox.Show("Insert success!")  
    }  
}  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @KwebenaAcquah-9104 ...

What is the fieldName that you are passing to that method? The column in the database table that contains that column referenced by fieldName must be a double datatype. If it's not, then you will get the InvalidCastException. I think that's what @Timon Yang-MSFT was telling you. If it's not a double datatype (and is a varchar, meaning it can contain non-numeric values), then you should handle this differently (and I don't think that you can do it with LINQ syntax.

Try something like this instead. It should work the way you want it to:

public List<string> getRank(string fieldName)   
 {  
         
    List<string> positions = new List<string>();  
    List<double> initialValues = new List<double>();  
    double value;  
    for (i = 0; i < dataTable.Rows.Count; i++)  
    {  
        if (double.TryParse(dataTable.Rows[i][fieldName].ToString(), out value))  
        {  
            initialValues.Add(value);  
        }  
    }  
    List<double> sortedValues = initialValues.Select(s => s).OrderByDescending(d => d).ToList();  
  
    for (i = 0; i < sortedValues.Count; i++)  
    {  
        positions.Add(sortedValues[i].ToString());  
        positions.Add(AddOrdinal(i + 1));  
    }  
  
    return positions;  
 }  

Hope that helps! =0)

---

~~Bonnie DeWitt [MVP since 2003]
http://geek-goddess-bonnie.blogspot.com