Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 227
thenextmogul Avatar asked Oct 14 '13 22:10

thenextmogul


People also ask

How do I increment a column in SQL Server?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

How can I add values to a specific column in SQL Server?

INSERT INTO Syntax Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...)

How do you add numbers in a column in SQL?

If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.

What does +1 do in SQL?

The statement 'select 1' from any table name means that it returns only 1. For example, If any table has 4 records then it will return 1 four times.


1 Answers

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 
like image 93
Guffa Avatar answered Sep 21 '22 06:09

Guffa