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(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim strConn As String
strConn = "Server = " & Environment.Mac hineName
strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"
conn = New SqlConnection(s trConn)
conn.Open()
CreateDataBase( )
CreateClientsTa ble()
End Sub
Private Sub CreateDataBase( )
Dim strSQL As String
strSQL = "if Exists (Select * From master..sysdata bases Where Name = 'VET')"
strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"
Dim cmd As New SqlCommand(strS QL, conn)
cmd.CommandType = CommandType.Tex t
Try
cmd.ExecuteNonQ uery()
Catch
MessageBox.Show ("Error Creating DB")
Finally
cmd.Dispose()
End Try
End Sub
Private Sub CreateClientsTa ble()
Me.Text = "Creating Clients Table..."
Dim strSQL As String = _
"USE VET" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM VET.dbo.sysobje cts " & _
"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," & _
"PhoneNumbe r NVarChar(20) NOT NULL," & _
"WorkNumber NVarChar(20)," & _
"CellNumber NVarChar(20)," & _
"Email NVarChar(50) NOT NULL," & _
"Balance Money NOT NULL," & _
"BalanceDat e DateTime NOT NULL," & _
"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _
"(ID))"
Dim cmd As New SqlCommand(strS QL, conn)
cmd.CommandType = CommandType.Tex t
Try
cmd.ExecuteNonQ uery()
Catch ex As SqlException
MessageBox.Show (ex.ToString, "Clients")
Finally
cmd.Dispose()
End Try
End Sub
Private Sub MakeClientStore dProcedure()
Dim strSQL As String = _
"USE VET" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM VET.dbo.sysobje cts " & _
"WHERE Name = 'ClientInfo' " & _
"AND TYPE = 'p')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP PROCEDURE ClientInfo" & vbCrLf & _
"END"
Dim cmd As New SqlCommand(strS QL, conn)
cmd.CommandType = CommandType.Tex t
Try
cmd.ExecuteNonQ uery()
cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _
"@ClientID int " & vbCrLf & _
"AS Select * " & vbCrLf & _
"FROM VET.dbo.Clients Where ID = @ClientID"
cmd.ExecuteNonQ uery()
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(strS ql, connVet)
connVet.Open()
cmdUpdate.Execu teNonQuery()
connVet.Close()
http://msdn.microsoft.com/library/de...aa-az_3ied.asp
Ken
-----------------------------
"David" <da***@orbitcom s.com> wrote in message
news:ux******** ******@TK2MSFTN GP12.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
