locked
How do i create first insert to sells table then update customerpayment table then if record doesnt exist in Customerpayment table then insert RRS feed

  • Question

  • User-2074858223 posted

    How do i create first insert to sells table then update customerpayment table then if record doesnt exist in Customerpayment table then insert




    string customerphone = ddlcustomerphone.SelectedItem.Text.Trim();
                                string productcategory = ddlproductcategory.SelectedItem.Text.Trim();
                                string name = ddlcustomername.SelectedItem.Text.Trim();
                                string address = ddlcustomeraddress.SelectedItem.Text.Trim();
                                decimal unitPrice = Convert.ToDecimal(txtprice.Text.Trim());
                                //  decimal deposit = Convert.ToDecimal(txtdeposit.Text.Trim());
                                int quantity = Convert.ToInt32(lblQuantityAvailable.Text.Trim());
                                int inserted = 0;
    
    
                                string paymentstatus = ddlpaymentstatus.SelectedItem.Text.Trim();
    
                                if (txtprice.Text.Length > 0 && txtrow.Text.Length > 0)
                                {
                                    float balance = !string.IsNullOrEmpty(txtamountpaid.Text.Trim()) ? float.Parse(txtamountpaid.Text.Trim()) : float.Parse("0"); ;
                                    double unitprice2 = Convert.ToDouble(txtprice.Text);
                                    double customerqty2 = Convert.ToDouble(txtrow.Text);
                                    double grandtotal = unitprice2 * customerqty2;
                                    double total = grandtotal;
                                    double bal = grandtotal - balance;
                                    double remain = bal;
    
                                    {
                                        if (txtrow.Text.Length > 0 && lblQuantityAvailable.Text.Length > 0)
                                        {
                                            float rowavailable = !string.IsNullOrEmpty(lblQuantityAvailable.Text.Trim()) ? float.Parse(lblQuantityAvailable.Text.Trim()) : float.Parse("0"); ;
                                            float row = !string.IsNullOrEmpty(txtrow.Text.Trim()) ? float.Parse(txtrow.Text.Trim()) : float.Parse("0"); ;
    
                                            double totalrow = row / rowavailable;
                                            double finalrow = totalrow;
    
                                            // if ((availableQuantity != 0) && (quantity != 0) && (quantity < availableQuantity))
                                            {
                                                using (SqlConnection con = new SqlConnection())
                                                {
                                                    con.ConnectionString = str;
                                                    using (SqlCommand cmd = new SqlCommand())
                                                    {
                                                        cmd.CommandType = CommandType.Text;
                                                        cmd.Connection = con;
                                                        //Change the query like below
                                                        //Make sure the column name matches the with the name provided in code as per your table
                                                        cmd.CommandText = "INSERT INTO Sells (UserName,CustomerName,CustomerAddress,CustomerPhone,ProductCategory,RowPrice,PaymentStatus,Rows_Ordered,Cartons,TotalAmount,AmountPaid,Balance) VALUES(@UserName,@CustomerName,@CustomerAddress,@CustomerPhone,@ProductCategory,@RowPrice,@PaymentStatus,@Rows_Ordered,@Cartons,@TotalAmount,@AmountPaid,@Balance)";
                                                        cmd.Parameters.AddWithValue("@UserName", Session["userName"]);
                                                        cmd.Parameters.AddWithValue("@CustomerName", ddlcustomername.SelectedItem.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@CustomerAddress", ddlcustomeraddress.SelectedItem.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@CustomerPhone", customerphone);
                                                        cmd.Parameters.AddWithValue("@ProductCategory", productcategory);
                                                        //  cmd.Parameters.AddWithValue("@Quantity_PerSqM", txtQuantity.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@RowPrice", txtprice.Text.Trim());
                                                        // cmd.Parameters.AddWithValue("@Quantity", txtQuantity.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@PaymentStatus", ddlpaymentstatus.SelectedItem.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@TotalAmount", grandtotal);
                                                        cmd.Parameters.AddWithValue("@Rows_Ordered", txtrow.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@Cartons", finalrow);
                                                        cmd.Parameters.AddWithValue("@AmountPaid", txtamountpaid.Text.Trim());
                                                        cmd.Parameters.AddWithValue("@Balance", bal);
                                                        con.Open();
                                                        inserted = cmd.ExecuteNonQuery();
                                                        con.Close();
    
                                                    }
                                                }
                                                //}
                                                // else
                                                {
                                                    // ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
                                                }
                                                if (inserted > 0)
                                                {
                                                   // int updatedStock = availableQuantity - quantity;
                                                    using (SqlConnection con = new SqlConnection())
                                                    {
                                                        con.ConnectionString = str;
                                                        using (SqlCommand cmd = new SqlCommand())
                                                        {
                                                            cmd.CommandType = CommandType.Text;
                                                            cmd.Connection = con;
                                                            cmd.CommandText = "UPDATE CustomerPayments SET TotalAmount=@TotalAmount WHERE CustomerPhone = @CustomerPhone";
                                                            cmd.Parameters.AddWithValue("@PaymentStatus", ddlpaymentstatus.SelectedItem.Text);
                                                            cmd.Parameters.AddWithValue("@AmountPaid", txtamountpaid.Text.Trim());
                                                            cmd.Parameters.AddWithValue("@TotalAmount", grandtotal);
                                                            cmd.Parameters.AddWithValue("@Balance", grandtotal);
                                                            cmd.Parameters.AddWithValue("@CustomerPhone", ddlcustomerphone.SelectedItem.Text);
                                                            cmd.Parameters.AddWithValue("@CustomerName", ddlcustomername.SelectedItem.Text);
                                                            con.Open();
                                                            inserted = cmd.ExecuteNonQuery();
                                                            con.Close();
    
                                                            string message2 = string.Empty;
                                                            {
                                                                lblMessage2.Visible = true;
                                                                lblMessage2.Text = "Data Submitted Successfully";
                                                            }
    
                                                            ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
    
                                                        }
                                                        else 
                                                        {
                                                            using (SqlConnection con = new SqlConnection())
                                                {
                                                    con.ConnectionString = str;
                                                    using (SqlCommand cmd = new SqlCommand())
                                                    {
                                                        cmd.CommandType = CommandType.Text;
                                                        cmd.Connection = con;
                                                        //Change the query like below
                                                        //Make sure the column name matches the with the name provided in code as per your table
                                                        cmd.CommandText = "INSERT INTO CustomerPayments (CustomerName,CustomerPhone,PaymentStatus,TotalAmount,AmountPaid,Balance) VALUES(@CustomerName,@CustomerPhone,@PaymentStatus,@TotalAmount,@AmountPaid,@Balance)";
                                                        cmd.Parameters.AddWithValue("@UserName", Session["userName"]);
                                                        cmd.Parameters.AddWithValue("@PaymentStatus", ddlpaymentstatus.SelectedItem.Text);
                                                            cmd.Parameters.AddWithValue("@AmountPaid", txtamountpaid.Text.Trim());
                                                            cmd.Parameters.AddWithValue("@TotalAmount", grandtotal);
                                                            cmd.Parameters.AddWithValue("@Balance", grandtotal);
                                                            cmd.Parameters.AddWithValue("@CustomerPhone", ddlcustomerphone.SelectedItem.Text);
                                                            cmd.Parameters.AddWithValue("@CustomerName", ddlcustomername.SelectedItem.Text);
                                                        con.Open();
                                                        inserted = cmd.ExecuteNonQuery();
                                                        con.Close();
                                                        }
                                                    }
                                                }
                                                BindProductsSells();
                                            }
    
    
    
    
                                            // ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
    
    
    
    
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
                
           
    Sunday, December 17, 2017 8:15 PM

All replies

  • User1400794712 posted

    Hi micah2012,

    According to your code, it seems the logic of it can just meet your needs.

    Is there anything wrong in this code?

    This code is incomplete, if it's convenient, please post more explanations here. Then we can understand the problem better.

    Best Regards,

    Daisy

    Monday, December 18, 2017 3:34 PM