Category: Visual Studio

26 Feb 2016 00:57

Joined:	21/12/2015
Level:	Bronze
Points:	20

Points:	5

VB.NET to retrieve data from sql server using datetime picker as its filter.

I am writing program to take all data between range of two datetime from sql using my datetimepicker as my variable. My problem is data returns null when i execute my code as below:

Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' AM'")
Dim b = DateTimePicker2.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' PM'")

Dim sql = "SELECT * FROM atable WHERE Date BETWEEN @Date1 AND @Date2"
Dim cmd = New SqlCommand(sql, con)
cmd.Connection = con
cmd.Parameters.AddWithValue("@Date1", a)
cmd.Parameters.AddWithValue("@Date2", b)
Dim myReader As SqlDataReader = cmd.ExecuteReader()
myreader return value of null. is this date format prob or my reader prob? Thanks

#764858

26 Feb 2016 01:21

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	2

Hi,
If you are using dateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' AM'") then you have to make sure that database column 'Date' must be of type datetime.
else you can use
dateTimePicker.Value.ToShortDateString()
Or
dateTimePicker.Value.Date.ToString()
Regards,
Shashikant Gurav

#764861

26 Feb 2016 18:59

Joined:	12/11/2009
Level:	Gold
Points:	43848

Points:	2

Hi,

Slightly modify your query like below and see if it works,


Dim sql = "SELECT * FROM atable WHERE Date > @Date1 AND and Date < @Date2"

Let me know the result after trying this.

Regards,
Asheej T K

#764872

28 Feb 2016 17:52

Joined:	21/12/2015
Level:	Bronze
Points:	20

Points:	1

Hi, Shashikant Gurav
I have already try your syntax but its not working. My date in database is look like this 2008-05-12 12:05:40 PM....If I convert my date time picker like this
Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd") it can fetch data but the problem is that if I choose the start date 2008-05-01 to end date 2008-05-03 the data retrieved only from 2008-05-01 to 2008-05-02 it will not fetching data until the end time means that it will be short of one day of data.

Thanks,

#764873

28 Feb 2016 17:55

Joined:	21/12/2015
Level:	Bronze
Points:	20

Points:	1

Hi, Asheej T K
I already try your code but its return null.

#764874

28 Feb 2016 21:16

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	4

Hi,
It is coming one short of date because your dates for eg. 2008-05-03 may be looks like this in database 2008-05-03 12:05:40 PM i.e. this date is interpreted as midnight when the day starts. So you have to make changes in your sql query as follows:
SELECT * FROM atable WHERE cast(Date as date) BETWEEN
@Date1 AND @Date2
And use Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd") in code to pass the FROM and TO dates.
Hope it helps.
Regards,
Shashikant Gurav
shashikantgurav22@gmail.com

#764888

29 Feb 2016 01:36

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	4

Hai Ronoer A Ahong,
Fir filtering using the date-picker values, the value of the date-picker and the database values must be in the same format.
To do so, you need to convert the values to a format when using in the query and use the same format for the database fields values.
Like below:


SELECT * FROM atable WHERE YourFormat(Date) >= YourFormat(@Date1) AND and YourFormat(Date) <= YourFormat(@Date2)

Keep in mind that both side the format is same.
Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

#764911

29 Feb 2016 20:28

Joined:	21/12/2015
Level:	Bronze
Points:	20

Points:	1

Hi Shashikant Gurav,
I already tried ur code example but i get and error on the CAST.

Hello Pawan Awasthi(DNS MVM),
I have try ur code and this is error when i executing it: 'dd' is not a recognized function name. dd is for my date format

Thanks,
regards,
RONOER

#764938

01 Mar 2016 01:25

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	3

Hi

try this code




SELECT * FROM atable WHERE Date1 BETWEEN '2016-03-01 02:48:13 AM' AND '2016-03-25 02:48:13 PM'


use test
--dROP table atable
create table atable
(
NAMEE VARCHAR(30),
date1 datetime
)

INSERT INTO atable VALUES ('AAAA','2016-03-01 02:48:13')
INSERT INTO atable VALUES ('BBBB','2016-03-25 02:48:13')
INSERT INTO atable VALUES ('AAAA','2016-03-28 02:48:13')

working good try this implemented in your page and let me know.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#764954

01 Mar 2016 05:08

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	2

Hi,
Can you please tell which error did u get on CAST? and which SQL server you are using?
Regards,
Shashikant Gurav
shashikantgurav22@gmail.com

#764956

01 Mar 2016 17:31

Joined:	21/12/2015
Level:	Bronze
Points:	20

Points:	1

I have already fix this issues. I change my datetimepicker format property into custom then give this format 'yyyy-MM-dd hh:mm:ss tt' instead of in the code. finally I set default value for the hour into start date 'yyyy-MM-dd 00:00:00 tt' and the end date '11:59:59 PM'(this is for 12 hours system). After I done it I just call the date timepicker like this
Dim a = DateTimePicker1.value()
Dim b = DateTimePicker2.value()
Thank You all.

VB.NET to retrieve data from sql server using datetime picker as its filter.

About dotnetspider.com

Quick Links

General

Services