how to search for rows using date value from datetimepicker control in vb.net

Question

0.00/5 (No votes)

See more:

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 even though i enter a date value which is already there in the database i still don't get any rows. what is the problem? is it something to do with the formatting of the date values or?

VB

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

Posted 21-Dec-14 21:06pm

hlsc1983

Add a Solution

Comments

DamithSL 22-Dec-14 3:16am

what is the column data type of student_dob?

hlsc1983 22-Dec-14 3:23am

its date type.... of course when I view the columns in sqlserver , it shows date in ' yyyy/mm/ dr format.

DamithSL 22-Dec-14 3:28am

are you get the message with "no record exist"?
have you debug and check the value of DateTimePicker1.Value and dataset object at runtime?

hlsc1983 22-Dec-14 4:23am

I checked the value . its something like 13-09-2005 14:52:00

DamithSL 22-Dec-14 5:08am

try with below
da.SelectCommand.Parameters.AddWithValue("@dob", DateTimePicker1.Value.Date)

hlsc1983 22-Dec-14 12:46pm

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' .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2014-12-21T23:09:00

Solution 1

set the Date of DateTimePicker value as below

VB

da.SelectCommand.Parameters.AddWithValue("@dob", DateTimePicker1.Value.Date)

Posted 21-Dec-14 23:09pm

DamithSL