C# Command.ExecuteNonQuery(); Error Incorrect syntax near 'VALUE'

Question

I've tried a ton of different things with this, I keep getting the same error on my

cmd.ExecuteNonQuery();

It throws me this.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Incorrect syntax near 'VALUE'.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Server_Testing
{
    class Program
    {
        static void Main(string[] args)
        {
            string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            SqlConnection con = new SqlConnection(conString);

            con.Open();

            SqlCommand cmd = new SqlCommand("INSERT INTO Test (Id, Name) VALUE (@Id, @Name)", con);

            if(con.State == ConnectionState.Open)
            {
                //cmd.Parameters.Add(new SqlParameter("@Id", 1));
                //cmd.Parameters.Add(new SqlParameter("@Name", "MyName"));
                //cmd.Parameters.AddWithValue("@Id", 1);
                //cmd.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = "MyName";
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Myname");

                cmd.ExecuteNonQuery();
            }
        }
    }
}

As you can see from the comments, I've tried some things, but non of if works for me, I cant seem to get it right, it must be the SQL string, but that should be fine as well.. Can anyone help me here?

Soner Gönül · Accepted Answer · 2014-11-21T09:58:56.497

It should be VALUES not VALUE.

INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}

Read INSERT (Transact-SQL)

And use using statement to dispose your SqlConnection and SqlCommand.

using(SqlConnection con = new SqlConnection(conString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "INSERT INTO Test (Id, Name) VALUES (@Id, @Name)";
   cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
   cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = "Myname";
   // I assumed your column types are Int and NVarChar
   con.Open();
   cmd.ExecuteNonQuery();
}

As a best practice, don't use AddWithValue method. It may generate unexpected results. Use .Add() method or it's overloads.

Read: Can we stop using AddWithValue() already?

C# Command.ExecuteNonQuery(); Error Incorrect syntax near 'VALUE'

1 Answers1