form1.frm

This is oracle connection test program.

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   4725
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   6975
   LinkTopic       =   "Form1"
   ScaleHeight     =   4725
   ScaleWidth      =   6975
   StartUpPosition =   3  'Windows 扁夯蔼
   Begin VB.CommandButton Command1 
      Caption         =   "QUERY"
      Height          =   495
      Left            =   5520
      TabIndex        =   6
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton SELECT_CNT 
      Caption         =   "SELECT CNT"
      Height          =   495
      Left            =   4200
      TabIndex        =   5
      Top             =   240
      Width           =   1215
   End
   Begin VB.ListBox List1 
      Height          =   2760
      Left            =   240
      TabIndex        =   4
      Top             =   1680
      Width           =   6495
   End
   Begin VB.TextBox Text1 
      Height          =   375
      Left            =   240
      TabIndex        =   3
      Top             =   960
      Width           =   6495
   End
   Begin VB.CommandButton SELECT 
      Caption         =   "SELECT"
      Height          =   495
      Left            =   2880
      TabIndex        =   2
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton INSERT 
      Caption         =   "INSERT"
      Height          =   495
      Left            =   1560
      TabIndex        =   1
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton OPEN 
      Caption         =   "OPEN"
      Height          =   495
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   1215
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private adoCnn As ADODB.Connection  'ADO狼 Connection 俺眉
Dim sql As String
Dim Rs As ADODB.Recordset

'OPEN
Private Sub OPEN_Click()
    OpenDataBase
End Sub

'ORACLE俊 ADO狼 Connection俺眉甫 楷搬矫虐绰 窃荐
Private Function OpenDataBase() As Boolean
    Dim strCnn As String

    OpenDataBase = True

    On Error GoTo ErrorHandler

    '楷搬 巩磊凯阑 累己茄促.
    strCnn = "driver={Microsoft ODBC for Oracle};" & _
             "UID=scott;PWD=tiger;" & _
             "CONNECTSTRING=OCPDBA;"
                 
    Set adoCnn = New ADODB.Connection
                        
    adoCnn.CursorLocation = adUseClient
    adoCnn.CommandTimeout = 7
    adoCnn.OPEN strCnn
    List1.AddItem "叼厚 立加 己傍"
    Exit Function

ErrorHandler:
    MsgBox adoCnn.State
    'MsgBox "[" & Err.Number & "] - [" & Err.Description & "]"
    Err.Clear
    OpenDataBase = False
    MsgBox "叼厚立加 角菩"
    List1.AddItem "叼厚 立加 角菩"
End Function

'INSERT
Private Sub INSERT_Click()
    On Error GoTo ErrorHandle
    
    OpenDataBase

    sql = "insert into HT_ROOMLOCKER (RSRESNO,LOISNO,LOTYPE,RNCODE,LOISNOTIME,LODEPDATE,LOISDATE)values (22,6,'3','1312',5,'20061010101010','20061010101010')"
    Text1.Text = sql
    'Exit Sub
    adoCnn.Execute sql
    adoCnn.Close
    
ErrorHandle:
    MsgBox Err.Description
    Err.Clear
End Sub

Private Sub SELECT_Click()
    OpenDataBase
    sql = "SELECT MAX(LOISNO)+1 as cnt  FROM HT_ROOMLOCKER where lotype='1'"
    Text1.Text = sql
    Set Rs = New ADODB.Recordset

    Rs.OPEN sql, adoCnn, adOpenStatic, adLockBatchOptimistic
    
    'List
    List1.AddItem "饭内靛 墨款磐 : " & Rs.RecordCount
    
    If Not (Rs.BOF And Rs.EOF) Then
        While (Not Rs.EOF)
            MsgBox Rs.Fields(0)
            Rs.MoveNext
        Wend
        Set Rs = Nothing
        adoCnn.Close
        Set adoCnn = Nothing
    Else
        Set Rs = Nothing
        adoCnn.Close
        Set adoCnn = Nothing
        MsgBox "八祸等 荤恩捞 绝嚼聪促."
    End If
End Sub

Private Sub SELECT_CNT_Click()
    OpenDataBase
    sql = "SELECT * FROM HT_ROOMLOCKER where LOTYPE='1'"
    Set Rs = New ADODB.Recordset

    Rs.OPEN sql, adoCnn, adOpenStatic, adLockBatchOptimistic

    'Count
    MsgBox Rs.RecordCount
    If Rs.RecordCount > 0 Then
        Set Rs = Nothing
        adoCnn.Close
        Set adoCnn = Nothing
        Exit Sub
    End If
End Sub

Private Sub Command1_Click()
    OpenDataBase
    sql = Text1.Text
    Set Rs = New ADODB.Recordset
    Rs.OPEN sql, adoCnn, adOpenStatic, adLockOptimistic
    MsgBox Rs.RecordCount
    
     If Not (Rs.BOF And Rs.EOF) Then
        While (Not Rs.EOF)
            List1.AddItem Rs!LOISNO
            Rs.MoveNext
        Wend
        Set Rs = Nothing
        adoCnn.Close
        Set adoCnn = Nothing
    End If
End Sub