Belajar VB.NET #2

Back

Project :

• Mengkoneksikan Database ke VB.net
• Menampilkan data dari database ke datagrid
• Membuat perintah Add, save, dan delete, exit, time, date, day

Program yang digunakan :

• Microsoft Visual Studio 2005
• Sql server 2005 Management Studio Express

Langkah-langkah :

• Instal Software Keduanya
• Masuk ke “Sql server 2005 management studio express”
  • Buat Database baru dengan nama “BukuTelepon”
  • Buat table baru dengan nama “BT”
  • Isi BT dengan id (char, primary key), Nama (varchar), Alamat varchar), NoTLP varchar), NoHP varchar)
  • Save
  • Masuk Microsoft Visual Studio 2005
    • File-New-Project
    • Pilih Windows Apllication  (Name : “”) (Location:”tempat menyimpan project”)
    • Mengkoneksikan database dengan VB :
  • Di kiri atas pilih “server exploler”
  • Connect to database
  • Pilih computer mana yg akan digunakan sebagai server pada “server name”
  • Lalu pilih nama database yg tadi telah dibuat pada “select or enter a database name”
  • Ok
    • Buat design form
      • Masukan 1 datagrid (name :DG1)
      • 3 label (name :txtjam, txthari, txttanggal)
      • 1 timer (enabled diganti jadi true, interval : 1000)
      • 4 Button (name :btnnew, btnsave, btndelete, btnexit)
      • 5 textbox (name :txtid, txtnama, txtalamat, txttlp, txthp)

vb.net sora

CODING

Imports System.Data.SqlClient

Public Class Form1

Private strConn As String = “Server=SIL-IT1\SQLEXPRESS;Database=BukuTelepon;Integrated Security=SSPI;”

Private IsAddNew As Boolean

‘//Script Untuk jam, hari, dan tanggal

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Dim harike As Integer

harike = DateTime.Now.DayOfWeek

If harike = 1 Then

Me.txthari.Text = “Senin”

ElseIf harike = 2 Then

Me.txthari.Text = “Selasa”

ElseIf harike = 3 Then

Me.txthari.Text = “Rabu”

ElseIf harike = 4 Then

Me.txthari.Text = “Kamis”

ElseIf harike = 5 Then

Me.txthari.Text = “Jumat”

ElseIf harike = 6 Then

Me.txthari.Text = “Sabtu”

ElseIf harike = 7 Then

Me.txthari.Text = “Minggu”

End If

Me.txtjam.Text = DateTime.Now.ToLongTimeString

Me.txttanggal.Text = DateTime.Now.ToLongDateString

End Sub

‘// Script untuk melakukan refresh

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

LoadGrid()

End Sub

‘// untuk mengkoneksikan database ke datagrid

Private Sub LoadGrid()

Dim conn As New SqlConnection(strConn)

Dim MyCommand As New SqlCommand

Dim DR As New SqlDataAdapter

Dim DS As New DataSet

Dim DT As New DataTable

‘//panggil data dari database ke datagrid

MyCommand.Connection = conn

Try

MyCommand = conn.CreateCommand

MyCommand.CommandText = “SELECT * FROM BT”

DR.SelectCommand = MyCommand

DR.Fill(DT)

DG1.DataSource = DT

‘DG1.DataMember = “BT”

DG1.ReadOnly = True

Catch ex As Exception

MsgBox(“Error: ” & ex.Source & ” : ” & ex.Message, MsgBoxStyle.OkOnly, ” Connection Error!! “)

End Try

End Sub

‘// Script untuk Delete

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Dim check As New Integer

Dim conn As New SqlConnection(strConn)

Dim MyCommand As New SqlCommand

If txtid.Text <> “” Then

End If

If MsgBox(“Apakah ingin menghapus data dengan id : ” & txtid.Text & ” ?”, MsgBoxStyle.OkCancel, “Info”) = MsgBoxResult.Ok Then

Try

conn.Open()

‘MyCommand = conn.CreateCommand

MyCommand.Connection = conn

MyCommand.CommandText = “DELETE FROM BT WHERE ID ='” & Trim(txtid.Text) & “‘”

‘MessageBox.Show(MyCommand.CommandText)

check = MyCommand.ExecuteNonQuery()

If check > 0 Then

MsgBox(“Data dengan id ” & Trim(txtid.Text) & ” Berhasil dihapus”, MsgBoxStyle.OkOnly, “Info”)

Else

MsgBox(“Data dengan id ” & Trim(txtid.Text) & ” Gagal dihapus”, MsgBoxStyle.OkOnly, “Info”)

End If

conn.Close()

Catch ex As Exception

MsgBox(“Error: ” & ex.Source & “: ” & ex.Message, MsgBoxStyle.OkOnly, “koneksi error !!”)

End Try

End If

‘MsgBox(“berhasil!!”, MsgBoxStyle.OkOnly, “Info”)

‘Me.Refresh()

LoadGrid()

End Sub

‘script untuk save dan Update

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

Dim check As New Integer

Dim conn As New SqlConnection(strConn)

Dim MyCommand1 As New SqlCommand

Dim DR As New SqlDataAdapter

‘Validasi

If txtid.Text = “” Or txtnama.Text = “” Or txtalamat.Text = “” Or txttlp.Text = “” Or txthp.Text = “” Then

MsgBox(“masukan data”, MsgBoxStyle.OkOnly)

Else

If MsgBox(” simpan ” & txtid.Text & ” ?”, MsgBoxStyle.OkCancel, ” save”) = MsgBoxResult.Cancel Then

‘do nothing

Else

Try

conn.Open()

‘If (DT.Rows.Count > 0) Then

‘    MsgBox(“data” & Trim(txtid.Text) & ” sudah ada di database “, MsgBoxStyle.OkOnly, “message : “)

‘Else

MyCommand1.Connection = conn

If IsAddNew = True Then

MyCommand1.CommandText = “INSERT INTO BT (id, Nama, Alamat, NoTLP, NoHP) VALUES(‘” & Trim(txtid.Text) & “‘,'” & Trim(txtnama.Text) & “‘,'” & Trim(txtalamat.Text) & “‘,'” & Trim(txttlp.Text) & “‘,'” & Trim(txthp.Text) & “‘)”

Else

MyCommand1.CommandText = “UPDATE BT SET id ='” & Trim(txtid.Text) & “‘,Nama ='” & Trim(txtnama.Text) & “‘, Alamat= ‘” & Trim(txtalamat.Text) & “‘,NoTLP= ‘” & Trim(txttlp.Text) & “‘ , NoHP='” & Trim(txthp.Text) & “‘ WHERE Id ='” & Trim(txtid.Text) & “‘”

End If

‘Cek apakah berhasil di update / insert

check = MyCommand1.ExecuteNonQuery()

If check > 0 Then

MsgBox(“Save” & Trim(txtid.Text) & “Success”, MsgBoxStyle.OkOnly, “SAVE:”)

Else

MsgBox(“Save” & Trim(txtid.Text) & “Failure”, MsgBoxStyle.OkOnly, “SAVE:”)

End If

conn.Close()

‘End If

Catch ex As Exception

MsgBox(“error:” & ex.Source & “:” & ex.Message, MsgBoxStyle.OkOnly, “koneksi error”)

End Try

End If

End If

LoadGrid()

End Sub

‘//script untuk NEW

Private Sub btnnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnew.Click

Dim p As New PersonInfo

p.ID = “”

p.Name = “”

p.Alamat = “”

p.NoTLP = “”

p.NoHP = “”

IsAddNew = True

End Sub

End Class

Back