How do I alter a column to include a primary key in VB.NET

Question

0.00/5 (No votes)

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 19-Apr-19 18:44pm

Member 11856456

Updated 19-Apr-19 19:04pm

Add a Solution

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

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)

Christian Graus · Accepted Answer · 2019-04-19T19:04:00

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?

Posted 19-Apr-19 19:04pm

Christian Graus

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.