How to fix Incorrect syntax near the keyword "NOT" during Update-Database using Migration

▲
-1
▼

I am new into software development. I am currently developing Asp.Net Core API. I have managed to do Add-migration successfully, the problem start when I need to Update-Database.

The error is as follows

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'NOT'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:9cf19409-67c7-4553-b2f9-5407db27d008
Error Number:156,State:1,Class:15
Incorrect syntax near the keyword 'NOT'.

Your assistance will be much appreciated.

c#
asp.net
.net-core
asp.net-core-webapi
asked on Stack Overflow May 31, 2019 by Ras C-kay sKatle • edited May 31, 2019 by ckuri

3 Answers

▲
2
▼

I faced the same issue in initial migration to create database. The PMC console throw " Incorrect syntax near the keyword 'NOT'. " error. after debugging couple of minutes i found in the .HasColumnType("varchar(3"); attribute for one of the field. It seems that during migration one bracket was lost in auto generated migration file - it looked like this "HasColumnType("varchar(3") instead it should look like this HasColumnType("varchar(3)") like this.

I found this in "/Migrations/..ModelSnapshot.cs", in both, "Up" and "Down"methods. and "/Migrations/..20190613110239_InitialCreate"

After adding bracket in this two places the Update-Database command works as intended.

I hope it will help! Happy Coding.

answered on Stack Overflow Jun 13, 2019 by YogeshJadhav
▲
1
▼

As highlighted by YogeshJadadhav, you might need to check your auto generated file for errors. Note that the error might not be highlighted by the debugger because its not a syntax error, but rather a string error. Here is a snippet of code showing how I resolved this issue.

Auto generated code with error

 Price = table.Column<decimal>(type: "decimal(18, 2", nullable: false)

Corrected Code

 Price = table.Column<decimal>(type: "decimal(18, 2)", nullable: false)

You will have to ractify this error in your migrationName.cs and migrationName.Designer.cs.

answered on Stack Overflow Oct 25, 2020 by Emmanuel Motsi
▲
0
▼

Reason: The reason is mostly because you've done syntactic error in your model class which is to be inserted as a table in database.

Solution:

  1. You can remove the last migration, edit the model class , create a new migration and update the database.
  2. You can edit the migration file correctly

Example: This was my mistake firstly in model class [Column(TypeName ="decimal(18,2")] public decimal Price { get; set; } The missing first bracket in "decimal(18,2". Then I deleted the previous migration , added first bracket, created new migration and updated database. It worked just fine.

answered on Stack Overflow Feb 22, 2021 by Dracula

User contributions licensed under CC BY-SA 3.0