title: Select Insert subquery from C# windows form in sqlserver table
body: <p>I am working on Inventory software for purchase sale of Products. I want to get profit loss after purchase and sale. Purchase form is working correctly. Now i want to sale a product from sale form and want to get purchase invoice Number where in hand quantity is greater than 0 and against each datagridview row item name for profit lose. So please suguest me how i how defined sub query in insert command which get Pur_Invoice_No from purchase table .
  given below query is generating double values error.</p>
  
  <p>Sale Form
  <a href="https://i.stack.imgur.com/ZMrm3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZMrm3.png" alt="This is my Form"></a></p>
  
  <p>Purchase Table</p>
  
  <p><a href="https://i.stack.imgur.com/gZWpH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gZWpH.png" alt="Purchase Table"></a></p>
  
  <pre><code>Sale_Invoice_No Item_Code   Item_Batch_No   Item_Pur_InvoiceNo  Item_Sale_Quantity  Item_Unit_RetailPrice   Sale_Item_Discount  Item_Discount_Amount
  </code></pre>
  
  <p>1   1           1   5   1   5
  1   2                       </p>
  
  <pre><code> try
              {
  
                  SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=mateenwin;User ID=sa;Password=123");
                  conn.Open();
  
                      SqlCommand commandd = new SqlCommand("Select max(Sale_Invoice_No) from Sale", conn);
  
                  if (string.IsNullOrEmpty(commandd.ExecuteScalar().ToString()))
                  {
                      InvoiceNo = Convert.ToInt32(1);
  
                  }
                  else
                  {
                      int tig =Convert.ToInt32( 1);
  
                      InvoiceNo = Convert.ToInt32(commandd.ExecuteScalar()) +tig;
                  }
                  foreach (DataGridViewRow row in saved.dataGridView1.Rows)
                  {
  
  
                      if (row.IsNewRow) continue;
                      int batch = 1;
                      for(int i=0;i&gt;batch;i++)
                      {
  
                      }
  
                      SqlConnection cons = new SqlConnection("Data Source=.;Initial Catalog=mateenwin;User ID=sa;Password=123");
  
                      SqlCommand command = new SqlCommand("Insert into Sale (Sale_Invoice_No,Payment_Type_ID,Customer_ID,Sale_Reference,Sale_Remarks,Item_Code,Item_Sale_Quantity,Item_Unit_RetailPrice,Sale_Item_Discount,Item_Discount_Amount,Item_Payable_Amount,Cash_Received,Return_Amount,Login_ID,User_ID,Item_Pur_InvoiceNo,Sale_Entry_Date)Values ('" + InvoiceNo + "','" + saved.comboBox1.SelectedValue + "','" + saved.comboBox2.SelectedValue + "','" + saved.textBox3.Text + "','" + saved.textBox4.Text + "','" + row.Cells[6].EditedFormattedValue + "','" + row.Cells[1].EditedFormattedValue + "','" + row.Cells[2].EditedFormattedValue + "','" + row.Cells[3].EditedFormattedValue + "','" + row.Cells[4].EditedFormattedValue + "','" + row.Cells[5].EditedFormattedValue + "','" + saved.textBox10.Text + "','" + saved.textBox11.Text + "','" + saved.comboBox3.SelectedValue + "','" + this.comboBox2.SelectedValue + "',(Select DISTINCT Pur_Invoice_No IN (Select DISTINCT Pur_Invoice_No from Purchase where Purchase.Item_Code ='" + row.Cells[6].EditedFormattedValue + "')),  GetDate())", cons);
  
                      cons.Open();
  
  
                      using (cons)
                      {
  
                          command.ExecuteNonQuery();
                      }
  
                  }
                  MessageBox.Show("Invoice successfully Saved");
  </code></pre>