CODEWITHSUNDEEP
ms-accessc#-4.0
Atul
Atul

Reputation: 155

how to insert current date into access database?

Actually im creating one application where i inserted user sales count, sales count is updating fine but when i edit in query by putting current date its not updating detail Here im putting whole code 

private void button1_Click_1(object sender, EventArgs e)
        {

            this.txtinput.MaxLength = 4;
            cmd = new OleDbCommand("update Login set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1 where [Unique_No]=@Unique_No and [To_Date]='#"+DateTime.Now.ToString("dd/MM/yyyy")+"#'", con);
            cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
                con.Open();
                int n = cmd.ExecuteNonQuery();

                    if (n == 0)
                    {
                        MessageBox.Show("Invalid Unique No. pls try again later");// **Debugger come to this line if i insert [To_Date]='#"+DateTime.Now.ToString("dd/MM/yyyy")+"#'** // if i remove above line in code then its updating fine 

                    }
                    else
                    {
                        this.DialogResult = DialogResult.OK;
                    }



                con.Close();
            }

        }

for reference pasting access table detail enter image description here enter image description here

Upvotes: 0

Views: 1731

Answers (3)

HansUp
HansUp

Reputation: 97101

Access SQL supports a Date() function which the db engine can use to determine the current date. So you don't need c# code to take the current date and concatenate it (eek!) into the text of your SQL statement. And you don't need to feed the date as a SQL parameter (which is less nasty), either. Simply let the db engine determine Date() for itself.

cmd = new OleDbCommand("update Login set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1 where [Unique_No]=@Unique_No and [To_Date]=Date()", con);

That suggestion was based on the SQL statement in your question. However, in the comments, it sounds like you want to change To_Date values to today's date for some records. I'm not clear about which records should be changed, so no idea what you need for a WHERE clause. But to update To_Date values to today's date, the Access SQL should start like this ...

UPDATE Login SET To_Date = Date() WHERE ...

Upvotes: 1

BJones
BJones

Reputation: 2460

Based on comments provided, you need to SET the date. In your original query, you are using it in the WHERE clause:

private void button1_Click_1(object sender, EventArgs e)
{
    DateTime toDay = DateTime.Now;

    this.txtinput.MaxLength = 4;
    cmd = new OleDbCommand("update Login set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1, [To_Date]= @to_day where [Unique_No]=@Unique_No", con);
    cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
    cmd.Parameters.AddWithValue("@to_Day", toDay.ToString("yyyyMMdd"));
        con.Open();
        int n = cmd.ExecuteNonQuery();

            if (n == 0)
            {
                MessageBox.Show("Invalid Unique No. pls try again later");// **Debugger come to this line if i insert [To_Date]='#"+DateTime.Now.ToString("dd/MM/yyyy")+"#'** // if i remove above line in code then its updating fine 

            }
            else
            {
                this.DialogResult = DialogResult.OK;
            }



        con.Close();
    }

}

You also had incorrect syntax which I have updated. Let me know if the above query works.

Upvotes: 0

A.Youssouf
A.Youssouf

Reputation: 28

try this :

private void button1_Click_1(object sender, EventArgs e)
    {
        DateTime toDay = DateTime.Now;

        this.txtinput.MaxLength = 4;
        cmd = new OleDbCommand("update Login set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1 where [Unique_No]=@Unique_No and [To_Date]= @to_day", con);
        cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
        cmd.Parameters.AddWithValue("@date_now", toDay.ToString("yyyyMMdd"));
            con.Open();
            int n = cmd.ExecuteNonQuery();

                if (n == 0)
                {
                    MessageBox.Show("Invalid Unique No. pls try again later");// **Debugger come to this line if i insert [To_Date]='#"+DateTime.Now.ToString("dd/MM/yyyy")+"#'** // if i remove above line in code then its updating fine 

                }
                else
                {
                    this.DialogResult = DialogResult.OK;
                }



            con.Close();
        }

    }

Upvotes: 0

Related Questions