locked
DeleteDataRow from Datatable by specific value RRS feed

  • Question

  • User35050261 posted

    this.dsInitial.Tables["P"]  have value looks...

    ProductID -- ProductName------------Generic Name-------------------------------

    P-00001         Lcef Capsule-200gm      Cefixime Trihydrate 200mg 1X 6's 135.34
    P-00004 Gastral 20mg Omeprazole 20 mg 6 X 10's 225.56
    P-00005 Peptral 20 mg Esomeprazole 20 mg 6 X 10's 315.79
    P-00006 Roxilab 250 mg Cefuroxime Axetil 250 mg 2 X 6's 225.56
    P-00007 Roxilab 500 mg Cefuroxime Axetil 500mg 1 X 6's 203.01
    P-00008 Roxilab PFS-70ml Cefuroxime Axetil 125mg/5ml 1's 149.41
    P-00009 Ketolab-10mg Ketorolac Tromethamine 10 mg 2 X 10's 180.45
    P-00010 Labcal-D Calcium Carbonate + Vitamin D 3 X 10's 112.78
    P-00011 Azilab 500mg Azithromicin Dihydrate 1 X 6's 135.34
    P-00012 Azilab PFS-35ml Azithromicin Dihydrate 250mg/5ml 35 ml X 1's 67.67
    P-00013 Ciproaid-500mg Ciprofloxacin 500mg 2 X 10's 210.53
    P-00014 Ciproaid PFS-60ml Ciprofloxacin 250mg/5ml 60 ml X 1's 67.67
    P-00015 Bisocard-5mg Bisoprolol Hemifumarate 5 mg 3 X 10's 225.56
    P-00016 Cardolab -5mg Amlodipine 5 mg 5 X 10's 375.94
    P-00017 Preslo-H Losartan potassium 50 mg + HCTZ 12.5 mg 3 X 10's 180.45
    P-00018 Flexilab 10 mg Baclofen 10 mg 3 X 10's 180.45
    P-00019 Montilab 10 mg Montilukast 10 mg 1 X 10's 112.78

    this.dsPD.Tables["STM"].Rows.Count   looks ..

    Year  ----------Month-------ProductID-------------

    2012                   2                     P-00001                       Lcef Capsule-200gm Cefixime Trihydrate 200mg 1X 6's 135.34 50.00 0.00 6767.00 0.00

    Now like to see this.dsInitial.Tables["P"] ...like this 

    IN SHORT

    I Like To Delete Row From this.dsInitial.Tables["P"] .. by productID, productID comes from this.dsPD.Tables["STM"]

    P-00004 Gastral 20mg Omeprazole 20 mg 6 X 10's 225.56
    P-00005 Peptral 20 mg Esomeprazole 20 mg 6 X 10's 315.79
    P-00006 Roxilab 250 mg Cefuroxime Axetil 250 mg 2 X 6's 225.56
    P-00007 Roxilab 500 mg Cefuroxime Axetil 500mg 1 X 6's 203.01
    P-00008 Roxilab PFS-70ml Cefuroxime Axetil 125mg/5ml 1's 149.41
    P-00009 Ketolab-10mg Ketorolac Tromethamine 10 mg 2 X 10's 180.45
    P-00010 Labcal-D Calcium Carbonate + Vitamin D 3 X 10's 112.78
    P-00011 Azilab 500mg Azithromicin Dihydrate 1 X 6's 135.34
    P-00012 Azilab PFS-35ml Azithromicin Dihydrate 250mg/5ml 35 ml X 1's 67.67
    P-00013 Ciproaid-500mg Ciprofloxacin 500mg 2 X 10's 210.53
    P-00014 Ciproaid PFS-60ml Ciprofloxacin 250mg/5ml 60 ml X 1's 67.67
    P-00015 Bisocard-5mg Bisoprolol Hemifumarate 5 mg 3 X 10's 225.56
    P-00016 Cardolab -5mg Amlodipine 5 mg 5 X 10's 375.94
    P-00017 Preslo-H Losartan potassium 50 mg + HCTZ 12.5 mg 3 X 10's 180.45
    P-00018 Flexilab 10 mg Baclofen 10 mg 3 X 10's 180.45
    P-00019 Montilab 10 mg Montilukast 10 mg 1 X 10's 112.78

    Wednesday, October 10, 2012 2:44 AM

Answers

  • User1429614832 posted
    string strProductsID = null;
    
    if (arrProdIds.Length == 1)
    {
       strProductsID = "('" + arrProdIds[0]["ProductID"] + "')";
    }
    else
    {
        for (int i = 0; i < arrProdIds.Length; i++) 
         { 
               if (i == 0) 
                   strProductsID = "('" + arrProdIds[i]["ProductID"] +  "',"; 
               else if (i == arrProdIds.Length - 1) 
                   strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')"; 
               else 
                    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',"; 
         }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2012 7:44 AM

All replies

  • User1429614832 posted

    Try the following code for your requirement --

     DataRow[] arrProdIds = dsPD.Tables["STM"].Select("ProductID <> 0");
    
    string strProductsID = null;
    for(int i = 0; i < arrProdIds .Length;i++)
    {
         if (i == 0)
              strProductsID = "(" + arrProdIds [i]["ProductID"]+",";
         else if (i == arrProdIds .Length - 1)
                strProductsID = strProductsID+ arrProdIds [i]["ProductID"] + ")";
          else
               strProductsID= strProductsID+ arrProdIds [i]["ProductID"]+",";
     }
    
    
    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);
    
    foreach (DataRow row in rowFiltered )
    {
         row.Delete();
    }
    
    dsInitial.Tables["P"].AcceptChanges();
     

    In the above code arrProdIds contains all the product Ids in table dsPD.Tables["STM"].

    Then we are preparing a string of these product Ids which are comma separated and enclosed in braces.

    Later rowFiltered contains the rows that are filtered from table dsInitial.Tables["P"] that has same Product Id as in dsPD.Tables["STM"]..

    Finally we are deleting all those rows from dsInitial.Tables["P"] that contains Product Id in PD.Tables["STM"].

    Wednesday, October 10, 2012 3:38 AM
  • User35050261 posted

    void btnSave_Click(object sender, EventArgs e)
    {

    try
    {
    this.showInformation("Saving data. Please wait it may take time...");
    this.dsPD.Tables["STM"].Clear();
    this.objDC.getDataFromControl(this.dsPD.Tables["STM"], "Year='" + cmbYear + "'", this.pnlMain);
    this.objDC.SetUserCodeForDataset(ref dsPD);

    DataTable dt = new DataTable();
    dt = dsPD.Tables["STC"].GetChanges();
    dsPD.Tables["STC"].Clear();

    foreach (DataRow dr in dt.Rows)
    {
    DataRow newDr = this.dsPD.Tables["STC"].NewRow();
    newDr["Year"] = dr["Year"];
    newDr["Month"] = dr["Month"];
    newDr["ProductID"] = dr["ProductID"];
    newDr["TP"] = dr["TP"];
    newDr["TargetQty"] = dr["TargetQty"];
    newDr["TotalValue"] = dr["TotalValue"];

    newDr["Location"] = dr["Location"];
    newDr["MachineNameIP"] = dr["MachineNameIP"];
    newDr["Transfered"] = dr["Transfered"];
    newDr["HOTransfered"] = dr["HOTransfered"];
    newDr["Addedby"] = dr["Addedby"];
    newDr["DateAdded"] = dr["DateAdded"];
    newDr["Updatedby"] = dr["Updatedby"];
    newDr["DateUpdated"] = dr["DateUpdated"];
    dsPD.Tables["STC"].Rows.Add(newDr);
    }
    this.objST.SaveSTData(ref dsPD);
    this.hideInformation();
    this.InitialData();


    #region
    this.objST.GetSTData(ref this.dsPD, cmbYear.Value.ToString(), cmbMonth.Value.ToString());


    //////////////////////////
    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> 0");
    string strProductsID = null;
    for (int i = 0; i < arrProdIds.Length; i++)
    {
    if (i == 0)
    strProductsID = "(" + arrProdIds[i]["ProductID"] + ",";
    else if (i == arrProdIds.Length - 1)
    strProductsID = strProductsID + arrProdIds[i]["ProductID"] + ")";
    else
    strProductsID = strProductsID + arrProdIds[i]["ProductID"] + ",";
    }
    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);
    foreach (DataRow row in rowFiltered)
    {
    row.Delete();
    }

    dsInitial.Tables["P"].AcceptChanges();

    ///////////////////////////////////////////////////
    if (this.dsPD.Tables["STM"].Rows.Count == 0)
    {
    foreach (DataRow dr in this.dsInitial.Tables["P"].Rows)
    {
    DataRow drNew = dsPD.Tables["STC"].NewRow();
    drNew["Year"] = cmbYear.Value;
    drNew["Month"] = cmbMonth.Value;
    drNew["ProductID"] = dr["ProductID"];
    drNew["ProductName"] = dr["ProductName"];
    drNew["GenericName"] = dr["GenericName"];
    drNew["PackSize"] = dr["PackSize"];
    drNew["TP"] = dr["TP"];
    drNew["TargetQty"] = 0;
    drNew["actualqty"] = 0;
    drNew["TotalValue"] = 0;
    drNew["actualvalue"] = 0;
    this.dsPD.Tables["STC"].Rows.Add(drNew);
    }
    this.grdSTChild.dataSource = this.dsPD.Tables["STC"];
    this.dsPD.Tables["STC"].AcceptChanges();
    }
    else
    {
    foreach (DataRow dr in this.dsInitial.Tables["P"].Rows)
    {
    DataRow drNew = dsPD.Tables["STC"].NewRow();
    drNew["Year"] = cmbYear.Value;
    drNew["Month"] = cmbMonth.Value;
    drNew["ProductID"] = dr["ProductID"];
    drNew["ProductName"] = dr["ProductName"];
    drNew["GenericName"] = dr["GenericName"];
    drNew["PackSize"] = dr["PackSize"];
    drNew["TP"] = dr["TP"];
    drNew["TargetQty"] = 0;
    drNew["actualqty"] = 0;
    drNew["TotalValue"] = 0;
    drNew["actualvalue"] = 0;
    this.dsPD.Tables["STC"].Rows.Add(drNew);
    }
    this.grdSTChild.dataSource = this.dsPD.Tables["STC"];
    this.dsPD.Tables["STC"].AcceptChanges();
    }
    #endregion

    return;
    }
    catch (Exception ex)
    {
    this.hideInformation();
    this.saveError("Error: \n" + ex.Message + "\n\nCall Stack: \n" + ex.StackTrace, this.Name, "btnSave_Click", true);
    return;
    }
    }

    Get error for line 

    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> 0");

    error

    Cannot perform '<>' operation on System.String and System.Int32

    Wednesday, October 10, 2012 4:24 AM
  • User1429614832 posted

    Cannot perform '<>' operation on System.String and System.Int32

    This error is occuring becaue as per the code sample I have assumed that the ProductId is an integer coulmn and hence applied the operator <> for checking integer value as not equal to 0.

    If ProductId is string in your case you get to use the condition ProductId <> ' '.

    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> ''");

    Wednesday, October 10, 2012 4:51 AM
  • User35050261 posted

    Cannot perform '<>' operation on System.String and System.Int32

    This error is occuring becaue as per the code sample I have assumed that the ProductId is an integer coulmn and hence applied the operator <> for checking integer value as not equal to 0.

    If ProductId is string in your case you get to use the condition ProductId <> ' '.

    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> ''");

    void btnSave_Click(object sender, EventArgs e)
    {

    try
    {
    this.showInformation("Saving data. Please wait it may take time...");
    this.dsPD.Tables["STM"].Clear();
    this.objDC.getDataFromControl(this.dsPD.Tables["STM"], "Year='" + cmbYear + "'", this.pnlMain);
    this.objDC.SetUserCodeForDataset(ref dsPD);

    DataTable dt = new DataTable();
    dt = dsPD.Tables["STC"].GetChanges();
    dsPD.Tables["STC"].Clear();

    foreach (DataRow dr in dt.Rows)
    {
    DataRow newDr = this.dsPD.Tables["STC"].NewRow();
    newDr["Year"] = dr["Year"];
    newDr["Month"] = dr["Month"];
    newDr["ProductID"] = dr["ProductID"];
    newDr["TP"] = dr["TP"];
    newDr["TargetQty"] = dr["TargetQty"];
    newDr["TotalValue"] = dr["TotalValue"];

    newDr["Location"] = dr["Location"];
    newDr["MachineNameIP"] = dr["MachineNameIP"];
    newDr["Transfered"] = dr["Transfered"];
    newDr["HOTransfered"] = dr["HOTransfered"];
    newDr["Addedby"] = dr["Addedby"];
    newDr["DateAdded"] = dr["DateAdded"];
    newDr["Updatedby"] = dr["Updatedby"];
    newDr["DateUpdated"] = dr["DateUpdated"];
    dsPD.Tables["STC"].Rows.Add(newDr);
    }
    this.objST.SaveSTData(ref dsPD);
    this.hideInformation();
    this.InitialData();


    #region
    this.objST.GetSTData(ref this.dsPD, cmbYear.Value.ToString(), cmbMonth.Value.ToString());


    //////////////////////////
    //DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> 0");
    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> ''");
    string strProductsID = null;
    for (int i = 0; i < arrProdIds.Length; i++)
    {
    if (i == 0)
    strProductsID = "(" + arrProdIds[i]["ProductID"] + ",";
    else if (i == arrProdIds.Length - 1)
    strProductsID = strProductsID + arrProdIds[i]["ProductID"] + ")";
    else
    strProductsID = strProductsID + arrProdIds[i]["ProductID"] + ",";
    }


    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);


    foreach (DataRow row in rowFiltered)
    {
    row.Delete();
    }

    dsInitial.Tables["P"].AcceptChanges();

    ///////////////////////////////////////////////////
    if (this.dsPD.Tables["STM"].Rows.Count == 0)
    {
    foreach (DataRow dr in this.dsInitial.Tables["P"].Rows)
    {
    DataRow drNew = dsPD.Tables["STC"].NewRow();
    drNew["Year"] = cmbYear.Value;
    drNew["Month"] = cmbMonth.Value;
    drNew["ProductID"] = dr["ProductID"];
    drNew["ProductName"] = dr["ProductName"];
    drNew["GenericName"] = dr["GenericName"];
    drNew["PackSize"] = dr["PackSize"];
    drNew["TP"] = dr["TP"];
    drNew["TargetQty"] = 0;
    drNew["actualqty"] = 0;
    drNew["TotalValue"] = 0;
    drNew["actualvalue"] = 0;
    this.dsPD.Tables["STC"].Rows.Add(drNew);
    }
    this.grdSTChild.dataSource = this.dsPD.Tables["STC"];
    this.dsPD.Tables["STC"].AcceptChanges();
    }
    else
    {
    foreach (DataRow dr in this.dsInitial.Tables["P"].Rows)
    {
    DataRow drNew = dsPD.Tables["STC"].NewRow();
    drNew["Year"] = cmbYear.Value;
    drNew["Month"] = cmbMonth.Value;
    drNew["ProductID"] = dr["ProductID"];
    drNew["ProductName"] = dr["ProductName"];
    drNew["GenericName"] = dr["GenericName"];
    drNew["PackSize"] = dr["PackSize"];
    drNew["TP"] = dr["TP"];
    drNew["TargetQty"] = 0;
    drNew["actualqty"] = 0;
    drNew["TotalValue"] = 0;
    drNew["actualvalue"] = 0;
    this.dsPD.Tables["STC"].Rows.Add(drNew);
    }
    this.grdSTChild.dataSource = this.dsPD.Tables["STC"];
    this.dsPD.Tables["STC"].AcceptChanges();
    }
    #endregion

    return;
    }
    catch (Exception ex)
    {
    this.hideInformation();
    this.saveError("Error: \n" + ex.Message + "\n\nCall Stack: \n" + ex.StackTrace, this.Name, "btnSave_Click", true);
    return;
    }

    getting value strProductsID = (P-00001,

    after executes this line

    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);

    Error

    Syntax error: Missing operand after '' operator.

    Wednesday, October 10, 2012 6:02 AM
  • User1429614832 posted
    DataRow[] arrProdIds = dsPD.Tables["STM"].Select("ProductID <> ''"); 
     
    string strProductsID = null;
    for (int i = 0; i < arrProdIds.Length; i++) 
    { 
        if (i == 0) 
            strProductsID = "('" + arrProdIds[i]["ProductID"] +  "',"; 
        else if (i == arrProdIds.Length - 1) 
            strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')"; 
        else 
            strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',"; 
    }
     
    have modified a section of code assuming that ProductId is string in your case.
    Try out by changing the above patch of code.

    Wednesday, October 10, 2012 7:00 AM
  • User35050261 posted

    DataRow[] arrProdIds = dsPD.Tables["STM"].Select("ProductID <> ''");
     
    string strProductsID = null;
    for (int i = 0; i < arrProdIds.Length; i++)
    {
        if (i == 0)
            strProductsID = "('" + arrProdIds[i]["ProductID"] +  "',";
        else if (i == arrProdIds.Length - 1)
            strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')";
        else
            strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',";
    }
     
    have modified a section of code assuming that ProductId is string in your case.
    Try out by changing the above patch of code.

    I have change the code below

    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> ''");

    string strProductsID = null;
    for (int i = 0; i < arrProdIds.Length; i++)
    {
    if (i == 0)
    strProductsID = "('" + arrProdIds[i]["ProductID"] + "',";
    else if (i == arrProdIds.Length - 1)
    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')";
    else
    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',";
    }



    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);


    foreach (DataRow row in rowFiltered)
    {
    row.Delete();
    }

    dsInitial.Tables["P"].AcceptChanges();

    strProductsID =  ('P-00001',  

    when I  out from for loop

    after that

    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);


    through error

    * please provide me full code for that 

    Syntax error: Missing operand after '' operator.

    System.Data

    at System.Data.ExpressionParser.Parse()
    at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
    at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
    at System.Data.DataTable.Select(String filterExpression)
    at LPLERP.DM.Client.frmSalesTarget.btnSave_Click(Object sender, EventArgs e) in E:\LPLERP\LPLERP.DM.Client\Master Info\frmSalesTarget.cs:line 286

    Wednesday, October 10, 2012 7:22 AM
  • User1429614832 posted
    string strProductsID = null;
    
    if (arrProdIds.Length == 1)
    {
       strProductsID = "('" + arrProdIds[0]["ProductID"] + "')";
    }
    else
    {
        for (int i = 0; i < arrProdIds.Length; i++) 
         { 
               if (i == 0) 
                   strProductsID = "('" + arrProdIds[i]["ProductID"] +  "',"; 
               else if (i == arrProdIds.Length - 1) 
                   strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')"; 
               else 
                    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',"; 
         }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2012 7:44 AM
  • User35050261 posted

    string strProductsID = null;
    
    if (arrProdIds.Length == 1)
    {
       strProductsID = "('" + arrProdIds[0]["ProductID"] + "')";
    }
    else
    {
        for (int i = 0; i < arrProdIds.Length; i++)
         {
               if (i == 0)
                   strProductsID = "('" + arrProdIds[i]["ProductID"] +  "',";
               else if (i == arrProdIds.Length - 1)
                   strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')";
               else
                    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',";
         }
    }

    The final full code is 

    DataRow[] arrProdIds = dsPD.Tables["STC"].Select("ProductID <> ''");
    string strProductsID = null;
    if (arrProdIds.Length == 1)
    {
    strProductsID = "('" + arrProdIds[0]["ProductID"] + "')";
    }
    else
    {
    for (int i = 0; i < arrProdIds.Length; i++)
    {
    if (i == 0)
    strProductsID = "('" + arrProdIds[i]["ProductID"] + "',";
    else if (i == arrProdIds.Length - 1)
    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "')";
    else
    strProductsID = strProductsID + "'" + arrProdIds[i]["ProductID"] + "',";
    }
    }

    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID IN " + strProductsID);
    foreach (DataRow row in rowFiltered)
    {
    row.Delete();
    }
    dsInitial.Tables["P"].AcceptChanges();

    It will be done by another process

    int i = 0;

    foreach(DataRow dr in this.dsPD.Tables["STC"].Rows)
    {
    string strProductsID = this.dsPD.Tables["STC"].Rows[i]["ProductID"].ToString();
    DataRow[] rowFiltered = dsInitial.Tables["P"].Select("ProductID = '" + strProductsID + "' "); //" + strProductsID);
    foreach (DataRow row in rowFiltered)
    {
    row.Delete();
    }

    dsInitial.Tables["P"].AcceptChanges();
    i++;

    Explained

    this.dsPD.Tables["STC"].Rows  have value so it will be rounded 

    then each time I get the productID value

    by that value I can find -- dsInitial.Tables["P"].Select("ProductID = '" + strProductsID + "' ");

    then delete the row 

    total event will be execute until the this.dsPD.Tables["STC"].Rows 's rows have value

    Wednesday, October 10, 2012 7:57 AM