MySQL Server ve Visual Basic - II

Dim conn As ADODB.Connection
Dim rsilkmysqltablo As ADODB.Recordset
Dim ik
Dim l_item As ListItem

Private Sub Form_Load()
db_adi = "deneme" 'database adı
db_server = "111.111.1.111" 'Server'ın bulunduğu bilgisayarın ip'si
db_port = "3306" 'bağlanılacak port: 3306
db_user = "root" 'kullanıcı adı
db_pass = "123456" 'şifre
ik = "Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=" & Chr$(34) & "DRIVER={MySQL ODBC 3.51 Driver}ESC=ATABASE=" & db_adi & ";SERVER=" & db_server & ";UID=" & db_user & ";PASSWORD=" & db_pass & ";PORT=" & db_port & ";OPTION=16387;STMT=;" & Chr$(34)
Set conn = New ADODB.Connection
conn.Open ik
Set rsilkmysqltablo = New ADODB.Recordset
Set rsilkmysqltablo.ActiveConnection = conn
rsilkmysqltablo.CursorLocation = adUseClient
'####################################
With LV
.ColumnHeaders.Add , , "adi"
.ColumnHeaders.Add , , "soyadi"
End With
'Sıralama açık ve a dan z ye
LV.Sorted = True
LV.SortOrder = lvwAscending
end sub


Private Sub Form_Unload(Cancel As Integer)
Set conn = Nothing
Set rsilkmysqltablo = Nothing
Set conn2 = Nothing
Set tablotablosu = Nothing
end sub

Sub LoadData()
LV.ListItems.Clear
Set rsilkmysqltablo = New ADODB.Recordset
rsilkmysqltablo.CursorLocation = adUseClient
rsilkmysqltablo.CursorType = adOpenStatic
rsilkmysqltablo.LockType = adLockReadOnly
Set rsilkmysqltablo.ActiveConnection = conn
rsilkmysqltablo.Source = "SELECT * FROM ilkmysqltablo ORDER BY soyadi ASC"
rsilkmysqltablo.Open
Do Until rsilkmysqltablo.EOF
Set l_item = LV.ListItems.Add(, , rsilkmysqltablo!adi)
l_item.SubItems(1) = rsilkmysqltablo!soyadi
rsilkmysqltablo.MoveNext
Loop
End Sub
[CENTER][/CENTER]

Private Sub LV_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
Select Case ColumnHeader.Index
Case 1: 'MsgBox "Birinci kolon"
If LV.SortOrder = lvwAscending Then
LV.SortOrder = lvwDescending
Else
LV.SortOrder = lvwAscending
End If
Case 2: 'MsgBox "İkinci kolon"
If LV.SortOrder = lvwAscending Then
LV.SortOrder = lvwDescending
Else
LV.SortOrder = lvwAscending
End If
End Select
End Sub

Private Sub LV_ItemClick(ByVal Item As MSComctlLib.ListItem)
Text1 = LV.SelectedItem
Text2 = LV.SelectedItem.SubItems(1)
End Sub




Private Sub LV_LostFocus()
Text1 = ""
Text2 = ""
End Sub

Private Sub Aramaislemleri()
On Error GoTo hata
LV.ListItems.Clear
Set rsilkmysqltablo = New ADODB.Recordset
rsilkmysqltablo.CursorLocation = adUseClient
rsilkmysqltablo.CursorType = adOpenStatic
rsilkmysqltablo.LockType = adLockReadOnly
Set rsilkmysqltablo.ActiveConnection = conn
If Text3 <> "" Then
rsilkmysqltablo.Source = "SELECT * FROM ilkmysqltablo WHERE adi like '" & Text3.Text & "%'"
End If

If Text4 <> "" Then
rsilkmysqltablo.Source = "SELECT * FROM ilkmysqltablo WHERE soyadi like '" & Text4 & "%'"
End If

If Text3 <> "" And Text4 <> "" Then
rsilkmysqltablo.Source = "SELECT * FROM ilkmysqltablo WHERE adi like '" & Text3 & "%' AND soyadi = '" & Text4 & "%'"
End If

rsilkmysqltablo.Open
Do Until rsilkmysqltablo.EOF
Set l_item = LV.ListItems.Add(, , rsilkmysqltablo!adi)
l_item.SubItems(1) = rsilkmysqltablo!soyadi
rsilkmysqltablo.MoveNext
Loop

hata:
End Sub



Private Sub Text3_Change()
Aramaislemleri
End Sub

Private Sub Command3_Click()
On Error GoTo hata
rsilkmysqltablo.Close
Set rsilkmysqltablo = New ADODB.Recordset
rsilkmysqltablo.Source = "DELETE FROM ilkmysqltablo WHERE adi = '" & LV.SelectedItem & "' AND soyadi = '" & LV.SelectedItem.SubItems(1) & "'"
rsilkmysqltablo.CursorLocation = adUseClient
rsilkmysqltablo.CursorType = adOpenDynamic
rsilkmysqltablo.LockType = adLockOptimistic
Set rsilkmysqltablo.ActiveConnection = conn
rsilkmysqltablo.Open
rsilkmysqltablo.Close
LoadData
hata: LoadData
End Sub

Private Sub Command2_Click()
LoadData
End Sub

Public Sub KayitEkle()
Set rsilkmysqltablo = New ADODB.Recordset
rsilkmysqltablo.Source = "ilkmysqltablo"
rsilkmysqltablo.CursorLocation = adUseClient
rsilkmysqltablo.CursorType = adOpenDynamic
rsilkmysqltablo.LockType = adLockOptimistic
Set rsilkmysqltablo.ActiveConnection = conn
rsilkmysqltablo.Open
rsilkmysqltablo.AddNew
rsilkmysqltablo!adi = Text1
rsilkmysqltablo!soyadi = Text2
rsilkmysqltablo.Update
Text1 = ""
Text2 = ""
Text1.SetFocus
End Sub




Private Sub Command1_Click()
KayitEkle
End Sub

Public Sub Duzelt()
Set rsilkmysqltablo = ADODB.Recordset
rsilkmysqltablo.Source = "ilkmysqltablo"
Set rsilkmysqltablo = conn.Openrecordset("ilkmysqltablo", dbopendynamic)
rsilkmysqltablo.Edit
rsilkmysqltablo!adi = "Txtduzeltadi"
rsilkmysqltablo!soyadi = "Txtduzeltsoyadi"
rsilkmysqltablo.Update
rsilkmysqltablo.Close
End Sub