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.
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:
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!