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

Question

1.00/5 (1 vote)

See more:

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

testID is primary key.

VB

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.

Posted 16-Jul-14 1:15am

Teledextri

Updated 16-Jul-14 1:18am

v3

Add a Solution

Comments

[no name] 16-Jul-14 7:58am

You don't. It's auto-incremented. What would be the point of having a auto-incremented field if you are just going to turn around and set it yourself?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2014-07-16T02:24:00

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

SQL

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')

You will get an error

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

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

VB

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

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

purvivani · Answer 2 · 2014-07-16T02:29:00

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.

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0