CODEWITHSUNDEEP
asp.netdatabasedatetime
qU3st
qU3st

Reputation: 97

Insert current datetime based onclick

I am currently trying to store datetime in the database. The idea is when onclick on the button, the database will store the current datetime in the database. I have tried some methods but they prompt me with this error:

"Incorrect syntax near '26/6/2013 00:00:00'"

This is my codes:

con.Open();
        query = "INSERT INTO dbo.url_map (long_url, expiry_date) Values ('" + tbLongURL.Text + "' , '" + DateTime.Today + "')";
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithValue("@long_url", tbLongURL.Text);
        cmd.Parameters.AddWithValue("@expiry_date", DateTime dt = DateTime.ParseExact(DateTime.Today.ToString(), "dd/MM/yyyy h:mm:ss tt", CultureInfo.InvariantCulture));
        cmd.ExecuteNonQuery();

what is wrong with the way i add the date time?

Any help will be greatly appreciated.

Upvotes: 0

Views: 2760

Answers (4)

Mohit
Mohit

Reputation: 115

You can directly bind the Current Date to 'expiry_date' column in SQL server.

Steps:

  1. Right click on your table and select 'Design'.
  2. Click on the desired column, 'expiry_date' in your case.
  3. In the column property, look for 'Default Value or Binding' in General Section.
  4. Put 'GETDATE()' as the default value, so whenever you insert the record, it will take current date as its value.

Upvotes: 0

varun
varun

Reputation: 76

    protected void Button1_Click(object sender, EventArgs e)
    {
        Insert();
    }
    public void Insert()
    {
        try
        {
            _conn = new SqlConnection(con);
            _cmd = new SqlCommand();
            _cmd.Connection = _conn;
            _conn.Open();
            _cmd.CommandType = CommandType.StoredProcedure;

            _cmd.CommandText = "insert_date";

            _cmd.Parameters.Add("@Date",SqlDbType.DateTime).Value = DateTime.Now;


            _cmd.ExecuteNonQuery();
            _conn.Close();
        }
        catch (Exception ex)
        { 

        }
    }

Stored Procedure:

create procedure insert_date @Date datetime as insert into DateTable values(@Date)

Result after insertion on table:

1 2013-06-26 14:29:36.987

Upvotes: 0

Microsoft DN
Microsoft DN

Reputation: 10030

Corrected query- 

   query = "INSERT INTO dbo.url_map (long_url, expiry_date) Values ('" + tbLongURL.Text + "', '" + DateTime.Today + "')";

Upvotes: 3

michele
michele

Reputation: 2091

If you use parameters I think your query should be

query = "INSERT INTO dbo.url_map (long_url, expiry_date) Values (@long_url, @expiry_date)";
//this way sqlcommand make sense
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@long_url", tbLongURL.Text);
cmd.Parameters.AddWithValue("@expiry_date", DateTime.Today);

Upvotes: 0

Related Questions