|
The
Form design, book_search.vb
Create Visual Basic
2010 Forn - book_search.vb
Classes used in this Form: Label,
Panel, ComboBox, Button, PictureBox, CheckBox,
TextBox, DataGrid, OleDbConnection,
ImageList
-
Create the Form
book_search.vb
Add New Item -
book_search.vb Form to work_VBNet_10
Project
- On the Projet
menu , click Add New Item ...,
Add New Item - work_VBNet_10 dialog box appears, Select
Windows form Icon,
in the Name box type
book_search.vb and then click
Add.
|
 |
|
Note:
In the Windows Forms Designer appears the
design form created and in the Properties
Windows displays the corresponding properties
and
The design Form book_search.vb created, appears the empty window Form
design |
|
|
The codes file, created
... |
Public
Class
book_search
Private
Sub
book_search_Load(ByVal
sender
As
System.Object,
ByVal e
As
System.EventArgs)
Handles
MyBase.Load
End
Sub
End
Class
|
|
|
|
1. |
Picture files... |
|
- The folder
c:\work_VBNet_10\work_VBNet_10\pic stored the
bmp file search1.bmp,
v_line6.bmp, btn_cancel.bmp,
btn_ok.bmp
|
2. |
The
book_search.vb
design |
|
 |
The Properties of
the Form -
book_search.vb... |
Name: Icon: Maximize: Menu: Size: Start
Position: Text: Window
State: |
book_search Icon False (none) 368,332 CenterScreen
book_search
... Normal | | | |
3. |
From the Toolbox/Windows Form add these
controls .... 10 Label controls, 5
Panel controls, 5 ComboBox
controls, 4 Button controls, 3 PictureBox
controls, 2 CheckBox controls,
2 TextBox controls, 1 DataGrid
controls, 1 OleDbConnection
controls, 1 ImageList controls
|
|
- Add
10 Label
control
- lcondition, Label
Properties
Name:
Font:
Size:
Text:
TextAlign: |
lcondition
Microsoft Sans Serif,
8.25pt
104, 16
condition
TopLeft | |
- lsearch1
- lbook
- lcity1
- lphone1
- lcountry1
- lfirst1
- lfather1
- lname1
- lbfound
|
- Add
5 Panel
controls
- Panel11,
Paneldetail
Properties
Name:
Size: |
Panel11
656,
368 | |
- Panel112
Size:
152,
304
- Panel113
Size:
168, 24
-
Panel114
Size:
656,
32
|
- Add
5 ComboBox
controls
- cmbname, ComboBox
Properties
Name:
Size: |
cmbname
136,
21 | |
- cmbfather
Size: 136,
21
-
cmbfirst
Size:
136, 21
- cmbcountry
Size: 136,
21
- cmbcity
Size: 136,
21
|
- Add
4 Button
controls
- Btnsearch, Button
Properties
Name:
Image:
Size:
Text: |
Btnsearch
System.Drawing.Bitmap
24, 72
| |
The
folder
c:\work_VBNet_10\work_VBNet_10\pic stored the bmp
files
search1.bmp
- btnclear
Text: clear
- Btnok
ImageList1
ImageIndex:
0
- Btncancel
mageList1
ImageIndex:
1
|
- Add
3 PictureBox
control
s
- Pic_line011,
PictureBox
Properties
Name:
Image:
Size
Mode: |
Pic_line011
System.Drawing.Bitmap
Autosize | |
Bitmap file
added =
C:\work_VBNet_10\work_VBNet_10\pic\v_line6.bmp
- Pic_line012 -
Bitmap file
added =
C:\work_VBNet_10\work_VBNet_10\pic\v_line6.bmp
- Pic_line013 -
Bitmap file
added =
C:\work_VBNet_10\work_VBNet_10\pic\v_line6.bmp
|
- Add
2 CheckBox
controls
- chk_allbook, CheckBox
Properties
Name:
Size: |
chk_allbook
16,
16 | |
-
chk_condition
Size: 16,
16 |
- Add
2 TextBox
controls
- txtsearch1, TextBox
Properties
Name:
Size:
Text: |
txtsearch1
18, 114
SEARCH | |
- txtphone
Size: 132,
20
|
- Add
the DataGrid control
- DataGrid1,
DataGrid
Properties
Use the DataGrid control to
display the fields of a data
source as columns in a table. Each
row in the DataGrid control represents a record
in the data source. The
DataGrid control supports selection,
editing, deleting, paging, and sorting.
Different column types determine
the behavior of the columns
in the control.
DataGrid1, DataGrid
Properties
Name:
Size:
TableStyles: |
DataGrid1
440, 328
(Collection) - of 5 columns
... | |
- Add
the ImageList control
-
ImageList1, ImageList
Properties
Name:
Images:
ImageSize: |
ImageList1
(Collection) - of 2
Bitmap
image
56,
36 | |
The folder
c:\work_VBNet_10\work_VBNet_10\pic stored the 2 bmp files
btn_cancel.bmp,
btn_ok.bmp | | |
|
book_search.vb file, the codes after modification
...
Note: Logical Classe Controls:
1- dbConnection1 '
connection
2- vselect '
Select command
3- vda '
Dataadapter
4- vdset '
DataSet
|
|
Imports
System.Data.OleDb
Imports System.Data
Public
Class
book_search
Inherits
System.Windows.Forms.Form
'
logical Connection = dbConnection
Protected
Const
OleDbConnectionString
As
String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\work_VBNet_10\work_VBNet_10\data\work_VBNet.mdb;Persist
Security Info=True"
Protected
connectionString
As
String =
OleDbConnectionString
Dim dbConnection1
As
New
OleDbConnection(connectionString)
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
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() = dbConnection1
' Select
command
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
dbConnection1.Open()
vda.Fill(vdset,
"book")
Catch fillException
As
System.Exception
System.Windows.Forms. MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
dbConnection1.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
vselect.Connection() = dbConnection1
' Select
command
vda =
New
OleDb.OleDbDataAdapter(vselect)
vdset =
New
DataSet
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
dbConnection1.Open()
vda.Fill(vdset,
"book")
Catch fillException
As
System.Exception
System.Windows.Forms. MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
dbConnection1.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() = dbConnection1
' Select
command
vselect.CommandTimeout = 30
vdset.EnforceConstraints =
False
Try
dbConnection1.Open()
vda.Fill(vdset,
"book")
Catch fillException
As
System.Exception
System.Windows.Forms. MessageBox.Show(fillException.Message)
End
Try
vdset.EnforceConstraints =
True
dbConnection1.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 | | |
|
|