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.

[TheCPUWizard]

You are asking two different questions. Adding a column to a DataSEt does not modify the underlying table.

If you want to modify a table, you can use the "ALTER TABLE" SQL command.

Once the table is modified, you should be able to re-generate your DataSet (if this is VS-2003, NEVER modify you generated datasets, derive from them in a seperate file. VS-2005 partial classes minimize/eliminate this restriction.)

[furjaw]

I added this line to my Visual Basic program, but, it didn't do anything:
PatientDataSet.Patient.Columns.Add("Case#")

If I go into DataSet Designer and add the column, I end up losing all of the work that I ever put into it. When I tried that, the program insisted that a date had to be entered into BirthDate. Previously, it did not give an exception if it was left blank.

I started getting an exception on this line:
If IsDBNull(PatientBindingSource.Current("BirthDate")) Then
Form1.PatientAgeString = ""
BirthDate = Form1.DefaultDate
endif

And my DataGridView was screwd up - the red "X" was greyed out and I could not delete any rows and there was no way to get it back.

[furjaw]

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

Adding the following statement did not help:
PatientDataSet.Patient.Columns.Add("Case#")

[TheCPUWizard]

Go back and re-read my last post.

1) Modifying a DataSet does NOT modify the database. You need to to that seperately.

2) NEVER modify the DataSet Designer Code. You *WILL* need to regenerate it and loose any manually made changes.

[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.
I know nothing about SQL. There are no SQL statements in my app. Everything is done by TableAdapter and BindingSource statements like:
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
That's why I don't know how to add a column to the SQL table.

[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

[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?