Create a SQL Database or Tables in SQL Database using VB.net

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hello£¬everyone,

My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the records in the database.

Is it possible to create a SQL Server database using vb.net? I know I can
use vb.net and ADOX to create a Access database. But I can't create SQL
database using vb.net.

Thanks in advance ,

Peter
 
Peter,

You can create an SQL database in VBNet, you cannot install(in a simple way)
a SQLserver in VBNet.

You can create an Access database including the file, here beneath is a
sample that I once made.

It is almost the same for both types, however for SQL you do not need that
AdoDb part and need another connectionString for which I give you some links
at the bottom, while it is better therefore to change for that everywhere
OleDb.OleDB for SQLClient.SQL. As well you need to create first for
SQLserver the database using a SQL statement like this. (In you connection
string have to leave the database name empty and or close and open the
connection again with a complete connectionstring or use the USE
SQLstatement.)

Dim strSQL As String = "CREATE DATABASE HKW"

The accessdatabase sample partially you can use it for SQL server
\\set a reference to COM adox ext 2.x for dll and security to use AdoDB for
creation
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db1.mdb")
'End of the AdoDB part
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
cmd = New OleDb.OleDbCommand("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
End Sub
End Class
///
http://www.connectionstrings.com/

http://www.able-consulting.com/ADO_Conn.htm

I hope this helps?

Cor
 
Cor,

Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?


Cor Ligthert said:
Peter,

You can create an SQL database in VBNet, you cannot install(in a simple way)
a SQLserver in VBNet.

You can create an Access database including the file, here beneath is a
sample that I once made.

It is almost the same for both types, however for SQL you do not need that
AdoDb part and need another connectionString for which I give you some links
at the bottom, while it is better therefore to change for that everywhere
OleDb.OleDB for SQLClient.SQL. As well you need to create first for
SQLserver the database using a SQL statement like this. (In you connection
string have to leave the database name empty and or close and open the
connection again with a complete connectionstring or use the USE
SQLstatement.)

Dim strSQL As String = "CREATE DATABASE HKW"

The accessdatabase sample partially you can use it for SQL server
\\set a reference to COM adox ext 2.x for dll and security to use AdoDB for
creation
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db1.mdb")
'End of the AdoDB part
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
cmd = New OleDb.OleDbCommand("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
End Sub
End Class
///
http://www.connectionstrings.com/

http://www.able-consulting.com/ADO_Conn.htm

I hope this helps?

Cor


Peter said:
Hello£¬everyone,

My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the records in the database.

Is it possible to create a SQL Server database using vb.net? I know I can
use vb.net and ADOX to create a Access database. But I can't create SQL
database using vb.net.

Thanks in advance ,

Peter
Click to expand...
Click to expand...
 
Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?
Click to expand...
Yes when it is the latest than it works almost the same as SQLserver with a
lot of limitation and withouth a GUI.

Cor.
 
Hi,

Here is some sample code on how to create a database, 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



Ken

-----------------

Hello£¬everyone,

My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the records in the database.

Is it possible to create a SQL Server database using vb.net? I know I can
use vb.net and ADOX to create a Access database. But I can't create SQL
database using vb.net.

Thanks in advance ,

Peter
 
Ken,

I would place in/before that "If exist section" a nice messagebox before the
table is dropped. You never know what people do when testing, maybe they
have somewhere such a table.

Just a thought

:-)

Cor

Ken Tucker said:
Hi,

Here is some sample code on how to create a database, 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



Ken

-----------------

Hello£¬everyone,

My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the records in the database.

Is it possible to create a SQL Server database using vb.net? I know I can
use vb.net and ADOX to create a Access database. But I can't create SQL
database using vb.net.

Thanks in advance ,

Peter
Click to expand...
 
Hi,

Good idea.

Ken
----------------
Ken,

I would place in/before that "If exist section" a nice messagebox before the
table is dropped. You never know what people do when testing, maybe they
have somewhere such a table.

Just a thought

:-)

Cor

Ken Tucker said:
Hi,

Here is some sample code on how to create a database, 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



Ken

-----------------

Hello£¬everyone,

My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the records in the database.

Is it possible to create a SQL Server database using vb.net? I know I can
use vb.net and ADOX to create a Access database. But I can't create SQL
database using vb.net.

Thanks in advance ,

Peter
Click to expand...
 
Hi,

Please note that you can use the vs.net server explorer provides a
gui for sql server. You can create databases, tables, stored procedures,
etc

Ken
----------------
Cor Ligthert said:
Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?
Click to expand...
Yes when it is the latest than it works almost the same as SQLserver with a
lot of limitation and withouth a GUI.

Cor.
 
Ken,

I like it more to create those in my program, that makes me independend from
any installer or whatever tool.

By the way I never succeeded in removing a database using the VS.net server
explorer, have you an idea what I probably do wrong?

Cor
 
Hi,

No i havent

Ken
-------------
Ken,

I like it more to create those in my program, that makes me independend from
any installer or whatever tool.

By the way I never succeeded in removing a database using the VS.net server
explorer, have you an idea what I probably do wrong?

Cor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vb.net Ms Access Database 2007 Reusable Procedures/queries/functions 5
installing SQL 2008 express and database with my vb app 2
How to call SQL Function from vb.net 2
Sync Database in VB.Net 1
Installing a database in SQL Express during VB app setup 2
Tutorial for Beginner 1
Run a vb net procedure when sql table insert is triggered 2
Best Stand Alone Database For Laptop in VB.Net? 5

Back
Top