Author : Prabir kumar Das
Date Submitted : 4/17/2006
Category : Database
Compatibility : VB 6
This code has been accessed 5418 times.
Task : This code helps to Administrate Oracle Database through VB by passing DML and DDL Commands.
(From:Prabir Kumar Das (Konnagar,West Bengal))
Contact me at:919331819078
Declarations
Code
Private Sub cer_Click()
Frame5.Visible = True
Label7.Caption = ctr1
Label8.Caption = ctr2
Timer1.Enabled = True
End Sub
Private Sub com_Click()
cmk = "provider=MSDAORA.1;User Id='" + Text2.Text + "'; password='" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo errorhandler:
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
Set cmd = New ADODB.Command
cmd.CommandText = "commit"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = ck
cmd.Execute
MsgBox "Commit Succeeded", vbInformation
Exit Sub
errorhandler:
MsgBox err.Description, vbCritical
End Sub
Private Sub Combo1_click()
cmk = "provider=MSDAORA.1;User Id= '" + Text2.Text + "'; password= '" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo errorhandler:
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
'sqk = "select tabtype from tab where tname = '" + Combo1.Text + "'"
sqk = "select tabtype from tab"
Set rk = New ADODB.Recordset
With rk
.Open sqk, ck, adOpenForwardOnly, adLockReadOnly
'Text4.Text = rk("tabtype")
Text1.Text = rk
.Close
End With
Set rk = Nothing
ck.Close
Set ck = Nothing
Exit Sub
errorhandler:
MsgBox err.Description, vbCritical
End Sub
Private Sub cm4_Click()
cmk = "provider=MSDAORA.1;User Id='" + Text2.Text + "'; password='" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo errorhandler:
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
Set cmd = New ADODB.Command
cmd.CommandText = "" + Text1.Text + ""
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = ck
cmd.Execute
ctr1 = ctr1 + 1
MsgBox "Job Completed Successfully", vbInformation
MsgBox "Total command(s) executed succesfully.", vbInformation
Exit Sub
errorhandler:
ctr2 = ctr2 + 1
comd = Text1.Text
List2.AddItem comd
Label9.Caption = List2.ListCount
MsgBox "Error in executing command.", vbExclamation
End Sub
Private Sub cm1_Click()
cmk = "provider=MSDAORA.1;User Id='" + Text2.Text + "'; password='" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo errorhandler:
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
With OraEditor
.Caption = "ORACLE EDITOR. - (" + Text2.Text + ")"
End With
Exit Sub
errorhandler:
MsgBox err.Description, vbCritical
End Sub
Private Sub cm5_Click()
Text1.Text = ""
Text1.SetFocus
End Sub
Private Sub cm3_Click()
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text2.Enabled = True
Text3.Enabled = True
Combo1.Clear
List1.Clear
MSFlexGrid1.Clear
Label3.Caption = ""
Text2.SetFocus
OraEditor.Caption = "ORACLE EDITOR."
End Sub
Private Sub cm2_Click()
cmk = "provider=MSDAORA.1;user id ='" + Text2.Text + "'; password= '" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo errorhandler:
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
sqk = "select * from tab"
Set rk = New ADODB.Recordset
With rk
.Open sqk, ck, adOpenForwardOnly, adLockReadOnly
Do While Not rk.EOF
Combo1.AddItem rk("tname")
List1.AddItem rk("tname")
rk.MoveNext
Loop
.Close
End With
Label3.Caption = Combo1.ListCount
Set rk = Nothing
ck.Close
Set ck = Nothing
cm2.Enabled = False
Exit Sub
errorhandler:
MsgBox err.Description, vbCritical
End Sub
Private Sub Form_Load()
ctr1 = 0
ctr2 = 0
cm1.Enabled = False
cm2.Enabled = False
Text4.Enabled = False
com.Enabled = False
MSFlexGrid1.ColWidth(0) = 1800
MSFlexGrid1.ColWidth(1) = 1800
End Sub
Private Sub hlp_Click()
help.Show , OraEditor
End Sub
Private Sub List1_Click()
cmk = "provider=MSDAORA.1;user id ='" + Text2.Text + "'; password= '" + Text3.Text + "';"
cmk = cmk & "Persist Security Info=False"
On Error GoTo ss
Set ck = New ADODB.Connection
With ck
.ConnectionString = cmk
.Open
End With
Set rk = ck.Execute("select * from " & List1.Text & "")
DTYPES
Exit Sub
ss:
MsgBox err.Description, vbCritical
End Sub
Private Sub List2_Click()
Text1.Text = List2.Text
ind = List2.ListIndex
List2.RemoveItem ind
Label9.Caption = List2.ListCount
End Sub
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 112 Then
Text1.Top = 0
Text1.Left = 0
Text1.Height = 10815
Text1.Width = 15255
ElseIf KeyCode = 113 Then
Text1.Top = 0
Text1.Left = 720
Text1.Height = 10815
Text1.Width = 15255
ElseIf KeyCode = 27 Then
Text1.Top = 3000
Text1.Left = 240
Text1.Height = 4305
Text1.Width = 7155
End If
End Sub
Private Sub text2_lostfocus()
Text2.Enabled = False
End Sub
Private Sub text3_lostfocus()
Text3.Enabled = False
End Sub
Private Sub Text3_Change()
cm1.Enabled = (Len(Text3.Text) > 0)
cm2.Enabled = (Len(Text3.Text) > 0)
com.Enabled = (Len(Text3.Text) > 0)
End Sub
Public Function DTYPES()
Dim a As String
counter2 = 1
MSFlexGrid1.Clear
MSFlexGrid1.Cols = 2
MSFlexGrid1.TextMatrix(0, 0) = "FIELD NAME"
MSFlexGrid1.TextMatrix(0, 1) = "TYPES"
MSFlexGrid1.Rows = 2
counter1 = 0
qqq:
On Error GoTo ppp:
MSFlexGrid1.TextMatrix(counter2, 0) = rk.Fields(counter1).Name
a = rk.Fields(counter1).Type
Select Case a
Case "131": dt = "Number"
dt = dt & "(" & rk.Fields(counter1).Precision & ")"
Case "129": dt = "Char"
dt = dt & "(" & rk.Fields(counter1).DefinedSize & ")"
Case "135": dt = "Date"
Case "200": dt = "Varchar"
dt = dt & "(" & rk.Fields(counter1).DefinedSize & ")"
Case "201": dt = "Long"
Case "204": dt = "Raw"
dt = dt & "(" & rk.Fields(counter1).DefinedSize & ")"
Case "205": dt = "Long Raw"
End Select
MSFlexGrid1.TextMatrix(counter2, 1) = dt
counter1 = counter1 + 1
counter2 = counter2 + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
GoTo qqq
ppp:
MsgBox err.Description, vbCritical
End Function
Private Sub Form_Activate()
MDIForm1.StatusBar1.Panels(2).Text = "Oracle Editor"
End Sub
Private Sub Form_Deactivate()
MDIForm1.StatusBar1.Panels(2).Text = ""
End Sub
Private Sub Form_Unload(Cancel As Integer)
MDIForm1.StatusBar1.Panels(2).Text = ""
End Sub
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 27 Then
Unload Me
End If
End Sub
Private Sub Timer1_Timer()
Frame5.Visible = False
Timer1.Enabled = False
End Sub