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

Question

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 :

ID(pk)
OrderID(pk)
Quantity
Particular
Rates
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

what error is it showing? any reason you cannot use LINQ to SQL by the way? — Pharabus
– Pharabus, Commented Sep 28, 2011 at 19:51
Let's hope textBoxName.Text doesn't contain value ' OR 1=1; DROP TABLE Customer;-- — p.campbell
– p.campbell, Commented Sep 28, 2011 at 19:51

p.campbell · Accepted Answer · 2011-09-28 20:01:06Z

2

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);
       }
    }

edited Sep 28, 2011 at 20:01

answered Sep 28, 2011 at 19:55

p.campbell

101k70 gold badges263 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abid Ali Over a year ago

p.campell, i dont know why do i scare from stored procedures, may b i dont know how to do work by using them. i will definitely learn to use storedprocedures.

Stefan Steiger · Accepted Answer · 2011-09-28 19:55:35Z

1

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

answered Sep 28, 2011 at 19:55

Stefan Steiger

83k70 gold badges405 silver badges461 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related