Menampilkan Data dari SQL Server 2008 di GridView


Setelah berhasil mengkoneksikan VB. NET ke SQL Server, dilanjutkan menampilkan data di gridview.

1.Tambahkan form search.aspx untuk membuat komponen pencarian data di gridview


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="search.aspx.vb" Inherits="search" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 100%;
}
.style2
{
width: 376px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
ID BARANG</td>
<td>
<asp:TextBox ID="txtIDBarang" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
NAMA BARANG</td>
<td>
<asp:TextBox ID="txtNamaBarang" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:Button ID="btnCari" runat="server" Text="Cari" />
</td>
</tr>
</table>
</div>
<asp:GridView ID="GridViewBarang" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"
ForeColor="#333333" GridLines="None"
Width="100%" PageSize="2">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333"/>
<Columns>
<asp:BoundField DataField="ID_BARANG" HeaderText="ID BARANG"
SortExpression="ID_BARANG" />
<asp:BoundField DataField="NAMA_BARANG" HeaderText="NAMA BARANG"
SortExpression="NAMA_BARANG" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</form>
</body>
</html>

Buka search.aspx.vb atau Klik kanan di search.aspx pilih view code

'library
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Partial Class search
Inherits System.Web.UI.Page
'Declare variable
Dim strCon As String = ConfigurationManager.ConnectionStrings("DB_INVENTORIConnectionString").ToString
Dim koneksi As New SqlConnection(strCon)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
IsiGridViewBarang()
End If
End Sub
'method isi gridview barang
Sub IsiGridViewBarang()
Dim sql As String = "SELECT * FROM t_barang"
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As New DataView

Dim strQuery As String = ""

If txtIDBarang.Text <> "" Then
strQuery = "ID_BARANG LIKE '%" & txtIDBarang.Text & "%'"
End If

If txtNamaBarang.Text <> "" Then
If strQuery <> "" Then
strQuery += " and NAMA_BARANG like '%" & txtNamaBarang.Text & "%'"
Else
strQuery = "NAMA_BARANG like '%" & txtNamaBarang.Text & "%'"
End If
End If

If strQuery <> "" Then
sql += " WHERE " & strQuery
End If

Try
da.SelectCommand = New SqlCommand(sql, koneksi)
da.Fill(ds, "t_barang")
dv = New DataView(ds.Tables("t_barang"))
Cache("Barang") = dv
GridViewBarang.DataSource = dv
GridViewBarang.DataBind()
Catch ex As Exception
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data tidak ada');", True)
End Try
End Sub
Sub ReadData(Optional ByVal xSort As String = "")
Dim dv As New DataView
dv = Cache("Barang")

If dv Is Nothing Then Exit Sub

If xSort <> "" Then
dv.Sort = xSort
End If

GridViewBarang.DataSource = dv
GridViewBarang.DataBind()
End Sub

Private Sub GridViewBarang_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridViewBarang.PageIndexChanging
GridViewBarang.PageIndex = e.NewPageIndex
ReadData()
End Sub
Private Sub GridViewBarang_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridViewBarang.Sorting
Dim xsort As String = ""
xsort = ViewState("ViewStateBarang")
If xsort = "ASC" Then
xsort = "DESC"
Else
xsort = "ASC"
End If
ViewState("ViewStateBarang") = xsort

ReadData(e.SortExpression & " " & xsort)
End Sub
'method mencari barang
Protected Sub btnCari_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCari.Click
IsiGridViewBarang()
End Sub
End Class

Klik kanan di search.aspx pilih Set as Start Page, run program atau tekan F5 maka akan muncul program di browser seperti gambar berikut

show data di gridview

Data barang tampil di gridview. Untuk pencarian data, isikan ID BARANG dan/atau NAMA BARANG kemudian tekan button Cari.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s