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
9  8236 
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.OLED B.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" <zl*****@sina.com>
... 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
Cor,
Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?
"Cor Ligthert" <no**********@planet.nl>
write:OM**************@TK2MSFTNGP11.phx.gbl... 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.OLED B.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" <zl*****@sina.com>
.. 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
> Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?
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
-----------------
"Peter" <zl*****@sina.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...
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 [MVP]" <vb***@bellsouth.net> 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
-----------------
"Peter" <zl*****@sina.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...
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
Hi,
Good idea.
Ken
----------------
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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 [MVP]" <vb***@bellsouth.net> 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
-----------------
"Peter" <zl*****@sina.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...
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
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" <no**********@planet.nl> wrote in message
news:OD**************@TK2MSFTNGP09.phx.gbl...
Why can I not install(in a simple way) a SQLserver in VBNet ? I have
installed MSDE2000,is it enough?
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
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
Hi,
No i havent
Ken
-------------
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:Oq**************@TK2MSFTNGP10.phx.gbl...
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lkrubner |
last post by:
I have a webserver through Rackspace. I create a domain. I create an
FTP user. I upload some files. I create a database called
testOfSetupScript and then I create a database user named setup.
I...
|
by: Michael Lauzon |
last post by:
This is not for a class, I have a group on SourceForge, this is what
one of the Developers is asking; the more advanced you can make it
right off all the better!:
Can someone please create...
|
by: intl04 |
last post by:
How do I create a data input form in Access that is external to the
Access database to which it's connected (if that's possible, which I
believe it is)? For example, if someone clicks on an Access...
|
by: G rumpy O ld D uffer |
last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.
I've been given 30+ (and counting) separate 'Weekly' Databases which
all...
|
by: teedilo |
last post by:
Our MS SQL (SQL Server 2000) DBA has database privileges locked down
pretty tightly. We end users/developers do not have administrator
privileges for most databases. That arrangement has worked...
|
by: max |
last post by:
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB...
|
by: etuncer |
last post by:
Hello All,
I have Access 2003, and am trying to build a database for my small
company. I want to be able to create a word document based on the data
entered through a form. the real question is...
|
by: lxyone |
last post by:
Using a flat file containing table names, fields, values whats the
best way of creating html pages?
I want control over the html pages ie
1. layout
2. what data to show
3. what controls to...
|
by: DeanL |
last post by:
Hi guys,
Does anyone know of a way to create multiple tables using information
stored in one table? I have a table with 4 columns (TableName,
ColumnName, DataType, DataSize) and wanted to know...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
