Hi,
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()
Ken
-----------------------------
"Coleen" <co**********@yaho.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
Hi All :-)
Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site
at:
http://msdn.microsoft.com/library/de...es_04_8jtx.asp
which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.
Can any one please help?
Thanks so much :-)
Coleen
