Click here to Skip to main content
15,887,027 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to save all rows of column(ItemName) into single field of (itemname) in access database. right now rows are saving in 1 row data in 1 field. but i want to store all row's data in 1 field on ItemName.
here is screenshot of that column. whose rows are need to be store in one field.
https://lh3.googleusercontent.com/-Q-kPiuPwlM4/V2eStcNAC5I/AAAAAAAAAME/XXjEVI_R3Nk7vvFbCX0_c9OxtVVEls13QCCo/s800/Untitled.png[^]

What I have tried:

con.Open();

for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{

string amounts = dataGridView1.Rows[i].Cells[0].Value.ToString();

OleDbCommand cmd = new OleDbCommand("insert into sale (ItemName) values ('" + amounts + "')", con);

cmd.ExecuteNonQuery();

MessageBox.Show("Data Registered");

}
Posted
Updated 2-Aug-17 18:55pm
Comments
R45H1D 20-Jun-16 3:06am    
bro item names are saving in seperate rows. but i want to save all item names in one field
R45H1D 20-Jun-16 3:07am    
your code save item names in this sort.
row1 a
row 2 b
but i want to save like this.
row 1 a,b
.net developer123456789 20-Jun-16 3:24am    
please try :-
StringBuilder sbAmount = new StringBuilder();
string Addcomma = "";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
sbAmount.AppendFormat("{0}{1}", Addcomma, dataGridView1.Rows[i].Cells[0].Value.ToString());
Addcomma = ",";
}

string amounts = sbAmount.ToString();

con.Open();

OleDbCommand cmd = new OleDbCommand("insert into sale (ItemName) values ('" + amounts + "')", con);

cmd.ExecuteNonQuery();

MessageBox.Show("Data Registered");

First off, why? That's not generally a good idea - although it's easy to do, and saves you a little work in your DB design, it gives huge problems later when you want to use the data again. It's normally a lot better to keep them as separate rows which some form of key back to the "common" row information. Slightly more complex to design, but considerably easier in the long run.
Second, don't do database access like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

But if you must...it's simple.
C#
StringBuilder sb = new StringBuilder();
string sep = "";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
   {
   sb.AppendFormat("{0}{1}", sep, dataGridView1.Rows[i].Cells[0].Value.ToString());
   sep = "|";
   }
string amounts = sb.ToString();
You can then insert the single value using a parameterized query:
C#
using (OleDbCommand cmd = new OleDbCommand("INSERT INTO sale (ItemName) VALUES (@AM)", con))
   {
   cmd.Parameters.AddWithValue("@AM", amounts);
   cmd.ExecuteNonQuery();
   }
MessageBox.Show("Data Registered");


"now... as the first picture of my application describes. i select item names from combo and other data. when i press save. all selected data should save in db on its relevant column.now let me know your suggestion. how i can save item names in seperate field but their invoice id should be same?"
Ah. You did that. Oh dear...

That's a poor database design. You're concerned that your DB will grow too large, and you are storing the same text information over, and over, and over again!
Look at your second screen shot. How many times are you going to store the same CompanyName? The same ItemName? That's not a good design.
What you need is separate tables.
One table holds Companies:
ID    CoName    CoAddress    TelNo    Email   DiscountPercentage   ...

One table holds Items:
ID    Description    Price

Another holds Invoices:
ID    CompanyID    InvoiceDate   InvoiceNumber   InvoiceTotalValue...

Another holds InvoiceLines:
ID    InvoiceID    ItemID     Quantity     Value

Where CompanyID, InvoiceID, and ItemID are foreign keys which refer back to the relevant table ID values.
All the ID values can be IDENTITY fields, so the DB will generate them for you automatically when the data is INSERTed.

When you do it like this, many things become trivial: a change in the price of something is a single location to change, and because you use that value to generate each invoice line value (item.price * quantity) it doesn't affect existing invoices - that's seriously important: you can go to jail for doing that!
This way, you only store info once, and changes all become trivial. And all DB systems are very, very good at working with data organised like this. They have special queries (look up SQL JOIN for details) to handle them, and they can ensure that you don't make mistakes in your data entry.
It looks like a lot more work to set up, but it isn't really - it's more work, but not much. And trust me on this: it saves you a HUGE amount of work later!
 
Share this answer
 
v2
Comments
R45H1D 20-Jun-16 3:13am    
bro 1st code is not saving data at all. and if saving all item names in 1 field is not a good option then how i can handle this. and if i store items name in seperate columns and the database will be huge. whats your precious suggestion?
OriginalGriff 20-Jun-16 3:23am    
"1st code is not saving data at all"
That's because it assembles it into a single piece of data...which the second piece of code saves.
Why do you think the DB will be any huger if you save them separately, than if you save them as a single string? And how did you expect to be able to use them if you did? Comma separated data is a PITA to use, as SQL string handling is very weak.
What exactly are you trying to do? What is this data used for?
R45H1D 20-Jun-16 3:40am    
ok let me explain. first see this screenshot
https://lh3.googleusercontent.com/-i1i12JOTXCI/V2edZlHLwwI/AAAAAAAAAMY/gGx1MCDpxgoOVZhq_WlBpvli44rITX35wCCo/s800/a.png
R45H1D 20-Jun-16 3:41am    
then this image. this is image of my db where item names are meant to be save
https://lh3.googleusercontent.com/-neiQsZ1gkMQ/V2edZ5y3UrI/AAAAAAAAAMg/askktbqxS_IVc-F1H8RdTM3QP4CeKxokQCCo/s800/b.png
R45H1D 20-Jun-16 3:44am    
now... as the first picture of my application describes. i select item names from combo and other data. when i press save. all selected data should save in db on its relevant column.now let me know your suggestion. how i can save item names in seperate field but their invoice id should be same?
Hi, as data is return in multiple rows from database, so you need use "DataSet" that is use for that purpose.

For Example:

var ds = new DataSet();
var conn = DACUtil.GetConnection();
try
{
    SqlCommand cmd = new SqlCommand("Your-Query-Here", conn);
    var da = new SqlDataAdapter { Select7Command = cmd };

    da.Fill(ds);

    ds.Tables[0].TableName = "Emp";
    ds.AcceptChanges();

}
finally
{
    conn?.Dispose();
}
return ds;


Hope it will helpful for you, please don't forget to mark my good rating. :)
 
Share this answer
 
Comments
[no name] 3-Aug-17 0:59am    
and after populating 'ds' from database, you simply configure it with GridView or use it for any other purpose.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900