CODEWITHSUNDEEP
c#sqlentity-framework-migrations
JohnNewbie
JohnNewbie

Reputation: 67

Error Number:102,State:1,Class:15 Incorrect syntax near ','

I'm trying to set up a Local SQL database with the below migration but I am getting error:

102,State:1,Class:15 Incorrect syntax near ','..

I'm not sure if what I am trying is possible and if so, where I have gone wrong. Any advice appreciated.

public partial class Baseline : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Areas",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    CountryId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Countries", t => t.CountryId, cascadeDelete: false);

        Sql("UPDATE Areas SET Name = 'Cumbria' WHERE Id =1");
        Sql("UPDATE Areas SET Name = 'Merseyside' WHERE Id =2");
        Sql("UPDATE Areas SET Name = 'Suffolk' WHERE Id =3");
        Sql("UPDATE Areas SET Name = 'Wiltshire' WHERE Id =4");
        Sql("UPDATE Areas SET Name = 'Scotland' WHERE Id =5");
        Sql("UPDATE Areas SET Name = 'Wales' WHERE Id =6");

        Sql("Update Areas SET CountryId = '1' WHERE Id =1, 2, 3, 4");
        Sql("Update Areas SET CountryId = '2' WHERE Id =5");
        Sql("Update Areas SET CountryId = '3' WHERE Id =6");

        CreateTable(
            "dbo.Countries",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);

        Sql("Update Countries SET Name = 'England' WHERE Id =1");
        Sql("Update Countries SET Name = 'Scotland' WHERE Id =2");
        Sql("Update Countries SET Name = 'Wales' WHERE Id =3");

        CreateTable(
            "dbo.DutyHolders",
            c => new
                {
                    Id = c.Long(nullable: false, identity: true),
                    dhName = c.String(),
                    dhAddress1 = c.String(),
                    dhAddress2 = c.String(),
                    dhAddress3 = c.String(),
                    dhTown = c.String(),
                    dhPostCode = c.String(),
                    dhCountry = c.String(),
                    dhPhoneNumber = c.String(),
                    dhAddressLine1 = c.String(),
                    dhPriority = c.Long(nullable: false),
                    dhCounty = c.String(),
                    dhEmail = c.String(),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Locals",
            c => new
            {
                Id = c.Int(nullable: false, identity: true),
                Name = c.String(),
                AreaId = c.Int(nullable: false),
            })
            .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Areas", t => t.AreaId, cascadeDelete: false);

        Sql("Update Locals SET Name = '[Carlisle District Council]' WHERE Id =1");
        Sql("Update Locals SET Name = '[Copeland Borough Council]' WHERE Id =2");
        Sql("Update Locals SET Name = '[Eden District Council]' WHERE Id =3");
        Sql("Update Locals SET Name = '[Liverpool Metropolitan District Council]' WHERE Id =4");
        Sql("Update Locals SET Name = '[Sefton Metropolitan District Council]' WHERE Id =5");
        Sql("Update Locals SET Name = '[Ipswich Borough Council]' WHERE Id =6");
        Sql("Update Locals SET Name = '[Waveney District Council]' WHERE Id =7");
        Sql("Update Locals SET Name = '[Swindon Unitary Authority]' WHERE Id =8");
        Sql("Update Locals SET Name = 'Wiltshire' WHERE Id =9");
        Sql("Update Locals SET Name = '[Highland Unitary Authority]' WHERE Id =10");
        Sql("Update Locals SET Name = '[Stirling Unitary Authority]' WHERE Id =11");
        Sql("Update Locals SET Name = '[Cardiff Unitary Authority]' WHERE Id =12");
        Sql("Update Locals SET Name = '[Swansea Unitary Authority]' WHERE Id =13");

        Sql("Update Countries SET AreaId = '1' WHERE Id =1, 2, 3");
        Sql("Update Countries SET AreaId = '2' WHERE Id =4, 5");
        Sql("Update Countries SET AreaId = '3' WHERE Id =6, 7");
        Sql("Update Countries SET AreaId = '4' WHERE Id =8, 9");
        Sql("Update Countries SET AreaId = '5' WHERE Id =10, 11");
        Sql("Update Countries SET AreaId = '6' WHERE Id =12, 13");

        CreateTable(
            "dbo.Notification_Link",
            c => new
            {
                Id = c.Int(nullable: false, identity: true),
                notificationId = c.Int(nullable: false),
                dhId = c.Long(nullable: false),
                roleId = c.Int(nullable: false),
            })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Notifications", t => t.notificationId, cascadeDelete: false)
            .ForeignKey("dbo.DutyHolders", t => t.dhId, cascadeDelete: false);


        CreateTable(
            "dbo.Notifications",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    IsNew = c.Boolean(nullable: false),
                    Address1 = c.String(nullable: false),
                    Address2 = c.String(nullable: false),
                    Address3 = c.String(),
                    Town = c.String(nullable: false),
                    County = c.String(),
                    PostCode = c.String(nullable: false),
                    Intial = c.Boolean(nullable: false),
                    Withdrawn = c.Boolean(nullable: false),
                    MultiSite = c.Boolean(nullable: false),
                    CountryId = c.Int(nullable: false),
                    AreaId = c.Int(nullable: false),
                    LocalId = c.Int(nullable: false),
                    Update = c.Boolean(nullable: false),
                    InitialNotificationId = c.String(),
                    Email = c.String(),
                    ProjectTypeId = c.Int(),
                    StartDate = c.DateTime(),
                    DutyHolder = c.String(),
                    CreatedDate = c.DateTime(),
                    ModifiedDate = c.DateTime(),
                    ProjectDescription = c.String(),
                    SubmitFlag = c.Boolean(nullable: false),
                    PeopleOnSite = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Areas", t => t.AreaId, cascadeDelete: true)
            .ForeignKey("dbo.Countries", t => t.CountryId, cascadeDelete: true)
            .ForeignKey("dbo.Locals", t => t.LocalId, cascadeDelete: true)
            .ForeignKey("dbo.ProjectTypes", t => t.ProjectTypeId)
            .Index(t => t.CountryId)
            .Index(t => t.AreaId)
            .Index(t => t.LocalId)
            .Index(t => t.ProjectTypeId);

        CreateTable(
            "dbo.ProjectTypes",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Description = c.String(),
                    Category = c.String(),
                })
            .PrimaryKey(t => t.Id);

        Sql("Update ProjectTypes SET Description = '[Asbestos Work]' WHERE Id =1");
        Sql("Update ProjectTypes SET Description = '[Building Services]' WHERE Id =2");
        Sql("Update ProjectTypes SET Description = '[Other Bridge]' WHERE Id =3");
        Sql("Update ProjectTypes SET Description = '[Track Renewal]' WHERE Id =4");

        Sql("Update ProjectTypes SET Category = 'FOD' WHERE Id =1, 2");
        Sql("Update ProjectTypes SET Category = 'RI' WHERE Id =3, 4");


        CreateTable(
            "dbo.Roles",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);

        Sql("Update Roles SET Name = 'Client' WHERE Id =1");
        Sql("Update Roles SET Name = '[Principal Designer]' WHERE Id =2");
        Sql("Update Roles SET Name = '[Principal Contractor]' WHERE Id =3");
        Sql("Update Roles SET Name = 'Designer' WHERE Id =4");
        Sql("Update Roles SET Name = 'Contractor' WHERE Id =5");

Upvotes: 0

Views: 12511

Answers (2)

David Lindon
David Lindon

Reputation: 355

The issue is with the line 

"Sql("Update Areas SET CountryId = '1' WHERE Id =1, 2, 3, 4");"

you can't have multiple values like that, you will need to use the SQL In -

Sql("Update Areas SET CountryId = '1' WHERE Id  in (1, 2, 3, 4)");

Upvotes: 1

Daniel Masterson
Daniel Masterson

Reputation: 534

Your issue is with the query: 

Update Areas SET CountryId = '1' WHERE Id =1, 2, 3, 4

It should be something like

UPDATE Areas SET CountryId = '1' WHERE Id IN (1, 2, 3, 4)

IN allows you to compare against a list of things

Upvotes: 3

Related Questions