Hi,
You can still use adox with vb.net. Here is some sample code on how
to create a database, table and stored procedure with an command.
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
Ken
-----------------
"Anthony Sox" <tr*******@hotmail.com> wrote in message
news:eX*************@tk2msftngp13.phx.gbl...
Anyone know the ADOX equvalent in VB.NET or ways of dynamically creating new
tables using vb.net code and MSacess or SQL server.
Thanks in advance
