Results 1 to 6 of 6

Thread: [RESOLVED] how to search for rows in database using date value from datetimepicker control

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2013
    Posts
    198

    Resolved [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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2013
    Posts
    198

    Re: how to search for rows in database using date value from datetimepicker control

    Quote Originally Posted by jmcilhinney View Post
    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' .

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to search for rows in database using date value from datetimepicker control

    Quote Originally Posted by hlsc1983 View Post
    I already tried that.
    I must have missed the part where you told us that.
    Quote Originally Posted by hlsc1983 View Post
    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?

  5. #5
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: how to search for rows in database using date value from datetimepicker control

    Quote Originally Posted by hlsc1983 View Post
    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.

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Apr 2013
    Posts
    198

    Re: how to search for rows in database using date value from datetimepicker control

    Quote Originally Posted by jmcilhinney View Post
    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
  •  



Click Here to Expand Forum to Full Width