c# datagridview display last month data only when form load

[lianpiau]

Hello expert, I had a problem in C# datagridview. I want display last month data or ?days only when form load.
I know use SQL query. But the problem is at datetime and string. my idea is today date - 30 days.

private void OT_Load(object sender, EventArgs e)
        {

            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            string sql = "SELECT * FROM OT ORDER BY OTDate";
            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();
            sCommand = new OleDbCommand(sql, connection);
            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "OT");
            sTable = sDs.Tables["OT"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["OT"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

            dtpDate.Value = System.DateTime.Today;

            timerName.Enabled = true;
            

        }

[prvnkmr194]

WHERE datetime1 < #12/1/2011# And datetime1 > #11/1/2011#;

this condition will display the record for the month of November.

[lianpiau]

WHERE datetime1 < #12/1/2011# And datetime1 > #11/1/2011#;

this condition will display the record for the month of November.

but this is hard code. when next month will display nothing.
I known logic is like these. But I use soft code, the datetime data type problem.

[prvnkmr194]

that means you have to collect the system date and store it variable and then use, you don't know how to get the system date or what?
please clear your question first.

[lianpiau]

that means you have to collect the system date and store it variable and then use, you don't know how to get the system date or what?
please clear your question first.

I known to get system time.

For example,

DateTime a = System.DateTime.Now();

DateTime b = a-30;

But The Problem is Datetime cannot minus int.

So i want use -30 to get last month. Then use in sql query.

[Momerath]

DateTime b = a.AddDays(-30)

[prvnkmr194]

DateTime thedate = System.DateTime.Now;
thedate -= new TimeSpan(30, 0, 0, 0);

this code subtract 30 days from current system date and assign it to thedate
Note : you may have to change the formate.
Feel free to ask any further problem with problem

[lianpiau]

DateTime thedate = System.DateTime.Now;
thedate -= new TimeSpan(30, 0, 0, 0);

this code subtract 30 days from current system date and assign it to thedate
Note : you may have to change the formate.
Feel free to ask any further problem with problem

it cause datatype mismatch

[lianpiau]

DateTime thedate = System.DateTime.Now;
thedate -= new TimeSpan(30, 0, 0, 0);

this code subtract 30 days from current system date and assign it to thedate
Note : you may have to change the formate.
Feel free to ask any further problem with problem

or I do like that:

private void OT_Load(object sender, EventArgs e)
        {
            dtpDate.Value = System.DateTime.Today;
            OleDbConnection conAuthor;
            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            conAuthor = new OleDbConnection(ConnectionString);
            OleDbCommand  cmdCheckID;
            

            conAuthor.Open();

            DateTime a = System.DateTime.Today;
            DateTime b = a.AddDays(-30);
            string format = ("dd/MMM/yy");
            string c = b.ToString(format);
            DateTime d = Convert.ToDateTime(c);


            string sql = "SELECT * FROM OT where OTDate>@OTDate ORDER BY OTDate";
            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();

            cmdCheckID = new OleDbCommand(sql, conAuthor);
            cmdCheckID.Parameters.AddWithValue("@OTDate", c);

            sCommand = new OleDbCommand(sql, connection);
            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "OT");
            sTable = sDs.Tables["OT"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["OT"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

            

            timerName.Enabled = true;
            

        }

It cause

[Momerath]

OleDB doesn't use named parameters, you need to rewrite your SQL query as

string sql = "SELECT * FROM OT where OTDate > ? ORDER BY OTDate";

You might need to change the way you add the parameter, but I don't do much OleDB stuff so I'm not sure.

[prvnkmr194]

Check this example I hope it will help to understand and in doing your project I am not going to edit your code.

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\mydatabase.mdb";
            cnn.Open();
            DateTime dt=System.DateTime.Now;
            dt -= new TimeSpan(30, 0, 0, 0);
            string str="#"+dt.ToString()+"#";

            cmd.CommandText = "select * from table1 WHERE dat>"+str  ;
            cmd.Connection = cnn;
            da.SelectCommand = cmd;
           
            da.Fill(ds, "table1");

            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "table1";
            cnn.Close();

If you still get any error please feel free to ask your problem but with update code. Best of luck

[lianpiau]

or I do like that:

private void OT_Load(object sender, EventArgs e)
        {
            dtpDate.Value = System.DateTime.Today;
            OleDbConnection conAuthor;
            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;

            conAuthor = new OleDbConnection(ConnectionString);
            OleDbCommand  cmdCheckID;
            

            conAuthor.Open();

            DateTime a = System.DateTime.Today;
            DateTime b = a.AddDays(-30);
            string format = ("dd/MMM/yy");
            string c = b.ToString(format);
            DateTime d = Convert.ToDateTime(c);


            string sql = "SELECT * FROM OT where OTDate>@OTDate ORDER BY OTDate";
            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();

            cmdCheckID = new OleDbCommand(sql, conAuthor);
            cmdCheckID.Parameters.AddWithValue("@OTDate", c);

            sCommand = new OleDbCommand(sql, connection);
            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "OT");
            sTable = sDs.Tables["OT"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["OT"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

            

            timerName.Enabled = true;
            

        }

It cause

The solution is

dtpDate.Value = System.DateTime.Today;
            
            ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
            string name = conSettings.ProviderName;
            string providerName = conSettings.ProviderName;
            string ConnectionString = conSettings.ConnectionString;
            

            DateTime a = System.DateTime.Today;
            DateTime b = a.AddDays(-30);
            //string format = ("dd/MMM/yy");
            //string c = b.ToString(format);
            

            string sql = "SELECT * FROM OT where OTDate >=@odate";

            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();
            sCommand = new OleDbCommand(sql, connection);
            sCommand.Parameters.AddWithValue("@odate", b);

            sAdapter = new OleDbDataAdapter(sCommand);
            sBuilder = new OleDbCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "OT");
            sTable = sDs.Tables["OT"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["OT"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            

            timerName.Enabled = true;

[prvnkmr194]

I think you din't notice my code carefully, you have to enclose your date like this

string b="#1/5/2011#";

look at line number 5 in my previous code ok

[lianpiau]

I think you din't notice my code carefully, you have to enclose your date like this

string b="#1/5/2011#";

look at line number 5 in my previous code ok

sorry. I missed to see. Thank you very much. My problem is solve already