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
tables using vb.net code and MSacess or SQL server.
Thanks in advance
-
-
Re: ADOX Question
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(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
Ken
-----------------
"Anthony Sox" <triggasox@hotm ail.com> wrote in message
news:eX8Q$p1UFH A.628@tk2msftng p13.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
-
Re: ADOX Question
Anthony,
As far as I know is there only one thing that you cannot do with ADONET,
what you can do with ADOX and that is creating an access database.
Creating tables dynamicly for whatever database you can doe with the SQL
statement "Create table"
You process that with the command.
xxxcommand.exec uteNonQuery(SQL STRING).
I hope this helps,
Cor
Comment
-
Re: ADOX Question
On Sat, 7 May 2005 18:14:40 -0500, "Anthony Sox" <triggasox@hotm ail.com> wrote:
¤ Anyone know the ADOX equvalent in VB.NET or ways of dynamically creating new
¤ tables using vb.net code and MSacess or SQL server.
Here is some additional info with respect to Access Jet SQL:
http://msdn.microsoft.com/library/de...l/acintsql.asp
Paul
~~~~
Microsoft MVP (Visual Basic)
Comment
Comment