C# Fill Datagridview Between 2 Datetimepicker selected value

Question

Below these code is what i've tried to achieve these but unfortunately i failed to do that, that's why i'm here. I really need help guys in solving these problem of mine.

Expected Output:
If user select a date between two datetimepicker, for example is from Sep 1 to Sep 21, so the row with the date of Sep 1 to 21 will be only displayed.

Nothing Happens here below these code.

NOTE:

DateReturn and DateReturn2 is both datetimepicker, the first one is DateReturn

public partial class SIMSSupplier : UserControl
{
ADDSupplier supply;
ADDPReturns returns;
public SIMSSupplier()
{
    InitializeComponent();

}
public DataTable dbdataset;
public void CustomDatetime()
{
    var now = DateTime.Now;
    var first = new DateTime(now.Year, now.Month, 1);
    var last = first.AddMonths(1).AddDays(-1);
    DateReturn.Value = new DateTime(now.Year, now.Month, 1);
    DateReturn2.Value = last;
}
public void ReturnDetails()
{
    FillSupplier(PurchaseReturn, "Select ReturnID, Supplier, Itemdescription, Modelno, Srp, Code, Date, Remarks from Purchase_Return");
}
private void FillSupplier(DataGridView grid, string request)
{
    using (var con = SQLConnection.GetConnection())
    using (var select = new SqlCommand(request, con))
    using (var sda = new SqlDataAdapter())
    {
        dbdataset = new DataTable();
        sda.SelectCommand = select;
        sda.Fill(dbdataset);
        grid.DataSource = new BindingSource() { DataSource = dbdataset };
        sda.Update(dbdataset);
    }
}
 private void SIMSSupplier_Load(object sender, EventArgs e)
 {           
    ReturnDetails();
    CustomDatetime();
 }
 private void DateReturn2_ValueChanged(object sender, EventArgs e)
 {
    using (var con = SQLConnection.GetConnection())
    {
        using (var select = new SqlCommand("Select * from Purchase_Return where Date between '" + DateReturn.Value.ToString() + "' and '" + DateReturn2.Value.ToString() + "'", con))
        {
            using (var sd = new SqlDataAdapter(select))
            {
                var dv = new DataView(dbdataset);
                PurchaseReturn.DataSource = dv;
            }
        }
    }
 }
}

I tried to select Sept 22, but nothing happen

have you debug on `DateReturn2_ValueChanged` ? and see what is the content of `dv` ?. i think you need to initialized new datatable instead of using dataView. or if you don't want to intiatlized new datatable, try to clear dbdataset before hand — chopperfield, Sep 21 '18 at 09:24
@chopperfield I tried to debug it, dv is null. Can you elaborate your suggestion. — Anonymous, Sep 21 '18 at 09:33

chopperfield · Answer 1 · 2018-09-24T02:07:14.893

1

I'm not sure if this 100% correct, but the main idea is try using new datatable to set it as datasource

private void DateReturn2_ValueChanged(object sender, EventArgs e)
 {
    using (var con = SQLConnection.GetConnection())
    {
         using (var select = new SqlCommand("Select * from Purchase_Return where Date between @date1 and @date2", con))
        {
            select.Parameters.Add("@date1",SqlDbType.Date).value= DateReturn1.Value;
            select.Parameters.Add("@date2",SqlDbType.Date).value= DateReturn2.Value;

            using (var sd = new SqlDataAdapter(select))
            {                    
                DataTable newDT= new DataTable();
                sd.selectcommand = select;
                sd.fill(newDT);
                //PurchaseReturn.DataSource = null;
                PurchaseReturn.DataSource = newDT;        
            }
        }
    }
 }

edited Sep 24 '18 at 02:07

answered Sep 21 '18 at 09:44

chopperfield

559
1
7
25

Downvoted - because you aren't using parameters in your answer - https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement - https://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection - and many more links – Rand Random Sep 21 '18 at 09:46
@RandRandom Really ?, that's more like suggestion dude. iam just try to give him an idea/concept to solved his problem – chopperfield Sep 21 '18 at 09:47
@RandRandom by doing so, you are discourage people to give an Answer, Comment instead of downvoting – chopperfield Sep 21 '18 at 09:49
1

Yes, really. It's that big of a problem, not a single developer on this whole world should ever ever concat parameters by string and always use parameters - you never want to face the same problems as Bobby faces - https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Rand Random Sep 21 '18 at 09:51
@RandRandom There you go ~ – chopperfield Sep 21 '18 at 09:57
Fixed it for ya. – Rand Random Sep 21 '18 at 10:00
1

Addwithvalue is not recommend in using parameters, downvoted you should give him a proper way. https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – Sep 21 '18 at 12:29

C# Fill Datagridview Between 2 Datetimepicker selected value

1 Answers1