SQL between statement

[daniel50096230]

I had two combo box..The first combo box is showing the month(eg.01,02,03---12) and another combo box is showing year(eg.2009,2010,2011).....

I had the following code:



conKk.Open()

'Dim strReportPath As String = "C:\Documents and Settings\Chee Mun\Desktop\FYPvb\FYP\CrystalReport1.rpt"

Dim mySelect As SqlCommand

mySelect = New SqlCommand("Select * from Payment Where PaymentDate Between '" & cbo1.text &"' And '" & cbo2.text &"'", conKk)

Dim mySqlDataAdapter As New SqlDataAdapter(mySelect)

Dim mydsPayment As New DataSet1

mySqlDataAdapter.Fill(mydsPayment, "Payment")




In my database,my PaymentDate is combination of date,month and year(Eg.30/12/2009)...SO how can i change the SQL statement based on what i choose from combo box 1 and combo box 2 to retrieve data?It means that if i choose 02 and 2009,the sql will retrieve those data that within feb 2009?

[GremlinSA]

Firstly you need to build a Full date for the SQL's Between .. so that it looks something like..

Select * from Payment Where PaymentDate Between '2009-02-01' And '2009-02-28'

then also remember that this will only turn up records from midnight 1 Feb up to before midnight 28 Feb .. (1st till the 27th)

so you will also want to add times to it ... so that you end up with

Select * from Payment Where PaymentDate Between '2009-02-01 00:00:00' And '2009-02-28 23:59:59'


I'd rather use a DateTimepicker and parse out the dates from that ...

Private _Sdate As Date
Private _Edate As Date

_SDate = DTP1.AddDays(1 - DTP1.Day).Date
_EDate = _SDate.AddMonths(1).Date
_DEDate = _SDate.AddMonths(1).AddDays(-1).Date

mySelect = New SqlCommand("Select * from Payment Where PaymentDate Between '" & _Sdate.Date &"' And '" & _Edate.Date &"'", conKk)

This will return three variables with the selected month's start and end dates...

_Sdate = Start Date (1st of the month)
_Edate = End date for SQL (1st of the Next month)
_DEdate = End date to display (last day of the month (28,29,30,31))

I use simmular methods to get weekly, monthly, Quarterly, and yearly reports from SQL ...

Gremmy..

[daniel50096230]

In my datetimepicker,I do not have the addmonths and add method something like this...

[DataMiser]

If I understand correctly you want all items for the selected month using your approach in the op your between statement should be something more like.



Between '" & cboMonth.text & "/1/" & cboYear.text & " 00:00:00' AND '" & cboMonth.text & "/" & LastDayOfMonth & "/" & cboYear.text & " 23:59:59' "

Note this is assuming that the variable LastDayOfMonth has been defined and set to the proper value for the selected month.
Also assuming that you are using SQL Server, If you are using Access then you would need to use the # character as the date delimiter rather than the ' character.

[GremlinSA]

In my datetimepicker,I do not have the addmonths and add method something like this...
What version of VB.NET are you using ?????

[dglienna]

Don't build strings to use in SQL statements. It's too easy for someone to INJECT something that may or may not have meaning to the db engine. It's the most common method of hacking a db.

Create PARAMETERS for Month/Day/Year, and you can SAFELY build any valid date

http://www.java2s.com/Code/VB/Database-ADO.net/Insertcommandwithparameters.htm

[GremlinSA]

Don't build strings to use in SQL statements. It's too easy for someone to INJECT something that may or may not have meaning to the db engine. It's the most common method of hacking a db.


all the more reason to use a DateTimePicker ... ;)

Now if the OP could only tell us what ver of VB.NET is in use ???