Incrementing an int during insert

Question

I'm trying to increment an integer in an MS Access table from a c# .net page during insert.

I'm getting a syntax error when attempting the following. Also unsure if I should be using an ExecuteNonQuery() or not?

OleDbCommand cmd = new OleDbCommand("INSERT INTO tblTarget(target,ref) VALUES(@target,(SELECT MAX(ref)+1 FROM tblTarget)", conn);

cmd.Parameters.AddWithValue("@target", TextTitle.Text);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Answer 1

You miss a bracket after tblTarget:

OleDbCommand cmd = 
    new OleDbCommand("INSERT INTO tblTarget(target,ref) VALUES(@target,(SELECT MAX(ref)+1 FROM tblTarget))", conn);

Here is a little review of your code, try using the using pattern:

using(var conn = new Connection())
{
    conn.Open();
    string sql = "INSERT INTO tblTarget(target,ref) VALUES(@target,(SELECT MAX(ref)+1 FROM tblTarget))";
    OleDbCommand cmd = new OleDbCommand(sql, conn);

    cmd.Parameters.AddWithValue("@target", TextTitle.Text);

    cmd.ExecuteNonQuery();
}

Answer 2

You're missing a bracket, try:

INSERT INTO tblTarget(target,ref) VALUES(@target,(SELECT MAX(ref)+1 FROM tblTarget))

But I think you are going to have other issues, you need something closer to this:

INSERT INTO tblTarget ( target, ref )
SELECT @target AS Targ, First((SELECT MAX(ref)+1 FROM tblTarget)) AS MaxRef
FROM tblTarget
GROUP BY @target;