How to add plus one (+1) to a SQL Server column in a SQL Query

SqlSql ServerTsql

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

"UPDATE TableName SET TableField = TableField + 1 WHERE SomeFilterField = @ParameterID"

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionthenextmogulView Question on Stackoverflow
Solution 1 - SqlGuffaView Answer on Stackoverflow
Solution 2 - SqlAshalyndView Answer on Stackoverflow