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

ADOX Question

P: n/a
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

Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Nov 21 '05 #2

P: n/a
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.executeNonQuery(SQLSTRING).

I hope this helps,

Cor
Nov 21 '05 #3

P: n/a
On Sat, 7 May 2005 18:14:40 -0500, "Anthony Sox" <tr*******@hotmail.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)
Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.