Public
Class
book_searchForm
Inherits System.Windows.Forms.Form
Dim vselect As
System.Data.OleDb.OleDbCommand ' Select command
Dim vda As
System.Data.OleDb.OleDbDataAdapter ' Dataadapter
Dim vdset As
System.Data.DataSet
Dim vitem_grid As
String
Dim searchflg As
Boolean
Dim s_flag As
Boolean
Windows Form Designer
generated code |
Private
Sub
book_searchForm_Load(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
MyBase.Load
Dim
vleft As
Integer
Dim
vtop As
Integer
' Move
vleft = fbooknote.Left +
fbooknote.Width -
Me.Width
vtop = fbooknote.Top
Me.Location = New
Point(vleft, vtop)
'resize
Me.Btnsearch.Left = (Me.Panel113.Width
- Me.Btnsearch.Width)
/ 2
Me.Btnok.Top = (Me.Panel114.Height
- Me.Btnok.Height) /
2
Me.Btncancel.Top =
Me.Btnok.Top
Me.Pic_line011.Top
= Me.Btnok.Top + ((Me.Btnok.Height
- (Me.Pic_line011.Height *
3)) / 4) 'Me.Btnok.Top
+ (Me.Pic_line011.Height * 2)
Me.Pic_line012.Top
= Me.Pic_line011.Top
+ Me.Pic_line011.Height
+ ((Me.Btnok.Height
- (Me.Pic_line011.Height *
3)) / 4) '(Me.Btnok.Top
+ Me.Btnok.Height) - (Me.Pic_line011.Height * 2)
Me.Pic_line013.Top
= Me.Pic_line012.Top
+ Me.Pic_line012.Height
+ ((Me.Btnok.Height
- (Me.Pic_line011.Height *
3)) / 4)
Me.Pic_line011.Left
= (Me.Btnok.Left -
Me.Pic_line011.Width) / 2
Me.Pic_line012.Left
= Me.Pic_line011.Left
Me.Pic_line013.Left
= Me.Pic_line011.Left
s_flag =
False
searchflg =
False
' sub_clearall()
Select
Case
booknote_type
Case
"find"
Me.Text = "search, book
note ..."
Case
"edit"
Me.Text = "edit, book
note ..."
End
Select
Me.chk_allbook.Checked =
True
sub_load("name")
sub_load("first")
sub_load("father")
sub_load("city")
sub_search("", "", "", "", "",
"")
End
Sub
'add data to comboboxes
Private
Sub
sub_load(ByVal
v_field As
String)
Dim
WhereSearch As
String
= ""
Dim
i As
Integer
Select
Case
v_field
Case
"name"
Me.cmbname.Items.Clear()
WhereSearch = " (Trim(name) <>
'')"
vselect =
New
OleDb.OleDbCommand("SELECT DISTINCT Trim(name) AS vname
FROM book WHERE" & WhereSearch & " ORDER BY Trim(name)")
'vselect = New
OleDb.OleDbCommand("SELECT DISTINCT Trim(name) AS vname
FROM book ORDER BY Trim(name)")
'vselect = New
OleDb.OleDbCommand("SELECT DISTINCT Trim(name) AS vname
FROM book" & WhereSearch & " ORDER BY Trim(name)")
Case
"first"
Me.cmbfirst.Items.Clear()
WhereSearch = " WHERE (Trim(first_name)
<>'')"
vselect =
New
OleDb.OleDbCommand("SELECT DISTINCT Trim(first_name) AS
vfirst FROM book" & WhereSearch & " ORDER BY
Trim(first_name)")
Case
"father"
Me.cmbfather.Items.Clear()
WhereSearch = " WHERE (Trim(father_name)
<>'')"
vselect =
New
OleDb.OleDbCommand("SELECT DISTINCT Trim(father_name) AS
vfather FROM book" & WhereSearch & " ORDER BY
Trim(father_name)")
Case
"city"
Me.cmbcity.Items.Clear()
WhereSearch = " WHERE (Trim(city)<>'')"
vselect =
New
OleDb.OleDbCommand("SELECT DISTINCT Trim(city) AS vcity
FROM book" & WhereSearch & " ORDER BY Trim(city)")
End
Select
vda =
New
OleDb.OleDbDataAdapter(vselect)
vdset =
New
DataSet
vselect.Connection() =
Me.OleDbConnection1
' Select command
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
Me.OleDbConnection1.Open()
vda.Fill(vdset, "book")
Catch
fillException As
System.Exception
System.Windows.Forms.MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
Me.OleDbConnection1.Close()
For
i = 0 To
vdset.Tables("book").Rows.Count - 1
Select
Case
v_field
Case
"name"
Me.cmbname.Items.Add(Trim(vdset.Tables("book").Rows(i).Item("vname")))
Case
"first"
Me.cmbfirst.Items.Add(Trim(vdset.Tables("book").Rows(i).Item("vfirst")))
Case
"father"
Me.cmbfather.Items.Add(Trim(vdset.Tables("book").Rows(i).Item("vfather")))
Case
"city"
Me.cmbcity.Items.Add(Trim(vdset.Tables("book").Rows(i).Item("vcity")))
End
Select
Next
End
Sub
Private
Sub
cmbname_SelectedIndexChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
cmbname.SelectedIndexChanged
If
s_flag Then
sub_check()
End
Sub
Private
Sub
sub_check()
If
(Me.chk_allbook.Checked
= True)
Then
Me.chk_allbook.Checked = False
If
(Me.Btnsearch.Enabled
= False)
Then
Me.Btnsearch.Enabled
= True
Me.Btnsearch.Focus()
End
Sub
Private
Sub
cmbfirst_SelectedIndexChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
cmbfirst.SelectedIndexChanged
If
s_flag Then
sub_check()
End
Sub
Private
Sub
cmbfather_SelectedIndexChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
cmbfather.SelectedIndexChanged
If
s_flag Then
sub_check()
End
Sub
Private
Sub
cmbcountry_SelectedIndexChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
cmbcountry.SelectedIndexChanged
If
s_flag Then
sub_check()
End
Sub
Private
Sub
cmbcity_SelectedIndexChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
cmbcity.SelectedIndexChanged
If
s_flag Then
sub_check()
End
Sub
Private
Sub
txtphone_KeyPress(ByVal
sender As
System.Object, ByVal
e As
System.Windows.Forms.KeyPressEventArgs)
Handles
txtphone.KeyPress
If
s_flag Then
sub_check()
End
Sub
Private
Sub
chk_condition_CheckedChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
chk_condition.CheckedChanged
If
Me.chk_condition.Checked = True
Then
Me.lcondition.Text =
"condition - Or"
End
If
If
Me.chk_condition.Checked = False
Then
Me.lcondition.Text =
"condition - And"
End
If
End
Sub
Private
Sub
chk_allbook_CheckedChanged(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles
chk_allbook.CheckedChanged
If
s_flag And
(Me.chk_allbook.Checked
= True)
Then
If
(Me.Btnsearch.Enabled
= False)
Then
Me.Btnsearch.Enabled
= True
End
If
End
Sub
Private
Sub
btnclear_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles btnclear.Click
sub_clearall()
End
Sub
Private
Sub
sub_clearall()
Me.chk_condition.Checked
= True
Me.lcondition.Text =
"condition - Or"
Me.cmbname.SelectedIndex
= -1
Me.cmbfirst.SelectedIndex = -1
Me.cmbfather.SelectedIndex = -1
Me.cmbcountry.SelectedIndex = -1
Me.cmbcity.SelectedIndex
= -1
Me.txtphone.Text = ""
If
(Me.Btnsearch.Enabled
= True)
Then
Me.Btnsearch.Enabled
= False
End
Sub
Private
Sub
Btnsearch_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles Btnsearch.Click
searchflg =
True
Select
Case
Me.chk_allbook.Checked
Case
True
sub_search("", "", "", "", "",
"")
Case
False
sub_search(Trim(Me.cmbname.Text),
Trim(Me.cmbfirst.Text),
Trim(Me.cmbfather.Text),
Trim(Me.cmbcountry.Text),
Trim(Me.cmbcity.Text),
Trim(Me.txtphone.Text))
End
Select
End
Sub
Private
Sub
sub_search(ByVal
v_name As
String,
ByVal
v_firstname As
String,
ByVal
v_fathername As
String,
ByVal
v_country As
String,
ByVal
v_city As
String,
ByVal
v_phone As
String)
Dim
WhereSearch As
String
= ""
Dim
vfound As
Integer
Select
Case
Me.chk_allbook.Checked
Case
True
WhereSearch = " WHERE (name <>
'')"
vselect =
New
OleDb.OleDbCommand("SELECT bookid,alternativePhone, city,
country, father_name, fax, first_name, homephone,
mobilephone, name, sex, workphone FROM book" & WhereSearch
& " ORDER BY name, first_name, father_name")
Case
False
Select
Case
Me.chk_condition.Checked
Case
True
'Or, condition
WhereSearch = " where ((Trim(name)
= '" & v_name & "' And name<>'" & "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(first_name) = '" & v_firstname & "' And first_name<>'"
& "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(father_name) = '" & v_fathername & "' And
father_name<>'" & "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(country) = '" & v_country & "' And country<>'" & ""
& "')"
WhereSearch = WhereSearch & " Or
(Trim(city) = '" & v_city & "' And city<>'" & "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(homephone) = '" & v_phone & "' And homephone ='" &
"" & "')"
WhereSearch = WhereSearch & " Or
(Trim(workphone) = '" & v_phone & "' And workphone<>'" &
"" & "')"
WhereSearch = WhereSearch & " Or
(Trim(mobilephone) = '" & v_phone & "' And mobilephone<>'"
& "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(alternativePhone) = '" & v_phone & "' And
alternativePhone<>'" & "" & "')"
WhereSearch = WhereSearch & " Or
(Trim(fax) = '" & v_phone & "' And fax<>'" & "" & "')"
WhereSearch = WhereSearch & ")"
Case
False
'And, condition
WhereSearch = " where ((Trim(name)
= '" & v_name & "' And name<>'" & "" & "')"
WhereSearch = WhereSearch & " And
(Trim(first_name) = '" & v_firstname & "' And first_name<>'"
& "" & "')"
WhereSearch = WhereSearch & " And
(Trim(father_name) = '" & v_fathername & "' And
father_name<>'" & "" & "')"
WhereSearch = WhereSearch & " And
(Trim(country) = '" & v_country & "' And country<>'" & ""
& "')"
WhereSearch = WhereSearch & " And
(Trim(city) = '" & v_city & "' And city<>'" & "" & "')"
WhereSearch = WhereSearch & " And
(Trim(homephone) = '" & v_phone & "' And homephone ='" &
"" & "')"
WhereSearch = WhereSearch & " And
(Trim(workphone) = '" & v_phone & "' And workphone<>'" &
"" & "')"
WhereSearch = WhereSearch & " And
(Trim(mobilephone) = '" & v_phone & "' And mobilephone<>'"
& "" & "')"
WhereSearch = WhereSearch & " And
(Trim(alternativePhone) = '" & v_phone & "' And
alternativePhone<>'" & "" & "')"
WhereSearch = WhereSearch & " And
(Trim(fax) = '" & v_phone & "' And fax<>'" & "" & "')"
WhereSearch = WhereSearch & ")"
End
Select
vselect =
New
OleDb.OleDbCommand("SELECT bookid,alternativePhone, city,
country, father_name, fax, first_name, homephone,
mobilephone, name, sex, workphone FROM book" & WhereSearch
& " ORDER BY name, first_name, father_name")
End
Select
vda =
New
OleDb.OleDbDataAdapter(vselect)
vdset =
New
DataSet
vselect.Connection() =
Me.OleDbConnection1
' Select command
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
Me.OleDbConnection1.Open()
vda.Fill(vdset, "book")
Catch
fillException As
System.Exception
System.Windows.Forms.MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
Me.OleDbConnection1.Close()
vfound =
vdset.Tables("book").Rows.Count
Me.DataGrid1.DataSource
= vdset
Me.DataGrid1.DataMember
= "book"
If
searchflg Then
Me.Btnsearch.Enabled =
True
Me.btnclear_Click(btnclear, Nothing)
searchflg =
False
End
If
Select
Case
Me.chk_allbook.Checked
Case
True
Me.lbfound.Text =
vfound.ToString + " - item(s) found, selected: all book(s)"
Case
False
Me.lbfound.Text =
vfound.ToString + " - item(s) found"
End
Select
s_flag =
True
Me.lbfound.Focus()
End
Sub
Private
Sub
sub_select()
Dim
WhereSearch As
String
= ""
Dim
vvrec As
Integer
WhereSearch = " WHERE (name <>
'')"
vselect =
New
OleDb.OleDbCommand("SELECT bookid, country, father_name,
first_name, name, sex FROM book" & WhereSearch & " ORDER
BY bookid")
vda =
New
OleDb.OleDbDataAdapter(vselect)
vdset =
New
DataSet
vselect.Connection() =
Me.OleDbConnection1
' Select command
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
Me.OleDbConnection1.Open()
vda.Fill(vdset, "book")
Catch
fillException As
System.Exception
System.Windows.Forms.MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
Me.OleDbConnection1.Close()
If
vdset.Tables("book").Rows.Count > 0
Then
For
vvrec = 0 To
(vdset.Tables("book").Rows.Count - 1)
If
(vdset.Tables("book").Rows(vvrec).Item("bookid").ToString
= vitem_grid) Then
book_recno = vvrec
Exit
Sub
End
If
Next
End
If
End
Sub
Private
Sub
DataGrid1_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles DataGrid1.Click
vitem_grid =
Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex,
0)
Me.Btnok.Enabled = True
End
Sub
Private
Sub
DataGrid1_MouseUp(ByVal
sender As
Object,
ByVal
e As
System.Windows.Forms.MouseEventArgs)
Handles
DataGrid1.MouseUp
vitem_grid =
Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex,
0)
Me.Btnok.Enabled = True
End
Sub
Private
Sub
Btnok_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles Btnok.Click
sub_select()
Select
Case
booknote_type
Case
"find"
Close()
fbooknote.sub_findok()
Exit
Sub
Case
"edit"
Close()
fbooknote.sub_modifyok()
End
Select
End
Sub
Private
Sub
Btncancel_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs)
Handles Btncancel.Click
Close()
fbooknote.Searchedit_cancel()
End
Sub
End
Class
|