How to select data from date

Question

0.00/5 (No votes)

See more:

, +

Hello Guys, i am selecting data from database into datagridview but when i select the data so datagridview show blank

What I have tried:

connection.Open();
            DataSet dsa = new DataSet();
            DataTable dt = new DataTable();
            dsa.Tables.Add(dt);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da = new OleDbDataAdapter("SELECT * from [Sales Record] where [Cash Sales] = '" + "Cash Sales" + "'And[Date] =#"+System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss:tt")+"#", connection);
            da.Fill(dt);
            dataGridView1.DataSource = dt;

            connection.Close();

Posted 3-Jan-17 4:54am

Member 9983063

Updated 3-Jan-17 6:02am

Add a Solution

Comments

[no name] 3-Jan-17 11:05am

Using a proper parameterized query LIKE YOU HAVE ALREADY BEEN TOLD might just make your error go away all by itself.

Peter Leow 3-Jan-17 11:08am

This System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss:tt") returns the datetime when the sql query is executed. Are you sure there are such records with this datetime?

3 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)

Wendelius · Answer 1 · 2017-01-03T05:30:00

There are quite a few problems to fix. Consider the following alternative

C#

string sql;
DataSet dsa = new DataSet();
DataTable dt = new DataTable();
dsa.Tables.Add(dt);
using (OleDbConnection connection = new OleDbConnection(connectionString)) {
   sql = @"SELECT * 
FROM  [Sales Record] 
WHERE [Cash Sales] = 'Cash Sales'
AND   [Date]       = @datecriteria";
   OleDbDataAdapter da =new OleDbDataAdapter(sql, connection);

   da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now;
   try {
       connection.Open();
       adapter.Fill(dt);
   }
   catch (Exception ex) {
      // do some proper error handling
   }
}

It's vital to always use parameters to prevent yourself from SQL injection[^] and to use using blocks to ensure proper disposal of resources.

What comes to the criteria itself it looks a bit odd that you're concatenating a static text to the where clause and that you use current time for the criteria. Just guessing but if you want to search for records for today, instead of

C#

da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now;

use

C#

da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now.Date;

OriginalGriff · Answer 2 · 2017-01-03T05:21:00

Don't do it like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. With dates, this can also eliminate translation problems where the DB server interprets the European date format as US and compares rows with mm/dd/yyyy instead.

Bear in mind that (as you have been told) your Date string returns the current date to the second and it's unlikely that there are many, or indeed any, records with that value. And if this query is executed on different PC's, it will quite likely return different time values for them all - PC clocks are not particularly synchronised. If you need accuracy to the second, you probably want to use a single, consistent time source for your checks and your row INSERT / UPDATEs, which means you should always use the DB server Date and Time values via the GETDATE function.

If you are after rows today, use DateTime.Now.Date, pass it as a parameterized value, and compare it with "Greater than or equal" instead of "Equals"

Pradeep Prasanna Ekanayaka · Answer 3 · 2017-01-03T06:02:00

Use this code.. Don't use sql quary like this. use mysql parameters. if not codes are not genuine

ok try this code

private void LoadData()
       {
           try
           {
               using (OleDbConnection connection = new OleDbConnection(connectionString))
               {
                   using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM  [Sales Record] WHERE [Cash Sales] = 'Cash Sales' AND [Date] = @datecriteria", /* Please Enter Connection Name to this place*/))
                   {
                       connection.Open();
                       oda.SelectCommand.Parameters.Clear();
                       oda.SelectCommand.Parameters.AddWithValue("@datecriteria", System.DateTime.Now);
                       DataTable dt = new DataTable();
                       oda.Fill(dt);
                       if (dt.Rows.Count > 0)
                       {
                           oda.Fill(dt);
                           dataGridView1.DataSource = dt;
                       }
                       else
                       {
                           dataGridView1.DataSource = null;
                       }
                       connection.Close();
                   }

               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }

       }

after that call this method in button click event like this

private void button1_Click(object sender, EventArgs e)
       {
           LoadData();
       }

if any question please comment ...
thank you

How to select data from date

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0