CODEWITHSUNDEEP
c#visual-studioms-access
Jonald Samilo
Jonald Samilo

Reputation: 325

Adding multiple TextBoxes in one column of database

I'm creating an Inventory System which can add multiple items in a single click. how could I do this? I can already save data but for just a single textbox.

//Add new Data if Item Code is not exit;
            {
                OleDbCommand cmdInsert = new OleDbCommand(@"insert into TblInventory (ItemCode,ProductName,Quantity,DateAndTime) values ('" + txtItem.Text + "','" + txtProduct.Text + "','" + txtQuantity.Text + "','" + time + "')");
                cmdInsert.Connection = con;

                cmdInsert.ExecuteNonQuery();
                MessageBox.Show("You added New " + txtQuantity.Text + " " + txtProduct.Text + " in the list", "New Item");
            }
           con.Close();

assuming that I have another textBoxes which are txtItem2, txtProduct2 and txtQuantity2. where can I locate those on my insert into statement?

Upvotes: 1

Views: 1288

Answers (2)

Tarek Abo ELkheir
Tarek Abo ELkheir

Reputation: 1331

You could create a stored procedure accepting xml as input parameter, and then in the stored procedure ypu will parse the xml and insert the data to the table,

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

First, use parameters instead of string concatenation, like below:

OleDbCommand cmdInsert = new OleDbCommand(
    @"insert into TblInventory (ItemCode,ProductName,Quantity,DateAndTime) values (@ItemCode,@ProductName,@Quantity,@DateAndTime)");
cmdInsert.Parameters.AddWithValue("ItemCode", txtItem.Text);
cmdInsert.Parameters.AddWithValue("ProductName", txtProduct.Text);
cmdInsert.Parameters.AddWithValue("Quantity", txtQuantity.Text);
cmdInsert.Parameters.AddWithValue("DateAndTime", time);

Second, if you need many inserts, use loop. Or, wrap your insert code in function with four parameters ItemCode,ProductName,Quantity,DateAndTime. Use them instead of direct references to txtSomething.Text values, i.e. (pseudocode):

InsertRecord(txtItem.Text, txtProduct.Text, ...);
InsertRecord(txtItem2.Text, txtProduduct2.Text, ...);

For loop you can write like below:

var rows = new[]
{
    new {Item = "item1" /*value from txtItem1*/, Product = "product1", Quantity = "Quantity1" /*should be int?*/},
    new {Item = "item2" /*value from txtItem2*/, Product = "product2", Quantity = "Quantity2"}
};

foreach (var row in rows)
{
    OleDbCommand cmdInsert = new OleDbCommand(
        @"insert into TblInventory (ItemCode,ProductName,Quantity,DateAndTime) values (@ItemCode,@ProductName,@Quantity,@DateAndTime)");
    cmdInsert.Parameters.AddWithValue("ItemCode", row.Item);
    cmdInsert.Parameters.AddWithValue("ProductName", row.Product);
    cmdInsert.Parameters.AddWithValue("Quantity", row.Quantity);
    cmdInsert.Parameters.AddWithValue("DateAndTime", DateTime.Now);
    cmdInsert.Connection = conn;
    cmdInsert.ExecuteNonQuery();
}

Upvotes: 1

Related Questions