-
Dec 22nd, 2014, 03:14 AM
#1
Thread Starter
Addicted Member
[RESOLVED] how to search for rows in database using date value from datetimepicker control
i am using vb.net and sql server. this is my code to search for rows using date value from a datetimepicker control. the problem is my query does not return any rows even though i pick a date value which is already there in the database. what is the problem? is it something to do with the formatting of the date values or?
Code:
sqlsearch = "Select * from Students where student_dob =@dob"
Dim da As New SqlDataAdapter(sqlsearch, con)
da.SelectCommand.Parameters.AddWithValue("@dob", DateTimePicker1.Value)
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count = 0 Then
MessageBox.Show("No record exists, try again", "SORRY!", MessageBoxButtons.OK)
Else
populate_lvw1()
End If
Last edited by hlsc1983; Dec 22nd, 2014 at 03:24 AM.
-
Dec 22nd, 2014, 04:50 AM
#2
Re: how to search for rows in database using date value from datetimepicker control
The most likely issue is that the value you're passing in includes a time and your database data has only a date or has the time zeroed. By default, the Value of a DateTimePicker is set to the current date and time, i.e. Date.Now. Even if you can't see it in the UI because of the format, the time part is there. If the user changes the date part, the time part remains unchanged. What you probably need to do is just zero the time, which you can do like this:
Code:
da.SelectCommand.Parameters.AddWithValue("@dob", DateTimePicker1.Value.Date)
The Date property of a DateTime returns another DateTime with the same date and the time zeroed.
-
Dec 22nd, 2014, 12:52 PM
#3
Thread Starter
Addicted Member
Re: how to search for rows in database using date value from datetimepicker control
Originally Posted by jmcilhinney
The most likely issue is that the value you're passing in includes a time and your database data has only a date or has the time zeroed. By default, the Value of a DateTimePicker is set to the current date and time, i.e. Date.Now. Even if you can't see it in the UI because of the format, the time part is there. If the user changes the date part, the time part remains unchanged. What you probably need to do is just zero the time, which you can do like this:
Code:
da.SelectCommand.Parameters.AddWithValue("@dob", DateTimePicker1.Value.Date)
The Date property of a DateTime returns another DateTime with the same date and the time zeroed.
I already tried that. in this case it returns all the rows if I pick a date which is present in the database whereas it is supposed to return only one row. if i pick a date which is not present in the database it displays the message ' no record exists' .
-
Dec 22nd, 2014, 08:11 PM
#4
Re: how to search for rows in database using date value from datetimepicker control
Originally Posted by hlsc1983
I already tried that.
I must have missed the part where you told us that.
Originally Posted by hlsc1983
in this case it returns all the rows if I pick a date which is present in the database whereas it is supposed to return only one row. if i pick a date which is not present in the database it displays the message ' no record exists' .
I doubt that. What does your `populate_lvw1` method look like?
By the way, if all you want is one DataTable then why not just create one DataTable instead of creating a useless DataSet?
-
Dec 23rd, 2014, 09:30 AM
#5
Re: how to search for rows in database using date value from datetimepicker control
Originally Posted by hlsc1983
I already tried that. in this case it returns all the rows if I pick a date which is present in the database whereas it is supposed to return only one row. if i pick a date which is not present in the database it displays the message ' no record exists' .
To clarify, are you saying that using 'DateTimePicker1.Value.Date' as your parameter in the above query returns all the rows in the database regardless of the actual DOB in the student_dob field? If so, that doesn't sound right. The field type for student_dob in your database is set to datetime and not text correct? You also might want to double check that.
-
Dec 23rd, 2014, 11:19 AM
#6
Thread Starter
Addicted Member
Re: how to search for rows in database using date value from datetimepicker control
Originally Posted by jmcilhinney
I must have missed the part where you told us that.
I doubt that. What does your `populate_lvw1` method look like?
By the way, if all you want is one DataTable then why not just create one DataTable instead of creating a useless DataSet?
you were right. there was another ' select' query in my 'populate_lvw()' . its working fine after removing it. thanks a lot.
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
|