View Code Snippet

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