CODEWITHSUNDEEP
c#
Abid Ali
Abid Ali

Reputation: 1747

How do I Insert records in database multiple Tables using winforms c#

I have a winform which i have attached here.. When i Insert record in Customer table, I also want to add the record in the orderlineitems table in my database as of which the record in the Order table will also be inserted. I have inserted the record in Customer table but when i insert record in OrderLineItems table, it shows an error.

I also wanted to ask that, my OrderLineItems Table in database contains columns as :

  1. ID(pk)
  2. OrderID(pk)
  3. Quantity
  4. Particular
  5. Rates
  6. Status

On my Form, I have only quantity, particular and rates fields from which i can get their values but i dont have Status and Orderid values on my winform. how do i get the values of status and orderId then?

My Code is:

private void buttonInsert_Click(object sender, EventArgs e)
    {
        string SQL = String.Format("Insert into Customer values ('{0}','{1}','{2}')", textBoxName.Text, textBoxContactNo.Text, textBoxAddress.Text);
        //string SQL1 = String.Format("Insert into Order values ({0},'{1}','{2}',{3})",);
        DataManagementClass dm = new DataManagementClass();

    int result = dm.ExecuteActionQuery(SQL);
    if (result > 0)
    {

        for (int i = 0; i < recordsDataGridView.RowCount ; i++)
        {
            string query = String.Format("Insert into OrderLineItems values({0},'{1}','{2}','{3}',{4})",7,QuantityColumn, ParticularColumn, RatesColumn,1);
            dm.ExecuteActionQuery(query);
        }
        //string query = String.Format("Insert into OrderLineItems values ('{0}','{1},'{2}'

    }

What am i doing wrong here. please guide me the correct way. Thanx.. WinForm Image

Upvotes: 0

Views: 1133

Answers (2)

p.campbell
p.campbell

Reputation: 100557

Suggest a few enhancements to get your business logic working and maintainable:

  • write a stored procedure for your CreateCustomer and CreateLineItem routines in your database.
  • use a SqlCommand and its Parameters collection. Stop whatever you're doing right now and guard against SQL injection.
  • remove all this code from the button click event.
  • create new methods like CreateCustomer and CreateLineItems. Call these method from your button click handler method.
 private void buttonInsert_Click(object sender, EventArgs e)
 {
   int result = CreateCustomer(textBoxName.Text.Trim(),
                               textBoxContactNo.Text.Trim(),
                               textBoxAddress.Text.Trim());
    if (result > 0)
    {
       foreach(var row in recordsDataGridView.Rows)
       {
           CreateLineItem(quantity, particulars, rates);
       }
    }

Upvotes: 2

Stefan Steiger
Stefan Steiger

Reputation: 82146

You are inserting values like this: '{1}','{2}','{3}'

This leads me to assume you are inserting string values.
If there is an apostroph in your string values, it will cause a syntax error.
Try 

yourstring.replace("'", "''")

on the arguments.


Also, do as p.campbell suggest..
Use parameters to prevent SQL injection..
Somebody might malicously take advantage of your code's open security holes..

Upvotes: 1

Related Questions