-
May 18th, 2017, 01:35 PM
#1
Thread Starter
Addicted Member
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
-
May 18th, 2017, 03:48 PM
#2
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.
-
May 18th, 2017, 05:06 PM
#3
Thread Starter
Addicted Member
Re: where condition and null
Originally Posted by si_the_geek
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.
-
May 18th, 2017, 05:40 PM
#4
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 >=
Originally Posted by abcd_2014
what do you mean by (if apt)
"if appropriate" or "if necessary"
-
May 18th, 2017, 07:24 PM
#5
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:
WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate)
Also, this:
csharp Code:
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:
com.Parameters.AddWithValue("@BeginDate", this.dPFrom.SelectedDate.Value.Date);
That's how you zero the time portion of a DateTime.
-
May 19th, 2017, 06:09 AM
#6
Thread Starter
Addicted Member
Re: where condition and null
Originally Posted by si_the_geek
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
-
May 19th, 2017, 06:11 AM
#7
Thread Starter
Addicted Member
Re: where condition and null
Originally Posted by jmcilhinney
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:
WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate)
Also, this:
csharp Code:
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:
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
-
May 19th, 2017, 06:46 AM
#8
Re: where condition and null
Originally Posted by abcd_2014
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.
-
May 25th, 2017, 04:34 PM
#9
Thread Starter
Addicted Member
Re: where condition and null
Originally Posted by jmcilhinney
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|