By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,583 Members | 617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,583 IT Pros & Developers. It's quick & easy.

Restructuring Database table in VB.NET 2003 help please

P: n/a
Hi,

Could someone please xplain how to add a field to an existing SQL table in
VB.Net

I have added the field in the Server Explorer and it shows up when I reload
the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he Dataapdtors
or Datasets ?

hanks in advance
Nov 21 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Hi,

I am not sure I understand the question. You can change a
database table using the alter table sql command. Here is some sample
code on how to create a database, table, alter table and stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.MachineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()

CreateDataBase()

CreateClientsTable()

End Sub

Private Sub CreateDataBase()

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"

strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch

MessageBox.Show("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTable()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients" & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumber NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDate DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStoredProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strSql, connVet)

connVet.Open()

cmdUpdate.ExecuteNonQuery()

connVet.Close()

http://msdn.microsoft.com/library/de...aa-az_3ied.asp
Ken

-----------------------------
"David" <da***@orbitcoms.com> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...
Hi,

Could someone please xplain how to add a field to an existing SQL table in
VB.Net

I have added the field in the Server Explorer and it shows up when I reload
the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he Dataapdtors
or Datasets ?

hanks in advance

Nov 21 '05 #2

P: n/a
Ken,

Thanks for the info.

The situation is that I have created and application that uses a SQL
database.
The database initially was created using Microsoft Access.

The SQL database is running on a local instance of MSDE server.

In VB.NET I created the connectivity using the components from the tool box
for a connection
then dataadaptors and datasets.
I load and refresh the datsets and update the dataadapters etc in code.

Now I want to add a new field to one of the tables in the database. I tried
using the Server Explorer and found I could right click the table and go to
design view and add a field.

Though the field shows up in the server explorer, I cannot access it withing
my program.
I am not sure what else must be done once you create the new field to get
your code to "see" it.

As I am new to ADO programming, I would like to be able to alter the
database structure as mentioned above, without needing to write all the
connectivity from scratch.

Thanks for any more comments you may have to assist.
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
Hi,

I am not sure I understand the question. You can change a
database table using the alter table sql command. Here is some sample
code on how to create a database, table, alter table and stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.MachineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()

CreateDataBase()

CreateClientsTable()

End Sub

Private Sub CreateDataBase()

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdatabases Where Name =
'VET')"

strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch

MessageBox.Show("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTable()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients" & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumber NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDate DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStoredProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strSql, connVet)

connVet.Open()

cmdUpdate.ExecuteNonQuery()

connVet.Close()

http://msdn.microsoft.com/library/de...aa-az_3ied.asp
Ken

-----------------------------
"David" <da***@orbitcoms.com> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...
Hi,

Could someone please xplain how to add a field to an existing SQL table in
VB.Net

I have added the field in the Server Explorer and it shows up when I
reload
the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he
Dataapdtors
or Datasets ?

hanks in advance

Nov 21 '05 #3

P: n/a
David,

Ken gave you the answer "Alter", with a lot of code in a sample, how to
handle these things.

He also gave you this string for the syntax.
http://msdn.microsoft.com/library/de...aa-az_3ied.asp

These commands you handle with
Execute.NonQuerry, see for that the sample how to use it that Ken gave.

Ken's sample create and delete and checking of existings of tables using
that.

When you know it, it is very easy.

By the way, there is a big difference between ADO and ADONET

I hope this helps,

Cor
Nov 21 '05 #4

P: n/a
Cor,

Are you saying I can just leave all the connectivity as is and then write
the code to add a column to the table and run Execute.Nonquery to have it
applied to the database. Where do I use Stored procs ?

Then I can remove it from code when the clumn is created ?

After I have created the new cloumn, I will detach the database from the
server so I can copy it to the CD image that is distributed ?

I distribute a copy of the mdf file on CD rom that the user can "Attach" to
their local MSDE
server when they install the program.

What is the Server Explorer doing when it lets you add columns in Design
view ? I imaginged this was doing the same as if I edited the SQL database
in Access.

It seems strange to me to write code to change the structure of the
database. I could understand this if I was writing a Database management
application that I could select new columns or tables at runtime but I do
not undertstand why I would need to program the alterations in code when it
is changing the underlying database structure that will be used in other
programs.

The listviews all currently have databindings via the properties for each
component (no code written). The application does not need to create or
modify the database structure during normal operation. The displays and file
saving are static with regard to waht columns are used.

Hope this makes some kind of sense. As mentioned, I am a database novice and
have used most of the built-in facilities for achieving connectivity instead
of doing it in code. I would like to avoid rewriting heaps of code and
implementing stored procedures that I do not yet understand.

I do understand that if I recreated the database in Access and then removed
and droped down the connection and adaptors etc that I would have access to
the additional column.
"Cor Ligthert" <no************@planet.nl> wrote in message
news:Ov**************@tk2msftngp13.phx.gbl...
David,

Ken gave you the answer "Alter", with a lot of code in a sample, how to
handle these things.

He also gave you this string for the syntax.
http://msdn.microsoft.com/library/de...aa-az_3ied.asp

These commands you handle with
Execute.NonQuerry, see for that the sample how to use it that Ken gave.

Ken's sample create and delete and checking of existings of tables using
that.

When you know it, it is very easy.

By the way, there is a big difference between ADO and ADONET

I hope this helps,

Cor

Nov 21 '05 #5

P: n/a
David,

Adding a column to a database should be a one time operation.

By instance by an update (new release) procedure of your program. It should
be a seperated program or very seperated class in your program, that you
than can change by every release.

This means that you have to add those changes as well if needed to your
Stored procedures and whatever other place.

Adding a column to a database should certainly not be a standard operation.

I hope this helps,

Cor
Nov 21 '05 #6

P: n/a
Hi,

If you added the field in the SQL Table, you only have to Refresh your
DataAdapters using that table. If you look at the SelectCommandText of your
DataAdapters, you can see that it doesn't use the "SELECT *" but "SELECT
fiel1, field2, ...".
So for every change to your table tou have to refresh your dataadapter
(evenso if you change the lenght of a field os stuff like that.
You can do that by right-clickin on your datadapter in Design-mode, and than
chose "Configure Data Adapter..."

I hope this is an answer to your question?

Pieter
"David" <da***@orbitcoms.com> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...
Hi,

Could someone please xplain how to add a field to an existing SQL table in
VB.Net

I have added the field in the Server Explorer and it shows up when I reload the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he Dataapdtors or Datasets ?

hanks in advance

Nov 21 '05 #7

P: n/a
Pieter,

That's exactly what I was hoping for. Thanks.
"DraguVaso" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Hi,

If you added the field in the SQL Table, you only have to Refresh your
DataAdapters using that table. If you look at the SelectCommandText of
your
DataAdapters, you can see that it doesn't use the "SELECT *" but "SELECT
fiel1, field2, ...".
So for every change to your table tou have to refresh your dataadapter
(evenso if you change the lenght of a field os stuff like that.
You can do that by right-clickin on your datadapter in Design-mode, and
than
chose "Configure Data Adapter..."

I hope this is an answer to your question?

Pieter
"David" <da***@orbitcoms.com> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...
Hi,

Could someone please xplain how to add a field to an existing SQL table
in
VB.Net

I have added the field in the Server Explorer and it shows up when I

reload
the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he

Dataapdtors
or Datasets ?

hanks in advance


Nov 21 '05 #8

P: n/a
Can someone provide a SQL string that adds the column "myNewCol" of data type
integer to my datatable named "myTable" that I can use in VB.Net!

--
Dennis in Houston
"David" wrote:
Pieter,

That's exactly what I was hoping for. Thanks.
"DraguVaso" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Hi,

If you added the field in the SQL Table, you only have to Refresh your
DataAdapters using that table. If you look at the SelectCommandText of
your
DataAdapters, you can see that it doesn't use the "SELECT *" but "SELECT
fiel1, field2, ...".
So for every change to your table tou have to refresh your dataadapter
(evenso if you change the lenght of a field os stuff like that.
You can do that by right-clickin on your datadapter in Design-mode, and
than
chose "Configure Data Adapter..."

I hope this is an answer to your question?

Pieter
"David" <da***@orbitcoms.com> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...
Hi,

Could someone please xplain how to add a field to an existing SQL table
in
VB.Net

I have added the field in the Server Explorer and it shows up when I

reload
the program
but I cannot access the field from within my program.

Is there something I need to refresh or do I need to recreate he

Dataapdtors
or Datasets ?

hanks in advance



Nov 21 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.