New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect SQL / Exception when inserting into a table that has only a PK column #127

Closed
pebezo opened this Issue Nov 19, 2012 · 1 comment

Comments

Projects
None yet
2 participants
@pebezo

pebezo commented Nov 19, 2012

Given the following SQL Server table:

CREATE TABLE [dbo].[Document](
    [DocumentId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
    [DocumentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and the following class:

  [TableName(Name = "Document")]
  public class Document
  {
    #region Mapping

    [PrimaryKey, Identity]
    public int DocumentId { get; set; }

    #endregion
  }

when calling:

  var doc = new Document();
  doc.DocumentId = db.InsertWithIdentity(doc);

the generated SQL is

INSERT INTO [Document] 
(

)
VALUES
(

)

SELECT SCOPE_IDENTITY()

which fires the following SqlException: Incorrect syntax near ')'.

There should probably be a special case for tables that only have a primary key field and no other fields.

Thanks.

@pebezo

This comment has been minimized.

pebezo commented Nov 19, 2012

The SQL query should be:

INSERT INTO [Document] DEFAULT VALUES; SELECT SCOPE_IDENTITY();

As a workaround, one could write:

  doc.DocumentId = db
     .SetCommand("INSERT INTO [Document] DEFAULT VALUES;SELECT SCOPE_IDENTITY();")
     .ExecuteScalar<int>();

Thanks.

igor-tkachev added a commit that referenced this issue Dec 9, 2012

@ghost ghost assigned igor-tkachev Dec 9, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment