this is the code help me solve it.
Public Class frmBorrowBooks
Dim myConnection As New mysqlConnection
Dim myCommand As New mysqlCommand
'im mycommandtext As New mysql
Dim dr As MySqlDataReader
Dim myCommand1 As New mysqlCommand
'im mycommandtext As New mysql
Dim dr1 As mysqlDataReader
Dim myCommand2 As New mysqlCommand
'im mycommandtext As New mysql
Dim dr2 As mysqlDataReader
Dim dt As mysqlDataAdapter
Dim itemSearch As String
Private Sub txtName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtName.TextChanged
itemSearch = "Name"
If Len(txtName.Text) > 0 Then
lvMember.Location = New System.Drawing.Point(123, 67)
lvMember.Visible = True
Display("SELECT ID ,`First Name` FROM student where `First Name` like '%" & txtName.Text & "%'", lview:=lvMember)
Else
lvMember.Visible = False
End If
End Sub
Sub Display(ByVal sSQL As String, ByRef lview As ListView)
lview.Items.Clear()
Dim myNumber As Integer
'If Not LI.ListView.co Then
'LI.ListView.clear()
Try
createConnection()
myConnection.Open()
myCommand = New MySqlCommand(sSQL, myConnection)
dr = myCommand.ExecuteReader()
While dr.Read()
Dim LI As New ListViewItem
myNumber += 1
'MsgBox(dr.FieldCount)
If dr.FieldCount = 1 Then
LI.Text = dr(0)
Else
LI.Text = dr(0)
For k = 1 To dr.FieldCount - 1
LI.SubItems.Add(dr(k))
Next
End If
If myNumber Mod 2 = 0 Then
LI.BackColor = Color.White
Else
LI.BackColor = Color.MintCream ' plum smokewhite
End If
lview.Items.Add(LI)
'LBOX_Contacts.Items.Add(dr(0).ToString())
End While
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
'MsgBox("here")
Finally
dr.Dispose()
myConnection.Close()
End Try
End Sub
Sub DisplayBorrowed(ByVal sSQL As String, ByRef lview As ListView)
lview.Items.Clear()
Dim myNumber As Integer
'If Not LI.ListView.co Then
'LI.ListView.clear()
Try
createConnection()
myConnection.Open()
myCommand = New mysqlCommand(sSQL, myConnection)
dr = myCommand.ExecuteReader()
While dr.Read()
Dim LI As New ListViewItem
LI.Text = dr(0)
myCommand2 = New mysqlCommand("select `book title` from librarybooks where `book id`=" & dr(1), myConnection)
dr2 = myCommand2.ExecuteReader()
While dr2.Read
LI.SubItems.Add(dr2(0))
End While
dr2.Close()
If dr(4) = "Student" Then
myCommand1 = New mysqlCommand("select `first name` from student where `id`=" & dr(2), myConnection)
Else
myCommand1 = New mysqlCommand("select `first name` from staff where `staff id`=" & dr(2), myConnection)
End If
dr1 = myCommand1.ExecuteReader()
While dr1.Read
LI.SubItems.Add(dr1(0))
End While
dr1.Close()
LI.SubItems.Add(dr(4))
LI.SubItems.Add(dr(3))
myNumber += 1
If myNumber Mod 2 = 0 Then
LI.BackColor = Color.White
Else
LI.BackColor = Color.MintCream ' plum smokewhite
End If
lview.Items.Add(LI)
'LBOX_Contacts.Items.Add(dr(0).ToString())
End While
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
'MsgBox("here")
Finally
'dr.Close()
myConnection.Close()
End Try
End Sub
Private Sub lvMember_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvMember.Click
Select Case itemSearch
Case "Name"
txtName.Text = lvMember.SelectedItems(0).SubItems(1).Text
'MsgBox(lvMember.SelectedItems(0).Text)
'lvMember.Items(0).Text
txtNameID.Text = lvMember.Items(0).Text
lvMember.Visible = False
Case "Book"
txtBookTitle.Text = lvMember.SelectedItems(0).SubItems(1).Text
'MsgBox(lvMember.SelectedItems(0).Text)
'lvMember.Items(0).Text
txtBookId.Text = lvMember.Items(0).Text
lvMember.Visible = False
End Select
End Sub
Private Sub lvMember_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvMember.SelectedIndexChanged
' MsgBox(lvMember.SelectedIndices)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Sub Save(ByVal sSQL As String)
Try
createConnection()
myCommand.CommandText = sSQL
myCommand.Connection = myConnection
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
Finally
'MsgBox("Successfully Saved.", MsgBoxStyle.OkOnly)
'dr.Close()
myConnection.Close()
End Try
End Sub
