• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This code helps to Administrate Oracle Database through VB by passing DML and DDL Commands. (From:P

This code helps to Administrate Oracle Database through VB by passing DML and DDL Commands. (From:P

This code helps to Administrate Oracle Database through VB by passing DML and DDL Commands. (From:Prabir Kumar Das (Konnagar,West Bengal))

API Declarations


'Programmed by: Prabir Das Contact Me at: [email protected]

Option Explicit
Dim cmk As String
Dim sqk, comd As String
Dim ck As New ADODB.Connection
Dim cmd As ADODB.Command
Dim rk As ADODB.Recordset
Dim counter1 As Integer
Dim counter2 As Integer
Dim dt As String
Dim ctr1, ctr2, ind As Integer



Rate This code helps to Administrate Oracle Database through VB by passing DML and DDL Commands. (From:P

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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