locked
[HELP] Insert one time but 4 records in table RRS feed

  • Question

  • User-1562956770 posted

    Hey guys can anybody help me, i've got annoying when insert to database inside while.

    here is my code:

    private void AdjustMachine()
        {
            string strPattern = "PFG";
            string strBrandCode, strStyleCode, strTypeCode;
            int intQty = 0, intCompanyID = 1;
            string strMachineCode, strSelectionCode;
            SqlDataReader drSelection = null;
    
            try
            {
                Conn.Open();
                strCmdSelect.Connection = Conn;
                strCmdSelect.CommandText = "SELECT " +
    	                                   "    mb.MachineBrandCode, " +
    	                                   "    mbs.MachineStyleCode, " +
    	                                   "    mbt.MachineTypeCode, " +
                                           "    rmd.ReceiveMachineDetail_Qty " +
                                           "FROM " +
    	                                   "    tstc_receive_machine_detail rmd " +
                                           "INNER JOIN tmst_machine_brand mb ON mb.MachineBrandName = rmd.ReceiveMachineDetail_Brand " +
                                           "INNER JOIN tmst_machine_brand_style mbs ON mbs.MachineStyleName = rmd.ReceiveMachineDetail_Style " +
                                           "INNER JOIN tmst_machine_brand_type mbt ON mbt.MachineTypeName = rmd.ReceiveMachineDetail_Type " +
                                           "WHERE " +
    	                                   "    ReceiveCode = '" + lblRcvCode.Text.ToString() + "'";
                using (SqlDataReader dr = strCmdSelect.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        strBrandCode = dr.GetString(0).ToString();
                        strStyleCode = dr.GetString(1).ToString();
                        strTypeCode = dr.GetString(2).ToString();
                        intQty = dr.GetInt32(3);
    
                        strSelectionCode = strPattern + "_" + strBrandCode + "_" + strStyleCode + "_" + strTypeCode;
                        strMachineCode = strSelectionCode;
    
                        if (strMachineCode != null)
                        {
                            strCmdSelect1.CommandText = "SELECT " +
                                                        "    MachineCode " +
                                                        "FROM " +
                                                        "    tmst_machine " +
                                                        "WHERE " +
                                                        "    MachineCode = '" + strMachineCode + "'";
                            drSelection = strCmdSelect1.ExecuteReader();
                            using (drSelection)
                            {
                                //while (drSelection.Read())
                                //{
                                    if (drSelection.Read())
                                    {
                                        Console.WriteLine(drSelection["MachineCode"]);
                                        if (strMachineCode == drSelection["MachineCode"].ToString())
                                        {
                                            //if machinecode was same in table will update qty
                                            //begin transact
                                            strCmdUpdate.Connection = Conn;
                                            strCmdUpdate.CommandText = "UPDATE tmst_machine " +
                                                                        "SET " +
                                                                           "MachineQty = MachineQty + " + intQty + " " +
                                                                        "WHERE " +
                                                                           "MachineCode = '" + strMachineCode + "'";
    
                                            strCmdUpdate.ExecuteNonQuery();
                                            //end transact
                                        }
                                    }
                                    else
                                    {
                                        //or if machinecode not same in the row of table
                                        //begin transact
                                        strCmdInsert3.Connection = Conn;
                                        strCmdInsert3.CommandText = "INSERT INTO tmst_machine( " +
                                                                  "    MachineCode, " +
                                                                  "    MachineBrandID, " +
                                                                  "    MachineStyleID, " +
                                                                  "    MachineTypeID, " +
                                                                  "    MachineCompanyID, " +
                                                                  "    MachineQty ) " +
                                                                  "SELECT " +
                                                                  "    '" + strMachineCode + "', " +
                                                                  "    mb.MachineID, " +
                                                                  "    mbs.MachineStyleID, " +
                                                                  "    mbt.MachineTypeID, " +
                                                                  "    '" + intCompanyID + "', " +
                                                                  "    '" + intQty + "' " +
                                                                  "FROM " +
                                                                  "    tstc_receive_machine_detail rmd " +
                                                                  "INNER JOIN tmst_machine_brand mb on mb.MachineBrandName = rmd.ReceiveMachineDetail_Brand " +
                                                                  "INNER JOIN tmst_machine_brand_style mbs on mbs.MachineStyleName = rmd.ReceiveMachineDetail_Style " +
                                                                  "INNER JOIN tmst_machine_brand_type mbt on mbt.MachineTypeName = rmd.ReceiveMachineDetail_Type " +
                                                                  "WHERE " +
                                                                  "    ReceiveCode = '" + lblRcvCode.Text.ToString() + "'";
    
                                        strCmdInsert3.ExecuteNonQuery();
                                        //end transact
                                        //drSelection.Close();
                                    }
                                //}
                            }
                        }
                    }//dr.Close();
                }
            }
            catch (Exception xx) { Response.Write("" + xx); }
            finally { Conn.Close(); }
        }


    FYI: in SqlDataReader dr my records is 2 and then try to insert into tmst_machine, and when i see tmst_machine is have 4 records not 2?

    this is show in table tmst_machine:

    tmst_machine

    can you help me?

    thank's for all your help guys, regards

    sorry for my bad english.

    Thursday, March 28, 2013 5:13 AM

All replies

  • User-1716253493 posted

    Maybe because you loop dr(2x) and drselection(2x)

    2 * 2 = 4

    Thursday, March 28, 2013 5:26 AM
  • User-1562956770 posted

    Maybe because you loop dr(2x) and drselection(2x)

    2 * 2 = 4

    thank's oned_gk i wil try fix the looping section and report back to here

    Thursday, March 28, 2013 5:32 AM