Search Database Record using DateTimePicker in C#Forms

Question

0.00/5 (No votes)

See more:

Hi Guys, I have linked a MS Access Database to a C# Form. I can Edit, View, Update each entries But I need to search a record in the database using DateTimePicker. For an example if i select a date, it should display the records in database on that date. I tried to solve this by my self, but failed. Anyone who can help me to solve this, I'd be really grateful. Thanks.

Notice

There is a Column name "Date" in the Database and Its data type is Date/Time

Posted 16-Sep-12 21:05pm

Hank Conory

Updated 16-Sep-12 21:06pm

v2

Add a Solution

5 solutions

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)

Bernhard Hiller · Answer 1 · 2012-09-16T22:19:00

Solution 1

Hm. Maybe your problem is the DateTime vs. Date issue: both the value of your column and the DateTimePicker also contains hours, minutes, etc.
Try to cut away those extra data in your sql query with the Date() function, e.g.

SQL

SELECT *
FROM MyTable 
WHERE Date([Date])=Date(@DateTimePickerValue)

Posted 16-Sep-12 22:19pm

Bernhard Hiller

Comments

Hank Conory 20-Sep-12 3:59am

Thanks Bernard But Still I couldn't solve it. I have post my code below. See if you can help =)

Trilok Arora · Answer 2 · 2012-09-17T06:51:00

Solution 2

If you use parameterised query then it should work.

Let say the DATE column name for your table is datecolumn.

string mySQL = "SELECT * FROM table WHERE (DateColumn = @dateColumn)"

SqlCommand cmd = new SqlCommand (mySQL)
cmd.Parameters.Add("@dateColumn", dateFromDateTimePicker);

SqlDataReader reader = cmd.ExecuteReader();

Posted 17-Sep-12 6:51am

Trilok Arora

Comments

Hank Conory 20-Sep-12 3:59am

Thanks Arora But Still I couldn't solve it. I have post my code below. See if you can help =)

Al Hizbul · Answer 3 · 2012-09-17T08:29:00

Solution 3

Just use this simple query:

C#

String query = "SELECT * FROM tblExpense WHERE date='" + date + "'";
                SqlConnection Conn = create_connection.CreateConnection();
                SqlCommand cmd = new SqlCommand(query, Conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    myResult.Text=reader["yourSearch"].ToString();

                }
                else
                {
                    MessageBox.Show("Not Found!!");
                }

Posted 17-Sep-12 8:29am

Al Hizbul

Comments

[no name] 17-Sep-12 14:47pm

Why would he want to use a query that invites SQL injection attacks?

Hank Conory 20-Sep-12 4:00am

Thanks Hizbul But Still I couldn't solve it. I have post my code below. See if you can help =)

Al Hizbul 20-Sep-12 4:33am

First you should create a class name create_connection for Database connection. This class may help you to further use.
<pre lang="cs">
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace dailyNotes
{
class create_connection
{
public static SqlConnection CreateConnection()
{
string ConnString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Hizbul Bahar\Documents\dailynotes.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
SqlConnection Conn = new SqlConnection(ConnString);
Conn.Open();
return Conn;
}
public void disconnect(SqlConnection conn)
{
conn.Close();
}
}
}</pre>

With this you should remove time from date.
<pre lang="c#">String dt=transDate.remove(10);</pre>
and then use my code that i provide you before. There i use this class for DB connection.

Anyway tnx for your response.

Hank Conory 21-Sep-12 5:53am

Thanks for spending your valuable time on this Hizbul. I'll try this and let you know the result a.s.a.p =]

Hank Conory · Answer 4 · 2012-09-19T21:57:00

But Guys still it is Displaying an error, the error is "Syntax error (missing operator) in query expression 'TransDate = 2012-09-20 12:00:00 a.m'
But I Changed the DateTimePicker format to "short" and Value as per short like "2012-09-20" But still when i press the search button it gives the
error. Guys am linking this with access, So um still stuck with this. Need help. Sorry for late message was busy with work, Boss is headache.Nyways Thanks for all replies =)

*****This is the code for search button

C#

private void butSearchS_Click(object sender, EventArgs e)
        {
            OleDbConnection CONN = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; DataSource=C:\\JohnFord.accdb;Persist Security Info=False;");
            DataTable DAT = new DataTable();
            OleDbDataAdapter DAA = new OleDbDataAdapter("SELECT * FROM MIS WHERE TransDate = " + (SearchDateS.Value), CONN);
            DAA.Fill(DAT);
            dataGrid1.DataSource = DAT;
        }

Al Hizbul · Answer 5 · 2012-09-19T22:20:00

First you should create a class name create_connection for Database connection. This class may help you to further use.

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace dailyNotes
{
    class create_connection
    {
        public static SqlConnection CreateConnection()
        {
            string ConnString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Hizbul Bahar\Documents\dailynotes.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            SqlConnection Conn = new SqlConnection(ConnString);
            Conn.Open();
            return Conn;
        }
        public void disconnect(SqlConnection conn)
        {
            conn.Close();
        }
    }
}

and then use my code that i provide you before. There i use this class for DB connection.

Anyway tnx for your response.

Search Database Record using DateTimePicker in C#Forms

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0