[VB6] CRUD

Deskripsi
     VB6 agar dapet melakukan Create , Read , Update , Delete Record.

How To
1. Add References
2. Create Variable Global untuk Connection dan Recordset
3. Create Connection to Database (Use UDL File or use Connection String)
4. Query (Insert, Retrieve , Update).

*Add Refferences
  Project>Refferences>Microsoft Active X Data Object 2.7 Library
  or you can add manual in path :
  C:\Program Files\Common Files\system\ado\msado.tlb

*Create Variable Global for connection and recordset (optional) in MODULE

   
Public ConnectSQLServer As ADODB.Connection  'connection SQL Server
Public ConnectORAServer As ADODB.Connection  'connection ORA Server
Public RSSQLSERVER ' for recordset untuk aplikasi - sql server
Public RSORASERVER ' for recordset untuk aplikasi - oracle

* Create UDL File
   untuk membuat UDL file , cukup buat file text kemudian di rename extensinya dari .txt menjadi .udl, kemudian klik 2x file UDL tersebut , pilih provider yang menggunakan OLE DB (SQL Server / Oracle / Access) yang akan digunakan.
  pilih tab connection kemudian isi data-datanya dan checklist allow saving password.

* Create Connection to Database

  
Public Function ConnectDatabaseSQLServer()

' Fungsi untuk melakukan koneksi ke database SQL server

On Error GoTo LocalErr

    Set ConnectSQLServer = New ADODB.Connection

    ConnectSQLServer.Open "FILE NAME=" & App.Path & "\SQLServer.udl"

    Exit Function

  

LocalErr:

    Screen.MousePointer = vbDefault

    If Err.Number = -2147467259 Then

        MsgBox "Connection failed, please check data link properties in path SQLServer.udl !," & Chr(13) & _

        "Or SQL Server not running. Open SQL Server service manager, server name and services must correct.", vbCritical, "Error Connection"

        Exit Function

    Else

        MsgBox Err.Number & Chr(13) & Err.Description, vbCritical + vbOKOnly, "Error Connection"

    End If

End Function

  
' Fungsi untuk melakukan koneksi ke database oracle
Public Function ConnectDatabaseORAServer()
On Error GoTo LocalErr

    Set ConnectORAServer = New ADODB.Connection

    ConnectORAServer.Open "FILE NAME=" & App.Path & "\ORAServer.udl"

Exit Function

  

LocalErr:

    Screen.MousePointer = vbDefault

    If Err.Number = -2147467259 Then

        MsgBox "Connection failed, please check data link properties in path ORAServer.udl !," & Chr(13) & _

        "Or ORACLE Server not running. Open Oracle Server service manager, server name and services must correct.", vbCritical, "Error Connection"

        Exit Function
    Else
        MsgBox Err.Number & Chr(13) & Err.Description, vbCritical + vbOKOnly, "Error Connection"

    End If

End Function

Query (Insert)

  
public function insertDataSQLSERVER()
  ' Insert no need recordset to accomodate data record, because no data to retrieve
   Dim strsql As String

   strsql = "insert table (blah1,blah2) values ('A','B')"
      

   ConnectSQLServer.BeginTrans
   ConnectSQLServer.Execute strsql
   ConnectSQLServer.CommitTrans
end function

Query (Retrieve)

  
public function retrieveDataSQLSERVER()
   Dim strsql As String
   Dim RSSQLSERVER As ADODB.Recordset
  
   ' Create Object Recordset 
   Set RSSQLSERVER = New ADODB.Recordset
   RSSQLSERVER .CursorLocation = adUseClient

   strsql = "select * From table where blah1 = 'A'"
   ' Check Status Record set , already Open or Still Close
   If RSSQLSERVER .State <> adStateClosed Then RSSQLSERVER .Close
   'FILL Recordset with data from Database 
   RSSQLSERVER .Open strsql, ConnectSQLServer, adOpenKeyset, adLockOptimistic
   'set recordset to grid

   Set yourgrid.DataSource = RSSQLSERVER

   'Check Record Count
   if RSSQLSERVER.recordcount > 0 then
         msgbox "total record : " & RSSQLSERVER .recordcount
         'sample test retrive some record
         ' syntax format :  ActiveRecordset!Field
         msgbox RSSQLSERVER !blah1
   else
         msgbox "No Record"
   endif

end function

Query (Update)

  
  
public function insertDataSQLSERVER()
 ' Update no need recordset to accomodate data record, because no data to retrieve
   Dim strsql As String

   strsql = "update table set blah1='C' where blah2='B' "
      
   ConnectSQLServer.BeginTrans
   ConnectSQLServer.Execute strsql
   ConnectSQLServer.CommitTrans
end function

Note :
   Semua query berlaku untuk semua database dari Access , SQL SERVER dan Oracle yang terpenting hanya pada koneksi Provider Databasenya (Use UDL File or Connection String).

* Terminate Connection or Recordset
   simple ! just add method .close
   for example (based on tutorial above and your variable) :
   ConnectSQLServer.close // closing connection
   and
   RSSqlServer.close // closing recordset

   don't forget your variable must be already as an object !