C# datagridview add date when I click checkbox

Question

0.00/5 (No votes)

See more:

hi everyone!
i want to put current date and time on selected row in cell Date_Pec when i cick the checkedbox.
and naturely update the sql database.

thank for your help

What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds;
        SqlCommandBuilder builder;
        private int i;

        public Form1()

        {
           
            InitializeComponent();
            con = new SqlConnection(CONNECTION.CONNECTION_STRING_1);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: cette ligne de code charge les données dans la table 'isothermicDataSet.USR_ProdMonitor'. Vous pouvez la déplacer ou la supprimer selon les besoins.
            this.uSR_ProdMonitorTableAdapter2.Fill(this.isothermicDataSet.USR_ProdMonitor);

            string sql = "SELECT date_pec,scandate,Poste,NoSeq,Couleur,ProductionLot FROM USR_ProdMonitor WHERE (Poste = N'Parclose')";

            

             ds=new DataSet();
             da=new SqlDataAdapter(sql,con);
             da.Fill(ds);
             builder=new SqlCommandBuilder(da);
             dataGridView1.DataSource=ds.Tables[0];

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            

            bool selected = !Convert.ToBoolean(dataGridView1.Rows[e.RowIndex].Cells["check"].Value);
            dataGridView1.Rows[1].Selected = true;

            if (selected)
            {
                SqlConnection con = new SqlConnection(CONNECTION.CONNECTION_STRING_1);
                string SqlCmdText = "INSERT INTO USR_ProdMonitor(Date_Pec)" + DateTime.Now ();


                int next = this.dataGridView1.CurrentRow.Index + 1;
                dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Red;
                this.dataGridView1.CurrentCell = this.dataGridView1.Rows[next].Cells[this.dataGridView1.CurrentCell.ColumnIndex];
            }
            else
            {
                dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightGreen;
                dataGridView1.Rows[e.RowIndex].Cells["check"].Value = !Convert.ToBoolean(dataGridView1.Rows[e.RowIndex].Cells["check"].Value);
                

            }
            {

        }
        }

        

        private void button1_Click(object sender, EventArgs e)
        {
            Application.Exit();

        }

       

        

      


}
}

Posted 26-Apr-19 1:37am

denis.larocque

Updated 26-Apr-19 4:55am

Add a Solution

Comments

OriginalGriff 26-Apr-19 7:40am

And?
What doe sit do that you didn't expect, or not do that you did?
What have you tried?
Where are you stuck?
What help do you need?

denis.larocque 26-Apr-19 7:46am

that if what i have try, but doent work,
what i want is when a click the checkbox in datagrid that input current date to the row in cell Date_pec, and update sql database (USR_Prodmonitor)

if (selected)
{
SqlConnection con = new SqlConnection(CONNECTION.CONNECTION_STRING_1);
string SqlCmdText = "INSERT INTO USR_ProdMonitor(Date_Pec)" + DateTime.Now

OriginalGriff 26-Apr-19 8:01am

"doent work" or "It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us what you tried to find out why it didn't work, and what is actually happening.
Tell us any error messages.

You've been here 6 years! You should know by now how to ask a question and give information that helps the person trying to answer!

Use the "Improve question" widget to edit your question and provide better information.

Richard Deeming 26-Apr-19 8:34am

That's not a valid SQL command; you're not using parameters; and you never execute it anyway.

using (SqlConnection con = new SqlConnection(CONNECTION.CONNECTION_STRING_1))
using (SqlCommand cmd = new SqlCommand("INSERT INTO USR_ProdMonitor (Date_Pec) VALUES (@Date_Pec)", con))
{
    cmd.Parameters.AddWithValue("@Date_Pec", DateTime.Now);
    con.Open();
    cmd.ExecuteNonQuery();
}

denis.larocque 26-Apr-19 8:39am

ok i have try that to, but that give me a error System.Data.SqlClient.SqlException : 'Incorrect syntax near '('.'

if (selected)
{
SqlConnection con = new SqlConnection(CONNECTION.CONNECTION_STRING_1);

SqlCommand cmd = new SqlCommand("UPDATE USR_ProdMonitor (Date_Pec) VALUES (@DATE)", con);
con.Open();
cmd.Parameters.AddWithValue("@DATE", DateTime.Now);
cmd.ExecuteNonQuery();
con.Close();

Richard Deeming 26-Apr-19 8:41am

Your question and your comment shows an INSERT command.

My comment shows an INSERT command.

You can't just replace the word "INSERT" with "UPDATE", because the syntax for the two is completely different.

If you want to UPDATE a record, then you need to issue a valid UPDATE command.

denis.larocque 26-Apr-19 8:51am

ok i will start from zero, i have a BD on sql server and this db have already data on it.
on my program i have a datagrid to show this data. in this datagrid i have put a checkbox.
when i check this checkbox that will change the color of the row and go to next row. (this portion of code work great)

But i want in same time, add date and hour on cell in this selected row.(Date_pec) and in same time update the sql database and refresh the datagrid.
I dont no if im clear (my english is poor loll)

Richard MacCutchan 26-Apr-19 8:31am

You create the SQL connection and the INSERT statement, but where do you actually execute the command?

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)

Gerry Schmitz · Accepted Answer · 2019-04-26T04:55:00

Solution 1

When you SELECT your "data source" the first time, add an extra "date column" (SELECT GETDATE() AS ...), and use that field to store your "date" in the SELECTED ITEM in the SELECTED ITEM CHANGED event (instead of mucking with the grid itself).

You now have a "record" (object) that you can use to update your db.

Posted 26-Apr-19 4:55am

Gerry Schmitz

Comments

denis.larocque 29-Apr-19 12:54pm

i have symplify and modify my code i now that work but..
when i want to refresh the datagrid nothing what i try work.

if i put datagrid1.refresh(); nothing refresh
and if i put this.uSR_ProdMonitorTableAdapter.FillBy(this.isothermicDataSet.USR_ProdMonitor);
that give me error:System.NullReferenceException the object reference is not define an object instance.

denis.larocque 29-Apr-19 12:55pm

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
SqlConnection con;
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
// TODO: cette ligne de code charge les données dans la table 'isothermicDataSet.USR_ProdMonitor'. Vous pouvez la déplacer ou la supprimer selon les besoins.
this.uSR_ProdMonitorTableAdapter.FillBy(this.isothermicDataSet.USR_ProdMonitor);
}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

bool selected = !Convert.ToBoolean(dataGridView1.Rows[e.RowIndex].Cells["check"].Value);
dataGridView1.Rows[1].Selected = true;
DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];

if (selected)
{

//change row color on Tab and change row
int next = this.dataGridView1.CurrentRow.Index + 1;
dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Red;
this.dataGridView1.CurrentCell = this.dataGridView1.Rows[next].Cells[this.dataGridView1.CurrentCell.ColumnIndex];

//add date to Date_pec
SqlConnection con = new SqlConnection(@"Data Source=srvqcsql-01;Initial Catalog=Isothermic;Integrated Security=True");
SqlCommand cmd = new SqlCommand("update USR_ProdMonitor SET Date_PEC = GETDATE() where Poste = 'PARCLOSE' and Date_PEC is null and ScanDate = (select top 1 MIN(ScanDate) from USR_ProdMonitor where Poste = 'PARCLOSE' and Date_PEC is null)", con);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

}
else
{
dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightGreen;
dataGridView1.Rows[e.RowIndex].Cells["check"].Value = !Convert.ToBoolean(dataGridView1.Rows[e.RowIndex].Cells["check"].Value);

}
this.uSR_ProdMonitorTableAdapter.FillBy(this.isothermicDataSet.USR_ProdMonitor);

}

}
}

denis.larocque 29-Apr-19 18:25pm

Thank you everyone all , work great now :)