Adres Kayıt Defteri Örnek Uygulama

Veritabanını aşağıdaki şekilde düzenliyelim.





Formu düzenliyelim.



Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1

Dim Conn As SqlConnection
Dim CMD As SqlCommand
Dim ADPKisi As New SqlDataAdapter
Dim ADPTel As New SqlDataAdapter
Dim DS As DataSet = New DataSet
Dim BSKisi As BindingSource
Dim BSTel As BindingSource
Dim CB As SqlCommandBuilder
Dim KisiSQL As String
Dim TelSQL As String
Dim KisiIdNo As Integer
Dim TelIdNo As Integer
Dim KisiCHNG As Boolean
Dim Cevap As String
Dim Vazgec As Boolean

Private Sub idnoBul()
Dim oku As SqlDataReader

If CONN.State <> ConnectionState.Open Then CONN.Open()

CMD = New SqlCommand("SELECT ident_current('Kisiler') AS kisiID", CONN)
KisiIDNo = CMD.ExecuteScalar
If KisiIDNo = 1 Then
CMD = New SqlCommand("SELECT * From Kisiler", CONN)
oku = CMD.ExecuteReader
oku.Read()
If oku.HasRows = False Then KisiIdNo = 0
oku.Close()
End If


CMD = New SqlCommand("SELECT ident_current('Telefon') AS TelID", CONN)

TelIDNo = CMD.ExecuteScalar

If TelIDNo = 1 Then
CMD = New SqlCommand("SELECT * From Telefon", CONN)
oku = CMD.ExecuteReader
oku.Read()
If oku.HasRows = False Then TelIdNo = 0
oku.Close()
End If

CONN.Close()

End Sub
Sub ConnStrTanimla()
Dim ConnStr As New SqlConnectionStringBuilder()
ConnStr.DataSource = "Ayaydın-PC\SQLEXPRESS"
ConnStr.AttachDBFilename = "|DataDirectory|AdresDefteri.mdf"
ConnStr.IntegratedSecurity = True 'Windows Kimlik Bilgileri
Conn = New SqlConnection(ConnStr.ConnectionString)
End Sub

Private Sub YeniFormHazirla()
DtpTarih.Value = Today
txtTCNo.Text = ""
txtAdi.Text = ""
txtSoyadi.Text = ""
txtAdres.Text = ""
txtSemt.Text = ""
txtSehir.Text = ""
BtnYeni.Text = "VAZGEÇ"
BtnSil.Enabled = False

KisiCHNG = False
End Sub

Private Sub KayitKontrol()
If KisiCHNG = True Then
Cevap = MsgBox("Değişiklikleriniz Kayıt Edilsin Mi ? ", MsgBoxStyle.YesNoCancel)
If Cevap = vbCancel Then
Vazgec = True
ElseIf Cevap = vbNo Then
Vazgec = False
Exit Sub
ElseIf Cevap = vbYes Then
Vazgec = False
KisiKaydet()
End If
End If
End Sub

Private Sub KisiKaydet()
VeriKontrol()
If Vazgec = True Then Exit Sub
If BtnYeni.Text = "VAZGEÇ" Then BSKisi.AddNew()
VeriYaz()
VeriTabaniGuncelle()
MsgBox("Kayıt Edildi")
End Sub

Private Sub VeriKontrol()
If Len(txtTCNo.Text) <> 10 Then
MsgBox("Tc Kimlik Numarasını kontrol ediniz.")
Vazgec = True
Exit Sub
ElseIf txtAdi.Text = "" Then
MsgBox("Kisi Adı Girmelisiniz.")
Vazgec = True
Exit Sub
ElseIf txtSoyadi.Text = "" Then
MsgBox("Kisi Soyadı Girmelisiniz.")
Vazgec = True
Exit Sub
Else
Vazgec = False
End If
End Sub

Private Sub VeriYaz()
BSKisi.Current("TcNo") = Val(txtTCNo.Text)
BSKisi.Current("Adi") = txtAdi.Text
BSKisi.Current("Soyadi") = txtSoyadi.Text
BSKisi.Current("Adres") = txtAdres.Text
BSKisi.Current("Semt") = txtSemt.Text
BSKisi.Current("Sehir") = txtSehir.Text
BSKisi.Current("GirisTarihi") = DtpTarih.Value
BSKisi.EndEdit()
End Sub

Private Sub VeriOku()
txtTCNo.Text = BSKisi.Current("TcNo")
txtAdi.Text = BSKisi.Current("Adi")
txtSoyadi.Text = BSKisi.Current("Soyadi")
txtAdres.Text = BSKisi.Current("Adres")
txtSemt.Text = BSKisi.Current("Semt")
txtSehir.Text = BSKisi.Current("Sehir")
DtpTarih.Value = BSKisi.Current("GirisTarihi")

KisiCHNG = False
BtnYeni.Text = "YENİ"
BtnSil.Enabled = True
End Sub

Private Sub VeriTabaniGuncelle()

CB = New SqlCommandBuilder
CB.DataAdapter = ADPKisi

ADPKisi.Update(DS, "Kisiler")

BSTel.EndEdit()

CB = New SqlCommandBuilder
CB.DataAdapter = ADPTel
ADPTel.Update(DS, "telefon")
End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
KayitKontrol()
If Vazgec = True Then e.Cancel = True
End Sub


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ConnStrTanimla()
idnoBul()
If Conn.State <> ConnectionState.Open Then Conn.Open()

ADPKisi.SelectCommand = New SqlCommand("Select * From Kisiler", Conn)
ADPTel.SelectCommand = New SqlCommand("Select * From Telefon", Conn)

ADPKisi.Fill(DS, "Kisiler")
ADPTel.Fill(DS, "Telefon")

Conn.Close()


DS.Tables("Kisiler").Columns("KisiID").AutoIncrement = True
DS.Tables("Telefon").Columns("TelID").AutoIncrement = True

DS.Tables("Kisiler").Columns("KisiID").AutoIncrementSeed = KisiIdNo + 1
DS.Tables("Telefon").Columns("TelID").AutoIncrementSeed = TelIdNo + 1

BSKisi = New BindingSource(DS, "Kisiler")
BSTel = New BindingSource(DS, "Telefon")

DataGridView1.DataSource = BSKisi
DataGridView1.Refresh()

DataGridView2.DataSource = BSTel
DataGridView2.Refresh()

Conn.Close()

YeniFormHazirla()

For i As Byte = 3 To DataGridView1.ColumnCount - 1
DataGridView1.Columns(i).Visible = False
Next

End Sub


Private Sub BtnYeni_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnYeni.Click
If BtnYeni.Text = "VAZGEÇ" Then
DS.RejectChanges()
If BSKisi.Count = 0 Then
YeniFormHazirla()
Else
BSKisi.MoveLast()
VeriOku()
End If
ElseIf BtnYeni.Text = "YENİ" Then
KayitKontrol()
If Vazgec = True Then Exit Sub
YeniFormHazirla()
End If
End Sub


Private Sub BtnKaydet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnKaydet.Click
VeriKontrol()
If Vazgec = True Then Exit Sub
If BtnYeni.Text = "VAZGEÇ" Then BSKisi.AddNew()
VeriYaz()
VeriTabaniGuncelle()
MsgBox("Kayıt Edildi")

BtnSil.Enabled = True
BtnYeni.Text = "YENİ"
KisiCHNG = False
End Sub


Private Sub BtnSil_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSil.Click
Cevap = MsgBox(txtAdi.Text & " " & txtSoyadi.Text & " Adres Defterinden Silinecektir. Onaylıyormusunuz ? ", MsgBoxStyle.YesNo)
If Cevap = vbNo Then Exit Sub

For i As Integer = 0 To BSTel.Count - 1
BSTel.RemoveAt(i)
Next

BSKisi.RemoveAt(BSKisi.Position)

VeriTabaniGuncelle()
MsgBox("Kayıt Silindi.")

BSKisi.MoveNext()
VeriOku()

End Sub

Private Sub BtnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click
KayitKontrol()
If Vazgec = True Then Exit Sub
BSKisi.MoveFirst()
VeriOku()
End Sub

Private Sub BtnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrevious.Click
KayitKontrol()
If Vazgec = True Then Exit Sub
BSKisi.MovePrevious()
VeriOku()
End Sub

Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNext.Click
KayitKontrol()
If Vazgec = True Then Exit Sub
BSKisi.MoveNext()
VeriOku()
End Sub

Private Sub BtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLast.Click
KayitKontrol()
If Vazgec = True Then Exit Sub
BSKisi.MoveLast()
VeriOku()
End Sub

Private Sub BtnKapat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnKapat.Click
End
End Sub

Private Sub DataGridView2_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView2.CellBeginEdit
BSTel.Current("KisiID") = KisiIdNo + 1
End Sub

End Class