where condition and null-VBForums
Results 1 to 9 of 9

Thread: where condition and null

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2014
    Posts
    148

    Resolved where condition and null

    friends
    this code work with me
    Code:
                com.CommandText = @"SELECT Safes.Income AS ايرادات, Safes.Expensive AS مصروفات, Safes.Beian AS البيان, Safes.InvoiceNo AS [رقم الفاتورة], Format(RegDate,'yyyy/mm/dd') AS [تاريخ التسجيل], IIf(([ClientName]) Is Null,'_____',[ClientName]) AS [اسم العميل], IIf(([SupplierName]) Is Null,'_______',[SupplierName]) AS [اسم المورد], Safes.SafeID AS [رقم الحركة], Expensives.ExpensiveName AS [اسم المصروف], Safes.Money_Income_Expensive_Type as [نوع الايداع], Safes.Currencys as [العمله], Safes.bankName as [اسم البنك]
                FROM ((Safes LEFT JOIN Suppliers ON Safes.SupplierID = Suppliers.SupplierID) LEFT JOIN Clients ON Safes.ClientID = Clients.ClientID) LEFT JOIN Expensives ON Safes.ExpensiveID = Expensives.ExpensiveID
                where (@Currencys IS NULL OR Safes.Currencys = @Currencys)
                And (@Money_Income_Expensive_Type IS NULL OR Safes.Money_Income_Expensive_Type = @Money_Income_Expensive_Type)
                And (@bankName IS NULL OR Safes.bankName = @bankName)
    
                    if (this.cboTypeOfCurrency.SelectedIndex == -1)
                    {
                        com.Parameters.AddWithValue("@Currencys", DBNull.Value);
                    }
                    else
                    {
                        com.Parameters.AddWithValue("@Currencys", this.cboTypeOfCurrency.Text);
                    }
    
    
    
                    if (this.cboTypeOf_Income_Expensive_Money.SelectedIndex == -1)
                    {
                        com.Parameters.AddWithValue("@Money_Income_Expensive_Type", DBNull.Value);
                    }
                    else
                    {
                        com.Parameters.AddWithValue("@Money_Income_Expensive_Type", this.cboTypeOf_Income_Expensive_Money.Text);
                    }
    
    
    
                    if (this.cboBankName.SelectedIndex == -1)
                    {
                        com.Parameters.AddWithValue("@bankName", DBNull.Value);
                    }
                    else
                    {
                        com.Parameters.AddWithValue("@bankName", this.cboBankName.Text);
                    }
    
                DataTable dt = new DataTable();
                dt.Load(com.ExecuteReader());

    what is my problem i want to add criteria on date field to above sql:

    i want i can enter date range or make date null

    i try like this but not work with me

    (Safes.RegDate between @BeginDate and @EndDate)


    Code:
                    if (dPFrom.Text == "")
                    {
                        com.Parameters.AddWithValue("@BeginDate", DBNull.Value);
                    }
                    else
                    {
                        com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.ToShortDateString());
                    }
    
                    if (dPTo.Text == "")
                    {
    
                        com.Parameters.AddWithValue("@EndDate", DBNull.Value);
                    }
                    else
                    {
                        com.Parameters.AddWithValue("@EndDate", this.dPTo.SelectedDate.Value.ToShortDateString());
                    }
    thank you very much

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,890

    Re: where condition and null

    Thread moved from the 'VB.Net' forum to the 'C#' forum

    You cannot use Between and Null together, so you need to build the command text with one or the other, then (if apt) add parameters for the values.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2014
    Posts
    148

    Re: where condition and null

    Quote Originally Posted by si_the_geek View Post
    Thread moved from the 'VB.Net' forum to the 'C#' forum

    You cannot use Between and Null together, so you need to build the command text with one or the other, then (if apt) add parameters for the values.
    si_the_geek
    thank you for your reply


    i need to give me the answer because my application and future application depend on date

    so i want you to show me how can i handle this problem

    i don't know the answer

    what do you mean by (if apt)

    I want to allow the user to choose a date between two dates or not to choose date at all

    thank you in advance
    Last edited by abcd_2014; May 18th, 2017 at 05:34 PM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,890

    Re: where condition and null

    Here's some pseudo-code to explain it more clearly:
    Code:
    if ([date range has been entered])
    {
       com.CommandText = "... AND (Safes.RegDate between @BeginDate and @EndDate)"
    } 
    else 
    {
       com.CommandText = "... "
    }
    
    ...
    
    if ([date range has been entered])
    {
        com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.ToShortDateString());
        com.Parameters.AddWithValue("@EndDate", this.dPTo.SelectedDate.Value.ToShortDateString());
    }
    If you want to allow the user to specify just BeginDate (or just EndDate) then you can't use Between, so use > or >=

    Quote Originally Posted by abcd_2014 View Post
    what do you mean by (if apt)
    "if appropriate" or "if necessary"

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    95,215

    Re: where condition and null

    It doesn't make sense to use BETWEEN if you aren't going to require the user to enter both dates. I'd do it like this:
    csharp Code:
    1. WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate)
    Also, this:
    csharp Code:
    1. com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.ToShortDateString());
    is just diabolical. DO NOT EVER convert a DateTime to a String unless it is for display or serialisation. If you are trying to compare a value to a date column then the value needs to be a date, NOT text, so don't add a parameter with a String as the value. That code should be:
    csharp Code:
    1. com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.Date);
    That's how you zero the time portion of a DateTime.

    2007-2016

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Feb 2014
    Posts
    148

    Re: where condition and null

    Quote Originally Posted by si_the_geek View Post
    Here's some pseudo-code to explain it more clearly:
    Code:
    if ([date range has been entered])
    {
       com.CommandText = "... AND (Safes.RegDate between @BeginDate and @EndDate)"
    } 
    else 
    {
       com.CommandText = "... "
    }
    
    ...
    
    if ([date range has been entered])
    {
        com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.ToShortDateString());
        com.Parameters.AddWithValue("@EndDate", this.dPTo.SelectedDate.Value.ToShortDateString());
    }
    If you want to allow the user to specify just BeginDate (or just EndDate) then you can't use Between, so use > or >=

    "if appropriate" or "if necessary"

    thank you for your user idea
    thank you

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2014
    Posts
    148

    Re: where condition and null

    Quote Originally Posted by jmcilhinney View Post
    It doesn't make sense to use BETWEEN if you aren't going to require the user to enter both dates. I'd do it like this:
    csharp Code:
    1. WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate)
    Also, this:
    csharp Code:
    1. com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.ToShortDateString());
    is just diabolical. DO NOT EVER convert a DateTime to a String unless it is for display or serialisation. If you are trying to compare a value to a date column then the value needs to be a date, NOT text, so don't add a parameter with a String as the value. That code should be:
    csharp Code:
    1. com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.Date);
    That's how you zero the time portion of a DateTime.
    i will tell you why i use
    this.dPFrom.SelectedDate.Value.ToShortDateString()

    because this code display date and time
    Code:
    this.dPFrom.SelectedDate.Value.Date

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    95,215

    Re: where condition and null

    Quote Originally Posted by abcd_2014 View Post
    i will tell you why i use
    this.dPFrom.SelectedDate.Value.ToShortDateString()

    because this code display date and time
    Code:
    this.dPFrom.SelectedDate.Value.Date
    That code doesn't "display" anything. That code produces a DateTime value with the time zeroed. A date/time value with the time zeroed, whether in C# code or in a database, is the equivalent of just a date. What I have suggested is the proper way to do it. It's the way you should do it.

    2007-2016

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2014
    Posts
    148

    Re: where condition and null

    Quote Originally Posted by jmcilhinney View Post
    That code doesn't "display" anything. That code produces a DateTime value with the time zeroed. A date/time value with the time zeroed, whether in C# code or in a database, is the equivalent of just a date. What I have suggested is the proper way to do it. It's the way you should do it.
    thank you
    i test your answer and i find it very useful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.