Creating new field while app running

[John]

Hi

Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?

Thanks

Regards

 

[=?iso-8859-1?B?Sulq6Q==?=]

you can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.

also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added

 

[Cor Ligthert [MVP]]

John,

You can, however it is probably not a good idea.

You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery

You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is

microsoft.public.dotnet.framework.adonet

I hope this helps,

Cor

 

[m.posseth]

or maybe

microsoft.public.sqlserver.programming ???? ( as there are more ways to
perform this as with ADO.Net )


ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )

if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following


perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files

you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )


regards

Michel Posseth

 

[Erland Sommarskog]

Is it possible to add a field to an sql server 20005 table
programmatically by a vb.net app while the vb.net app has the table
being viewed on a vb.net form?

Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.

What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.

Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.