我如何在SQL Server mdf中设置VWD 2010中的自动增量 [英] How do I in a SQL Server mdf set the auto-increment in VWD 2010

问题描述

如何在SQL Server中将mdf设置为VWD 2010中的自动增量？



testID是主键。

How do I in a SQL Server mdf set the auto-increment in VWD 2010?

testID is primary key.

Try
            cmdStr = "INSERT INTO [test] ([testID],[datetime],[col1],[col2],[col3]) Values (@testID,@datetime,@col1,@col2,@col3);"
            Using conn As New SqlConnection(connStr)
                Using cmd As New SqlCommand(cmdStr, conn)
                    conn.Open()
                    cmd.Parameters.AddWithValue("@testID", 1)
                    cmd.Parameters.AddWithValue("@datetime", DateTime.Now)
                    cmd.Parameters.AddWithValue("@col1", TextBox2.Text)
                    cmd.Parameters.AddWithValue("@col2", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@col3", TextBox4.Text)
                    cmd.ExecuteNonQuery()
                    conn.Close()
                    cmd.Dispose()
                    conn.Dispose()
                End Using
            End Using
        Catch ex As Exception
            TextBox1.Text = "Insert Into: " & ex.Message
        End Try

错误代码：

Error code:

Insert Into: Violation of PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.data'.
The statement has been terminated.

推荐答案

试试看，这样你就可以看到你哪里出错了（你可以练习在 SQLFiddle [ ^ ]）

Try this out so you can see where you have gone wrong (you can practice at SQLFiddle[^])

Create table test(
  TestID int identity(1,1),
  DT datetime,
  col1 varchar(max),
  col2 varchar(max),
  col3 varchar(max) 
  )

insert into test (TestID, DT, col1, col2, col3) values(1,GETDATE(), 'a','B','X')

您将收到错误

Quote：

架构创建失败：当IDENTITY_INSERT设置为OFF时，无法在表'test'中为identity列插入显式值。：

Schema Creation Failed: Cannot insert explicit value for identity column in table 'test' when IDENTITY_INSERT is set to OFF.:

现在你可能想把IDENTITY_INSERT设置为'ON' - 但是看看Wes Aday的评论...什么首先要有身份列？



允许SQL为你自动执行此操作！

Now you may be tempted to set IDENTITY_INSERT to 'ON' - but see the comment from Wes Aday ... what would be the point of having that identity column in the first place??

Allow SQL to automatically do that for you!

insert into test (DT, col1, col2, col3) values(GETDATE(), 'a','B','X')

事实上，如果你提供所有列的数据，除了标识列，那么你甚至不需要列列表。

In fact, if you are providing data for all of the columns except the identity column then you don't even need the list of columns.

insert into test values(GETDATE(), 'a','B','X')

同样适用，我得到以下

works just as well, I get the following

1 July, 16 2014 12:19:24+0000 a B X
2 July, 16 2014 12:19:24+0000 a B X

注意事项 ：我将该列名称从 [datetime] 更改为 DT 。使用 SQL保留字是一个非常糟糕的主意[ ^ ]作为列名...只是因为你可以通过使用方括号[]包围列名称并不意味着您应该

Point to Note: I changed that column name from [datetime] to DT. It is a very bad idea to use SQL Reserved Words[^] as column names... just because you can get away with it by surrounding the column name with square brackets [] doesn't mean that you should

您需要在2个位置更改代码，因为您已在模式中将testID设置为自动增量值。 />
1）



cmdStr =INSERT INTO [test]（[testID]，[datetime]，[col1]，[col2]，[ col3]）值（@ testID，@ datetime，@ col1，@ col2，@ col3）;



更改此内容



cmdStr =INSERT INTO [test]（[datetime]，[col1]，[col2]，[col3]）值（@ datetime，@ col1，@ col2，@ col3）;



因为testID是你的主键，它是自动增量所以不需要在你的插入命令中使用它。



2）



不需要下面的代码 - 删除下面的行

cmd.Parameters.AddWithValue（@ testID，1）



可能是这个将有助于解决您的问题。

you need to change your code in 2 places because you have set testID as autoincrement value in your schema.
1)

cmdStr = "INSERT INTO [test] ([testID],[datetime],[col1],[col2],[col3]) Values (@testID,@datetime,@col1,@col2,@col3);"

change this with

cmdStr = "INSERT INTO [test] ([datetime],[col1],[col2],[col3]) Values (@datetime,@col1,@col2,@col3);"

because testID is your primary key and it is autoincrement so no need to take this in your insert command.

2)

no need to below code - remove below line
cmd.Parameters.AddWithValue("@testID", 1)

might be this will be help to solve your issue.

这篇关于我如何在SQL Server mdf中设置VWD 2010中的自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！