Konu 36: tukan uygulaması. veritabani, modül ve vt işlemleri, iki tabloda işlem, vt'den sorgu çağırma(rapor)

Örnek Uygulama

Ekran Görüntüsü

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        urunler.ShowDialog()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        musteriler.ShowDialog()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        satis.ShowDialog()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim gun As Integer = Now.Day

        Dim sql As String = "select * from musteri where maasGunu=" & gun & ""
        Dim vt As New veritabani
        Dim dt As DataTable = vt.VeriGoster(sql)
        For i As Integer = 0 To dt.Rows.Count - 1
            MsgBox(dt.Rows(i).Item("ad") & " " & dt.Rows(i).Item("soyad") & " adlı müşterinin maaş günü gelmiştir. tepesine bin.")
        Next
    End Sub
End Class

Ekran Görüntüsü

Public Class urunler
    Dim vt As New veritabani
    Dim secilen As String
    Private Sub urunler_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = vt.VeriGoster("select * from urunler")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "insert into urunler (barkod,urunadi,adet,tur,alisF,satisF) values('" & TextBox1.Text & "','" & TextBox2.Text & "'," & Val(TextBox3.Text) & ",'" & TextBox4.Text & "'," & vt.ParaDonustur(TextBox5.Text) & "," & vt.ParaDonustur(TextBox6.Text) & ")"
        vt.Calistir(sql)
        DataGridView1.DataSource = vt.VeriGoster("select * from urunler")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sql As String = "update urunler set barkod='" & TextBox12.Text & "',urunadi='" & TextBox11.Text & "',adet=" & Val(TextBox10.Text) & ",tur='" & TextBox9.Text & "',alisF=" & vt.ParaDonustur(TextBox8.Text) & ",satisF=" & vt.ParaDonustur(TextBox7.Text) & " where sira=" & secilen
        vt.Calistir(sql)
        sql = "select * from urunler"
        DataGridView1.DataSource = vt.VeriGoster(sql)

    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secilen = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        TextBox12.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        TextBox11.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
        TextBox10.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value
        TextBox9.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value
        TextBox8.Text = DataGridView1.Rows(e.RowIndex).Cells(5).Value
        TextBox7.Text = DataGridView1.Rows(e.RowIndex).Cells(6).Value

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String = "delete from urunler where sira=" & secilen & ""
        vt.Calistir(sql)
        sql = "select * from urunler"
        DataGridView1.DataSource = vt.VeriGoster(sql)
    End Sub

    Private Sub TextBox13_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox13.TextChanged
        Dim hangisi As String = ""
        If RadioButton1.Checked Then
            hangisi = "barkod"
        ElseIf RadioButton2.Checked Then
            hangisi = "urunadi"
        ElseIf RadioButton3.Checked Then
            hangisi = "tur"
        End If

        Dim sql As String = "select * from urunler where " & hangisi & " like '%" & TextBox13.Text & "%'"
        DataGridView1.DataSource = vt.VeriGoster(sql)
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim sql As String = "update urunler set adet=" & Val(TextBox10.Text) + Val(TextBox14.Text) & " where sira=" & secilen & ""
        vt.Calistir(sql)
        sql = "select * from urunler"
        DataGridView1.DataSource = vt.VeriGoster(sql)
    End Sub
End Class

Ekran Görüntüsü

Public Class musteriler
    Dim vt As New veritabani
    Dim secMusteri As Integer
    Private Sub musteriler_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = vt.VeriGoster("select * from musteri")
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Dim sql As String = "select * from musteri where ad like '%" & TextBox1.Text & "%'"
        DataGridView1.DataSource = vt.VeriGoster(sql)
    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secMusteri = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        Dim sql As String = "select * from rapor where veresiye=true and sira=" & secMusteri
        Dim dtRapor As DataTable = vt.VeriGoster(sql)
        DataGridView2.DataSource = dtRapor

        Dim toplamBorc As Decimal = 0
        For i As Integer = 0 To dtRapor.Rows.Count - 1
            toplamBorc += dtRapor.Rows(i).Item("Adet") * dtRapor.Rows(i).Item("Fiyat")
        Next
        Label3.Text = toplamBorc
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "update rapor set veresiye=false where veresiye=true and sira=" & secMusteri
        vt.Calistir(sql)
        sql = "select * from rapor where veresiye=true and sira=" & secMusteri
        DataGridView2.DataSource = vt.VeriGoster(sql)

    End Sub
End Class

Ekran Görüntüsü

Public Class satis
    Dim vt As New veritabani
    Dim dtUrunler As DataTable
    Dim secMusteri As Integer

    Private Sub satis_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sql As String = "select * from urunler"
        dtUrunler = vt.VeriGoster(sql)
        sql = "select sira as Sıra,ad as Ad,soyad as Soyad from musteri"
        DataGridView1.DataSource = vt.VeriGoster(sql)

        sql = "select * from rapor"
        DataGridView2.DataSource = vt.VeriGoster(sql)
    End Sub

    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress
        If (e.KeyChar = Chr(Keys.Enter)) Then
            Dim barkod As String = TextBox2.Text

            Dim sql As String = "select * from urunler where barkod='" & barkod & "'"
            dtUrunler = vt.VeriGoster(sql)

            If dtUrunler.Rows.Count > 0 Then
                Dim satisF As Decimal = dtUrunler.Rows(0).Item("satisF")
                Dim adet As Integer = dtUrunler.Rows(0).Item("adet")
                Label3.Text = satisF * Val(TextBox1.Text)
                Dim kalan As Integer = adet - Val(TextBox1.Text)
                Dim secUrun As Integer = dtUrunler.Rows(0).Item("sira")
                If kalan < 0 Then
                    MsgBox("stokta o kadar ürün yok. olmaz")
                Else
                    If kalan <= 50 Then
                        MsgBox("dikkat " & dtUrunler.Rows(0).Item("urunadi") & " isimli ürün tükenmek üzere. lütfen toptancı memedi arayın.")
                    End If

                    'satış işlemleri
                    sql = "update urunler set adet=" & kalan & " where barkod='" & barkod & "'"
                    vt.Calistir(sql)

                    sql = "insert into satis (mSira,uSira,adet,satisF,tarih,kime,veresiyemi) values(" & secMusteri & "," & secUrun & "," & Val(TextBox1.Text) & "," & vt.ParaDonustur(satisF) & ",#" & vt.TarihDonustur(Now) & "#,'" & TextBox4.Text & "'," & CheckBox1.Checked & ")"
                    vt.Calistir(sql)
                    sql = "select * from rapor"
                    DataGridView2.DataSource = vt.VeriGoster(sql)
                End If

            End If
        End If

    End Sub

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
        Dim sql As String = "select sira as Sıra,ad as Ad,soyad as Soyad from musteri where ad like '%" & TextBox3.Text & "%'"
        DataGridView1.DataSource = vt.VeriGoster(sql)
    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secMusteri = DataGridView1.Rows(e.RowIndex).Cells(0).Value
    End Sub
End Class
Imports System.Data.OleDb
Public Class veritabani
    Public baglanti As New OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;Data Source=tukan.mdb")
    Public Function VeriGoster(ByVal sql As String) As DataTable
        Dim adaptor As New OleDbDataAdapter(sql, baglanti)
        Dim dt As New DataTable
        adaptor.Fill(dt)
        baglanti.Close()
        Return dt
    End Function

    Public Sub Calistir(ByVal sql As String)
        Dim komut As New OleDbCommand(sql, baglanti)
        baglanti.Open()
        komut.ExecuteNonQuery()
        baglanti.Close()
    End Sub

    Public Function ParaDonustur(ByVal para As String) As String
        Dim yeniHali As String = Replace(para, ",", ".")
        If (IsNumeric(yeniHali)) Then
            Return yeniHali
        Else
            Return "hata"
        End If
    End Function

    Public Function TarihDonustur(ByVal tarih As Date) As String
        Dim yeni As String = tarih.Month & "/" & tarih.Day & "/" & tarih.Year
        Return yeni
    End Function

End Class

Veritabani Class'ı  

Ekran Görüntüsü (veritabanı - tablolar)

Ekran Görüntüsü (veritabanı - sorgular)

SELECT musteri.sira, musteri.ad AS Ad, musteri.soyad AS Soyad, urunler.urunadi AS ÜrünAdı, satis.adet AS Adet, satis.satisF AS Fiyat, satis.tarih AS Tarih, satis.kime AS Şahıs, satis.veresiyemi AS veresiye
FROM urunler INNER JOIN (musteri INNER JOIN satis ON musteri.sira = satis.mSira) ON urunler.sira = satis.uSira;

Konu 35: dataset ornegi modul kullanimi,tarih aralığı arama

Örnek Uygulama

Ekran Görüntüsü

Public Class Form1
    Dim secilen As Integer
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        baglanti = New OleDb.OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;Data Source=" & Application.StartupPath & "\veritabani.mdb;")

        DataGridView1.DataSource = verigoster("select * from alacak_defteri")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "insert into alacak_defteri(ad,soyad,borc,tarih) values('" & TextBox1.Text & "','" & TextBox2.Text & "'," & Val(TextBox3.Text) & ",'" & DateTimePicker1.Value.Date & "')"
        calistir(sql)

        DataGridView1.DataSource = verigoster("select * from alacak_defteri")
    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secilen = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        Label1.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        Label2.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        Label3.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
        Label4.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value
        Label5.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sql As String = "update alacak_defteri set ad='" & TextBox4.Text & "', soyad='" & TextBox5.Text & "', borc=" & TextBox6.Text & ", tarih='" & DateTimePicker2.Value.Date & "' where sira=" & secilen
        calistir(sql)

        DataGridView1.DataSource = verigoster("select * from alacak_defteri")

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String = "delete from alacak_defteri where sira=" & secilen
        calistir(sql)

        DataGridView1.DataSource = verigoster("select * from alacak_defteri")
    End Sub

    Private Sub TextBox7_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox7.TextChanged, TextBox8.TextChanged, TextBox9.TextChanged
        Dim alan As String = ""

        If sender.name = "TextBox7" Then
            alan = "ad"
        ElseIf sender.name = "TextBox8" Then
            alan = "soyad"
        End If

        Dim sql As String = "select * from alacak_defteri where " & alan & " like '%" & TextBox7.Text & "%'"

        If sender.name = "TextBox9" Then
            If TextBox9.Text = "" Then
                sql = "select * from alacak_defteri"
            Else
                sql = "select * from alacak_defteri where borc=" & Val(TextBox9.Text)
            End If
        End If

        DataGridView1.DataSource = verigoster(sql)
    End Sub

    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged, DateTimePicker4.ValueChanged

        Dim tarih1 As Date = DateTimePicker3.Value.Date
        Dim tarih2 As Date = DateTimePicker4.Value.Date
        Dim sql As String = "SELECT * FROM(alacak_defteri) WHERE (((alacak_defteri.tarih) Between #" & tarih1.Month & "/" & tarih1.Day & "/" & tarih1.Year & "# And #" & tarih2.Month & "/" & tarih2.Day & "/" & tarih2.Year & "#));"

        DataGridView1.DataSource = verigoster(sql)
    End Sub
End Class
Imports System.Data.OleDb
Module Module1
    Public baglanti As OleDbConnection

    Public Function verigoster(ByVal sql As String) As DataTable
        Dim adaptor As New OleDbDataAdapter(sql, baglanti)
        baglanti.Close()
        Dim dt As New DataTable
        adaptor.Fill(dt)
        Return dt
    End Function

    Public Sub calistir(ByVal sql As String)
        Dim komut As New OleDbCommand(sql, baglanti)
        baglanti.Open()
        komut.ExecuteNonQuery()
        baglanti.Close()
    End Sub

End Module

Konu 34: sql veritabanı, click_once ile projenin publish edilmesi

Örnek Uygulama

Ekran Görüntüsü

 

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim vt_yeni As String = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        Dim yeni_yol As String = System.IO.Path.Combine(vt_yeni, "rehber.mdf")
        Dim eski As String = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, "rehber.mdf")

        If Not System.IO.File.Exists(yeni_yol) Then
            System.IO.File.Copy(eski, yeni_yol)
        End If

        Dim cumle As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & yeni_yol & ";Integrated Security=True;User Instance=True"
        Dim baglanti As New SqlConnection(cumle)
        Dim sql As String = "select * from kisiler"
        Dim adaptor As New SqlDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "kisiler")
        baglanti.Close()
        DataGridView1.DataSource = ds.Tables("kisiler")

    End Sub

End Class

Ekran Görüntüsü (veritabanı)

Konu 33: veritabani, modül ve vt işlemleri, iki tabloda işlem, vt'den sorgu çağırma(rapor)

Örnek Uygulama

Ekran Görüntüsü

 

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        baglanti = New OleDb.OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;Data Source=" & Application.StartupPath & "\vt1.mdb")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        urun.Show()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        musteri.Show()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        satis.Show()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        rapor.Show()
    End Sub
End Class

Ekran Görüntüsü

 

Public Class urun
    Dim secilen As Integer
    Private Sub urun_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'verileri goster
        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")

    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secilen = DataGridView1.Rows(e.RowIndex).Cells(0).Value

        TextBox8.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        TextBox7.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
        TextBox6.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value
        TextBox5.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value
        TextBox14.Text = DataGridView1.Rows(e.RowIndex).Cells(6).Value

        Label1.Text = secilen
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim para As String = Replace(TextBox4.Text, ",", ".")
        If IsNumeric(para) = False Then
            MsgBox("para birimini hatalı girdiniz")
            Exi t Sub
        End If
        Dim para1 As String = Replace(TextBox3.Text, ",", ".")
        If IsNumeric(para1) = False Then
            MsgBox("para birimini hatalı girdiniz")
            Exi t Sub
        End If

        Dim sql As String = "insert into urun (barkod,ad,alis,satis,tarih,adet) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & para1 & "'," & para & ",'" & Now.Date & "'," & Val(TextBox13.Text) & ")"
        calistir(sql)

        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim para As String = Replace(TextBox6.Text, ",", ".")
        If IsNumeric(para) = False Then
            MsgBox("para birimini hatalı girdiniz")
            Exi t Sub
        End If
        Dim para1 As String = Replace(TextBox5.Text, ",", ".")
        If IsNumeric(para1) = False Then
            MsgBox("para birimini hatalı girdiniz")
            Exi t Sub
        End If
        Dim sql As String = "update urun set barkod='" & TextBox8.Text & "',ad='" & TextBox7.Text & "',alis=" & para & ",satis=" & para1 & ",tarih='" & Now.Date & "',adet=" & Val(TextBox14.Text) & " where sira_no=" & secilen
        calistir(sql)
        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String = "delete from urun where sira_no=" & secilen
        calistir(sql)

        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")
    End Sub

    Private Sub TextBox12_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox12.TextChanged, TextBox11.TextChanged, TextBox10.TextChanged, TextBox9.TextChanged, TextBox15.TextChanged, DateTimePicker1.ValueChanged

        Dim sql As String = "select * from urun where "
        If CheckBox1.Checked Then
            sql &= "barkod like '%" & TextBox12.Text & "%' and "
        End If
        If CheckBox2.Checked Then
            sql &= "ad like '%" & TextBox11.Text & "%' and "
        End If
        If CheckBox3.Checked Then
            sql &= "alis like '%" & TextBox10.Text & "%' and "
        End If
        If CheckBox4.Checked Then
            sql &= "satis like %" & TextBox9.Text & "% and "
        End If
        If CheckBox5.Checked Then
            sql &= "adet like %" & TextBox9.Text & "% and "
        End If
        If CheckBox6.Checked Then
            Dim tar As Date = DateTimePicker1.Value
            Dim tarih As String = tar.Month & "/" & tar.Day & "/" & tar.Year
            sql &= "tarih =#" & tarih & "# and "
        End If

        If sql.EndsWith("and ") Then
            'son 4 karakteri sil
            sql = sql.Remove(sql.Length - 4, 4)
        ElseIf sql.EndsWith("where ") Then
            'son 6 karakteri sil
            sql = sql.Remove(sql.Length - 6, 6)
        End If

        DataGridView1.DataSource = goster(sql).Tables("tablo")
    End Sub

End Class

Ekran Görüntüsü

 

Public Class musteri
    Dim baglanti As OleDb.OleDbConnection
    Dim secilen As Integer
    Dim dt As DataTable

    Private Sub musteri_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt = goster("select * from musteri").Tables("tablo")
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "insert into musteri (ad,soyad,tel,adres) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"
        calistir(sql)
        dt = goster("select * from musteri").Tables("tablo")
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 
        Dim sql As String = "update musteri set ad='" & TextBox5.Text & "',soyad='" & TextBox6.Text & "',tel='" & TextBox7.Text & "',adres='" & TextBox8.Text & "' where sira_no=" & secilen
        calistir(sql)
        dt = goster("select * from musteri").Tables("tablo")
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String = "delete from musteri where sira_no=" & secilen
        calistir(sql)
        dt = goster("select * from musteri").Tables("tablo")
        DataGridView1.DataSource = dt
    End Sub
    Private Sub TextBox12_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox12.TextChanged, TextBox11.TextChanged, TextBox10.TextChanged, TextBox9.TextChanged
        'Dim sql As String = "select * from kisiler where ad like '%a%' and soyad like '%a%' and tel like '%1%' and para like 1 and"
        Dim sql As String = "select * from musteri where "
        If CheckBox1.Checked Then
            sql &= "ad like '%" & TextBox12.Text & "%' and "
        End If
        If CheckBox2.Checked Then
            sql &= "soyad like '%" & TextBox11.Text & "%' and "
        End If
        If CheckBox3.Checked Then
            sql &= "tel like '%" & TextBox10.Text & "%' and "
        End If
        If CheckBox4.Checked Then
            sql &= "adres like %" & TextBox9.Text & "% and "
        End If

        If sql.EndsWith("and ") Then
            'son 4 karakteri sil
            sql = sql.Remove(sql.Length - 4, 4)
        ElseIf sql.EndsWith("where ") Then
            'son 6 karakteri sil
            sql = sql.Remove(sql.Length - 6, 6)
        End If

        dt = goster(sql).Tables("tablo")
        DataGridView1.DataSource = dt

    End Sub
    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter

        secilen = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        TextBox5.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        TextBox6.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
        TextBox7.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value
        TextBox8.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value

        Label9.Text = secilen
    End Sub

End Class

Ekran Görüntüsü

 

Public Class satis
    Dim secUrun, secMusteri As Integer
    Dim satis_fiyati As Integer
    Dim adet As Integer
    Private Sub satis_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")
        DataGridView2.DataSource = goster("select * from musteri").Tables("tablo")
        DataGridView3.DataSource = goster("select * from satis").Tables("tablo")
    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secUrun = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        satis_fiyati = DataGridView1.Rows(e.RowIndex).Cells(4).Value
        adet = DataGridView1.Rows(e.RowIndex).Cells(6).Value
        Label1.Text = secUrun
    End Sub

    Private Sub DataGridView2_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.RowEnter
        secMusteri = DataGridView2.Rows(e.RowIndex).Cells(0).Value
        Label2.Text = secMusteri
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'satis tablosuna yeni kayıt

        Dim sql As String = "insert into satis (musteri_sira,urun_sira,satis,adet,tarih) values (" & secMusteri & "," & secUrun & "," & satis_fiyati & "," & Val(TextBox1.Text) & "," & Now.Date & ")"
        calistir(sql)
        DataGridView3.DataSource = goster("select * from satis").Tables("tablo")

        'urun tablosunda guncelleme
        Dim kalan As Integer = adet - Val(TextBox1.Text)
        sql = "update urun set adet=" & kalan & " where sira_no=" & secUrun
        calistir(sql)
        DataGridView1.DataSource = goster("select * from urun").Tables("tablo")
    End Sub
End Class
Module Module1
    Public baglanti As OleDb.OleDbConnection

    Public Function goster(ByVal sql As String) As DataSet
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "tablo")
        baglanti.Close()
        Return ds
    End Function

    Public Sub calistir(ByVal sql As String)
        Dim komut As New OleDb.OleDbCommand(sql, baglanti)
        komut.Connection.Open()
        komut.ExecuteNonQuery()
        baglanti.Close()
    End Sub
End Module

 

Ekran Görüntüsü (veritabanı)

         

Konu 32: datasetten listbox'a tüm kayıtları çekme, datasetten listbox'a teker teker kayıt çekme, data reader yöntemi

Örnek Uygulama

Ekran Görüntüsü

Public Class Form1
    Dim baglanti As OleDb.OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        baglanti = New OleDb.OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;data Source=" & Application.StartupPath & "\vt.mdb")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "select * from kisiler"
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "kisiler")
        baglanti.Close()

        ListBox1.DataSource = ds.Tables("kisiler")
        ListBox1.DisplayMember = "ad"

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sql As String = "select * from kisiler"
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "kisiler")
        baglanti.Close()

        For i As Integer = 0 To ds.Tables("kisiler").Rows.Count - 1
            ListBox2.Items.Add(ds.Tables("kisiler").Rows(i).Item(2))
        Next
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sql As String = "select * from kisiler"
        Dim komut As New OleDb.OleDbCommand(sql, baglanti)
        komut.Connection.Open()
        Dim okur As OleDb.OleDbDataReader
        okur = komut.ExecuteReader

        While okur.Read
            ListBox3.Items.Add(okur.GetString(3))
        End While
        baglanti.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim sql As String = "select * from il"
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "il")
        baglanti.Close()

        ComboBox1.DataSource = ds.Tables("il")
        ComboBox1.DisplayMember = "sehir"
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sql As String = "select * from ilce where sehir=" & (ComboBox1.SelectedIndex + 1)
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        Dim ds As New DataSet
        adaptor.Fill(ds, "ilce")
        baglanti.Close()

        ComboBox2.DataSource = ds.Tables("ilce")
        ComboBox2.DisplayMember = "ilce"
    End Sub
End Class

Konu 31: datagridview görüntüleme ayarları, datagridview'dan veritabanına direkt kayıt

Örnek Uygulama

Ekran Görüntüsü

Public Class Form1
    Dim baglanti As OleDb.OleDbConnection
    Dim secilen As Integer
    Dim ds As DataSet

    Sub verigoster()
        Dim sql As String = "select * from kisiler"
        Dim adaptor As New OleDb.OleDbDataAdapter(sql, baglanti)
        ds = New DataSet
        adaptor.Fill(ds, "kisiler")
        baglanti.Close()
        DataGridView1.DataSource = ds.Tables("kisiler")
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        baglanti = New OleDb.OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;data Source=" & Application.StartupPath & "\vt.mdb")

        DataGridView1.AutoGenerateColumns = False
        DataGridView1.Columns.Add("sira", "SIRA")
        DataGridView1.Columns.Add("ad", "ADI")
        DataGridView1.Columns.Add("soyad", "SOYADI")
        DataGridView1.Columns.Add("tel", "TELEFON NUMARASI")
        DataGridView1.Columns.Add("para", "PARA DURUMU")
        DataGridView1.Columns("ad").Width = 75
        DataGridView1.Columns("soyad").Width = 75
        DataGridView1.Columns("tel").Width = 100
        DataGridView1.Columns("para").Width = 50

        DataGridView1.Columns("sira").DataPropertyName = "sira_no"
        DataGridView1.Columns("ad").DataPropertyName = "ad"
        DataGridView1.Columns("soyad").DataPropertyName = "soyad"
        DataGridView1.Columns("tel").DataPropertyName = "tel"
        DataGridView1.Columns("para").DataPropertyName = "para"

        DataGridView1.Columns("sira").Visible = False

        verigoster()
    End Sub

    Sub guncelle()
        Dim adaptor As New OleDb.OleDbDataAdapter("select * from kisiler", baglanti)
        Dim yapici As New OleDb.OleDbCommandBuilder(adaptor)
        adaptor.Update(ds.Tables("kisiler"))
        baglanti.Close()

        verigoster()
    End Sub

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        secilen = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        Label1.Text = secilen
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        guncelle()
    End Sub

End Class

Konu 30: Datasetteki Kayıtları Teker Teker Gezinme, Listboxta Datasource Ayarı Ve Veri Görüntüleme

Örnek Uygulama

Ekran Görüntüsü

Public Class Form1
    Dim baglanti As OleDb.OleDbConnection
    Sub verigoster()
        ListBox1.Items.Clear()
        ListBox2.Items.Clear()
        ListBox3.Items.Clear()
        Dim adaptor As New OleDb.OleDbDataAdapter("select * from rehber", baglanti)
        Dim veriseti As New DataSet
        adaptor.Fill(veriseti, "rehber")
        baglanti.Close()
        'datasetteki verileri teker teker gezmemizi saglar
        For i As Integer = 0 To veriseti.Tables("rehber").Rows.Count - 1
            Dim ad, soyad, tel As String
            ad = veriseti.Tables("rehber").Rows(i).Item(1)
            soyad = veriseti.Tables("rehber").Rows(i).Item(2)
            tel = veriseti.Tables("rehber").Rows(i).Item(3)
            ListBox1.Items.Add(ad)
            ListBox2.Items.Add(soyad)
            ListBox3.Items.Add(tel)
        Next
        'listboxın datasourceunu verisetinden cekmesini saglıyoruz
        ListBox4.DataSource = veriseti.Tables("rehber")
        'ListBox4.DisplayMember = "ad"
        'listbox verileri cekerken, verisetinin hangi sutunundan veriler alması gerektigini bildiriyoruz
        ListBox4.DisplayMember = veriseti.Tables("rehber").Columns(1).ColumnName
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        baglanti = New OleDb.OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;Data source=" & Application.StartupPath & "\defter.mdb;")
        verigoster()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        verigoster()
    End Sub
End Class