I want to update the remaining days automatically from database to datagridview according to current date?

Question

1.00/5 (1 vote)

See more:

I want update Days_left column automatically according to current date

What I have tried:

<pre>namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {

        SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-F1TCIFJ;Initial Catalog=tempdb;Integrated Security=True");
        private int selectedRow;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            //// TODO: This line of code loads data into the 'tempdbDataSet1.khaas' table. You can move, or remove it, as needed.
            //this.khaasTableAdapter.Fill(this.tempdbDataSet1.khaas);

            alternateColorChange(dataGridViewKhaas);
            dataGridViewKhaas.MultiSelect = false;
                       


        }

        public void alternateColorChange(DataGridView dgv)
        {
            dgv.RowsDefaultCellStyle.BackColor = Color.LightBlue;
            dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
        }


        //...................................................KHAAS START....................................................//

        private void btnKSave_Click(object sender, EventArgs e)
        {
            if (txtKVNumber.Text == "")
            {
                string myStringVariable1 = string.Empty;
                MessageBox.Show("Vehicle Number is required");
            }
            else if (cboKVColor.Text == "")
            {
                string myStringVariable2 = string.Empty;
                MessageBox.Show("Select Vehicle Color");
            }

            //For combobox validation we should follow the below code.
            else if (cboKVBrand.Text == "")
            {
                string myStringVariable3 = string.Empty;
                MessageBox.Show("Select Vehicle Brand");
            }
            else if (cboKVType.Text == "")
            {
                string myStringVariable12 = string.Empty;
                MessageBox.Show("Select Vehicle Type");
            }
            //else if (dateTimePickerKhaas.Value.Date == "")
            //{
            //    string myStringVariable4 = string.Empty;
            //    MessageBox.Show("Select Vehicle Color");
            //}

            else if (txtKOName.Text == "")
            {
                string myStringVariable5 = string.Empty;
                MessageBox.Show("Owner Name is required");
            }
            //else if (txtKChassisNum.Text == "")
            //{
            //    string myStringVariable6 = string.Empty;
            //    MessageBox.Show("Chassis Number is required");
            //}
            else if (txtKCivilID.Text == "")
            {
                string myStringVariable7 = string.Empty;
                MessageBox.Show("Civil ID is required");
            }
            else if (txtKTelephone.Text == "")
            {
                string myStringVariable8 = string.Empty;
                MessageBox.Show("Telephone Number is required");
            }
            else
            {
                con.Open();
                String query = "INSERT INTO khaas (VNumber, VColor, VType, VBrand, VEDate, DaysLeft, OName, CivilID, Telephone) VALUES('" + txtKVNumber.Text + "', '" + cboKVColor.Text + "', '" + cboKVType.Text + "', '" + cboKVBrand.Text + "', '" + dateTimePickerKhaas.Value.ToString("MM/dd/yyyy") + "'  ,'" + txtKDaysLeft.Text + "','" + txtKOName.Text + "', '" + txtKCivilID.Text + "', '" + txtKTelephone.Text + "' )";
                SqlDataAdapter sda = new SqlDataAdapter(query, con);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridViewKhaas.DataSource = dt;
                //sda.SelectCommand.ExecuteNonQuery();

                con.Close();
                grd_fillKhaas();


                MessageBox.Show("Inserted Successfully");
                txtKVNumber.Text = "";
                cboKVColor.Text = "";
                cboKVType.Text = "";
                cboKVBrand.Text = "";
                dateTimePickerKhaas.Value = DateTime.Now;
                txtKDaysLeft.Text = "";
                txtKOName.Text = "";
                //txtKChassisNum.Text = "";
                txtKCivilID.Text = "";
                txtKTelephone.Text = "";
            }
        }
        public void grd_fillKhaas()
        {
            //con.Open();
            String query = "SELECT * FROM khaas";
            SqlDataAdapter sda = new SqlDataAdapter(query, con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridViewKhaas.DataSource = dt;
            //con.Close();
        }

        private void dateTimePickerKhaas_ValueChanged(object sender, EventArgs e)
        {
            DateTime from = dateTimePickerKhaas.Value;
            DateTime to = DateTime.Now;
            TimeSpan Tspan = from - to;
            double days = Tspan.TotalDays;
            txtKDaysLeft.Text = days.ToString("0");
        }

        private void btnKUpdate_Click(object sender, EventArgs e)
        {
            if ((txtKVNumber.Text == "") || (cboKVColor.Text == "") || (cboKVType.Text == "") || (cboKVBrand.Text == "") || (txtKDaysLeft.Text == "") || (txtKOName.Text == "") || (txtKCivilID.Text == "") || (txtKTelephone.Text == ""))
            {
                MessageBox.Show("Please select a recored to Update");
            }
            else
            {



                DialogResult upd = MessageBox.Show("Are you Sure you want to Update?" + txtKVNumber.Text + "", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (upd == DialogResult.Yes)
                {
                    con.Open();

                    SqlCommand cmd = new SqlCommand("UPDATE khaas SET VColor='" + cboKVColor.Text + "', VType='" + cboKVType.Text + "', VBrand='" + cboKVBrand.Text + "', VEDate='" + dateTimePickerKhaas.Value.ToString("MM/dd/yyyy") + "', DaysLeft='" + txtKDaysLeft.Text + "', OName='" + txtKOName.Text + "', CivilID='" + txtKCivilID.Text + "', Telephone='" + txtKTelephone.Text + "' WHERE VNumber='" + txtKVNumber.Text + "'", con);
                    cmd.ExecuteNonQuery();

                    con.Close();
                    MessageBox.Show("Record Updated Successfully!");
                    //displaydata();
                    grd_fillKhaas();
                    //clear();
                    txtKVNumber.Text = "";
                    cboKVColor.Text = "";
                    cboKVType.Text = "";
                    cboKVBrand.Text = "";
                    dateTimePickerKhaas.Value = DateTime.Now;
                    txtKDaysLeft.Text = "";
                    txtKOName.Text = "";
                    //txtKChassisNum.Text = "";
                    txtKCivilID.Text = "";
                    txtKTelephone.Text = "";
                    btnKSave.Enabled = true;

                }
                else
                {
                    txtKVNumber.Text = "";
                    cboKVColor.Text = "";
                    cboKVType.Text = "";
                    cboKVBrand.Text = "";
                    dateTimePickerKhaas.Value = DateTime.Now;
                    txtKDaysLeft.Text = "";
                    txtKOName.Text = "";
                    //txtKChassisNum.Text = "";
                    txtKCivilID.Text = "";
                    txtKTelephone.Text = "";
                    btnKSave.Enabled = true;
                    this.Show();
                }
            }
        }

        private void dataGridViewKhaas_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == -1) return;
            selectedRow = e.RowIndex;
            DataGridViewRow row = dataGridViewKhaas.Rows[selectedRow];
            txtKVNumber.Text = row.Cells[0].Value.ToString();
            cboKVColor.Text = row.Cells[1].Value.ToString();
            cboKVType.Text = row.Cells[2].Value.ToString();
            cboKVBrand.Text = row.Cells[3].Value.ToString();
            //dateTimePicker1.Value = row.Cells[4].Value.ToString("");
            txtKDaysLeft.Text = row.Cells[5].Value.ToString();
            txtKOName.Text = row.Cells[6].Value.ToString();
            //txtKChassisNum.Text = row.Cells[7].Value.ToString();
            txtKCivilID.Text = row.Cells[7].Value.ToString();
            txtKTelephone.Text = row.Cells[8].Value.ToString();
            btnKSave.Enabled = false;
        }

        private void btnKDelete_Click(object sender, EventArgs e)
        {
            if ((txtKVNumber.Text == "") || (cboKVColor.Text == "") || (cboKVType.Text == "") || (cboKVBrand.Text == "") || (txtKDaysLeft.Text == "") || (txtKOName.Text == "") || (txtKCivilID.Text == "") || (txtKTelephone.Text == ""))
            {
                MessageBox.Show("Please select a recored to Delete");
            }
            else
            {



                DialogResult del = MessageBox.Show("Are you Sure you want to Delete?" + txtKVNumber.Text + "", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (del == DialogResult.Yes)
                {
                    con.Open();

                    SqlCommand cmd = new SqlCommand("DELETE from khaas WHERE (VNumber='" + txtKVNumber.Text + "')", con);
                    cmd.ExecuteNonQuery();

                    con.Close();
                    MessageBox.Show("Record Deleted Successfully!");
                    //displaydata();
                    grd_fillKhaas();
                    //clear();
                    txtKVNumber.Text = "";
                    cboKVColor.Text = "";
                    cboKVType.Text = "";
                    cboKVBrand.Text = "";
                    dateTimePickerKhaas.Value = DateTime.Now;
                    txtKDaysLeft.Text = "";
                    txtKOName.Text = "";
                    //txtKChassisNum.Text = "";
                    txtKCivilID.Text = "";
                    txtKTelephone.Text = "";
                    btnKSave.Enabled = true;


                }

                else
                {
                    txtKVNumber.Text = "";
                    cboKVColor.Text = "";
                    cboKVType.Text = "";
                    cboKVBrand.Text = "";
                    dateTimePickerKhaas.Value = DateTime.Now;
                    txtKDaysLeft.Text = "";
                    txtKOName.Text = "";
                    //txtKChassisNum.Text = "";
                    txtKCivilID.Text = "";
                    txtKTelephone.Text = "";
                    btnKSave.Enabled = true;
                    this.Show();
                }
            }
        }

        private void btnKClear_Click(object sender, EventArgs e)
        {
            txtKVNumber.Text = "";
            cboKVColor.Text = "";
            cboKVType.Text = "";
            cboKVBrand.Text = "";
            dateTimePickerKhaas.Value = DateTime.Now;
            txtKDaysLeft.Text = "";
            txtKOName.Text = "";
            //txtKChassisNum.Text = "";
            txtKCivilID.Text = "";
            txtKTelephone.Text = "";
            btnKSave.Enabled = true;
            this.Show();
        }

        private void txtKSearch_TextChanged(object sender, EventArgs e)
        {
            con.Open();
            String query = "SELECT * FROM khaas WHERE VNumber like'" + txtKSearch.Text + "%'";
            SqlDataAdapter sda = new SqlDataAdapter(query, con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridViewKhaas.DataSource = dt;
            con.Close();
        }

        private void btnKPrint_Click(object sender, EventArgs e)
        {
            DGVPrinter printer = new DGVPrinter();
            printer.Title = "NAQAL KHAAS"; //Header
            printer.SubTitle = string.Format("Date {0}", DateTime.Now.Date.ToString("MM/dd/yyyy"));
            printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
            printer.PageNumbers = true;
            printer.PageNumberInHeader = false;
            printer.PorportionalColumns = true;
            printer.HeaderCellAlignment = StringAlignment.Near;
            printer.Footer = "Future International Transport Company"; //Footer
            printer.FooterSpacing = 15;
            printer.PrintDataGridView(dataGridViewKhaas);
        }

        //......................................................KHAAS END.......................................................//

Posted 3-Jul-17 21:53pm

Member 13151446

Updated 3-Jul-17 23:40pm

v2

Add a Solution

Comments

Richard MacCutchan 4-Jul-17 4:30am

Are you rebuilding the application and overwriting your live database with the template?

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

Member 7870345 · Answer 1 · 2017-07-03T22:35:00

If your table have disappeared, I think a sql injection attack have ocurred.

This is posible because you create the sql stament by appending strings that corresponds with what the user have typed

"INSERT INTO khaas (VNumber, VColor, VType, VBrand, VEDate, DaysLeft, OName, CivilID, Telephone) VALUES('" + txtKVNumber.Text + "', '" + cboKVColor.Text + "', '" + cboKVType.Text + "', '" + cboKVBrand.Text + "', '" + dateTimePickerKhaas.Value.ToString("MM/dd/yyyy") + "'  ,'" + txtKDaysLeft.Text + "','" + txtKOName.Text + "', '" + txtKCivilID.Text + "', '" + txtKTelephone.Text + "' )";

If, for example, the user type in the txtKTelepone somethig like:
999' ; 'DROP TABLE khass
your stament is converted in a INSERT followed by a DROP TABLE.

The way to solve this kind of attack is never, never, never use the user entrys by appending to string, instead use parameters in your sql stament:

 String query = "INSERT INTO khaas (VNumber, VColor, ... blah blah... , Telephone) VALUES(@vnumber,@vcolor, ... blah, blah... , @telephone )";
SqlCommand command= new SqlCommand(query,con);
command.Parameters.AddWithValue("@vnumber",txtKVNumber.Text);
command.Parameters.AddWithValue("@vcolor",cboKVColor.Text)
... blah, blah..
command.Parameters.AddWithValue("@telephone",txtKTelephone.Text);

OriginalGriff · Answer 2 · 2017-07-03T22:37:00

Not like that...
Two problems to start with, one trivial, one very serious.
The trivial one is easy: don't hard-code connection strings. Always use a configuration file (or at the very least a single const value if you don;t know how to use configuration files yet) - hard coding means the application has to be changed in every place that connects to your DB when you release the code, and that means reliability problems as you need to retest everything, but against a production DB this time...

The serious one is that you should 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.

The disappearance of the DB table (or it's data) is unlikely to be related to your code - unless you have specific code elsewhere to do that - you need to look at what else accesses the DB, but since the SQL instance is accessed via a "whole name" PC specification, rather than a "localhost" address, it's quite possible that some other user or application is deleting it for you.

I want to update the remaining days automatically from database to datagridview according to current date?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0