Input Data VB.net dengan menggunakan SQL

SQL {

create database dbPerpustakaan
go
use dbPerpustakaan
go
create table Mahasiswa
(
NPM char(10) not null  primary key,
Nama varchar(40) not null,
Jenis_Kelamin char(1) check(jenis_kelamin in ('P','L'))
)
go
}

CONEECTION(CLASS){
Public Class Connection
Public cmd As SqlClient.SqlCommand
Public cn As SqlClient.SqlConnection
Public da As SqlClient.SqlDataAdapter
Public dr As SqlClient.SqlDataReader
Public ds As DataSet

Public Sub OpenConn()
'312-08\SQLEXPRESS
cn = New SqlClient.SqlConnection
cn.ConnectionString = "Data Source =313-19\SQLEXPRESS(dari Komputer anda) ;" & _
"initial catalog=dbPerpustakaan; Integrated security = TRUE"
cn.Open()

End Sub

Public Sub MakeCommand(ByVal xSql As String)
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = xSql

End Sub
Public Sub closeConn()
If Not cn Is Nothing Then
cn.Close()
cn = Nothing
End If
End Sub
End Class

}




FORM
Public Class frmMhs
Dim Mhs As New Connection
Dim sql As String
Private Sub cmdSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSimpan.Click
Dim jenkel As String
If txtnpm.Text.Trim.Length = 0 Then
MessageBox.Show("Form NPM harus diisi ", "NPM", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtnpm.Focus() : Exit Sub
ElseIf txtnama.Text.Trim.Length = 0 Then
MessageBox.Show("Form Nama harus diisi ", "Nama Mahasiswa", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtnama.Focus() : Exit Sub
End If

If rbl.Checked = True Then
jenkel = "L"
Else
jenkel = "P"
End If

sql = "select * from mahasiswa where npm='" & txtnpm.Text & "'"
Mhs.OpenConn()
Mhs.MakeCommand(sql)
Mhs.dr = Mhs.cmd.ExecuteReader
If Mhs.dr.Read Then
sql = "update mahasiswa set NPM ='" & txtnpm.Text & _
"', Nama ='" & txtnama.Text & _
"', Jenis_Kelamin = '" & jenkel & _
"' where NPM = '" & txtnpm.Text & "'"
Mhs.MakeCommand(sql)
MessageBox.Show("Update sukses !", _
"Information", MessageBoxButtons.OK, _
MessageBoxIcon.Information)
Else
sql = "insert into mahasiswa values ('" & txtnpm.Text & "','" & _
txtnama.Text & "','" & jenkel & "')"
Mhs.MakeCommand(sql)
MessageBox.Show("Simpan data berhasil !", "Information", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Mhs.dr.Close()
Mhs.cmd.ExecuteNonQuery()
Mhs.cmd = Nothing
Mhs.closeConn() : bersih() : txtnpm.Focus()

End Sub

Private Sub cmdHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHapus.Click
Dim tanya As String = InputBox("Masukkan NPM !", "Informasi")
If tanya = "" Then Exit Sub

sql = "select * from mahasiswa where npm='" & tanya & "'"
Mhs.OpenConn()
Mhs.MakeCommand(sql)
Mhs.dr = Mhs.cmd.ExecuteReader

If Mhs.dr.Read Then
txtnpm.Text = Mhs.dr!npm
txtnama.Text = Mhs.dr!nama
If Mhs.dr!jenis_kelamin = "L" Then
rbl.Checked = True
Else
rbp.Checked = True
End If
Mhs.dr.Close()
Dim confirm As String = ""
confirm = MessageBox.Show("Hapus dari data Mahasiswa? ", "Info", _
MessageBoxButtons.YesNo, _
MessageBoxIcon.Question)
If confirm = vbYes Then
sql = "delete from mahasiswa where npm = '" & txtnpm.Text & "'"
Mhs.MakeCommand(sql)
Mhs.cmd.ExecuteNonQuery()
End If
End If

Mhs.dr.Close() : Mhs.closeConn() : bersih()
End Sub

Private Sub bersih()
txtnpm.Text = "" : txtnama.Text = "" : rbl.Checked = False : rbp.Checked = False
End Sub

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
bersih()
End Sub

Private Sub frmMhs_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
Panel2.Width = Me.Width
End Sub

Private Sub frmMhs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class

Posted in: