Filtering Date in DataGridView VB.NET



Hey all,



Came across this site while scouring the web for a solution to my problem. All of the answers on here seem very helpful and non judgmental. That is hard to come by in a lot of programming forums, so thanks.



My Question.



I am using VS 2008 and have a DataGridView linked to a SQL table. One of the columns in my table is a dueDate column. On my form I have a set of check boxes and one of them is set to display all rows that have a dueDate > today's date (well that's what I want it to do). I have set up another filter on other criteria in other columns and am able to get it to work fine but I can't seem to find the trick to get the date comparison to work.



Here's the line I am having an issue with.



```
BillLogBindingSource.Filter = ("dueDate < System.DateTime.Now.Date")
```

Thanks in advance for any advice.
filter at the backend server only?
If I am not wrong, you are trying to read or loop through the rows from datatable. This error states that the datasource(DataTable) is not having any rows. To avoid this exception use this before you read or loop through datatable:  


```
if(dt.Rows.Count  > 0 ){
     //Do your stuff here.
}
```
  

  

  

--Amit java.sql.timestamp is a subclass of java.util.date - it should work the same for comparison purposes:

<https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/timestamp.html>

if you specifically need a date, you could convert it back pretty easily; check if isinstance(java.sql.timestamp… [@najoua.abbaci](/u/najoua.abbaci)

mabye the flow can be adopted to following:

assign:

leftside: drresult (datatype: datarow)

right side:

dt\_datepv.asenumerable.orderby(function ( r ) datetime.parseexact(r(0).tostring.trim, “dd.mm.yyyy”, system.globalization.cultureinfo.currentuiculture.datetimeformat)).last()

so… You could try using the in line view like this:


Select table1.col1, table1.col2, table1…., table2.col1, table2.col2, table2….  

 from  

(SELECT *  

FROM dbo.Shaft\_Scales  

WHERE dbo.Shaft\_Scales.ID = (SELECT max(ID) from dbo.Shaft\_Scales ID) as table1,  

(SELECT *  

FROM OtherTable  

WHERE dbo.OtherTable.ID = (SELECT max(ID) from dbo.OtherTable ID) as table2


—————- another approach from meandyou 


This works on [at least some] SQL processors


SELECT T1.* , T2.*  

FROM table1 T1, table2 T2  

WHERE …


Do we have to discuss why not to use “SELECT * ” in your SQL?<pre>


Try this


SELECT * FROM T1 as a, T2 as b WHERE a.ID = (SELECT max(ID) from T1)  

and b.ID = (SELECT max(ID) from T2);</pre> Looks like YYMMDD to me. I would try to verify this in the documentation or whatever information source you have on the old database. You could also look for inappropriate numbers like MM not between 1 and 12 and DD not between 1 and 31. If you find any inappropriate numbers than this is the wrong approach. This does not look like a date based on an offset number of days after a start date because the start date would have to be about 2600 years ago. Resolved:  

  

FORM 1.  

  

Copy Code

```
private void btnModificaContractul\_Click(object sender, EventArgs e)
        {


            string[] IDProdusContractPrelugit = new string[dataProduseContract.Rows.Count];
            string[] IDProdusContractRetras = new string[dataProduseContract.Rows.Count];

            for (int i = 0; i < dataProduseContract.Rows.Count; i++)
            {

                if (Convert.ToBoolean(dataProduseContract.Rows[i].Cells[0].Value) == false)
                {
                    IDProdusContractPrelugit[i] = dataProduseContract.Rows[i].Cells[5].Value.ToString();
                }
                else
                {
                    IDProdusContractRetras[i] = dataProduseContract.Rows[i].Cells[5].Value.ToString();
                }
            }

           

            try
            {
                frmModificaContract ModificareContract = new frmModificaContract(IDProdusContractPrelugit, IDProdusContractRetras);
                ModificareContract.ShowDialog();
            }
            catch { MessageBox.Show("Trebuie să selectaţi un contract din listă pentru a edita.", "Datele furnizate sunt incomplete."); }
           
        }
```
  

  

FORM 2.  

  

Copy Code

```
string[] IDProdusContractPrelugit;
        string[] IDProdusContractRetras;

        public frmModificaContract(string[] IDProdusContractPrelugit, string[] IDProdusContractRetras)
        {
            InitializeComponent();
            this.IDProdusContractPrelugit = IDProdusContractPrelugit;
            this.IDProdusContractRetras = IDProdusContractRetras;
            AfisareProduseContract();
        }


        void AfisareProduseContract()
        {

            foreach (string ContractPrelungit in IDProdusContractPrelugit)
            {
                if (ContractPrelungit != null)
                {
                    Program.Connection.CommandText = "select * from ContractItems WHERE ContractItemId=@ContractItemId";
                    Program.Connection.AddParameter("@ContractItemId", ContractPrelungit);

                    DataTable Table = new DataTable();
                    Program.Connection.FillDataTable(Table, true);


                    for (int i = 0; i < Table.Rows.Count; i++)
                    {
                        if (String.IsNullOrWhiteSpace(Table.Rows[i]["DeletedId"].ToString()))
                        {
                            bool validare;
                            validare = true;

                            dataProduseContract.Rows.Add(validare, Table.Rows[i]["Name"].ToString(), Table.Rows[i]["Quantity"].ToString() + " " + Table.Rows[i]["QuantityUnit"].ToString(), Table.Rows[i]["Payment"].ToString(), Table.Rows[i]["Description"].ToString(), Table.Rows[i]["ContractItemId"].ToString());
                        }
                        else
                        {
                            bool validarescos;
                            validarescos = false;
                            dataProduseContract.Rows.Add(validarescos, Table.Rows[i]["Name"].ToString(), Table.Rows[i]["Quantity"].ToString() + " " + Table.Rows[i]["QuantityUnit"].ToString(), Table.Rows[i]["Payment"].ToString(), Table.Rows[i]["Description"].ToString(), Table.Rows[i]["ContractItemId"].ToString());
                            dataProduseContract.Enabled = false;
                        }

                    }
                }
            }

            
        }
```
  

  

The problem is allwais i have Null value in array. Turns out If I tweked each method 



```
foreach (var dataColumn in readFields.Select(column => new DataColumn(columntypeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
{
    csvData.Columns.Add(dataColumn);
}

```

and in the second Method I changed it to use an index rather than column name



```
for (var count = 0; count < table.Columns.Count; count++)
{
    sqlBulkCopy.ColumnMappings.Add(count, count);
}

``` Your test is redundant, since you are already querying only for matching records. In this case, you will get no rows if there is no match, therefore you will never hit the else clause in the loop.  

  

Instead, it is enough to check if there are any matching records at all. The example below shows how (note that you can put a condition straight into Any() instead of specifying Where()).  

  

C#Copy Code

```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace CustomerMaster
{
    public partial class CustomerMaster : System.Web.UI.Page
    {
 
        private void AddData()
        {
            
            OperationsDataContext AddDb = new OperationsDataContext();
            CustomerMast cm = new CustomerMast();
            
                        
            cm.CustomerCode = txtCustCde.Text;
            cm.CustomerName = txtCustName.Text;
            cm.CustomerAddress = txtCustAdd.Text;
            cm.BrkrCode = txtBrkCode.Text;
            cm.BrokerName = txtBrkName.Text.
            cm.CrrCode = txtCrrCode.CarrierCode;
            cm.CarrierName =  txtCrrName.Text;
            cm.StnCode=sm.StationCode;
            cm.StationName = txtStnName.Text;
            cm.VatNo=txtVatNo.Text;
            cm.TinNo=txtTinNo.Text;
            cm.TMCO=txtTMCONo.Text;
            cm.PanNo = txtPAN.Text;
			
	    if(AddDb.CustomerMasts.Any(c=>c.CustomerCode == txtCustCde.Text))
            {
	        Response.Write("Error! CustomerCode Exits");
	    }
	    else
	    {
                AddDb.BrokerMasts.InsertOnSubmit(bm);
                AddDb.CarrierMasts.InsertOnSubmit(crm);
                AddDb.StationMasts.InsertOnSubmit(sm);
                AddDb.CustomerMasts.InsertOnSubmit(cm);

	        AddDb.SubmitChanges();
            }         
        }
 
        protected void btnAdd\_Click(object sender, EventArgs e)
        {
            AddData();
        }
    }
}
``` [@whyyouandi](/u/whyyouandi)

[image] whyyouandi:

i want to append another column of data.

you can add a column with [add data column] activity.

<https://www.uipath.com/activities-guide/add-data-column>

[image] whyyouandi:

and then edit the scrap date per row.

and to add a value to a target column, ch… Hi JimCreech,


It is possible to achieve a similar result in Dataiku by going through the following steps:


* run a [Window](https://knowledge.dataiku.com/latest/courses/visual-recipes/window.html) recipe using id as partitioning column, event as ordering column, and compute the lagdiff values on days
* run a [Prepare](https://knowledge.dataiku.com/latest/courses/basics/prepare-data/concept-prepare-recipe.html) recipe which will:
	+ run the assertions on the specific conditions you mentioned and create a dummy\_tmp column we'll reuse at the final step
	+ filter out the records where event == 01 (since we won't need it for the aggregation)
* run a [Groupby](https://knowledge.dataiku.com/latest/courses/basics/group-data/group-summary.html) recipe to aggregate at the id level and get the minimum of the dummy\_tmp column (which should be 1 if all conditions you described are verified, 0 otherwise).


You will find attached a sample DSS project with an example of implementation of those steps using the small example data mentioned in your post. If you are working on a regular basis with time series data, you may find this [tutorial](https://knowledge.dataiku.com/latest/courses/visual-recipes/window/window.html) on the Window recipe capabilities useful.


Hope this helps !


Best,


Harizo




[View solution in original post](/t5/General-Discussion/Using-Lag-and-Lead-in-Dataiku/m-p/19308#M1146)