Metoda pencarian kata dengan textbox di datagridview

Dalam suatu aplikasi, biasanya ada pencarian kata dalam daftar/list yang ditampilkan dalam suatu Grid. Misalnya pencarian Nama, Alamat, Nomor Invoice dsb. Dalam hal ini penulis akan membahas pencarian dimana user mengetikan kata dalam sebuah textbox, sementara kursor didatagrid akan mencari kata yang sama sesuai dengan kata dalam textbox. Baiklah kita asumsikan bahwa kita sudah mempunyai aplikasi dan data yang akan ditampilkan seperti gambar dibawah ini :




proses pencarian gambar diatas adalah pencarian Nomor Akun. ketika kita mengetikan Nomor Akun yang kita inginkan, kursor yang berwarna biru di dalam grid akan menunjukkannya. Listing Programnya adalah sebagai berikut :

 Private Sub txtFind_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFind.TextChanged
 
        Dim xloop1 As Integer
        Dim sPola As String
        Dim sbuka As String
        Dim SBetul As Boolean


        sPola = txtFind.Text + "*"


        For xloop1 = 1 To Gridme.RowCount - 1
            sbuka = Gridme.Rows(xloop1 - 1).Cells(0).Value
            SBetul = UCase(sbuka) Like UCase(sPola)

            If SBetul = True Then
                Gridme.CurrentCell = Gridme.Item(0, xloop1 - 1)
                Exit Sub
            End If
        Next
    End Sub

keterangan Listing Program :

    sPola = txtFind.Text + "*" : string yang akan dicari
    sbuka = Gridme.Rows(xloop1 - 1).Cells(0).Value : string pembanding dalam Grid dengan alamat cell = 0
    SBetul = UCase(sbuka) Like UCase(sPola) : Proses pembandingan kata yang dicari dengan pembanding

     If SBetul = True Then
           Gridme.CurrentCell = Gridme.Item(0, xloop1 - 1)
           Exit Sub
     End If
    : Jika sBetul = true, maka kursor akan diam row yang sama dengan kata yang dicari

hasilnya akan seperti ini,





demikian cara pencarian dengan menggunakan textbox. semoga bermanfaat.

Cara VB.NET Koneksi ke Database SQL

Waduhhh.. sudah lama tidak share knowledge, dikarenakan sibuk dengan pekerjaan. disela-sela waktu ini saya sempatkan untuk share. Untuk share kali ini adalah tentang koneksi ke database. Saya akan memberikan contoh koneksi ke berbagai jenis software penyimpan data. Baik kita mulai saja... sharingnya.

A. Koneksi ke SQL

•     SQL ODBC connection strings

               Standard Security: 

                       "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;

                    Uid=Your_Username;Pwd=Your_Password;"

          Trusted connection:

               "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;

                Trusted_Connection=yes;"

•  SQL OLE DB connection strings

             Standard Security:

                   "Provider=SQLOLEDB;Data Source=Your_Server_Name;

                     Initial Catalog=Your_Database_Name;UserId=Your_Username;

                     Password=Your_Password;"

            Trusted connection:

                     "Provider=SQLOLEDB;Data Source=Your_Server_Name;

                      Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

• SQL OleDbConnection .NET strings

          Standard Security:

                 "Provider=SQLOLEDB;Data Source=Your_Server_Name;

                  Initial Catalog= Your_Database_Name;UserId=Your_Username;

                  Password=Your_Password;"

          Trusted connection:

                      "Provider=SQLOLEDB;Data Source=Your_Server_Name;
                   Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

• SQL SqlConnection .NET strings

               Standard Security:

             1. "Data Source=Your_Server_Name;

                  Initial Catalog= Your_Database_Name;UserId=Your_Username;

                  Password=Your_Password;" 

             2. "Server=Your_Server_Name;Database=Your_Database_Name;

                  UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"

          Trusted connection:

             1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;

                  Integrated Security=SSPI;"

             2."Server=Your_Server_Name;Database=Your_Database_Name;

                  Trusted_Connection=True;"

Cara VB.NET Koneksi ke Database MySQL dan Oracle

MySQL connection strings

MySQL ODBC connection strings

Open connection to local MySQL database using MySQL ODBC 3.51 Driver

"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"

MySQL OLE DB & OleDbConnection (.NET framework) connection strings

Open connection to MySQL database:

"Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password;"

Oracle connection strings

Oracle ODBC connection strings

Open connection to Oracle database using ODBC

"Driver= {Microsoft ODBCforOracle};Server=Your_Oracle_Server.world;Uid=Your_Username;Pwd=Your_Password;"

Oracle OLE DB & OleDbConnection (.NET framework) connection strings

Open connection to Oracle database with standard security:

1. "Provider=MSDAORA;Data Source= Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"

2. "Provider= OraOLEDB.Oracle;Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"

Open trusted connection to Oracle database

"Provider= OraOLEDB.Oracle;DataSource=Your_Oracle_Database;OSAuthent=1;"

Cara VB.NET Koneksi ke Database MS Access

MS Access connection strings

MS Access ODBC connection strings

Standard Security:

"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username;Pwd=Your_Password;"

Workgroup:

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

Exclusive "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb; Exclusive=1; Uid=Your_Username; Pwd=Your_Password;"

MS Access OLE DB & OleDbConnection (.NET framework) connection strings

Open connection to Access database:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="

Open connection to Access database using Workgroup (System database):

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:System Database=c:\App1\Your_System_Database_Name.mdw"

Open connection to password protected Access database:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"

Open connection to Access database located on a network share:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\Your_Database_Name.mdb"

Open connection to Access database located on a remote server:

"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"

Cara Ekspor DataGridView ke Excel

Dalam beberapa aplikasi, banyak yang menginginkan hasil pengolahan data yang ditampilkan di grid. langsung di Printout menggunakan excel. selain mudah untuk di print juga dapat diedit jika terjadi kesalahan input. di sini kita akan membahas itu. dan ini List Prognya.

Imports System.Data

Imports System.Data.SqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _

        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection

        Dim connectionString As String

        Dim sql As String

        connectionString = "data source=servername;" & _

        "initial catalog=databasename;user id=username;password=password;"

        cnn = New SqlConnection(connectionString)

        cnn.Open()

        sql = "SELECT * FROM Product"

        Dim dscmd As New SqlDataAdapter(sql, cnn)

        Dim ds As New DataSet

        dscmd.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

        cnn.Close()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button2.Click

        Dim xlApp As Excel.Application

        Dim xlWorkBook As Excel.Workbook

        Dim xlWorkSheet As Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value

        Dim i As Integer

        Dim j As Integer

        xlApp = New Excel.ApplicationClass

        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2

            For j = 0 To DataGridView1.ColumnCount - 1

                xlWorkSheet.Cells(i + 1, j + 1) = _

                    DataGridView1(j, i).Value.ToString()

            Next

        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")

        xlWorkBook.Close()

        xlApp.Quit()

        releaseObject(xlApp)

        releaseObject(xlWorkBook)

        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

End Class

Demikian ekspor data ke excel. silahkan dicoba !!!

Memasukkan Hasil Input di TextBox ke DataGridView dengan sort Descending

Kontrol DataGridView digunakan untuk menampilkan data. Baik Data dari hasil query maupun dari textbox. Kita akan membahas bagaimana caranya memasukkan data ke datagridview dari textbox dengan hasil berurut secara descending. Jadi setiap data yang diinput baru akan ditempatkan di baris pertama.
 
Kita buat form dan komponennya lihat gambar :

 
 dan ketik list Programnya sebagai berikut :  

Public Class Form2  
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        gridme.Rows.Add(1)

        gridme.Rows(gridme.RowCount - 2).Cells(0).Value = CStr(gridme.RowCount - 1)
        gridme.Rows(gridme.RowCount - 2).Cells(1).Value = TextBox1.Text
        gridme.Rows(gridme.RowCount - 2).Cells(2).Value = TextBox2.Text
        gridme.Rows(gridme.RowCount - 2).Cells(3).Value = TextBox3.Text
        gridme.Rows(gridme.RowCount - 2).Cells(4).Value = TextBox4.Text
        gridme.Rows(gridme.RowCount - 2).Cells(5).Value = TextBox5.Text  

        gridme.Sort(New comparer)
        gridme.Update()


    End Sub


   Public Class comparer Implements System.Collections.IComparer

      Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
          Dim dataX As DataGridViewRow = DirectCast(x, DataGridViewRow)
          Dim dataY As DataGridViewRow = DirectCast(y, DataGridViewRow)


'sort descending
          Return CInt(dataY.Cells(0).Value).CompareTo(CInt(dataX.Cells(0).Value))

     End Function
  End Class 
End Class  

 
Ini adalah hasil akhirnya.
selamat mencoba.