Adding a column to an SQL database

[furjaw]

Visual Basic 2005
How do I go about adding a column to an SQL database.
Everytime I did it in the past it was a disaster and I literally had to start all over again from square one.
I would go into Dataset Designer to add the column then I would have all kinds of problems with the form that displays the DataGridView and would have to recreate the form from scratch.
I don't want to recreate the form because it is working fine with the users not entering anything in the BirthDate field and I can't remember what I did to keep it from insisting that a date be entered.
I am not talking about adding the column programmatically.

 

[mrdenny]

I would imaging that the problem could be caused by adding the column somewhere other than the end of the table.

I would recommend using either the SQL UI or the ALTER TABLE command to add the column.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[furjaw]

OK, I muddled around until I got it working in debug.
Then, when I installed it, I got an exception "Case# is an invalid column" and none of my data records appeared.

 

[philhege]

Alias the column name or rename it.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[mrdenny]

You don't want to use any special characters (%#@$&*, etc) in your column names. If you do use special characters then you need to enclose the field name in brackets [Case#].

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[furjaw]

I have a number of users running this application in remote locations.
Of course, they cannot lose their existing data.
It was requested that I include a column called "CaseNbr" in the table.
The file that I need to add the column to is an SQL table that is being updated via a DataGridView.
I think that it was a mistake to add the column via DataSet Designer, so, I restored my project from a backup eliminating that step.
So, now the column does not exist in the database and I am back to square one.
I would like to add the column to the SQL tables via my app so that my various users can have it added to their databases. Every user has his own database.
There is only one program that accesses the table and it is very simple.
The user selects (or adds) a row (Patient record) then the program simply passes that Patient's info to the main form of the application.

 

[mrdenny]

You will probably want to check with the folks in the .NET forums and see how they recommend to do it.

From the SQL Server side an ALTER TABLE command is the method to use. Doing it through .NET is going to be outside the experteise of may of the folks in this forum.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[furjaw]

I added the following statement to my program:

Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))

But it did not do anything.

I added the statement as the first command in the Form Load routine:

Public Class Patient
Public Sub Patient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
End Sub

 

[ArtieChoke]

I would think you need to do an alter statement directly on the table. The Columns.Add probably adds a column to the control and not the database.

Use the ADO.NET connection object and open a connection to the database. Then issue the alter statement to add the column.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[furjaw]

How do I do that?

Here is how MSDN tells you to do it (I just added these statements to my Form Load routine):

Dim columns As DataColumnCollection = Me.PatientDataSet.Patient.Columns
If columns.Contains("CaseNbr") Then
MsgBox("'CaseNbr' column already exists" )
Else
Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))
End If

It bombed out with this message:
"An attempt to attach an auto-named database for file
C:\Documents and Settings\Owner\My Documents\Visual Studio 2005\Projects\
OrthoLabRx\OrthoLabRx\bin\Debug\Patient.mdf failed.
A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

 

[mrdenny]

You are well outside of SQL Server world at this point. You will probably get a better answer in a .NET forum as that appears to be a .NET error message and probably 99% of the people in this forum don't know much about .NET.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[furjaw]

OK, I deleted the Patient.mdf in Server Explorer.
Then I opened a New Connection and browsed to the .mdf file under the output directory (\bin\debug).
Now I can see the new column in Server Explorer.
But, how do I get it to appear in Data Sources of Solution Explorer?

 

[mrdenny]

Again, this would be a .NET question which would be better answered in the .NET forums.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[onpnt]

The topic has completely gone in the wrong direction and I'm curious why it is being discussed here??

furjaw, There is a very good reason Tek-Tips is not made up of one bloated, unrelated and unstructured forum. You've been pointed to seek out the .NET forums. Why do you insist on posting more questions after that has been pointed out?

You know it wouldn't be as frustrating if it didn't happen time and time again in most cases post rate after post. The concept is not that hard. Yet if you cannot grasp that I'm curious how you figured out a web browser enough to find this site.

forum796

There are several FAQ's on how to ask questions. Everyone has a section on "picking the right forum". Other strong points are not flying off the topic at hand and causing a mess. If you have another question seeing as your fix and unearthed your mess, go to the other forum of that specific technology and ask there.

The members here all work along with take the time to help you and every other member that comes here. They do not need frustrations like this after they were kind enough to send you in the correct direction. That will only cause them to leave and you and the rest to be without their expertise in fixing those ever loving messes that we all seem to create.

I have great hopes you and all others that come here confused read this and start using the forums the correct way so those forums better assist you and everyone else.

[sub]____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own[/sub]