Insert Data ke SQL Server 2008 dengan VB. Net


Setelah berhasil menampilkan data di gridview, dilanjutkan dengan insert data.

1. Tambahkan form insert.aspx untuk membuat komponen insert data.

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

<!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 id="Head1" runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 100%;
}
.style2
{
width: 371px;
}
</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="btnSimpan" runat="server" Text="Simpan" />
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td></td>
</tr>
<tr>
<td colspan="2">
<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></td>
</tr>
</table>

</div>
</form>
</body>
</html>

2.Buka insert.aspx.vb atau klik kanan di insert.aspx pilih view code

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

Partial Class insert
Inherits System.Web.UI.Page
' declare variable
Public koneksiString As String = "Server=.;Database=db_inventori;User Id=sa;Password=t3lk0ms3l"
Dim koneksi As New SqlConnection(koneksiString)
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
IsiGrid()
End If
End Sub
'Method Isi Gridview Barang
Sub IsiGrid()
Dim sql As String = "select * from t_barang"
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As New DataView

Try
da.SelectCommand = New SqlCommand(sql, koneksi)
da.Fill(ds, "t_barang")
dv = New DataView(ds.Tables("t_barang"))
Cache("t_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

'Method Simpan data ke tabel t_barang
Protected Sub btnSimpan_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
If txtIDBarang.Text <> "" Then
koneksi.Open()
perintah.Connection = koneksi
perintah.CommandType = CommandType.Text
perintah.CommandText = "INSERT INTO t_barang(id_barang, nama_barang) VALUES('" & txtIDBarang.Text & "','" & txtNamaBarang.Text & "')"
Try
perintah.ExecuteNonQuery()
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data Sukses disimpan');", True)
IsiGrid()
Catch ex As Exception
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('Data Gagal disimpan');", True)
End Try
koneksi.Close()
Else
ClientScript.RegisterStartupScript(Me.GetType(), "MyAlert", "alert('ID Barang tidak boleh kosong');", True)
End If
End Sub

Sub ReadData(Optional ByVal xSort As String = "")
Dim dv As New DataView
dv = Cache("t_barang")

If dv Is Nothing Then Exit Sub

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

GridViewBarang.DataSource = dv
GridViewBarang.DataBind()
End Sub
'method paging gridview
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

'method sorting data di gridview
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("ViewSatateBarang")
If xsort = "ASC" Then
xsort = "DESC"
Else
xsort = "ASC"
End If
ViewState("ViewSatateBarang") = xsort

ReadData(e.SortExpression & " " & xsort)
End Sub
End Class

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

insert data

Isikan data di textbox, tekan tombol Simpan

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