question

KwebenaAcquah-9104 avatar image
0 Votes"
KwebenaAcquah-9104 asked KwebenaAcquah-9104 edited

how to update multiple rows in one column using C#

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;

77315-wpf.png

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






dotnet-csharpwindows-wpf
wpf.png (20.7 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

please some one help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! me please am begging pleas help!!!!!!!!!!!!!!!!!!!!!!!!!!!!

0 Votes 0 ·
TimonYang-MSFT avatar image TimonYang-MSFT KwebenaAcquah-9104 ·

@KwebenaAcquah-9104
Sorry, I have replied many times but none of them seem to be very suitable for your situation, so I think I may have misunderstood your needs.
Can you manually create the initial data table and the result table and insert some data to show me what the final result you want to be like.
I think it shouldn't be difficult to write code, but I can't get your point.

1 Vote 1 ·

here is the loaded datagrid directly from database;
78991-capturet.png

so what am talking about is to update each rows from one textbox to a specific column after which the digits have been changed like this;

example: say from the image above

Column MATH
Row: 1 Dennis will be 2nd
Row: 2 Genesis will be 4th
Row: 3 Hell will be 3rd
Row: 4 Israel will be 1st

like in this image here;
78934-captur5e.png


then after the update button event happens the final expected result will be;
78982-capture.png

this is what i want and i have been trying to pass across please can this be possible if yes can i be thought on how to do this, please sir (i wish you understand me this time please if not i would be glad to learn and explain further) thanks

0 Votes 0 ·
capturet.png (4.1 KiB)
captur5e.png (6.8 KiB)
capture.png (4.2 KiB)
Show more comments
TimonYang-MSFT avatar image
1 Vote"
TimonYang-MSFT answered KwebenaAcquah-9104 edited

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!")
     }
 }

80048-capture.png


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.


2.png (9.5 KiB)
code.txt (5.2 KiB)
capture.png (26.8 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

please sir i have checked it but it works perfectly well but i have a little problem with some part of your code please can you help on this error Sir;81246-capterure.png



please what can i do to solve this issue Sir?

0 Votes 0 ·
capterure.png (27.5 KiB)
TimonYang-MSFT avatar image TimonYang-MSFT KwebenaAcquah-9104 ·

This problem may be caused by the fact that some values of the data table you are currently using cannot be converted to the double type. I'm not sure if there are non-numeric values in the original table, or if the wrong table is used for some reason.
First divide this code into two sentences.

   var  datas = dataTable.AsEnumerable();
             List<double> values = datas.Select(s => s.Field<double>(fieldName)).OrderByDescending(d=>d).ToList();

81426-2.png
You can set a breakpoint at the position in the picture, run the project, and make the code reach the breakpoint position, hover the mouse over the data table to view the current data table, if it is not the original score table, please share the current code to me . To prevent the reply from exceeding the limit of 1000 characters, you can put the code in a txt file as an attachment.

0 Votes 0 ·
2.png (14.4 KiB)

please sir can you help me to allow null in each of the column if any occures in the database Table; within this part of the code below;

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

thanks Sir

0 Votes 0 ·
myerrorcode.txt (718 B)

thanks very much

0 Votes 0 ·
BonnieDeWitt-QnA avatar image
1 Vote"
BonnieDeWitt-QnA answered BonnieDeWitt-QnA commented

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 @TimonYang-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


· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks to all of your prodigies my problem is solved, thanks very much to all of you i will definitely share to others your knowledge. thanks thanks very very much (TimonYang God Bless You For following me through out)

0 Votes 0 ·

please i have just one more question to ask; what if i have a NULL column within the dataTable how can i solve this?

0 Votes 0 ·

First make sure that the fieldName does not equal DBNull.Value:

 if ((dataTable.Rows[i][fieldName] != DBNull.Value && double.TryParse(dataTable.Rows[i][fieldName].ToString(), out value))
  {
      initialValues.Add(value);
  }
0 Votes 0 ·

can i use your code on TimonYang own method?

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

please help me add the check for the null column within this code (thanks) or even allow null



0 Votes 0 ·

please help me make to allow null if any within the DB thanks

0 Votes 0 ·
Show more comments