How to add a column to an SQL database

[furjaw]

Visual Basic 2005
I used DataSet Designer to add a column called "Case#" to an existing SQL table.
The table is used by a DataGridView in a VB 2005 program.
I got it working on my development computer in debug mode.
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#")

 

[cjard]

Visual Basic 2005
I used DataSet Designer to add a column called "Case#" to an existing SQL table.

You seem to be confused as to what the dataset designer is for. It is a designer for datasets, a client side entity used to hold data temporarily downlaoded from a database.

IT DOES NOT EDIT DATABASES. IT IS NOTHING TO DO WITH DATABASE TABLES

Suggest you use the "Server Explorer" window to modify your tables in the database..

 

[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

 

[cjard]

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.

If the column must be edited by the user, then it will need to be in the dataset.

So, now the column does not exist in the database and I am back to square one.

Just incase you somehow missed the point i was making before; it matters not whether you put a column in a DataTable in the DataSet designer or not. It doesnt affect the database, it has nothing to do with the database, and there is no concept of "now the column doesnt exist in the database" because it never did!

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.

Then you will need to issue a DDL statement, that looks something like:

ALTER TABLE myTab ADD CaseNbr VARCHAR2(500)

The bold bit is the type of column, this is a 500 char string..

I know nothing about SQL.

I recommend you learn a very little bit, from www.w3schools.com

There are no SQL statements in my app. Everything is done by TableAdapter

TableAdapters contain SQL statements. By default they take a simplistic route of selecting everything from a database, and can usually generate the relevant update commands if the table has a primary key.

Trust me, your app does use SQL, you jsut havent seen it. I recommend you do look at it, because downloading all 1000 records from a table, just because you want to look at the 3 whose FirstName start with "Smith" isnt wise

That's why I don't know how to add a column to the SQL table.

OK, well, a DDL is what you need, take a read of the w3schools link..

As for how you would get it into your program, i dont recommend you do. Instead, make a separate app that contains only the following code:

Dim cmd as New SqlCommand("ALTER TABLE myTable ADD CaseNbr VARCHAR2(500)", New SqlConnection(conn_string))
cmd.ExecuteNonQuery

Ensure the sql is correct (see w3schools), and the conn_string can be replaced with whatever is in the settings of the other app..

 

[cjard]

ps; the DW2 link in my sig contains some articles about theory of operation of database access in .NET 2.. have a read of them, so you at least gain an appreciation for how it works.

 

[furjaw]

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."

 

[Paszt]

furjaw, as cjard has pointed out, datasets are different from databases. A dataset in ADO.NET is an in memory storage medium. You can add columns to a dataset, but this is usually done to display calculated columns. Adding a column to a dataset in no way affects a database. If you want to store the data in a column of a dataset, you will need a corresponding column or field in the dataset.
cjard has already shown you how to add a field to a database.

Explaining databases fully would require a very large book, and is beyond the scope of a forum, so you will be doing yourself a favor to read some of the articles posted above.

The error message you have posted most likely has nothing to do with the code you have shown.

 

[cjard]

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

You arent listening, and I'm not in the habit of repeating myself. Re-read everything I have written in this topic, googling for anything you dont understand.

 

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

 

[cjard]

Dont cross-post

http://www.vbdotnetforums.com/showthread.php?t=19470&goto=newpost