How to add plus one (+1) to a SQL Server column in a SQL Query
The simple question is, how do you increment a field value in a MS Query by 1 ? I am trying to add 1 (+1) to an int
column in my SQL Server database using a parametrized method. Similar to an i++ operation on a variable. I am using the following method:
public static int UpdateFieldCount(int parameterId)
{
// variable to hold the number of rows updated or the success of the query
int updatesuccess = 0;
// build your connection string
string connectionstring = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionstring);
// build your SQL Query statement
string SQLString = "UPDATE TableName SET TableField + 1 WHERE SomeFilterField = @ParameterID";
SqlCommand sqlcmd = new SqlCommand(SQLString, conn);
sqlcmd.Parameters.AddWithValue("@ParameterID", parameterID);
conn.Open();
updatesuccess = sqlcmd.ExecuteNonQuery();
conn.Close();
return updatesuccess;
}
This method is throwing the following error related to the plus sign (+) in my sql query:
Incorrect syntax near '+'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '+'.
Source Error:
Line 315:
Line 316: conn.Open();
Line 317: updatesuccess = sqlcmd.ExecuteNonQuery();
Line 318: conn.Close();
Line 319:Source File: c:\testdevlocation\appname\App_Code\ClassFileName.cs Line: 317
Any advice on this?
You need both a value and a field to assign it to. The value is TableField + 1
, so the assignment is:
SET TableField = TableField + 1
"UPDATE TableName SET TableField = TableField + 1 WHERE SomeFilterField = @ParameterID"