ExecuteNonQuery error

Question

I need to update my database to set a date to the current timestamp. However, my ExecuteNonQuery is giving an exception "Incorrect syntax near ')' ". Can anyone tell me what is wrong? This is the function with the error:

protected void setprint(object sender, EventArgs ev)
{
    GridViewRow row = ((LinkButton)sender).Parent.Parent as GridViewRow;
    inv = row.Cells[1].ToString().Trim();

    con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
    cmd = new SqlCommand("update invdata set ViewDate = CURRENT_TIMESTAMP() where InvoiceNumber= '" + inv + "'", con);
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
    }
    finally
    {
        if (con != null)
            con.Close();
    }

}

You should use parameterised queries. This query is open to a SQL injection attack: http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Paddy, Jun 05 '14 at 09:15
@DavidPilkington - I know, you have already answered it. This is why it is a comment (in an attempt to be helpful) rather than an answer. — Paddy, Jun 05 '14 at 09:23

score 4 · Answer 1 · answered Jun 05 '14 at 09:14

4

CURRENT_TIMESTAMP should not have ()

So change it to

cmd = new SqlCommand("update invdata set ViewDate = CURRENT_TIMESTAMP where InvoiceNumber= '" + inv + "'", con);

answered Jun 05 '14 at 09:14

David Pilkington

13,528
3
41
73

Thanks. This worked. But now it's saying 'cannot update a timestamp column'. Is this true? – kray Jun 05 '14 at 09:24
@kray Have a look here http://stackoverflow.com/questions/4592658/sql-server-updating-a-time-stamp-column it is updated automatically – David Pilkington Jun 05 '14 at 09:26

score 1 · Accepted Answer · answered Jun 05 '14 at 09:20

1

Instead of using CURRENT_TIMESTAMP, use GetDate() function to get current date and time.

answered Jun 05 '14 at 09:20

Ricky

2,323
6
22
22

user2270653 · Answer 3 · 2014-06-05T09:39:21.980

I would also recommend adding your SQL Connection into a using block this way the connection is disposed and you do not have to manually close it in a finally block.

   private void Form1_Load(object sender, EventArgs e)
    {
        using (con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString())) ;
        {
            cmd = new SqlCommand("update invdata set ViewDate = GETDATE() where InvoiceNumber= '" + inv + "'", con);


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

}

ExecuteNonQuery error

3 Answers3