Option Strict Off
Option Explicit On
Friend Class frmSQL
Inherits System.Windows.Forms.Form
#Region “Windows Form Designer generated code ”
Public Sub New()
MyBase.New()
If m_vb6FormDefInstance Is Nothing Then
If m_InitializingDefInstance Then
m_vb6FormDefInstance = Me
Else
Try
‘For the start-up form, the first instance created is the default instance.
If System.Reflection.Assembly.GetExecutingAssembly.EntryPoint.DeclaringType Is Me.GetType Then
m_vb6FormDefInstance = Me
End If
Catch
End Try
End If
End If
‘This call is required by the Windows Form Designer.
InitializeComponent()
End Sub
‘Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
If Disposing Then
If Not components Is Nothing Then
components.Dispose()
End If
End If
MyBase.Dispose(Disposing)
End Sub
‘Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
Public ToolTip1 As System.Windows.Forms.ToolTip
Public WithEvents Command2 As System.Windows.Forms.Button
Public WithEvents Command1 As System.Windows.Forms.Button
‘NOTE: The following procedure is required by the Windows Form Designer
‘It can be modified using the Windows Form Designer.
‘Do not modify it using the code editor.
Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmSQL))
Me.components = New System.ComponentModel.Container()
Me.ToolTip1 = New System.Windows.Forms.ToolTip(components)
Me.ToolTip1.Active = True
Me.Command2 = New System.Windows.Forms.Button
Me.Command1 = New System.Windows.Forms.Button
Me.Text = “Work with SQL server”
Me.ClientSize = New System.Drawing.Size(288, 186)
Me.Location = New System.Drawing.Point(4, 23)
Me.StartPosition = System.Windows.Forms.FormStartPosition.WindowsDefaultLocation
Me.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.Control
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows.Forms.Cursors.Default
Me.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.ShowInTaskbar = True
Me.HelpButton = False
Me.WindowState = System.Windows.Forms.FormWindowState.Normal
Me.Name = “frmSQL”
Me.Command2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command2.Text = “Create Table”
Me.Command2.Size = New System.Drawing.Size(89, 33)
Me.Command2.Location = New System.Drawing.Point(136, 72)
Me.Command2.TabIndex = 1
Me.Command2.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Command2.BackColor = System.Drawing.SystemColors.Control
Me.Command2.CausesValidation = True
Me.Command2.Enabled = True
Me.Command2.ForeColor = System.Drawing.SystemColors.ControlText
Me.Command2.Cursor = System.Windows.Forms.Cursors.Default
Me.Command2.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Command2.TabStop = True
Me.Command2.Name = “Command2”
Me.Command1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command1.Text = “Create DB”
Me.Command1.Size = New System.Drawing.Size(89, 33)
Me.Command1.Location = New System.Drawing.Point(40, 72)
Me.Command1.TabIndex = 0
Me.Command1.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Command1.BackColor = System.Drawing.SystemColors.Control
Me.Command1.CausesValidation = True
Me.Command1.Enabled = True
Me.Command1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Command1.Cursor = System.Windows.Forms.Cursors.Default
Me.Command1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Command1.TabStop = True
Me.Command1.Name = “Command1”
Me.Controls.Add(Command2)
Me.Controls.Add(Command1)
End Sub
#End Region
#Region “Upgrade Support ”
Private Shared m_vb6FormDefInstance As frmSQL
Private Shared m_InitializingDefInstance As Boolean
Public Shared Property DefInstance() As frmSQL
Get
If m_vb6FormDefInstance Is Nothing OrElse m_vb6FormDefInstance.IsDisposed Then
m_InitializingDefInstance = True
m_vb6FormDefInstance = New frmSQL()
m_InitializingDefInstance = False
End If
DefInstance = m_vb6FormDefInstance
End Get
Set
m_vb6FormDefInstance = Value
End Set
End Property
#End Region
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As New SQL_BD
Private Sub CreateTavle_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command1.Click
Call db.Create_db(“TESTDB”)
End Sub
Private Sub CreateDB_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command2.Click
Dim conStr, ColamList As String
conStr = “Data Source=DBNAME\DBTABLE;Initial Catalog= mydb;Integrated Security=SSPI;User ID=sa;Password=password;Connection Timeout=60”
ColamList = “AddressId int,adBName varchar(30), adbAddress varchar(80)”
Call db.Create_Table(conStr, “AddressBook”, ColamList)
End Sub
End Class
Create_Table and Create_db class
Option Strict Off
Option Explicit On
Friend Class SQL_BD
Dim con As New ADODB.Connection
”Create Database in SQL Server
Public Function Create_db(ByVal db_name As String) As Object
On Error Resume Next
Dim SQl As String
If con.State = ADODB.ObjectStateEnum.adStateOpen Then con.Close()
‘Connect with Master Database
con.Open((“Data Source=GETAFIX\OPTIMATIX;Initial Catalog= mydb;Integrated Security=SSPI;User ID=sa;Password=womble;Connection Timeout=60”))
SQl = “CREATE DATABASE ” & db_name & ” ” & ” ON” & “( NAME = ” & db_name & “_dat,” & ” FILENAME = ‘c:\program files\microsoft sql server\mssql\data\” & db_name & “dat.mdf’, ” & ” SIZE = 10, ” & ” MAXSIZE = 50,” & ” FILEGROWTH = 5 ) ”
SQl = SQl & ” LOG ON ” & ” ( NAME = ‘” & db_name & “_log’, ” & ” FILENAME = ‘c:\program files\microsoft sql server\mssql\data\” & db_name & “log.ldf’,” & ” SIZE = 5MB,” & ” MAXSIZE = 25MB, ” & ” FILEGROWTH = 5MB )”
con.Execute(SQl)
con.Close()
End Function
”Create table in working database
Public Function Create_Table(ByVal conString As String, ByVal tab_name As String, ByVal table_colams_list As String) As Object
On Error GoTo e
If con.State = ADODB.ObjectStateEnum.adStateOpen Then con.Close()
‘Connect with working Database
con.Open((conString))
Dim SQl As String
SQl = “if exists (select table_name from INFORMATION_SCHEMA.TABLES where table_name='” & tab_name & “‘) ” & ” drop table ” & tab_name
con.Execute(SQl)
SQl = “CREATE table ” & tab_name & “( ” & table_colams_list & ” )”
con.Execute(SQl)
con.Close()
e:
If Err.Number Then
MsgBox(Err.Description, MsgBoxStyle.Critical)
Err.Clear()
End If
End Function
End Class
Thys Michels Blog 