Click here to Skip to main content
15,211,000 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have tried to create an alter table statement to change my current column to include a primary key.

here is my code:

sqlcon.Open()
           Dim cmdDBCC As New System.Data.SqlClient.SqlCommand("ALTER TABLE [" & dtname & "] ADD PRIMARY KEY(" & dtcolumn & ")", sqlcon)
           cmdDBCC.ExecuteNonQuery()
           sqlcon.Close()



However, when I run the code it does not work and skips over the executenonquery. Is there a good approach to altering a column to include a PK?

What I have tried:

I have tried to use the code above and I have also tried to use constraint with the code and neither of them worked.

sqlcon.Open()
           Dim cmdDBCC As New System.Data.SqlClient.SqlCommand("ALTER TABLE [" & dtname & "] ADD CONSTRAINT PK_" & dtname & " PRIMARY KEY(" & dtcolumn & ")", sqlcon)
           cmdDBCC.ExecuteNonQuery()
           sqlcon.Close()
Posted
Updated 19-Apr-19 19:04pm
Comments
ZurdoDev 22-Apr-19 16:05pm
   
What do you mean it skips over ExecuteNonQuery. That can't happen without an error being thrown before it.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You need to define 'does not work'. First emit your string and run it in SQL Server management studio to make sure your SQL is correct. If it is, then check your code to run it.

This code will not work on a table that has a primary key already. You can only have one. I can't think of any earthly reason for this code to exist. Why do you need to set it dynamically?
   
Comments
Member 11856456 20-Apr-19 10:48am
   
I originally had an Identity(1,1) with autoincrement. However, I removed this and started over. I did not realize that during this process I forgot to make certain tables with PK. So, for these tables I was looking to insert the primary key dynamically.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100