Eventually unable to add more columns to a table in a MS SQL Server database.

I am using the following code to add columns to a table (Server Management Objects library):

	Column newColumn = new Column(tbl, name, DataType.Bit);
	newColumn.Nullable = false;
	newColumn.AddDefaultConstraint();
	newColumn.DefaultConstraint.Text = "0";
	tbl.Columns.Add(newColumn);
	tbl.Alter();

Open in new window


This works just fine for a while, but without fail, I am eventually no longer able to add columns.  I get the error, "Alter failed for Table 'dbo.myTable'."  (The InnerException.Message is "An exception occurred while executing a Transact-SQL statement or batch.")

I'm having a devil of a time trying to pinpoint the source of this problem.  It might happen after I've actually edited some of the cells in the table, but I'm not sure.

Note that column rename and remove operations continue to work with no apparent problems.  It's just adding columns that fails.

I'm hoping that someone out there with experience with this exception can help me out.
MiloDCooperAsked:
Who is Participating?
 
MiloDCooperAuthor Commented:
Hmmm.

newColumn.AddDefaultConstraint("DF_" + name);

That gets rid of the problem, but something tells me that is not a permanent solution.
0
 
QPRCommented:
you may have some sort of lock on your table that is prohibiting it from being altered.
Try to re-run the statement after a period of time and see if it works then
0
 
MiloDCooperAuthor Commented:
Upon further investigation, the error I'm getting is this:

"There is already an object named 'DF_myTable_NEW_COLUMN' in the database. Could not create constraint."
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gururaj BadamCommented:
The problem could be newColumn.AddDefaultConstraint(); may be generating a Constraint Name which is clashing with the existing name. It's good to specify a property constraint name on your behalf to avoid such cases.
0
 
timexistCommented:
I am just curious why do you use .net code to add columns to a table.
As I think this is not a very good practice.
0
 
timexistCommented:
Normally, you pass data into database by .net apps, let database uses stored procedure to handle the data process.
It is very rare to see others using .net code to add column to a table.
I think database needs to be properly designed before start  to do coding.

Normally, the task for .net code should be passed in and out the info data, not create a new database table column.

What happen if the table is locked, and you cannot update the table structure?
The info from .net form will be lost.

You can have different ways to store the data.

If you are planing to write an application, my suggestion will be prepare database first,
then start write some code.

Any appilcations always can be  separated two parts, data and function.

And data is the key.


have fun
0
 
MiloDCooperAuthor Commented:
@novice_novice: Can you elaborate, please?  Is newColumn.AddDefaultConstraint("DF_" + name) enough to do the trick?  If so, then how can I be sure that executing that command again won't cause problems when the value of name is the same as it was in a previous operation?

@timexist: I spent three days setting up the server and designing the database and table.  I'm using .NET code because I'm adding SQL database functionality to an existing application.  This application is used chiefly by personnel who've had no experience with databases, so I'm presenting them with an accessible UI that gives them the ability to add, remove, and rename columns (which will be a normal part of their workflow).
0
 
Gururaj BadamCommented:
If you're adding constraint on a column it should only be once not again unless it's a different validation. Database expects names to be unique irrespective of whether it's associated with same Table or different.
0
 
MiloDCooperAuthor Commented:
@Novice_Novice: The thing is, the objects that the database was telling me already existed were for columns that I had *removed* earlier.

What worries me is that my users will remove a column, then later add a column with the same name (hey, I have to cover every possibility), whereupon the database will whine that "there is already an object named blah blah blah."

When I remove a column (i.e. tbl.Columns[name].Remove()), is there some extra clean-up or refresh that I should be doing?
0
 
MiloDCooperAuthor Commented:
Sorry, I meant tbl.Columns[name].Drop(), not tbl.Columns[name].Remove().
0
 
Gururaj BadamCommented:
I'm expecting your code looks like this

tbl.Columns[name].Drop();
tbl.Alter();

If so, the Alter method should have updated the table to remove the specified column.
0
 
MiloDCooperAuthor Commented:
@ Novice_Novice: Yeah, that's my code.  just Drop() then Alter().

Everything seems to work OK since I supplied newColumn.AddDefaultConstraint() with a string, but I'd really love to know why not doing so confounded my table.  That's the only part of my code that changed.  It doesn't make sense to me.  Why would using the parameter-less version of AddDefaultConstraint() introduce vestigial data from previously dropped columns into my table??
0
 
Gururaj BadamCommented:
OK here's the catch . I think you will have to remove the constraints first before dropping the table.

What i'll advise you is try to remove the column by writing SQL statement and see whether the order matches with that of your code in c#
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.