28 November 2008

Koneksi dengan database

Untuk dapat mengakses suatu database dari visual basic, Anda harus membuat koneksi ke database tersebut. Visual basic dapat menangani berbagai database, teks bakan worksheet excel. Terdapat beberapa cara yang tersedia untuk itu sesuai dengan database yang digunakan

Cara 1. Menggunakan Object ADO

Anda dapat menggunakan icon Adodc (Microsoft ActiveX Data Objects Data Control) pada toolbox. Jika pada toolbox tidak terdapat icon Adodc, Anda dapat menambahkannya dengan memilih menu Project, Components (Ctrl+T) lalu beri tanda pilih pada Microsoft ADO Data Control (OLEDB) dan klik Apply. Sekarang kita akan mencoba mengaitkan database Ms. Acess yang diberi nama Pembelian.mdb. Untuk itu, lakukan langkah-langkah sebagai berikut:

  1. Pilih menu File lalu New.
  2. Pilih VB Enterprise (Professional) Edition Controls.
  3. Klik OK.
  4. Klik ganda pada folder Forms pada jendela proyek lalu klik ganda pada Form 1 sampai muncul tampilan form.
  5. Klik icon Adodc pada toolbox.
  6. Klik dan seret pada jendela form, sehingga muncul tampilan seperti Gb. Objek yang tergambar dalam form disebut objek data.
  7. Tentukan properti data sebagai berikut:
    1. Name db Pembelian
    2. Caption Data Barang
    3. EOFAcction 2
  8. Tentukan properti ConnectionString dengan cara mengklik tombol … pada kolom ConnectionString sehingga muncul tam pilan seperti pada Gb
  9. Pilih OptionButton pada Use Connection String lalu klik Build sehingga muncul tampilan seperti pada Gb
  10. Pilih Microsoft Jet 4.0 OLEDB Provider lalu klik Next.
  11. Pilih (dengan mengklik tombol …) atau ketik nama database yang dipakai pada kolom Select or enter a database name.
  12. Klik Test Connection jika perlu, lalu kllk OK dua kali sampai tampilan kembali ke jendela Form.
  13. Tentukan properti RecordSource dengan cara mengklik tombol pada kolom RecordSource sehingga muncul tampilan seperti pada Gb
  14. Pada kolom Command Type pilih 2-adCmdTable. Setelah kolom ini diisi, maka Anda akan dapat mengisi kolom Table or Stored Procedure Name. lsilah dengan memilib tabel Barang seperti pada Gb
  15. Klik OK.
  16. Simpan form dan proyek dengan nama VB-Lat-01

Cara 2: Mendeklarasikan Objek Ado

Object ado dapat dideklarasi melalui coding. Tiga object bawaan ADO dalam pengembangan aplikasi adalah :

1. Obcet Connection

untuk menangani koneksi antara program dengan sumber data atau server. Deklarasi dapat bersifat lokal maupun global serta dapat diletakan pada level form, modul atau class modul. ADO paling umum membuka suatu koneksi dengan menggunakan obyek Connection Open metoda. Sintaks untuk Metoda Open ditunjukkan sbb:

Dim connection as New ADODB.Connection

connection.Open ConnectionString, UserID, Password, OpenOptions

Sebagai alternatif, anda dapat menggunakan teknik pintas, recordset.Open, untuk membuka koneksi yang tersembunyi dan mengeluarkan suatu perintah atas koneksi dalam satu operasi. Lakukan hal ini dengan mengaktivkan suatu koneksi yang valid seperti argumentasi ActiveConnection ke Metoda Open. Ini adalah sintaksis untuk masing-masing metoda di Visual Basic:

Dim recordset as ADODB.Recordset Set

recordset = New ADODB.Recordset

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Contoh :

Deklarasi object Connection:

Dim cn As New ADODB.Connection

atau

Public cn As New ADODB.connection

Selanjutnya mengalokasikan memori untuk onject connection sbb:

Set cn = New ADODB.Connection

Kemudian isi property object connoction tersebut sbb:

Contoh berikut adalah property untuk MSSQL Server

cn.connectionString = “Perovider=SQLOLEDB;” & _

”Server=NamaServer; ” & _

“Initial catalog= inventory” & _

“user Id = zulidamel” _

“Password=terserah”

cn.Open

atau

koneksi = “Perovider=SQLOLEDB;” & _

”Server=NamaServer; ” & _

“Initial catalog= inventory” & _

“user Id = zulidamel” _

“Password=terserah”

cn.Open koneksi

Jika tidak ingin menggunak authentik pada proses koneksi gunakan cara berikut:

koneksi = “Provider=SQLOLEDB.1;” & _

“Integrated Security Info=False;” & _

“Data Source = NamaServer” cn.Open koneksiBila koneksi sudah tidak diperlukan tutuplah koneksi dengan perintah

”Set cn = Nothing”

2. Object Command

Digunakan untuk perintah SQL ke sumber data atau server. Deklarasi object command sbb:

Dim CmdBarang As New ADODB.Command

atau

Public Cmd as New ADODB.Command

selanjutnya alokasikan memori untuk object Command sbb:

Set CmdBarang = New ADODB.Command

Kemudian Isi property object command

CmdBarang.ActiveConection = cn

CmdBarang.CommandText = “select * from barang”

CmdBarang.CommandType = adCmdText

3. Object Recordset

Digunakan sebagao penampung hasil eksekusi dalam bentuk tabel. Deklarasi object record sbb:

Dim rsBarang As New ADODB.Recordset

atau

Public rsBarang As New ADODB.Recordset

selanjutnya alokasikan memori ynryk object recordset sbb:

Set rsBarang = New ADODB.recordset

lalu isikan property object record set

rsBarang.CusrsorTYpe = adOpenDynamic

rsBarang.LockType = adLockOptimistic

rsbarang.Open cmdBarang

atau

rsbarang,open CmdBarang,, adOpenDynamic, adlockOptimistic, adCmdtext

Data Control

Visual Basic terus berkembang dalam pengembangan general purpose Windows, tetapi kemampuan database tidaklah bagian murni dari desainnya. Data Control, Visual Basic menambahkan, membuat Visual Basic suatu alat yang jauh lebih tangguh untuk pembuatan pemrograman database.

data-control.jpgData Control memberikan kemudahkan untuk mendapat data ke dalam program anda. Anda dapat dengan mudah mengaitkan suatu file database, lalu mengaitkan data lainnya untuk memindahkan data dari database ke aplikasi anda dan sebaliknya. Data Control pada suatu database dan menetapkan tabel mana yang akan diambil, lalu mengaitkan ke pengendali Data Control, menetapkan field yang akan ditampilkan. Pada waktu itu aplikasi anda dapat mengambil, menampilkan, memperbaharui dan membuat database merekam. Alir data di dalam skenario ini ditandai pada gambar.

Menulis dan membaca dari file Text

Hampir setiap pemrograman yang saya lakukan, memerlukan perintah untuk membaca dan menulis File text.
Sebagai tambahan Informasi, kali ini saya akan menampilkan syntax bagaimana caranya membaca dan menulis file text melalui bahasa pemrograman Visual Basic.
Berikut List Syntaxnya …

1. Menulis Ke File Text

Dim LocTextFile As String
LocTextFile = “C:\windows\SvAdd.txt”

Open LocTextFile For Output As #1 ‘Buka file text
Print #1, “Server_Name” ‘Modify Baris Pertama”
Print #1, “Database_Name” ‘Modify Baris Kedua
Print #1, “User” ‘Modify Baris Ketiga
Print #1, “Pwd” ‘Modify Baris Keempat
Close #1

2. Membaca dari File Text

Dim LocTextFile As String
Dim ServerAddress As String
Dim DatabaseName As String
Dim UserID As String
Dim PwdSql As String
LocTextFile = “C:\windows\SvAdd.txt”
Open LocTextFile For Input As #1 ‘Buka file text
Line Input #1, ServerAddress ‘Baca Baris Pertama
Line Input #1, DatabaseName ‘Baca Baris Kedua
Line Input #1, UserID ‘Baca Baris Ketiga
Line Input #1, PwdSql ‘Baca Baris Keempat
Close #1 ‘Tutup File file text


Membuat dan menghapus folder melalui VB

Posting berikut akan memberikan cara untuk membuat dan menghapus suatu folder menggunakan program Visual Basic.
Tips ini sangat membantu kita untuk menyimpan file dari hasil suatu proses.
Berikut kumpulan Syntaxknya :

Pada saat akan membuat direktori baru, kita harus pastikan bahwa folder
tersebut benar - benar baru (tidak ada sebelumnya).

Menghapus Suatu Folder :

RmDir (”c:\FolderTest”)

2. Sebelum menjalankan hapus Folder, pastikan isi dari folder tersebut sudah Kosong,
Cara untuk menghapus isi Dari folder tersebut :

Kill “c:\FolderTest\*.*”

3. Membuat Direktory Baru :

MkDir (”c:\FolderTest”)

Selamat Mencoba !!

Menghubungkan VB dengan semua database

1. SQL Server
  • ODBC
    Standard Security:
    "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

    Trusted connection:
    "Driver={SQL Server};Server=Aron1;Database=pubs;
    Trusted_Connection=yes;"

    Prompt for username and password:
    oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

  • OLE DB, OleDbConnection (.NET)

    Standard Security:
    "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

    Trusted Connection:
    "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
    (use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)

    Prompt for username and password:
    oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Aron1;Initial Catalog=pubs;"

    Connect via an IP address:
    "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"

    (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))

  • SqlConnection (.NET)
    Standard Security:

    "Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" - or -
    "Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"

    (both connection strings produces the same result)


    Trusted Connection:
    "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" - or -"Server=Aron1;Database=pubs;Trusted_Connection=True;"

    (both connection strings produces the same result)
    (use serverName\instanceName as Data Source to use an specifik SQLServer instance,
    only SQLServer2000)

    Connect via an IP address:
    "Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
    (DBMSSOCN=TCP/IP instead of Named Pipes,
    at the end of the Data Source is the port to use (1433 is the default))

    Declare the SqlConnection:
    C#:using System.Data.SqlClient;SqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString="my connection string";oSQLConn.Open();

    VB.NET:
    Imports System.Data.SqlClientDim oSQLConn As SqlConnection = New SqlConnection()oSQLConn.ConnectionString="my connection string"oSQLConn.Open()
  • Data Shape
    MS Data Shape"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"

2.

SQL Server 2005

  • SQL Native Client ODBC Driver
    Standard security:
    "Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"

    Trusted connection:
    "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

    EquivalentsIntegrated Security=SSPI equals Trusted_Connection=yes

    Prompt for username and password:
    oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"

    Enabling MARS (multiple active result sets):

    "Driver={SQL Native Client};Server=Aron1;Database=pubs;

    Trusted_Connection=yes;MARS_Connection=yes"

    EquivalentsMultipleActiveResultSets=true equals MARS_Connection=yes


    Encrypt data sent over network:

    "Driver={SQL Native Client};Server=Aron1;Database=pubs;

    Trusted_Connection=yes;Encrypt=yes"
    Attach a database file on connect to a local SQL Server Express instance:

    "Driver={SQL Native Client};Server=.\SQLExpress;

    AttachDbFilename=c:\asd\qwe\mydbfile.mdf;

    Database=dbname;Trusted_Connection=Yes;"

    - or -

    "Driver={SQL Native Client};Server=.\SQLExpress;

    AttachDbFilename=DataDirectorymydbfile.mdf;

    Database=dbname;Trusted_Connection=Yes;"

    (use DataDirectory when your database file resides in the data directory)

    Why is the "Database" parameter needed?
    Answer:
    If the database was previously attached, SQL Server does not reattach it
    (it uses the attached database as the default for the connection).

    (the package contains booth the ODBC driver and the OLE DB provider)
    Using SQL Server 2005 Express? Don't miss the server name syntax:

    SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
  • SQL Native Client OLE DB Provider
    Standard security:

    "Provider=SQLNCLI;Server=Aron1;Database=pubs;
    UID=sa;PWD=asdasd;"
    Trusted connection:
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;
    Trusted_Connection=yes;"
    Equivalents

    Integrated Security=SSPI equals Trusted_Connection=yes


    Prompt for username and password:
    oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
    Enabling MARS (multiple active result sets):
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;
    Trusted_Connection=yes;MarsConn=yes"

    Equivalents

    MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes


    Encrypt data sent over network:
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;
    Trusted_Connection=yes;Encrypt=yes"

    Attach a database file on connect to a local SQL Server Express instance:
    "Provider=SQLNCLI;Server=.\SQLExpress;
    AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
    Database=dbname;Trusted_Connection=Yes;"
    - or -
    "Provider=SQLNCLI;Server=.\SQLExpress;
    AttachDbFilename=DataDirectorymydbfile.mdf;
    Database=dbname;Trusted_Connection=Yes;"

    (use DataDirectory when your database file resides in the data directory)

    Why is the "Database" parameter needed?
    Answer: If the database was previously attached,
    SQL Server does not reattach it
    (it uses the attached database as the default for the connection).



    (the package contains booth the ODBC driver and the OLE DB provider)
    Using SQL Server 2005 Express? Don't miss the server name syntax:
    SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

  • SqlConnection (.NET)
    Standard Security:
    "Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

    - or -

    "Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"

    (both connection strings produces the same result)

    Trusted Connection:
    "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

    - or -

    "Server=Aron1;Database=pubs;Trusted_Connection=True;"

    (both connection strings produces the same result)

    (use serverName\instanceName as Data Source to use an specifik SQLServer instance)

    Connect via an IP address:
    "Data Source=190.190.200.100,1433;
    Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"

    (DBMSSOCN=TCP/IP instead of Named Pipes,
    at the end of the Data Source is the port to use (1433 is the default))

    Enabling MARS (multiple active result sets):
    "Server=Aron1;Database=pubs;
    Trusted_Connection=True;MultipleActiveResultSets=true"

    Note! Use ADO.NET 2.0 for MARS functionality.
    MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1
    Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >>

    Attach a database file on connect to a local
    SQL Server Express instance:
    "Server=.\SQLExpress;
    AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
    Database=dbname;
    Trusted_Connection=Yes;"
    - or -
    "Server=.\SQLExpress;
    AttachDbFilename=DataDirectorymydbfile.mdf;
    Database=dbname;
    Trusted_Connection=Yes;"

    (use DataDirectory when your database file resides in the data directory)
    Why is the "Database" parameter needed?
    Answer: If the database was previously attached,
    SQL Server does not reattach it (
    it uses the attached database as the default for the connection).

    Using "User Instance" on a local SQL Server Express instance:
    "Data Source=.\SQLExpress;integrated security=true;
    attachdbfilename=DataDirectory\mydb.mdf;user instance=true;"

    The "User Instance" functionality creates a new SQL Server
    instance on the fly during connect. This works only on a local SQL Server 2005
    instance and only when connecting using windows authentication over local named pipes.
    The purpose is to be able to create a full rights SQL Server instance to a user
    with limited administrative rights on the computer.
    To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)
    Using SQL Server 2005 Express? Don't miss the server name syntax:
    SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

  • Context Connection - connecting to "self" from within your
    CLR stored prodedure/function
    C#:

    using(SqlConnection connection = new SqlConnection("context connection=true"))
    { connection.Open();
    // Use the connection
    }

    Visual Basic:
    Using connection as new SqlConnection("context connection=true")
    connection.Open() ' Use the connection
    End Using

    The context connection lets you execute Transact-SQL statements
    in the same context (connection) that your code was invoked in the first place.



3. Access
  • ODBC
    Standard Security:

    "Driver={Microsoft Access Driver (*.mdb)};
    Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

    Workgroup:
    "Driver={Microsoft Access Driver (*.mdb)};
    Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;"

    Exclusive:
    "Driver={Microsoft Access Driver (*.mdb)};
    Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

  • OLE DB, OleDbConnection (.NET)
    Standard security:

    "Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

    Workgroup (system database):
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"

    With password:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"


4. Oracle

  • ODBC
    New version:

    "Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;
    Uid=Username;Pwd=asdasd;"

    Old version:
    "Driver={Microsoft ODBC Driver for Oracle};
    ConnectString=OracleServer.world;
    Uid=myUsername;Pwd=myPassword;"

  • OLE DB, OleDbConnection (.NET)
    Standard security:
    "Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"

    This one's from Microsoft, the following are from Oracle

    Standard Security:
    "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"

    Trusted Connection:
    "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"

  • OracleConnection (.NET)

    Standard:
    "Data Source=MyOracleDB;Integrated Security=yes;"

    This one works only with Oracle 8i release 3 or later

    Specifying username and password:
    "Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"

    This one works only with Oracle 8i release 3 or later

  • Declare the OracleConnection:
    C#:
    using System.Data.OracleClient;
    OracleConnection oOracleConn = new OracleConnection();
    oOracleConn.ConnectionString = "my connection string";
    oOracleConn.Open();

    VB.NET:
    Imports System.Data.OracleClient
    Dim oOracleConn As OracleConnection = New OracleConnection()
    OracleConn.ConnectionString = "my connection string"
    oOracleConn.Open()

    Missing the System.Data.OracleClient namespace?
    Download .NET Managed Provider for Oracle >>Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner

  • Core Labs OraDirect (.NET)
    Standard:
    "User ID=scott; Password=tiger; Host=ora; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0"

    Read more at
    Core Lab and the product page.

  • Data Shape
    MS Data Shape:
    "Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;user id=username;password=mypw"



5. MySQL

  • MyODBC
    MyODBC 2.50 Local database:

    "Driver={mySQL};Server=localhost;
    Option=16834;Database=mydatabase;"

    MyODBC 2.50 Remote database:
    "Driver={mySQL};Server=data.domain.com;
    Port=3306;Option=131072;Stmt=;
    Database=my-database;Uid=username;Pwd=password;"

    MyODBC 3.51 Local database:
    "DRIVER={MySQL ODBC 3.51 Driver};
    SERVER=localhost;DATABASE=myDatabase;
    USER=myUsername;
    PASSWORD=myPassword;OPTION=3;"

    MyODBC 3.51 Remote database:
    "DRIVER={MySQL ODBC 3.51 Driver};
    SERVER=data.domain.com;
    PORT=3306;DATABASE=myDatabase; USER=myUsername;PASSWORD=myPassword;OPTION=3;"

  • OLE DB, OleDbConnection (.NET)
    Standard:

    "Provider=MySQLProv;Data Source=mydb;
    User Id=UserName;Password=asdasd;"
    Connector/Net 1.0 (.NET)

    Standard:
    "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"

    Download the driver at
    MySQL Developer Zone.

    Specifying port:
    "Server=Server;Port=1234;Database=Test;
    Uid=UserName;Pwd=asdasd;"

    Default port is 3306. Enter value -1 to use a named pipe connection.

    Declare the MySqlClient connection:
    C#:
    using MySql.Data.MySqlClient;
    MySqlConnection oMySqlConn = new MySqlConnection();
    oMySqlConn.ConnectionString = "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;";
    oMySqlConn.Open();

    VB.NET:
    Imports MySql.Data.MySqlClient
    Dim oMySqlConn As MySqlConnection = New MySqlConnection()
    oMySqlConn.ConnectionString = "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"
    oMySqlConn.Open()

  • MySqlConnection (.NET)
    eInfoDesigns.dbProvider:

    "Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"

    This one is used with
    eInfoDesigns dbProvider, an add-on to .NET

    Declare the MySqlConnection:
    C#:
    using eInfoDesigns.dbProvider.MySqlClient;
    MySqlConnection oMySqlConn = new MySqlConnection();
    oMySqlConn.ConnectionString = "my connection string";
    oMySqlConn.Open();

    VB.NET:
    Imports eInfoDesigns.dbProvider.MySqlClient
    Dim oMySqlConn As MySqlConnection = New MySqlConnection()
    oMySqlConn.ConnectionString = "my connection string"
    oMySqlConn.Open()

  • SevenObjects MySqlClient (.NET)
    Standard:

    "Host=server; UserName=myusername; Password=mypassword;Database=mydb;"

    This is a freeware ADO.Net data provider from
    SevenObjects

  • Core Labs MySQLDirect (.NET)
    Standard:
    "User ID=root; Password=pwd; Host=localhost; Port=3306; Database=test;
    Direct=true; Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;
    Max Pool Size=100; Connection Lifetime=0"

6.Interbase

  • ODBC, Easysoft
    Local computer:
    "Driver={Easysoft IB6 ODBC};
    Server=localhost;Database=localhost:C:\mydatabase.gdb;
    Uid=username;Pwd=password"

    Remote Computer:
    "Driver={Easysoft IB6 ODBC};
    Server=ComputerName;
    Database=ComputerName:C:\mydatabase.gdb;
    Uid=username;Pwd=password"

  • ODBC, Intersolv
    Local computer:
    "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=localhost;Database=localhost:C:\mydatabase.gdb;
    Uid=username;Pwd=password"

    Remote Computer:
    "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};
    Server=ComputerName;
    Database=ComputerName:C:\mydatabase.gdb;
    Uid=username;Pwd=password"

    This driver are provided by
    DataDirect Technologies >> (formerly Intersolv)

  • OLE DB, SIBPROvider
    Standard:
    "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey"

    Specifying character set:
    "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey;character set=ISO8859_1"

    Specifying role:
    "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey;role=DIGITADORES"


7.IBM DB2

  • OLE DB, OleDbConnection (.NET) from ms
    TCP/IP:
    "Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;
    User ID=MyUser;Password=MyPW"

    APPC:
    "Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;
    User ID=MyUser;Password=MyPW"

  • IBM's OLE DB Provider (shipped with IBM DB2 UDB v7 or above)
    TCP/IP:
    Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;
    PROTOCOL=TCPIP;
    PORT=50000;uid=myUserName;pwd=myPwd;

  • ODBC
    Standard:

    "driver={IBM DB2 ODBC DRIVER};Database=myDbName;hostname=myServerName;
    port=myPortNum;protocol=TCPIP;
    uid=myUserName; pwd=myPwd"

8.Sybase

  • ODBC
    Standard Sybase System 12 Enterprise Open Client:
    "Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"

    Standard Sybase System 12.5 Enterprise Open Client:
    "Driver={SYBASE ASE ODBC Driver};NA=HOSTNAME,PORT_NUMBER;
    UID=username;PWD=password"

    Standard Sybase System 11:
    "Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;Database=mydb"

    For more information check out the
    Adaptive Server Enterprise Document Sets

    Intersolv 3.10:
    "Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"

    Sybase SQL Anywhere (former Watcom SQL ODBC driver):
    "ODBC; Driver=Sybase SQL Anywhere 5.0;
    DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;
    Uid=username;Pwd=password;Dsn="""""

    Note! The two double quota ("") following the DSN parameter at
    the end are escaped quotas (VB syntax),
    you may have to change this to your
    language specific escape syntax (\") or maybe single quota (').
    The empty DSN parameter is indeed critical as not including it will result in error 7778.
    OLE DB
    Adaptive Server Anywhere (ASA):
    "Provider=ASAProv;Data source=myASA"

    Read more in the ASA User Guide (part 1, chapter 2) >>

    Adaptive Server Enterprise (ASE) with Data Source .IDS file:
    "Provider=Sybase ASE OLE DB Provider; Data source=myASE"

    Note that you must create a Data Source .IDS file using
    the Sybase Data Advministrator. These .IDS files resemble ODBC DSNs.

  • Adaptive Server Enterprise (ASE):
    "Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000;
    Catalog=myDBname;User Id=username;Password=password"

    - some reports on problem using the above one, try the following as an alternative -

    "Provider=Sybase.ASEOLEDBProvider;
    Server Name=myASEserver,5000;Initial Catalog=myDBname;
    User Id=username;Password=password"

    This one works only from Open Client 12.5 where the server port number feature works,
    allowing fully qualified connection strings to be used without
    defining any .IDS Data Source files.

  • AseConnection (.NET)
    Standard:

    "Data Source='myASEserver';Port=5000;Database='myDBname';
    UID='username';PWD='password';"

    Declare the AseConnection:
    C#:
    using Sybase.Data.AseClient;
    AseConnection oCon = new AseConnection();
    oCon.ConnectionString="my connection string";
    oCon.Open();

    VB.NET:
    Imports System.Data.AseClientDim oCon As AseConnection = New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()


9.Informix

  • ODBC
    Informix 3.30:
    "Dsn='';Driver={INFORMIX 3.30 32 BIT};Host=hostname;
    Server=myserver;Service=service-name;Protocol=olsoctcp;
    Database=mydb;UID=username;PWD=myPwd

    Informix-CLI 2.5:
    "Driver={Informix-CLI 2.5 (32 Bit)};Server=myserver;
    Database=mydb;Uid=username;Pwd=myPwd"

  • OLE DB
    IBM Informix OLE DB Provider:
    "Provider=Ifxoledbc.2;password=myPw;User ID=myUser;Data
    Source=dbName@serverName;
    Persist Security Info=true"

10. Ingres

  • ODBC
    DSN-less:
    "Provider=MSDASQL.1;DRIVER=Ingres;SRVR=xxxxx;
    DB=xxxxx;Persist Security Info=False;uid=xxxx;
    pwd=xxxxx;SELECTLOOPS=N;
    Extended Properties=""SERVER=xxxxx;
    DATABASE=xxxxx;SERVERTYPE=INGRES"""

    Important note! The two double quota ("") in the string are
    escaped quotas (VB syntax), you may have to change this to your
    language specific escape syntax (ex. \") or maybe single quota (').

11. Mimer SQL

  • ODBC
    Standard Security:
    "Driver={MIMER};Database=mydb;Uid=myuser;Pwd=mypw;"

    Prompt for username and password:
    "Driver={MIMER};Database=mydb;"

12. Lightbase

  • Standard
    Standard:
    "user=USERLOGIN;password=PASSWORD;UDB=USERBASE;
    server=SERVERNAME"

13.PostgreSQL

  • Core Labs PostgreSQLDirect (.NET)
    Standard:

    "User ID=root; Password=pwd; Host=localhost; Port=5432;
    Database=testdb;Pooling=true;
    Min Pool Size=0; Max Pool Size=100; Connection Lifetime=0"

    Read more at
    Core Lab and the product page.

  • PostgreSQL driver
    Standard:
    "DRIVER={PostgreSQL};SERVER=ipaddress;port=5432;
    DATABASE=dbname;UID=username;PWD=password;"
  • Npgsql by pgFoundry (.NET)
    SSL activated:
    "Server=127.0.0.1;Port=5432;Userid=myuserid;
    password=mypw;Protocol=3;SSL=true;
    Pooling=true;MinPoolSize=3;MaxPoolSize=20;
    Encoding=UNICODE;Timeout=20;SslMode=Require"

    Without SSL:
    "Server=127.0.0.1;Port=5432;Userid=myuserid;
    password=mypw;
    Protocol=3;SSL=false;
    Pooling=true;MinPoolSize=1;MaxPoolSize=20;
    Encoding=UNICODE;Timeout=15;SslMode=Disable"


14. Paradox

  • ODBC
    5.X:

    Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;
    CollatingSequence=ASCII"

    7.X:
    "Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\myDb;
    DefaultDir=C:\myDb;DriverId=538;FIL=Paradox 7.X;
    MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"
  • OleDbConnection (.NET)
    Standard

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb;
    Extended Properties=Paradox 5.x;"


15. DSN

  • ODBC
    DSN:
    "DSN=myDsn;Uid=username;Pwd=;"

    File DSN:
    "FILEDSN=c:\myData.dsn;Uid=username;Pwd=;"

16. Firebird

  • .NET - Firebird .Net Data Provider
    Standard:

    "User=SYSDBA;Password=masterkey;
    Database=SampleDatabase.fdb;
    DataSource=localhost;
    Port=3050;Dialect=3;Charset=NONE;Role=;
    Connection lifetime=15;Pooling=true;MinPoolSize=0;
    MaxPoolSize=50;Packet Size=8192;ServerType=0"

17. Excel

  • ODBC
    Standard:

    "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;
    Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"

    TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$"
    and wrapped in "[" "]" brackets.

  • OLE DB
    Standard:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

    "HDR=Yes;" indicates that the first row contains columnnames, not data

    "IMEX=1;" tells the driver to always read "intermixed"
    data columns as text. Note that this option might affect excel sheet write access negative.

    TIP! SQL syntax: "SELECT * FROM [sheet1$]" -
    i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

    TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
    located registry REG_DWORD "TypeGuessRows". That's the key to n
    ot letting Excel use only the first 8 rows to guess the columns data type.
    Set this value to 0 to scan all rows. This might hurt performance.

    Important note! The two double quota ("") in the string are escaped quotas (VB syntax),
    you may have to change this to your language specific escape syntax (ex. \") or
    maybe single quota (').


18. Text

  • ODBC
    Standard:
    "Driver={Microsoft Text Driver (*.txt; *.csv)};
    Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"
  • OLE DB
    Standard:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

    "HDR=Yes;" indicates that the first row contains columnnames, not data

    Important note! The two double quota ("") in the string are escaped quotas (VB syntax),
    you may have to change this to your language specific escape syntax (ex. \") or maybe single quota (').

19. DBF / FoxPro

  • ODBC
    standard:
    "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;"
  • OLE DB, OleDbConnection (.NET)
    standard:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;
    Extended Properties=dBASE IV;User ID=Admin;Password="

20. AS/400 (iSeries)

  • OLE DB, OleDbConnection (.NET)
    IBM Client Access OLE DB provider:

    "PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;
    USER ID=myUserName;PASSWORD=myPwd"

    Where MY_SYSTEM_NAME is the name given to the system
    connection in OperationsNavigator

    IBM Client Access OLE DB provider:
    "PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USER ID=myUserName;PASSWORD=myPwd;DEFAULT COLLECTION=MY_LIBRARY;"

    Where MY_SYSTEM_NAME is the name given to the System Connection,
    and MY_LIBRARY is the name given to the library in iSeries Navigator.

  • ODBC
    IBM Client Access ODBC driver:

    "Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;
    Uid=myUserName;Pwd=myPwd"

21. Exchange

22. Visual FoxPro

  • OLE DB, OleDbConnection (.NET)
    Database container (.DBC):

    "Provider=vfpoledb.1;Data Source=C:\MyDbFolder\MyDbContainer.dbc;
    Collating Sequence=machine"

    Free table directory:
    "Provider=vfpoledb.1;Data Source=C:\MyDataDirectory\;Collating Sequence=general"

    Force the provider to use an ODBC DSN:
    "Provider=vfpoledb.1;DSN=MyDSN"

    Read more (Microsoft msdn) >>
  • ODBC
    Database container (.DBC):

    "Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;
    SourceDB=c:\myvfpdb.dbc;Exclusive=No;
    NULL=NO;
    Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"

    Free Table directory:
    "Driver={Microsoft Visual FoxPro Driver};
    SourceType=DBF;SourceDB=c:\myvfpdbfolder;Exclusive=No;
    Collate=Machine;
    NULL=NO; DELETED=NO;BACKGROUNDFETCH=NO"

    "Collate=Machine" is the default setting, for other settings check the
    list of supported collating sequences >>
    Microsoft Visual Foxpro site:
    http://msdn.microsoft.com/vfoxpro


23 Pervasive

  • ODBC
    Standard:

    "Driver={Pervasive ODBC Client Interface};ServerName=srvname;dbq=@dbname"
    Pervasive ODBC info >>
  • OLE DB
    Standard:
    "Provider=PervasiveOLEDB;Data Source=C:\path"

24. UDL

  • UDL
    UDL:

    "File Name=c:\myDataLink.udl;"

Membuat tabel sementara di SQL Server

Ada kalanya pada saat kita melakukan programming vb misalnya kita membutuhkan suatu table yang bersifat sementara atau temporary table, misalnya untuk menampung hasil dari suatu proses atau query.

Membuat tabel sementara saat ini adalah hampir sama dengan membuat sebuah tabel biasa. terutama adalah pengecualian penamaan tabel. Sebuah hash('#') karakter pertama sebagai karakter dalam tabel nama menandakan bahwa sementara table.Disini ada dua jenis tabel sementara, lokal dan global.Tabel lokal sementara memiliki satu tabel hash('#')
Tabel global adalah terlihat oleh semua pengguna dan secara otomatis akan dihapus ketika terakhir pengguna yang memiliki referensi tabel disconnects.

Salah satu contoh tabel lokal sementara:

create table #foo
(
CarIndex smallint,
CarType varchar(20)
)

Salah satu contoh tabel global sementara:

create table ##baz
(
CarIndex smallint,
CarType varchar(20)
)

Selamat mencoba dan mempraktekan..

Membuat Laporan Menggunakan VSFlexgrid

Saya akan coba bahas suatu program menggunakan VB - MYSQL ditambah sedikit aksesoris vsflexgrid dan crystal report

Berikut ini adalah tampilan , kira kira program yang akan kita buat :


(click gambar untuk memperbesar)
Program ini kita buat simple, database mysql dengan satu table.
Step Pertama adalah Menyiapkan Database :
1. Buat satu database dengan nama : “Market”
2. Buat tablenya sebagai berikut :

CREATE TABLE `marketdetail` (
`ID` varchar(255) character set utf8 default NULL,
`Comodity` varchar(255) character set utf8 default NULL,
`Quantity` float default NULL,
`Negara` varchar(255) character set utf8 default NULL,
`Dest` varchar(255) character set utf8 default NULL,
`Tanggal` varchar(255) character set utf8 default NULL,
`CW` float default NULL,
`BERAT` varchar(255) character set utf8 default NULL,
`Market_id` mediumint(9) NOT NULL auto_increment,
PRIMARY KEY (`Market_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=99 ;—-

Setelah database beserta tablenya dibuat, berikutnya kita siapkan modul entrynya menggunakan aplikasi VB. Berikut tampilan dan listing programnya

Lakukan langkah Berikut :

1. Buat satu Modul (namakan global.bas)
2. Isikan modul tersebut dengan Syntak2 Berikut :

Public objCnn As New ADODB.Connection
Public objcmd As New ADODB.Command
Public objRst As New ADODB.Recordset
Public USRText As String
Public PWDText As String
Public PortText As String
Public strconnect As String
Public ServerAddress As String
Public DatabaseName As String
Sub SQLConnection()
ServerAddress = “localhost”
USRText = “root”
PWDText = “”
DatabaseName = “market”
PortText = “”
Set objCnn = Nothing
strconnect = “Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=” & Chr$(34) & “DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=” & DatabaseName & “;SERVER=” & ServerAddress & “;UID=” & USRText & “;PASSWORD=” & PWDText & “;PORT=” & PortText & “;OPTION=16387;STMT=;” & Chr$(34)
objCnn.CursorLocation = adUseClient
objCnn.ConnectionTimeout = 0
objCnn.Open strconnect
End Sub

Sub SQLDisconnection()
Set objRst = Nothing
Set objcmd = Nothing
objCnn.Close
End Sub

2. Buat Form seperti Dibawah ini

3. Pada tombol Save isikan syntak berikut (perhatikan pada penamaan objek Text Box dan Tanggal !!) :

Select Case MsgBox(”Do you want to save the record?”, vbQuestion + vbYesNoCancel, “Confirmation”)
Case vbYes
‘ If Not bValidate Then Exit Sub

Call SQLConnection
strSQL = “INSERT INTO marketdetail (ID, Comodity, Quantity, Negara, Dest, Tanggal, CW, Berat) ” & _
“VALUES (’” + Trim(Me.TxtName.Item(0).Text) + “‘ , ‘” + Trim(Me.TxtName.Item(1).Text) + “‘, ” & Me.TxtName.Item(2).Text & “,” & _
” ‘” + Trim(Me.TxtName.Item(3).Text) + “‘ , ‘” + Trim(Me.TxtName.Item(4).Text) + “‘, ‘” + Format(Me.TxtDate(0).Value, “mm/dd/yy”) + “‘, ‘” & Me.TxtName.Item(5).Text & “‘, ‘” & Me.TxtName.Item(6).Text & “‘)”
objCnn.Execute (strSQL)
Call SQLDisconnection
Case vbNo
msgbox “Gak Jadi !!”
Case vbCancel
Msgbox “Batal”
End Select

Selanjutnya, data akan kita tampilkan pada VSFlexgrid dan terakhir Crystal report

I. Buat tampilan seperti dibawah ini ,
Show Data

dgn komposisi sebagai berikut :
1. satu buah combo box dengan nama CboFields
2. satu buah vsFlexgrid dengan nama VSFlexGrid1
3. dua buah command button dengan nama :
cmdprocess dan cmdprint

II. Berikut syntax2-nya :

1. Form load :Private Sub Form_Load()
Me.Left = 0
Me.Top = 0
Me.VSFlexGrid1.Rows = 1
Call SQLConnection
Me.CboFields.Clear

’syntax dibawah ini digunakan untuk mengisi combo box dengan
’nama2 filed dari table marketdetail

With objcmd
.ActiveConnection = objCnn
.CommandText = “show columns from marketdetail”
.CommandType = adCmdText
End With
Set objRst = objcmd.Execute
Do While objRst.EOF = False
Me.CboFields.AddItem objRst.Fields(0)
objRst.MoveNext
DoEvents
Loop
Call SQLDisconnection
End Sub

2. tombol Process (CmdProcess)

Private Sub CmdProcess_Click()
On Error GoTo erhan
Call SQLConnection
Dim i As Integer
Dim y As Integer
Dim cnt1 As Integer
i = 0
y = 0
If Len(Trim(Me.CboFields.Text)) = 0 Then
MsgBox “Please select Order By Selection !”, vbOKOnly + vbInformation
Exit Sub
End If
With objcmd
.ActiveConnection = objCnn
.CommandText = “Select * From marketdetail order by ” + Me.CboFields.Text + ” asc”
.CommandType = adCmdText
End With
Set objRst = objcmd.Execute
If objRst.EOF = False Then
cnt1 = objRst.RecordCount
For i = 0 To objRst.Fields.Count - 1
Me.VSFlexGrid1.Rows = 1
Me.VSFlexGrid1.Cols = 11
Me.VSFlexGrid1.Row = 0
Me.VSFlexGrid1.Col = i + 1
Me.VSFlexGrid1.Text = objRst.Fields(i).Name
Me.VSFlexGrid1.CellFontBold = True
Next
i = 0
y = 1
Do While objRst.EOF = False
Me.VSFlexGrid1.Rows = Me.VSFlexGrid1.Rows + 1
For i = 0 To objRst.Fields.Count - 1
Me.VSFlexGrid1.TextMatrix(y, i + 1) = Trim(objRst.Fields(i))
DoEvents
Next i
y = y + 1
DoEvents
objRst.MoveNext
DoEvents
Loop
Else
Exit Sub
End If
Call SQLDisconnection


DoEvents
Me.VSFlexGrid1.AllowUserResizing = flexResizeBoth
i = 0
For i = 1 To 10
Me.VSFlexGrid1.MergeCol(i) = True
Next i
SortAndShow

VSFlexGrid1.MergeCells = 1
VSFlexGrid1.MergeCells = 2

VSFlexGrid1.Subtotal flexSTSum, -1, 3, m_Fmt, , RGB(255, 0, 0), True
VSFlexGrid1.Subtotal flexSTSum, -1, 7, m_Fmt, , RGB(255, 0, 0), True

VSFlexGrid1.Subtotal flexSTSum, 1, 3, m_Fmt, , RGB(0, 0, 255), True
VSFlexGrid1.Subtotal flexSTSum, 1, 7, m_Fmt, , RGB(255, 0, 0), True
Exit Sub
erhan:
MsgBox Err.Description
Call SQLDisconnection
End Sub
”—– additional procedure

Sub SortAndShow()
Dim r%, c%
On Error GoTo erhan
VSFlexGrid1.Redraw = False

r = VSFlexGrid1.Row
c = VSFlexGrid1.Col

VSFlexGrid1.Col = 0
VSFlexGrid1.ColSel = VSFlexGrid1.Cols - 1
VSFlexGrid1.Row = r
VSFlexGrid1.Col = c
VSFlexGrid1.Redraw = True
Exit Sub
erhan:
MsgBox Err.Description
Resume Next
End Sub

3. tombol Print (cmdprint)

untuk tombol print ini pastikan reportnya sudah dibuat terlebih dahulu, dengan nama file reportnya adalah “mymarket.rpt”


Private Sub CmdPrint_Click()
On Error GoTo erhan
LocText$ = LCase(App.path)
If Right$(App.path, 1) <> “\” Then LocText$ = LocText$ + “\”
CrystalReport1.Connect = strconnect
CrystalReport1.ReportFileName = LocText$ + “mymarket.rpt”

On Error GoTo erhan
CrystalReport1.Action = 0
Exit Sub
erhan:
MsgBox Err.Description
End Sub

jadi hasil akhirnya harusnya seperti ini :

hasil akhir

dan seperti ini

report

Koneksi VB dengan Oracle menggunakan ODBC

Pada posting ini saya coba tuliskan cara koneksi VB ke Oracle via ODBC dan Connection string .
Berikut Selengkapnya :

Coba buat form seperti berikut :

vbora

Kita akan mengisikan kode pada Tombol “Connect ODBC” dan “Connection String”
1. Tombol Connect ODBC
Private Sub cmdConnectODBC_Click()
On Error GoTo erhan
Dim Objcnn As New ADODB.Connection
Dim SQLString As String
SQLString = “DSN=RyanOra;UID=ryan;PWD=Killme;”
Objcnn.Open SQLString
Objcnn.CommandTimeout = 0
MsgBox “Sucess”
Exit Sub
erhan:
MsgBox Err.Description
End Sub

Kode diatas dapat dijalankan,d engan Asumsi, Kita sudah membuat ODBC data sourcenya dengan Nama : RyanORa

2. Tombol Connection String

Private Sub CmdConStr_Click()
On Error GoTo erhan
Dim Objcnn As New ADODB.Connection
Dim SQLString As String
SQLString = “Provider=OraOLEDB.Oracle.1;Password=itonly;Persist Security Info=True;User ID=ryan;Data Source=ORARYAN”

Objcnn.Open SQLString
Objcnn.CommandTimeout = 0
MsgBox “Sucess”
Exit Sub
erhan:
MsgBox Err.Description
End Sub

Kode diatas menerangkan, cara koneksi untuk ke database oracle :RYANORA

24 November 2008

Membuat Program Barcode

Kode di bawah ini akan menunjukkan cara untuk input data ke dalam kotak teks dan mendapatkan barcode dari gambar itu. Anda juga akan mempelajari bagaimana untuk bekerja dengan clipboard checksum dan kontrol.

Untuk menggunakan, baru memulai Visual Basic Proyek, menambahkan formulir untuk proyek dan paste kode di bawah ini ke dalamnya. Anda akan memiliki visual untuk membuat kotak (qty4), tombol perintah, frame (qty2), label, tombol pilihan (qty4), gambar kotak (qty2) dan kotak teks.

Berikut tampilan preview dari program barcode maker yang kita akan buat.Untuk desain anda bisa membuat sesuai dengan

Barcode Software

Option Explicit
Dim BCtype As Long


Private Sub makeBC()
Select Case BCtype
Case 0
make39
Case 1
makei25
Case 2
make128
Case 3
makeCodabar
End Select
End Sub



Private Sub make39()
Dim x As Long, y As Long, pos As Long
Dim Bardata As String
Dim Cur As String
Dim CurVal As Long
Dim chksum As Long
Dim chkchr As String
Dim temp As String
Dim BC(43) As String
'3 of the 9 elements are wide: 0=narrow, 1=wide
BC(0) = "000110100" '0
BC(1) = "100100001" '1
BC(2) = "001100001" '2
BC(3) = "101100000" '3
BC(4) = "000110001" '4
BC(5) = "100110000" '5
BC(6) = "001110000" '6
BC(7) = "000100101" '7
BC(8) = "100100100" '8
BC(9) = "001100100" '9
BC(10) = "100001001" 'A
BC(11) = "001001001" 'B
BC(12) = "101001000" 'C
BC(13) = "000011001" 'D
BC(14) = "100011000" 'E
BC(15) = "001011000" 'F
BC(16) = "000001101" 'G
BC(17) = "100001100" 'H
BC(18) = "001001100" 'I
BC(19) = "000011100" 'J
BC(20) = "100000011" 'K
BC(21) = "001000011" 'L
BC(22) = "101000010" 'M
BC(23) = "000010011" 'N
BC(24) = "100010010" 'O
BC(25) = "001010010" 'P
BC(26) = "000000111" 'Q
BC(27) = "100000110" 'R
BC(28) = "001000110" 'S
BC(29) = "000010110" 'T
BC(30) = "110000001" 'U
BC(31) = "011000001" 'V
BC(32) = "111000000" 'W
BC(33) = "010010001" 'X
BC(34) = "110010000" 'Y
BC(35) = "011010000" 'Z
BC(36) = "010000101" '-
BC(37) = "110000100" '.
BC(38) = "011000100" '
BC(39) = "010101000" '$
BC(40) = "010100010" '/
BC(41) = "010001010" '+
BC(42) = "000101010" '%
BC(43) = "010010100" '* (used for start/stop character only)

Picture1.Cls
If Text1.Text = "" Then Exit Sub
pos = 20
Bardata = UCase(Text1.Text)

'Check for invalid characters, build temp string & calculate check sum
For x = 1 To Len(Bardata)
Cur = Mid$(Bardata, x, 1)
Select Case Cur
Case "0" To "9"
CurVal = Val(Cur)
Case "A" To "Z"
CurVal = Asc(Cur) - 55
Case "-"
CurVal = 36
Case "."
CurVal = 37
Case " "
CurVal = 38
Case "$"
CurVal = 39
Case "/"
CurVal = 40
Case "+"
CurVal = 41
Case "%"
CurVal = 42
Case Else 'oops!
Picture1.Print Cur & " is Invalid"
Exit Sub
End Select
temp = temp & BC(CurVal) & "0" '"0"= add intercharactor gap (1 narrow space)
chksum = chksum + CurVal
Next

'Add Check Character? (rarely used, but i put it here anyway...)
If Check1(2).Value Then
chksum = chksum Mod 43
temp = temp & BC(chksum) & "0"
chkchr = Mid$("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%*", chksum + 1, 1)
End If

'Add Start & Stop characters (must have 'em for valid barcodes)
temp = BC(43) & "0" & temp & BC(43)

'Generate Barcode
For x = 1 To Len(temp)
If x Mod 2 = 0 Then
'SPACE
pos = pos + 1 + (2 * Val(Mid$(temp, x, 1))) + Check1(0).Value
Else
'BAR
For y = 1 To 1 + (2 * Val(Mid$(temp, x, 1)))
Picture1.Line (pos, 1)-(pos, 58 - Check1(1) * 8)
pos = pos + 1
Next
End If
Next

'Add Label?
If Check1(1).Value Then
Picture1.CurrentX = 35 + Len(Bardata) * (5 + Check1(0).Value * 2) 'kinda center
Picture1.CurrentY = 50
Picture1.Print Bardata & chkchr;
End If
End Sub


Private Sub makei25()
Dim x As Long, y As Long, pos As Long
Dim Bardata As String
Dim Cur As String
Dim temp As String
Dim chksum As Long
Dim BC(11) As String
'2 of the 5 elements are wide: 0=narrow, 1=wide
BC(0) = "00110" '0
BC(1) = "10001" '1
BC(2) = "01001" '2
BC(3) = "11000" '3
BC(4) = "00101" '4
BC(5) = "10100" '5
BC(6) = "01100" '6
BC(7) = "00011" '7
BC(8) = "10010" '8
BC(9) = "01010" '9
BC(10) = "0000" 'Start chr
BC(11) = "100" 'Stop chr

Picture1.Cls
If Text1.Text = "" Then Exit Sub
pos = 20
Bardata = Text1.Text

'make even num of digits by adding a leading 0
If Len(Bardata) Mod 2 And Not Check1(2).Value Then Bardata = "0" & Bardata
If Not (Len(Bardata) Mod 2) And Check1(2).Value Then Bardata = "0" & Bardata

'Check for invalid characters and calculate check sum
For x = 1 To Len(Bardata)
Cur = Mid$(Bardata, x, 1)
If Cur < "0" Or Cur > "9" Then
Picture1.Print Cur & " is Invalid"
Exit Sub
End If
'make checksum
If x Mod 2 Then
chksum = chksum + CLng(Cur) * 3
Else
chksum = chksum + CLng(Cur)
End If
Next

'add check chr to bardata (if selected)
If Check1(2).Value Then
chksum = (10 - chksum Mod 10) Mod 10
Bardata = Bardata & Chr$(48 + chksum)
End If

'interleave the code into a temp string - what'd you think the name meant?
For x = 1 To Len(Bardata) Step 2
For y = 1 To 5
temp = temp & Mid$(BC(Val(Mid$(Bardata, x, 1))), y, 1)
temp = temp & Mid$(BC(Val(Mid$(Bardata, x + 1, 1))), y, 1)
Next
Next

'add Start & Stop characters
temp = BC(10) & temp & BC(11)

'Generate Barcode
For x = 1 To Len(temp)
If x Mod 2 = 0 Then
'SPACE
pos = pos + 1 + (2 * Val(Mid$(temp, x, 1))) + Check1(0).Value
Else
'BAR
For y = 1 To 1 + (2 * Val(Mid$(temp, x, 1)))
Picture1.Line (pos, 1)-(pos, 58 - Check1(1) * 8)
pos = pos + 1
Next
End If
Next

'Add Label?
If Check1(1).Value Then
Picture1.CurrentX = 20 + Len(Bardata) * (2 + Check1(0).Value * 1.3) 'kinda center
Picture1.CurrentY = 50
Picture1.Print Bardata;
End If
End Sub


Private Sub make128()
Dim x As Long, y As Long, pos As Long
Dim Bardata As String
Dim Cur As String
Dim CurVal As Long
Dim chksum As Long
Dim temp As String
Dim BC(106) As String
'code 128 is basically the ASCII chr set.
'4 element sizes : 1=narrowest, 4=widest
BC(0) = "212222" '
BC(1) = "222122" '!
BC(2) = "222221" '"
BC(3) = "121223" '#
BC(4) = "121322" '$
BC(5) = "131222" '%
BC(6) = "122213" '&
BC(7) = "122312" ''
BC(8) = "132212" '(
BC(9) = "221213" ')
BC(10) = "221312" '*
BC(11) = "231212" '+
BC(12) = "112232" ',
BC(13) = "122132" '-
BC(14) = "122231" '.
BC(15) = "113222" '/
BC(16) = "123122" '0
BC(17) = "123221" '1
BC(18) = "223211" '2
BC(19) = "221132" '3
BC(20) = "221231" '4
BC(21) = "213212" '5
BC(22) = "223112" '6
BC(23) = "312131" '7
BC(24) = "311222" '8
BC(25) = "321122" '9
BC(26) = "321221" ':
BC(27) = "312212" ';
BC(28) = "322112" '<>
BC(31) = "212321" '?
BC(32) = "232121" '@
BC(33) = "111323" 'A
BC(34) = "131123" 'B
BC(35) = "131321" 'C
BC(36) = "112313" 'D
BC(37) = "132113" 'E
BC(38) = "132311" 'F
BC(39) = "211313" 'G
BC(40) = "231113" 'H
BC(41) = "231311" 'I
BC(42) = "112133" 'J
BC(43) = "112331" 'K
BC(44) = "132131" 'L
BC(45) = "113123" 'M
BC(46) = "113321" 'N
BC(47) = "133121" 'O
BC(48) = "313121" 'P
BC(49) = "211331" 'Q
BC(50) = "231131" 'R
BC(51) = "213113" 'S
BC(52) = "213311" 'T
BC(53) = "213131" 'U
BC(54) = "311123" 'V
BC(55) = "311321" 'W
BC(56) = "331121" 'X
BC(57) = "312113" 'Y
BC(58) = "312311" 'Z
BC(59) = "332111" '[
BC(60) = "314111" '\
BC(61) = "221411" ']
BC(62) = "431111" '^
BC(63) = "111224" '_
BC(64) = "111422" '`
BC(65) = "121124" 'a
BC(66) = "121421" 'b
BC(67) = "141122" 'c
BC(68) = "141221" 'd
BC(69) = "112214" 'e
BC(70) = "112412" 'f
BC(71) = "122114" 'g
BC(72) = "122411" 'h
BC(73) = "142112" 'i
BC(74) = "142211" 'j
BC(75) = "241211" 'k
BC(76) = "221114" 'l
BC(77) = "413111" 'm
BC(78) = "241112" 'n
BC(79) = "134111" 'o
BC(80) = "111242" 'p
BC(81) = "121142" 'q
BC(82) = "121241" 'r
BC(83) = "114212" 's
BC(84) = "124112" 't
BC(85) = "124211" 'u
BC(86) = "411212" 'v
BC(87) = "421112" 'w
BC(88) = "421211" 'x
BC(89) = "212141" 'y
BC(90) = "214121" 'z
BC(91) = "412121" '{
BC(92) = "111143" '|
BC(93) = "111341" '}
BC(94) = "131141" '~
BC(95) = "114113" ' *not used in this sub
BC(96) = "114311" 'FNC 3 *not used in this sub
BC(97) = "411113" 'FNC 2 *not used in this sub
BC(98) = "411311" 'SHIFT *not used in this sub
BC(99) = "113141" 'CODE C *not used in this sub
BC(100) = "114131" 'FNC 4 *not used in this sub
BC(101) = "311141" 'CODE A *not used in this sub
BC(102) = "411131" 'FNC 1 *not used in this sub
BC(103) = "211412" 'START A *not used in this sub
BC(104) = "211214" 'START B
BC(105) = "211232" 'START C *not used in this sub
BC(106) = "2331112" 'STOP

Picture1.Cls
If Text1.Text = "" Then Exit Sub
pos = 20
Bardata = Text1.Text

'Check for invalid characters, calculate check sum & build temp string
For x = 1 To Len(Bardata)
Cur = Mid$(Bardata, x, 1)
If Cur < " " Or Cur > "~" Then
Picture1.Print "Invalid Character(s)"
Exit Sub
End If
CurVal = Asc(Cur) - 32
temp = temp + BC(CurVal)
chksum = chksum + CurVal * x
Next

'Add start, stop & check characters
chksum = (chksum + 104) Mod 103
temp = BC(104) & temp & BC(chksum) & BC(106)

'Generate Barcode
For x = 1 To Len(temp)
If x Mod 2 = 0 Then
'SPACE
pos = pos + (Val(Mid$(temp, x, 1))) + Check1(0).Value
Else
'BAR
For y = 1 To (Val(Mid$(temp, x, 1)))
Picture1.Line (pos, 1)-(pos, 58 - Check1(1) * 8)
pos = pos + 1
Next
End If
Next

'Add Label?
If Check1(1).Value Then
Picture1.CurrentX = 30 + Len(Bardata) * (3 + Check1(0).Value * 2) 'kinda center
Picture1.CurrentY = 50
Picture1.Print Bardata;
End If
End Sub


Private Sub makeCodabar()
Dim x As Long, y As Long, pos As Long
Dim Bardata As String
Dim Cur As String
Dim CurVal As Long
Dim temp As String
Dim BC(19) As String
'Codabar, also known as NW-7
BC(0) = "0000011" '0
BC(1) = "0000110" '1
BC(2) = "0001001" '2
BC(3) = "1100000" '3
BC(4) = "0010010" '4
BC(5) = "1000010" '5
BC(6) = "0100001" '6
BC(7) = "0100100" '7
BC(8) = "0110000" '8
BC(9) = "1001000" '9
BC(10) = "0001100" '-
BC(11) = "0011000" '$
BC(12) = "1000101" ':
BC(13) = "1010001" '/
BC(14) = "1010100" '.
BC(15) = "0010101" '+
BC(16) = "0011010" 'start/stop A
BC(17) = "0101001" 'start/stop B
BC(18) = "0001011" 'start/stop C
BC(19) = "0001110" 'start/stop D

Picture1.Cls
If Text1.Text = "" Then Exit Sub
pos = 20
Bardata = Text1.Text

For x = 1 To Len(Bardata)
Cur = Mid$(Bardata, x, 1)
Select Case Cur
Case "0" To "9"
CurVal = Val(Cur)
Case "a" To "d"
CurVal = Asc(Cur) - 81
Case "-"
CurVal = 10
Case "$"
CurVal = 11
Case ":"
CurVal = 12
Case "/"
CurVal = 13
Case "."
CurVal = 14
Case "+"
CurVal = 15
Case Else 'oops!
Picture1.Print Cur & " is Invalid"
Exit Sub
End Select
temp = temp & BC(CurVal) & "0" '"0"= add intercharactor gap (1 narrow space)
Next

'Add Start & Stop characters (using "A" for both here)
temp = BC(16) & "0" & temp & BC(16)

'Generate Barcode
For x = 1 To Len(temp)
If x Mod 2 = 0 Then
'SPACE
pos = pos + 1 + (2 * Val(Mid$(temp, x, 1))) + Check1(0).Value
Else
'BAR
For y = 1 To 1 + (2 * Val(Mid$(temp, x, 1)))
Picture1.Line (pos, 1)-(pos, 58 - Check1(1) * 8)
pos = pos + 1
Next
End If
Next

'Add Label?
If Check1(1).Value Then
Picture1.CurrentX = 30 + Len(Bardata) * (3 + Check1(0).Value * 2) 'kinda center
Picture1.CurrentY = 50
Picture1.Print Bardata;
End If
End Sub


Private Sub Form_Resize()
Picture1.Width = Form1.Width - 360
makeBC
End Sub


Private Sub Option1_Click(Index As Integer)
Select Case Index
Case 0
Check1(2).ToolTipText = "Optional"
Check1(2).Value = 0
Check1(2).Enabled = True
Case 1
Check1(2).ToolTipText = "Optional"
Check1(2).Value = 0
Check1(2).Enabled = True
Case 2
Check1(2).ToolTipText = "Not optional"
Check1(2).Value = 1
Check1(2).Enabled = False
Case 3
Check1(2).ToolTipText = "Not used"
Check1(2).Value = 0
Check1(2).Enabled = False
End Select
BCtype = Index
makeBC
End Sub


Private Sub Text1_Change()
makeBC
End Sub


Private Sub Check1_Click(Index As Integer)
makeBC
End Sub


Private Sub Command1_Click()
Clipboard.Clear
Clipboard.SetData Picture1.Image
End Sub

22 November 2008

Membuat angka terbilang dengan bahasa indonesia

Dalam latihan ini penulis mencoba membuat angka menjadi tulisan terbilang
dengan bahasa Indonesia dari besar uang yang dimasukkan ke
dalam textbox. Besar angka yang dimasukkan bukan bilangan
pecahan, tapi bilangan bulat.
Membuat parameter "MataUang" yang bersifat opsional, di mana
default-nya = "rupiah". Jika parameter ini dikosongkan dengan ""
maka kata "rupiah" tidak muncul. Jika parameter ini tidak ada
(hanya memiliki 1 parameter), kata "rupiah" ditampilkan.

Persiapan:
1. Buat 1 Project baru dengan 1 Form dan 2 TextBox.
2. Text1 untuk input dengan angka, Text2 menampilkan terbilang.
3. Ketik coding berikut ke dalam editor form yang bertalian.

Ketik coding berikut ini pada Form.
Public Function TerbilangBulat(strAngka As String, _
Optional MataUang As String = "rupiah") As String
Dim strJmlHuruf$, intPecahan As Integer
Dim strPecahan$, Urai$, Bil1$, strTot$, Bil2$
Dim X As Integer, Y As Integer, z As Integer
On Error GoTo Pesan
Dim strValid As String, huruf As String * 1
Dim i As Integer
'Periksa setiap karakter yg diketikkan ke kotak

‘UserID
strValid = "1234567890"
For i% = 1 To Len(strAngka)
huruf = Chr(Asc(Mid(strAngka, i%, 1)))
If InStr(strValid, huruf) = 0 Then
Set AngkaTerbilang = Nothing
MsgBox "Harus karakter angka!", _
vbCritical, "Karakter Tidak Valid"
Exit Function
End If
Next i%
If strAngka = "" Then Exit Function
If Len(Trim(strAngka)) > 15 Then GoTo Pesan
strJmlHuruf = LTrim(strAngka)
'intPecahan = Val(Right(Mid(strAngka, 15, 2), 2))
If (intPecahan = 0) Then
strPecahan = ""
Else
'strPecahan = LTrim(Str(intPecahan)) + "/100 "
strPecahan = ""
End If
X = 0
Y = 0
Urai = ""
While (X < Len(strJmlHuruf))
X = X + 1
218
strTot = Mid(strJmlHuruf, X, 1)
Y = Y + Val(strTot)
z = Len(strJmlHuruf) - X + 1
Select Case Val(strTot)
Case 1
If (z = 1 Or z = 7 Or z = 10 Or z = 13) Then
Bil1 = "satu "
ElseIf (z = 4) Then
If (X = 1) Then
Bil1 = "se"
Else
Bil1 = "satu "
End If
ElseIf (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14)
Then
X = X + 1
strTot = Mid(strJmlHuruf, X, 1)
z = Len(strJmlHuruf) - X + 1
Bil2 = ""
Select Case Val(strTot)
Case 0: Bil1 = "sepuluh "
Case 1: Bil1 = "sebelas "
Case 2: Bil1 = "dua belas "
Case 3: Bil1 = "tiga belas "
Case 4: Bil1 = "empat belas "
Case 5: Bil1 = "lima belas "
Case 6: Bil1 = "enam belas "
Case 7: Bil1 = "tujuh belas "
Case 8: Bil1 = "delapan belas "
Case 9: Bil1 = "sembilan belas "
End Select
Else
Bil1 = "se"
End If
Case 2: Bil1 = "dua "
Case 3: Bil1 = "tiga "
Case 4: Bil1 = "empat "
Case 5: Bil1 = "lima "
Case 6: Bil1 = "enam "
Case 7: Bil1 = "tujuh "
Case 8: Bil1 = "delapan "
Case 9: Bil1 = "sembilan "
Case Else
Bil1 = ""
End Select
If (Val(strTot) > 0) Then
If (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14) Then
Bil2 = "puluh "
ElseIf (z = 3 Or z = 6 Or z = 9 Or z = 12 Or z = 15)
Then
Bil2 = "ratus "
Else
Bil2 = ""
End If
Else
Bil2 = ""
End If
219
If (Y > 0) Then
Select Case z
Case 4: Bil2 = Bil2 + "ribu "
Y = 0
Case 7: Bil2 = Bil2 + "juta "
Y = 0
Case 10: Bil2 = Bil2 + "milyar "
Y = 0
Case 13: Bil2 = Bil2 + "trilyun "
Y = 0
End Select
End If
Urai = Urai + Bil1 + Bil2
Wend
Urai = Urai + strPecahan
TerbilangBulat = (Urai & MataUang)
Exit Function
Pesan:
TerbilangBulat = "(maksimal 15 digit)"
End Function
Private Sub Text1_Change()
Text2.Text = TerbilangBulat(Text1.Text)
End Sub

Awal Kalimat Kapital

kali ini kita latihan fungsi untuk menampilkan huruf pertama dari
setiap kalimat menjadi huruf besar/kapital.

Persiapan:
1. Buat 1 Project baru dengan 1 Form dan 1 TextBox.
2. Ketik coding berikut ke dalam editor form yang bertalian.

Ketik coding berikut ini pada Form.
Public Function AwalKalimatKapital(strKalimat As _ String)
Dim Temp1 As String, Temp2 As String
Dim Lokasi As Integer, i As Integer
Dim huruf As String * 1
Temp1$ = LCase(strKalimat) 'Kecilkan dulu semua
For i% = 1 To Len(Temp1$)
huruf = Chr(Asc(Mid(strKalimat, i%, 1)))
If huruf = "." Then
Lokasi% = i% + 2
End If
If i% = 1 Or i% = Lokasi% Then
Temp2$ = Temp2$ + UCase(Chr(Asc(Mid(Temp1$, _
i%, 1))))
Else
Temp2$ = Temp2$ + LCase(Chr(Asc(Mid(Temp1$, _
i%, 1))))
End If
Next i
AwalKalimatKapital = Temp2$
End Function
Private Sub Text1_Change()
Dim posisi As Integer
posisi = Text1.SelStart
216
Text1.Text = AwalKalimatKapital(Text1.Text)
Text1.SelStart = posisi
End Sub

Memindahkan File

Latihan kali ini kita buat program untuk memindahkan file dari suatu
folder ke folder lain.

Persiapan:
1. Buat 1 Project dengan 1 Form, 1 Module, dan 1
Commandbutton.
2. Ketik coding berikut ke dalam editor form Anda.

Ketik coding berikut ini pada Module.
Declare Function MoveFile Lib "kernel32" Alias _ "MoveFileA"
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String) As Long

Ketik coding berikut ini pada Form.
Private Sub Command1_Click()
'Contoh ini memindahkan file 'c:\MyFile.Zip' ke ‘direktori
'c:\MyDir'.
A = MoveFile("c:\MyFile.Zip", "c:\MyDir\MyFile.Zip")
If A Then
MsgBox "File berhasil dipindahkan!", _
vbInformation, "Sukses Pindah File"
Else
MsgBox "Error. File belum dipindahkan!" & _
Chr(13) & _
"Kemungkinan file asal tidak ada" & _
Chr(13) & _
"atau file sudah ada di dalam " & _
Chr(13) & _
"direktori tujuan!", vbCritical, "Gagal _
Pindah File"
End If
End Sub