use buku
CREATE TABLE buku (NoBuku varchar(10) PRIMARY KEY not null, Judul varchar(200), Pengarang varchar(50), Penerbit varchar(50), ISBN varchar(50), CetakanKe varchar(5), Ringkasan text )
insert into buku values
('00001', 'Pengantar Ilmu Statistik I', 'Drs. Ilham Rasyad', 'Gita Madya', '1234567', '1', 'Statistik Dasar, Probabilitas, dll'),
('00002', 'Pengantar Kalkulus I', 'Drs. Hidayat', 'Madya Daya', '42523', '1', 'Perhitungan')
di VB.Net
Create New Project dengan nama sesukanya (Contoh: Pustaka) pilih ASP.NET Web Application.
Isi Source di Default.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="WebApplication1._Default" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
Di GridView Choose Data Source : SqlDataSource1
Configure Data Source di SqlDataSource :
Choose Your Data Connection:
Pilih Server Name dan Database-nya:
Pilih Next:
Sehingga di Web.Config akan tampil connectionStrings-nya:
Buat Form dengan nama FrmListBuku.aspx
Source-nya:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="FrmListBuku.aspx.vb" Inherits="Pustaka.FrmListBuku" %>
<!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>Buku Pustaka</title>
<style type="text/css">
.style1
{
width: 100%;
}
.style2
{
width: 141px;
}
.style3
{
font-size: x-large;
font-weight: bold;
}
</style>
</head>
<body style="font-family: Calibri">
<form id="form1" runat="server">
<div>
<table class="style1">
<tr>
<td colspan="2" class="style3">
PENCARIAN DATA BARANG</td>
</tr>
<tr>
<td class="style2">
Judul</td>
<td>
<asp:TextBox ID="TxtJudul" runat="server" MaxLength="200" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Pengarang</td>
<td>
<asp:TextBox ID="TxtPengarang" runat="server" MaxLength="50" Width="255px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Penerbit</td>
<td>
<asp:TextBox ID="TxtPenerbit" runat="server" MaxLength="50" Width="255px"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="BtnCari" runat="server" Text="Refresh" />
<asp:Button ID="BtnTambah" runat="server" Text="Tambah" />
</td>
</tr>
</table>
</div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="NoBuku" ForeColor="#333333" GridLines="None" PageSize="5">
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="NoBuku" HeaderText="No Buku" ReadOnly="True"
SortExpression="NoBuku" />
<asp:BoundField DataField="Judul" HeaderText="Judul" SortExpression="Judul" />
<asp:BoundField DataField="Pengarang" HeaderText="Pengarang"
SortExpression="Pengarang" />
<asp:BoundField DataField="Penerbit" HeaderText="Penerbit"
SortExpression="Penerbit" />
<asp:BoundField DataField="ISBN" HeaderText="ISBN" SortExpression="ISBN" />
<asp:BoundField DataField="CetakanKe" HeaderText="Cetakan Ke"
SortExpression="CetakanKe" />
<asp:BoundField DataField="Ringkasan" HeaderText="Ringkasan"
SortExpression="Ringkasan" />
<asp:CommandField ShowSelectButton="True" />
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
View Code-nya :
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Public Class FrmListBuku
Inherits System.Web.UI.Page
Dim ConStr As String = ConfigurationManager.ConnectionStrings("bukuConnectionString").ToString
Dim Koneksi As New SqlConnection(ConStr)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
IsiGrid()
End If
End Sub
Sub IsiGrid()
Dim sql As String = "select * from buku"
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As New DataView
Dim kr As String = ""
If TxtJudul.Text <> "" Then
kr = "Judul like '%" & TxtJudul.Text & "%'"
End If
If TxtPengarang.Text <> "" Then
If kr <> "" Then
kr += " or Pengarang like '%" & TxtPengarang.Text & "%'"
Else
kr = "Pengarang like '%" & TxtPengarang.Text & "%'"
End If
End If
If TxtPenerbit.Text <> "" Then
If kr <> "" Then
kr += " or Penerbit like '%" & TxtPenerbit.Text & "%'"
Else
kr = "Penerbit like '%" & TxtPenerbit.Text & "%'"
End If
End If
If kr <> "" Then
sql += " where " & kr
End If
Try
da.SelectCommand = New SqlCommand(sql, Koneksi)
da.Fill(ds, "Buku")
dv = New DataView(ds.Tables("Buku"))
Cache("Buku") = dv
GridView1.DataSource = dv
GridView1.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("Buku")
If dv Is Nothing Then
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data sudah habis');", True)
End If
If xSort <> "" Then dv.Sort = xSort
GridView1.DataSource = dv
GridView1.DataBind()
End Sub
Private Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
Dim Brs As GridViewRow = GridView1.SelectedRow
Dim Data As String = Brs.Cells(0).Text
Response.Redirect("FrmBuku.aspx?ID=" & Data & "")
End Sub
Private Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
ReadData()
End Sub
Private Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
Dim xSort As String = ""
xSort = ViewState("Buku")
If xSort = "Asc" Then
xSort = "Desc"
Else
xSort = "Asc"
End If
ViewState("Buku") = xSort
ReadData(e.SortExpression & " " & xSort)
End Sub
Protected Sub BtnCari_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnCari.Click
If TxtJudul.Text <> "" And TxtPengarang.Text <> "" And TxtPenerbit.Text <> "" Then
IsiGrid()
Else
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data Pencarian tidak boleh kosong');", True)
End If
End Sub
Protected Sub BtnTambah_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnTambah.Click
Response.Redirect("FrmBuku.aspx")
End Sub
End Class
Buat Form dengan nama FrmBuku.aspx
Source-nya:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="FrmBuku.aspx.vb" Inherits="Pustaka.FrmBuku" %>
<!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>Form Buku</title>
<style type="text/css">
.style1
{
width: 100%;
}
.style2
{
}
.style3
{
width: 148px;
}
.style4
{
width: 148px;
height: 26px;
}
.style5
{
height: 26px;
}
.style6
{
width: 148px;
height: 30px;
}
.style7
{
height: 30px;
}
</style>
</head>
<body style="font-family: Calibri">
<form id="form1" runat="server">
<div>
<table class="style1">
<tr>
<td colspan="2" style="font-weight: 700; font-size: x-large">
PEMASUKAN DATA BUKU</td>
</tr>
<tr>
<td class="style3">
No. Buku</td>
<td>
<asp:TextBox ID="TxtNoBuku" runat="server" MaxLength="10"></asp:TextBox>
<asp:TextBox ID="TxtNoBukuLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
Judul</td>
<td>
<asp:TextBox ID="TxtJudul" runat="server" Width="500px" MaxLength="200"></asp:TextBox>
<asp:TextBox ID="TxtJudulLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style4">
Pengarang</td>
<td class="style5">
<asp:TextBox ID="TxtPengarang" runat="server" Width="255px" MaxLength="50"></asp:TextBox>
<asp:TextBox ID="TxtPengarangLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style6">
Penerbit</td>
<td class="style7">
<asp:TextBox ID="TxtPenerbit" runat="server" Width="255" MaxLength="50"></asp:TextBox>
<asp:TextBox ID="TxtPenerbitLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
ISBN</td>
<td>
<asp:TextBox ID="TxtISBN" runat="server" MaxLength="50" Width="255px"></asp:TextBox>
<asp:TextBox ID="TxtISBNLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
Cetakan Ke</td>
<td>
<asp:TextBox ID="TxtCetakanKe" runat="server" MaxLength="10"></asp:TextBox>
<asp:TextBox ID="TxtCetakanKeLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
Ringkasan</td>
<td>
<asp:TextBox ID="TxtRingkasan" runat="server" Width="500px"></asp:TextBox>
<asp:TextBox ID="TxtRingkasanLama" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
<asp:Button ID="BtnSimpan" runat="server" Text="Simpan" />
<asp:Button ID="BtnBatal" runat="server" Text="Batal" />
</td>
<td class="style2">
<asp:TextBox ID="TxtKode" runat="server" Visible="False"></asp:TextBox>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
View Code-nya :
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Public Class FrmBuku
Inherits System.Web.UI.Page
Dim ConStr As String = ConfigurationManager.ConnectionStrings("bukuConnectionString").ToString
Dim Koneksi As New SqlConnection(ConStr)
Dim perintah As New SqlCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
TxtKode.Text = "Insert"
TxtNoBuku.Text = Request.QueryString("ID")
If TxtNoBuku.Text <> "" Then
IsiData()
End If
End If
End Sub
'menampilkan data menggunakan data reader
Sub IsiData()
Dim sql As String = "SELECT * FROM Buku WHERE NoBuku='"& TxtNoBuku.Text & "'"
Dim dr As SqlDataReader
Dim Comm As New SqlCommand
Try
Koneksi.Open()
Comm = New SqlCommand(sql, Koneksi)
dr = Comm.ExecuteReader
dr.Read()
If dr.HasRows Then
TxtNoBuku.Text = dr("NoBuku").ToString
TxtNoBukuLama.Text = dr("NoBuku").ToString
TxtJudul.Text = dr("Judul").ToString
TxtJudulLama.Text = dr("Judul").ToString
TxtPengarang.Text = dr("Pengarang").ToString
TxtPengarangLama.Text = dr("Pengarang").ToString
TxtPenerbit.Text = dr("Penerbit").ToString
TxtPenerbitLama.Text = dr("Penerbit").ToString
TxtISBN.Text = dr("ISBN").ToString
TxtISBNLama.Text = dr("ISBN").ToString
TxtCetakanKe.Text = dr("CetakanKe").ToString
TxtCetakanKeLama.Text = dr("CetakanKe").ToString
TxtRingkasan.Text = dr("Ringkasan").ToString
TxtRingkasanLama.Text = dr("Ringkasan").ToString
TxtKode.Text = "Update"
Else
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data yang di cari tidak ada')", True)
End If
Catch ex As Exception
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Pencarian Data gagal')", True)
Finally
Koneksi.Close()
End Try
End Sub
Protected Sub BtnBatal_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnBatal.Click
Response.Redirect("FrmListBuku.aspx")
End Sub
Protected Sub BtnSimpan_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSimpan.Click
If TxtNoBuku.Text <> "" And TxtJudul.Text <> "" And TxtPengarang.Text <> "" Then
If TxtKode.Text = "Insert" Then
'Insert Data
Koneksi.Open()
perintah.Connection = Koneksi
perintah.CommandType = CommandType.Text
perintah.CommandText = "INSERT INTO Buku VALUES('" &TxtNoBuku.Text & "','" &TxtJudul.Text & "','" &TxtPengarang.Text & "','"& TxtPenerbit.Text & "','"& TxtISBN.Text & "','"& TxtCetakanKe.Text & "','"& TxtRingkasan.Text & "')"
Try
perintah.ExecuteNonQuery()
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data " & TxtNoBuku.Text & " Sukses di-Simpan');", True)
Catch ex As Exception
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data Gagal disimpan');", True)
End Try
Koneksi.Close()
Else
'Update Data
If TxtNoBuku.Text = TxtNoBukuLama.Text And TxtJudul.Text = TxtJudulLama.Text And TxtPengarang.Text = TxtPengarangLama.Text And TxtPenerbit.Text = TxtPenerbitLama.Text And TxtISBN.Text = TxtISBNLama.Text And TxtCetakanKe.Text = TxtCetakanKeLama.Text And TxtRingkasan.Text = TxtRingkasanLama.Text Then
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data tidak berubah');", True)
Else
Koneksi.Open()
perintah.Connection = Koneksi
perintah.CommandType = CommandType.Text
perintah.CommandText = "UPDATE Buku SET NoBuku='" &TxtNoBuku.Text & "',Judul='"& TxtJudul.Text & "',Pengarang='"& TxtPengarang.Text & "',Penerbit='"& TxtPenerbit.Text & "',ISBN='"& TxtISBN.Text & "',CetakanKe='"& TxtCetakanKe.Text & "',Ringkasan='"& TxtRingkasan.Text & "' WHERE NoBuku='" & TxtNoBukuLama.Text & "'"
Try
perintah.ExecuteNonQuery()
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data " &TxtNoBuku.Text & " Sukses di-Update');", True)
Catch ex As Exception
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data Gagal disimpan');", True)
End Try
Koneksi.Close()
End If
End If
Else
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data tidak boleh kosong');", True)
End If
End Sub
End Class
Tidak ada komentar:
Posting Komentar