CODEWITHSUNDEEP
c#winformssqlitedatagridview
YellowJazz
YellowJazz

Reputation: 15

Insert data into table(from select) using 2 DataGridView c# windowsForm

Please help me to understand where I go wrong. ok let's go! 2 DataGridViews, in first I'm store services in second order list. when I push button Save, this code will happen:

public void insert_sales_list()
    {
        conn.Open();
        foreach (DataGridViewRow row in dgvService.SelectedRows)
        {               
            SQLiteCommand cmd = new SQLiteCommand("insert into sales_list (sales_created_date, sales_created_name, emp_name, cust_phone, cust_name, planned_date, planned_time, service_name, discount, price, order_id) values (@ocd, @ocn, @emp, @c_phone, @c_name, @p_date, @p_time, @sn, @disc, @price, @o_id)", conn);             
            cmd.Parameters.AddWithValue("@ocd", DateTime.Now);
            cmd.Parameters.AddWithValue("@ocn", lblLoginUser.Text);
            cmd.Parameters.AddWithValue("@emp", dgvOrderList.CurrentRow.Cells[1].Value.ToString());
            cmd.Parameters.AddWithValue("@c_phone", dgvOrderList.CurrentRow.Cells[2].Value.ToString());
            cmd.Parameters.AddWithValue("@c_name", dgvOrderList.CurrentRow.Cells[3].Value.ToString());
            cmd.Parameters.AddWithValue("@p_date", dgvOrderList.CurrentRow.Cells[5].Value);
            cmd.Parameters.AddWithValue("@p_time", dgvOrderList.CurrentRow.Cells[6].Value.ToString());
            cmd.Parameters.AddWithValue("@sn", row.Cells[0].Value.ToString());
            cmd.Parameters.AddWithValue("@disc", dgvOrderList.CurrentRow.Cells[4].Value.ToString());
            cmd.Parameters.AddWithValue("@price", row.Cells[1].Value.ToString());
            cmd.Parameters.AddWithValue("@o_id", dgvOrderList.CurrentRow.Cells["order id"].Value);
            cmd.ExecuteNonQuery();

            string sql = "update order_list set status = 'Saved' where id = '" + dgvOrderList.CurrentRow.Cells["order id"].Value + "'";
            cmd = new SQLiteCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }
        conn.Close();

By this code you see that I just insert data from Order List to Sales List, user choose service or services from DataGridView.Service, he can take any service from the list. This code works very well.

Next step. I have another table where each service have own materials, for example - men's haircut have soap, shampoo and tissue paper in materials. And I need to insert these data in SalesMaterials Table. And I think code is wrong, please help me to find this error? code:

public void insert_sales_materials()
    {
        conn.Open();
        foreach (DataGridViewRow row in dgvService.SelectedRows)
        {               
            string Query = "insert into sales_list_materials(order_id, material_id, norma, created_name, creation_date) " +
                "values( select '" + dgvOrderList.CurrentRow.Cells["order id"].Value + "', a.material_id, a.norma, '" + lblLoginUser.Text + "', '" + DateTime.Now + "'  from service_materials a where a.service_id = '" + row.Cells[2].Value + "')";
            SQLiteCommand cmd = new SQLiteCommand(Query, conn);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }

Error:

Additional information: SQLite error

near "select": syntax error

Upvotes: 0

Views: 48

Answers (1)

YellowJazz
YellowJazz

Reputation: 15

Ok I got it! when you insert data with select, please did not use word values =)) correct code for all of you:

public void insert_sales_materials()
    {
        conn.Open();
        foreach (DataGridViewRow row in dgvService.SelectedRows)
        {               
            string Query = "insert into sales_list_materials(order_id, material_id, norma, created_name, creation_date) " +
                "select '" + dgvOrderList.CurrentRow.Cells["order id"].Value + "', a.material_id, a.norma, '" + lblLoginUser.Text + "', '" + DateTime.Now + "'  from service_materials a where a.service_id = '" + row.Cells[2].Value + "'";
            SQLiteCommand cmd = new SQLiteCommand(Query, conn);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }

Upvotes: 1

Related Questions