0

So I'm stumped on this problem.

I need to get all the column values of another table (tbladdbenefit) and add them to another table (payrolltable) and insert all the values of the first table into a single cell.

In this case, I'm trying to insert all of the values of column "benefit" to a single cell of "benefit" on the new table, the same thing for "BenefitAmount".

So far I am using Parameters.AddWithValue but to no avail. I have 2 data from the column benefit from the first table but it only shows the record I highlighted. Which is not what I want to do. I want to display and add all the records of my parent table

Any suggestions?

 cmd = new SqlCommand("INSERT INTO payrolltable " + 
                      "(Name, " + 
                      "Position, " + 
                      "Honoraria, " + 
                      "Total, " + 
                      "Benefit, " + 
                      "BenefitAmount, " + 
                      "Deduction, " + 
                      "DeductionAmount) " + 
                      "VALUES " + 
                      "(@name, " + 
                      "@position, " + 
                      "@honoraria, " + 
                      "@total, " + 
                      "@benefit, " + 
                      "@benefitamount, " + 
                      "@deduction, " + 
                      "@deductionamount)", con);

 cmd.Parameters.AddWithValue("@name", txtfname.Text + " " + txtlname.Text);
 cmd.Parameters.AddWithValue("@position" , txtposition.Text);
 cmd.Parameters.AddWithValue("@honoraria", txtsalary.Text);
 cmd.Parameters.AddWithValue("@total", 323232);
 cmd.Parameters.AddWithValue("@benefit", SqlDbType.VarChar);
 cmd.Parameters.AddWithValue("@benefitamount", SqlDbType.BigInt);
 cmd.Parameters.AddWithValue("@deduction", " ");
 cmd.Parameters.AddWithValue("@deductionamount", " ");
              
                
 for (int i = 0; i < tbladdbenefit.Rows.Count - 1; i++)
 {
  cmd.Parameters["@benefit"].Value = tbladdbenefit.Rows[i].Cells[1].Value;
  cmd.Parameters["@benefitamount"].Value = tbladdbenefit.Rows[i].Cells[2].Value;
 }
Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
echooooslam
  • 15
  • 4

1 Answers1

0

There are a couple of ways to store multiple values in a column of a database table.

You can store it as CSV (comma separated values) or JSON or XML, But it will have its downside when you want to update this column values or want to query on this table.

Also just refer to this as well.

Sample as requested:

Please follow the sample to help you to understand the concept.

List<string> ls = new List<string>();
for (int i = 0; i < tbladdbenefit.Rows.Count - 1; i++)
{
    ls.Add(tbladdbenefit.Rows[i].Cells[1].Value);
}

string csv = string.Join(",", ls);
cmd.Parameters["@benefit"].Value = csv;
Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
  • can you give me an example? so I can fully understand it? Thanks! – echooooslam Nov 19 '22 at 09:05
  • still gets only the first value, but not all the values in the whole column – echooooslam Nov 19 '22 at 10:27
  • Then you need to put a breakpoint and debug your code line by line. @echooooslam – Vivek Nuna Nov 19 '22 at 10:46
  • Thank you! I fixed it! But I'm facing another problem. It says "Specified Cast Is not Valid" when I try to do the same to my BenefitAmount. Changed to when declaring my list – echooooslam Nov 20 '22 at 04:13
  • @echooooslam first you have to accept this answer and then post another question for your new question. On a side note you will have to change the BenefitAmount to varchar in database table not int. Because you are storing a CSV – Vivek Nuna Nov 20 '22 at 04:16
  • thanks! already accepted your answer and thank you for much for helping me – echooooslam Nov 20 '22 at 08:03