Visual Basic Programming

L ậ p tr ì nh Visual Basic 6.0 BµI gi¶ng

VSoft – Vinh University

M«I tr−êng Visual Basic Toolbar

Visual basic 6.0

Form

™

Control Toolbox Slide 1

VSoft Group – Vinh University

™

Form Layout Slide 4

Control Toolbox

Khëi ®éng Giíi thiÖu m«i tr−êng lµm viÖc cña VB – – – – –

Properties

Components of the VB Design Environment

VSoft Group – Vinh University

BµI 1 (Buæi 1) ™

Project Explorer

Pointer

FORM Cöa sæ Project explorer Thuéc tÝnh (Properties) Sù kiÖn (Event) Ph−¬ng thøc (Method)

Picture Text Box Check Box Option/Radio Button List Box

Lµm viÖc víi c¸c ®iÒu khiÓn Label, Textbox, Command button. ™ Bµi tËp ™

Timer Drive List Box File List Box Image

Label Group Box Command Button Combo Box Horizontal Scroll Bar Vertical Scroll Bar Directory List Box Shape Line Data Control OLE

VSoft Group – Vinh University

Slide 2

Slide 5

VSoft Group – Vinh University

Khëi ®éng vb

Events - Sù kiÖn ™ ™

Form, mäi thø trªn form lµ mét ‘Control - §iÒu khiÓn’ Mçi Control cã mét tËp t−¬ng øng c¸c Events

- M∙ cña mçi sù kiÖn cã thÓ ®−îc bæ sung vµo 1 event

Click ®óp - Standard EXE

C¸c sù kiÖn cña Command Button

- Khi mét sù kiÖn x¶y ra, nÕu m∙ ®−îc ®−a vµo sù kiÖn th× nã sÏ thùc hiÖn ®o¹n m∙, ng−îc l¹i nã thùc hiÖn ngÇm ®Þnh

VSoft Group – Vinh University

Slide 3

VSoft Group – Vinh University

Slide 6

Page 1

L ậ p tr ì nh Visual Basic 6.0

VSoft – Vinh University

Project Explorer Cöa sæ Properties ™

Controls - Properties – Mçi ®iÒu khiÓn cã mét tËp c¸c thuéc tÝnh. – TÊt c¶ c¸c ®iÒu kiÓn ®Òu cã mét sè thuéc tÝnh chung nh− : Height, Name, Color,...

C¸c thuéc tÝnh cã thÓ ®−îc thay ®æi khi thiÕt kÕ hoÆc ë thêi gian ch¹y ch−¬ng tr×nh. ™ Thêi ®iÓm thiÕt kÕ

Cöa sæ code

Cöa sæ Form

™

–

Main Menu

Thay ®æi c¸c thuéc tÝnh cña ®iÒu kiÓn ®∙ chän. VÝ dô:

Tªn Form dïng ®Ó truy nhËp VSoft Group – Vinh University

Slide 7

TÖp ch−¬ng tr×nh Visual Basic

Thêi ®iÓm ch¹y ch−¬ng tr×nh –

Trong ch−¬ng tr×nh:

™

Project File

™

Form Files

<§iÒu khiÓn>. =

–

–

Private Sub Form1_load() Form1.Caption = “Main Menu” End Sub

–

§Ó truy nhËp mét ®iÒu khiÓn trªn mét form kh¸c, ph¶i sö dông tªn form ®øng tr−íc tªn ®iÒu khiÓn: VÝ dô:

Main Menu

™

Custom Controls

™

Save As...

–

–

Slide 8

‘.FRM’ ‘.FRX’

Modules

Form2.Label5.Caption = “Welcome to VB” VSoft Group – Vinh University

‘.VBP’

™

–

™

Slide 10

VSoft Group – Vinh University

Cöa sæ Properties ™

Tªn tÖp ghi trªn ®Üa

‘.BAS’ ‘.VBX’ files Possible problem Slide 11

VSoft Group – Vinh University

Xö lý sù kiÖn

Sö dông Help cña Control Chän ®iÒu khiÓn cÇn Help Ên phÝm F1 ™ Chó ý: §Ó Help ®−îc cÇn ph¶i cµi ®Æt bé MSDN ™

Sù kiÖn g©y ra (bµn phÝm, con chuét)

™

Code Executed

VSoft Group – Vinh University

Slide 9

F1

VSoft Group – Vinh University

Slide 12

Page 2

L ậ p tr ì nh Visual Basic 6.0 C¸c thuéc tÝnh cña FORM ™ C¸c

Controls: Label

thuéc tÝnh c¬ b¶n

)Name )Backcolor )BorderStyle )Caption )Enabled )Font )ForeColor )Icon )MaxButton )MinButton )MouseIcon )Moveable )Picture )StartUpposition

Tªn form dïng ®Ó truy nhËp trong Project Mµu nÒn cña form KiÓu ®−êng viÒn cña Form Dßng v¨n b¶n hiÓn thÞ trªn tiªu ®Ò Form Cho phÐp thao t¸c trªn Form hay kh«ng ? Ph«ng ch÷ trªn Form Mµu ch÷ BiÓu t−îng cña Form Cã/kh«ng nót phãng to cña Form Cã/kh«ng nót thu nhá cña Form BiÓu t−îng tù ®Þnh nghÜa cña chuét. Cã/Kh«ng di chuyÓn form ¶nh nÒn cña Form VÞ trÝ xuÊt hiÖn cña Form khi ch¹y

)Visible

Cã/kh«ng thÊy ®iÒu khiÓn

™

Mét label chØ dïng ®Ó hiÓn thÞ c¸c th«ng tin

™

§Ó hiÓn thÞ c¸c th«ng tin: – Thay ®æi thuéc tÝnh Caption t¹i thêi ®iÓm thiÕt kÕ. – G¸n gi¸ trÞ cho thuéc tÝnh Caption t¹i thêi ®iÓm ch¹y ch−¬ng tr×nh

Command1_Click() Label1.Caption = “Welcome to VB” End Slide 13

VSoft Group – Vinh University

Controls: Text Box

Tuú theo c¸c ®iÒu khiÓn kh¸c nhau th× cã c¸c Evens kh¸c nhau, nh−ng nãi chung cã mét sè Even th−êng ®−îc sö dông: Sù

kiÖn

)Load )UnLoad )Active )Click )Change )DblClick )Gotfocus )KeyDown )KeyPress )LostFocus )MouseDown )MouseMove )MouseUp

™

Textbox

™

C¸c thuéc tÝnh c¬ b¶n:

Slide 14

+ Text : lµ gi¸ trÞ cña text box. + DataFormat : §Þnh d¹ng kiÓu d÷ liÖu + Enabled : Cã/kh«ng cho thao t¸c trªn textbox + Font , ForeColor: ®Æt Font ch÷, Mµu ch÷ + MaxLength: §é dµi tèi ®a khi nhËp d÷ liÖu (tÝnh b»ng ký tù) + MultiLine: Cã/kh«ng cho phÐp nhËp nhiÒu dßng d÷ liÖu. + PasswordChar: Ký tù xuÊt hiÖn khi sö dông Textbox lµm Password. + ScrollBar: Cã/kh«ng thanh tr−ît khi nhËp d÷ liÖu nhiÒu dßng. ™

Mét sè thuéc tÝnh kh¸c ®−îc nãi thªm ë phÇn Command button.

™

Chóng ta còng cã thÓ g¸n néi dung cña mét Text Box cho mét biÕn.

™

C¸c sù kiÖn: Xem trang c¸c sù kiÖn th−êng dïng

HiÓn thÞ form Èn Form In d÷ liÖu lªn form Xo¸ d÷ liÖu ®∙ in. Lµm t−¬i d÷ liÖu trªn form. Di chuyÓn Form.

Slide 17

VSoft Group – Vinh University

C¸c ph−¬ng thøc th−êng dïng )Show )Hide )Print )Clear )Refresh )Move

lµ mét ®iªu khiÓn cho phÐp nhËp/söa d÷ liÖu.

+ Name: Tªn cña Textbox (th−êng ®−îc ®Æt l¹i b»ng tiÒn tè txt)

XÈy ra khi Form ®−îc khëi ®éng. Form bÞ gi¶I phãng. KÝch ho¹t form, ®−îc thùc hiÖn sau ph−¬ng Click chuét vµo ®iÒu khiÓn Söa ®æi chuæi ký tù trong text box hoÆc combo box Click ®óp chuét lªn ®iÒu khiÓn Di chuyÓn con trá ®Õn ®èi t−îng. NhÊn 1 phÝm khi con trá n»m ë ®iÒu khiÓn Ên vµ th¶ mét phÝm bÊt kú khi con trá n»m ë ®iÒu khiÓn. XÈy ra khi di chuyÓn con trá khái ®iÒu khiÓn NhÊn 1 nót chuét bÊt kú khi con trá chuét ë ®iÒu khiÓn Di chuyÓn con trá chuét ngang qua ®èi t−îng Th¶ nót chuét ®∙ Ên khi con trá chuét ë ®iÒu khiÓn

VSoft Group – Vinh University

Slide 16

VSoft Group – Vinh University

C¸c Evens th−êng dïng ™

VSoft – Vinh University

Command Button ™

Command button hiÖn mét sù kiÖn nµo ®ã.

™

C¸c thuéc tÝnh c¬ b¶n:

lµ mét ®iÒu khiÓn th−êng ®−îc dïng ®Ó thùc

+ Name: Tªn cña Command Button (th−êng ®−îc ®Æt l¹i b»ng tiÌn tè cmd) + Caption: Dßng text hiÓn thÞ trªn Command Button + Enabled : Cã/kh«ng cho phÐp thao t¸c trªn command. + Style: KiÓu nót (0 - Standard, 1- Graphic) + TabIndex: Sè thø tù khi Ên phÝm TAB + TabStop: Cã/kh«ng ng¨n chÆn khi Ên phÝm TAB ®Õn. + ToolTipText: Dßng Text xuÊt hiÖn khi chuyÓn con trá chuét ®Õn nót. + Visible: XuÊt hiÖn/ Èn nót command button. ™

Sù kiÖn: + Click: Sù kiÖn xÈy ra khi Click chuét vµo Command Button.

VSoft Group – Vinh University

Slide 15

VSoft Group – Vinh University

Slide 18

Page 3

L ậ p tr ì nh Visual Basic 6.0 Thªm mét form ™

VSoft – Vinh University

Chän form cÇn ch¹y

Click chuét lªn menu Project -> Add form

™

KÝch chuét vµo menu Project -> Project1 Properties.. Chän form cÇn ch¹y

• New -> Thªm form míi • Existing -> Thªm form ®∙ cã • Trªn cöa sæ Project xuÊt hiÖn

Slide 19

VSoft Group – Vinh University

Slide 22

VSoft Group – Vinh University

VÞ dô ®Çu tiªn

Bµi tËp thùc hµnh 1 (Buæi 2)

NhËp 2 c¹nh h×nh ch÷ nhËt tÝnh diÖn tÝch. Thªm 1 form míi ™ Ghi form: File -> Save ™ ThiÕt kÕ Form:

™

Text=“”

™

™

Bµi 1: T¹o mét Form cho phÐp: – Mét textbox ®Ó nhËp b¸n kÝnh cña ®−êng trßn.

™

– Mét textbox hiÓn thÞ kÕt qu¶ cña diÖn tÝch h×nh trßn – Mét textbox hiÓn thÞ kÕt qu¶ cña chu vi ®−êng trßn. – Ba command button, mét ®Ó tÝnh, mét cho tÝnh tiÕp vµ mét ®Ó tho¸t.

Name: Canh1

Bµi 2: T¹o 2 form – Form 1 cã 1 text box ®Ó nhËp c¸c dßng v¨n b¶n vµ 1 nót lÖnh “TiÕp”

Label1 Label2

Name: CmdTinh

– Form 2 cã 1 text box vµ 2 nót lÖnh “Trë vÒ” vµ “KÕt thóc”.

Caption=“TÝnh”

– Yªu cÇu:

Name: CmdThoat

Label3

Caption=“Tho¸t”

‹

Ch¹y Form1, sau ®ã nhËp d÷ liÖu vµo Textbox, bÊm nót “TiÕp”

‹

HiÖn thÞ Form2 trong ®ã textbox cña Form2 lÊy d÷ liÖu cña Textbox Form1.

‹

BÊm nót “Trë vÒ” ®Ó vÒ Form1 hoÆc bÊm nót “KÕt thóc” ®Ó dõng ch−¬ng tr×nh.

Name: Canh2 Text=“” Slide 20

VSoft Group – Vinh University

VSoft Group – Vinh University

ViÕt c¸c sù kiÖn

Bµi 2. Có ph¸p ng«n ng÷ To¸n tö KiÓu d÷ liÖu ™ Khai b¸o biÕn, h»ng ™ Ph¹m vi cña biÕn, h»ng ™ C¸c hµm ™ C¸c cÊu tróc lËp tr×nh ™

- KÝch ®óp vµo nót

™

- KÝch ®óp vµo nót

– – – –

click click ™

VSoft Group – Vinh University

Slide 23

Slide 21

If ... then Select case .... While .... Do .... loop while

Bµi tËp

VSoft Group – Vinh University

Slide 24

Page 4

L ậ p tr ì nh Visual Basic 6.0

Khai b¸o biÕn - h»ng

Arithmetic Operators ™ ™

Arithmetic operation

Symbol

VSoft – Vinh University

Khai b¸o biÕn: Dim VariableName As type

Example Tõ kho¸

Exponentiation

^

2^3 = 8

Multiplication & division

*, /

2/4 = 0.5

Addition & subtraction

+, -

2+2=4

Integer division Modulo arithmetic

\ Mod

7\2 = 3 7 Mod 2 = 1

String addition String concatenation

+ &

Lín h¬n

<

BÐ h¬n

™

=

B»ng nhau

™

>=

Lín h¬n hoÆc b»ng

™

<=

BÐ h¬n hoÆc b»ng

™

<>

Kh¸c nhau

™

And

vµ

™

Or Not

HoÆc Phñ ®Þnh

™

– Ph¶i b¾t ®Çu b»ng 1 ký tù – TiÕp theo lµ c¸c ký tù, sè, hoÆc ký tù g¹ch d−íi ( _ ) – Kh«ng ®−îc trïng víi tõ kho¸

Ph¹m vi cña biÕn - h»ng møc form ™

C¸c khai b¸o møc form ®−îc khai b¸o ë: General Declarations vµ lµ biÓn tæng thÓ cña form.

™

BiÕn, h»ng tæng thÓ ®−îc dïng trong tÊt c¶ c¸c hµm, thñ tôc vµ c¶ trong c¸c sù kiÖn cña c¸c ®iÒu khiÓn trong Form.

™

™

Slide 26

Byte Integer Long (integer) Single(floating-point) Double(floating point) Currency(scaled integer) String Variant Boolean Date User-defined(Type) VSoft Group – Vinh University

Storage size 1 bytes 2 bytes 4 bytes 4 bytes 8 bytes 8 bytes 1 byte/char Varies 2 bytes 8 bytes size bytes

Slide 28

VSoft Group – Vinh University

Form1 General Declarations Dim sName1 As String Sub Command1_Click () Dim iNum1 As Integer Available variables: sName1, iNum1

C¸c khai b¸o trong mét hµm, thñ tôc, sù kiÖn ®−îc khai trong hµm hoÆc thñ tôc vµ lµ biÕn côc bé.

Sub Command2_Click () Dim iNum2 As Integer

BiÕn tæng thÓ cña Project xem phÇn sau.

Available variables: sName1 iNum2 Slide 29

VSoft Group – Vinh University

Standard Data Types Data type

Quy t¾c ®Æt tªn biÕn:

Const Pi = 3.14

"abc" + "def "= “abcdef" "abc" & "def "= "abcdef"

VSoft Group – Vinh University

™

™

(remainder 1)

C¸c to¸n tö so s¸nh >

Khai b¸o h»ng: Const ConstName = VÝ dô: Dim iNumber As Integer Dim x, y As Single

(goes 3 times)

Slide 25

™

KiÓu d÷ liÖu

™

™

VSoft Group – Vinh University

™

Tªn biÕn

(2*2*2 or 23)

String FUNCTIons

Example Name iAge iCount lMax fSalary dWidth cPrice sName vData

uPerson Slide 27

™

InStr(str1, str2)

Tr¶ vÒ vÞ trÝ cña x©u str2 trong str1

™

Lcase(str)

ChuyÓn x©u str thµnh x©u ch÷ th−êng

™

Ucase(str)

ChuyÓn x©u str thµnh x©u ch÷ hoa

™

Len(str)

Tr¶ vÒ chiÒu dµI cña x©u str

™

Left(str, n)

Tr¶ vÒ n ký tù ®Çu tiªn trong x©u str

™

Right(str,n)

Tr¶ vÒ n ký tù cuèi cïng trong x©u str

™

Mid(str, m, n)

Tr¶ vÒ x©u cã n ký tù trÝch tõ x©u str b¾t ®Çu ë vÞ trÝ m.

™

Ltrim(str)

Tr¶ vÒ x©u bá c¸c ký tù tr¾ng bªn tr¸I cña str

™

Rtrim(str)

Tr¶ vÒ x©u bá c¸c ký tù tr¾ng bªn ph¶i cña str

™

Trim(str)

Tr¶ vÒ x©u bá c¸c ký tù tr¾ng hai ®Çu trong x©u str.

™

StrComp(str1, str2) Tr¶ vÒ 0 nÕu =, -1 nÕu str1<str2, 1 nÕu str1>str2

™

Val(str)

VSoft Group – Vinh University

ChuyÓn x©u str thµnh sè Slide 30

Page 5

L ậ p tr ì nh Visual Basic 6.0

Có ph¸p: If...Then…End if

Other Functions ™

Format(expression, format): chuyÓn expression thµnh ®Þnh d¹ng.

™

™

Str(number) Round(expression,n) Sqr(expression) IsNumeric(expression)

™

IsDate(expression)

™

IsNull(expression)

™

IsEmpty(expression)

™ ™

VSoft – Vinh University

: ChuyÓn sè thµnh x©u. : Quy trßn biÓu thøc víi n sè thËp ph©n. : C¨n bËc 2 cña biÓu thøc : Tr¶ vÒ TRUE nÕu expression lµ bt sè Tr¶ vÒ FALSE nÕu expression kh«ng lµ bt sè : Tr¶ vÒ TRUE nÕu expression lµ bt ngµy Tr¶ vÒ FALSE nÕu expression kh«ng lµ bt ngµy : Tr¶ vÒ TRUE nÕu expression lµ bt Null Tr¶ vÒ FALSE nÕu expression kh¸c Null : Tr¶ vÒ TRUE nÕu expression lµ bt rçng Tr¶ vÒ FALSE nÕu expression kh¸c rçng

™

Cho phÐp cã nhiÒu h¬n 1 lÖnh (statement) trong ®iÒu kiÖn.

™

D¹ng lÖnh: If ( condition ) Then {action 1 when condition is TRUE} {actions ... when condition is TRUE} End If

™

VÝ dô: KiÓm tra text1 kh«ng ph¶i lµ sè th× tho¸t thñ tôc If Not isNumeric(text1.text) Then Msgbox “Khong phai la so” Exit sub End If

Ngoµi ra cßn mét sè hµm to¸n häc nh− abs, int, sin,.. Slide 31

VSoft Group – Vinh University

C¸c cÊu tróc lËp tr×nh

Có ph¸p: If...Then...Else…end if ™

™ CÊu

tróc rÏ nh¸nh

™ CÊu

tróc chän

™ CÊu

tróc lÆp

Slide 34

VSoft Group – Vinh University

D¹ng lÖnh: If ( condition ) Then {actions ... when condition is TRUE} Else {actions ... when condition is FALSE} End If

™

VÝ dô: KiÓm tra text1 lµ sè ch¼n hay sè lÏ If (val(text1.text) mod 2 <>0) Then txtKetqua.Text = “Text1 lµ sã lÏ” Else txtKetqua.Text = “Text1 lµ sè ch½n” End If

VSoft Group – Vinh University

Slide 32

Có ph¸p: If...Then...

Có ph¸p: If...Then...ElseIf...Else.. ™

™

Trong Visual Basic cã hai d¹ng if .. then ...chÝnh:

™

D¹ng 1:

ElseIf ( condition ) Then {actions ... when condition is TRUE} Else {actions ... when both other conditions are FALSE} End If

If ( condition ) Then {actions when condition is TRUE} VÝ dô: KiÓm tra sè nhËp vµo Text1 lµ sè ch¼n hay kh«ng ? If (val(text1.text) mod 2 = 0) Then txtKetqua.Text = “Sè ch¼n”

™

D¹ng 2:

™

If ( condition ) Then {TRUE action} Else {FALSE action} VÝ dô: If (n mod 2<>0) Then Label1.Caption = ”Sè lÏ" Else Label1.Caption = ”Sè ch¼n"

VSoft Group – Vinh University

VÝ dô: If (val(Text1.Text) mod 3 = 0) Then txtKetqua.Text = ” Text1 chia het cho 3” ElseIf (val(Text1.text) mod 3 = 1) Then txtKetqua.Text =“ Text1 chia 3 du 1” Else txtKetqua.Text =“ Text1 chia 3 du 2” End If

If...Then...Else...

™

D¹ng lÖnh: If ( condition ) Then {actions ... when condition is TRUE}

If...Then... ™

Slide 35

VSoft Group – Vinh University

Slide 33

VSoft Group – Vinh University

Slide 36

Page 6

L ậ p tr ì nh Visual Basic 6.0

C©u lÖnh: Select Case…End select

KÕt hîp c¸c §K víi AND vµ OR ™

™

™

If (Condition 1) AND (Condition2) Then Condition 1

Condition 2

D¹ng lÖnh:

Gi¸ trÞ biÓu thøc

FALSE

FALSE

FALSE

TRUE

FALSE

FALSE

FALSE

TRUE

FALSE

TRUE

TRUE

TRUE

Select Case test expression Case expression 1 {actions ... when expression 1 is TRUE} Case expression 2, expression 3 {actions ... when expression 2 or 3 are TRUE} Case expression 4 To expression 5 {actions ...when in expression 4 To 5 range is TRUE} Case Is = expression 6 {actions ... when test expression = expression 6} Case Is >= expression 7 {actions ... when test expression >= expression 7} Case Else {actions ... when all conditions are FALSE} End Select

If (Condition 1) OR (Condition2) Then Condition1

Condition 2

Gi¸ trÞ biÓu thøc

FALSE

FALSE

FALSE

TRUE

FALSE

TRUE

FALSE

TRUE

TRUE

TRUE

TRUE

TRUE Slide 37

VSoft Group – Vinh University

C©u lÖnh: Select Case…End select ™

If NOT (Condition 1) Then BiÓu thøc gi¸ trÞ

FALSE TRUE

VÝ dô: Select Case iVal Case 1 Label1.Caption = "Case 1" Case 2, 3, 4 Label1.Caption = "Case 2, 3, 4" Case 5 To 7 Label1.Caption = "Case 5 To 7" Case Is = iEight Label1.Caption = "Case Is = iEight" Case Is >= 9 Label1.Caption = "Case Is >= 9" Case Else Label1.Caption = "Case Else

Cã thÓ ®¶o kÕt qu¶ cña mét biÓu thøc logic b»ng c¸ch dïng to¸n tö NOT:

Condition 1

Slide 40

VSoft Group – Vinh University

To¸n tö NOT ™

VSoft – Vinh University

TRUE FALSE

End Select VSoft Group – Vinh University

Slide 38

VSoft Group – Vinh University

C©u lÖnh lÆp

C©u lÖnh : Select Case ™

Slide 41

™

D¹ng ®¬n gi¶n nhÊt cña lÖnh Select lµ:

C¸c cÊu tróc lÆp trong Visual Basic:

Select Case test expression

)For...Next

Case expression list

)For Each … Next

{actions ... when expression list is TRUE}

)Do while...Loop

Case Else

) Do

{actions ... when all conditions are FALSE}

loop … while

)While ....Wend

End Select

)Do…. Loop Until condition ™

Cã thÓ cã nhiÒu biÓu thøc trong expression list.

™

Case Else cã thÓ cã hoÆc kh«ng

VSoft Group – Vinh University

)Do Until condition……. Loop

Slide 39

VSoft Group – Vinh University

Slide 42

Page 7

L ậ p tr ì nh Visual Basic 6.0

VÝ dô vÒ For Each

CÊu tróc: For...Next ™ ™

D¹ng lÖnh:

[Exit for]

Next [Count]

™

Count cã thÓ lµ c¸c kiÓu d÷ liÖu sau: – Integer or Long – Single or Double – Currency

VSoft Group – Vinh University

T−¬ng tù, x©y dùng mét thñ tôc ®Æt c¸c Label, Textbox trong Form cã tªn font lµ “.VnArial”, cë ch÷ lµ 10, mµu ch÷ ®á. ™ Gäi c¸c thñ tôc trong Form: Call ClearTexts ™

Slide 43

CÊu tróc : Do While ….Loop

Cã thÓ chØ ra kÝch th−íc b−íc nh¶y (step size):

™

Sub Command1_Click () Dim iCount As Integer For iCount = 100 To 0 Step -10 Print "iCount = "; iCount Next iCount End Sub ™ ™

NÕu kh«ng chØ ra Step biÕn Count sÏ tù t¨ng lªn 1 ë mçi lÇn lÆp. ™ Kh«ng nªn thay ®æi gi¸ trÞ cña biÕn ®iÒu khiÓn trong vßng lÆp Slide 44

§Çu tiªn kiÓm tra ®iÒu kiÖn (condition) C¸c lÖnh trong Do While ®−îc thùc hiÖn trong khi condition lµ True, kÕt thóc khi nã lµ False.

VSoft Group – Vinh University

CÊu tróc: For Each...Next ™

D¹ng lÖnh: Do While condition statements [exit do] Loop

™

VSoft Group – Vinh University

Slide 46

VSoft Group – Vinh University

CÊu tróc: For...Next ™

X©y dùng mét thñ tôc cho phÐp xo¸ c¸c Textbox vÒ tr¾ng: Private Sub ClearTexts() Dim Dk As Control ‘Lµ kiÓu ®iÒu khiÓn For Each Dk In Me If TypeOf Dk Is TextBox Then Dk.Text = "" Next End Sub

For Count = StartValue To EndValue [Step Increment] statements... ™

VSoft – Vinh University

Slide 47

CÊu tróc: Do...Loop While

D¹ng lÖnh:

™

D¹ng lÖnh:

For Each element In Group Do

statement

statements

Next [element]

[Exit do] Loop While condition

™

Vßng lÆp nµy ®−îc dïng khi kh«ng biÕt chÝnh x¸c bao nhiªu phÇn tö trong tËp hîp.

™

C¸c phÇn tö trong tËp hîp chØ cã thÓ lµ biÕn Variant, biÕn Object.

VSoft Group – Vinh University

Slide 45

™

C©u lÖnh Ýt nhÊt ®−îc thùc hiÖn 1 lÇn.

™

C¸c c©u lÖnh trong Do ...Loop While ®−îc thùc hiÖn trong khi condition lµ True.

VSoft Group – Vinh University

Slide 48

Page 8

L ậ p tr ì nh Visual Basic 6.0

Bµi 3. C¸c ®iÒu khiÓn kh¸c, Modul (Buæi 5)

Mét sè cÊu tróc lËp tr×nh kh¸c ™

VSoft – Vinh University

Check box Option Buttons ™ Frame ™ Combo box ™ List box ™ Shape ™ Line ™ Image ™ Menu ™ Modul ™

Visual Basic cung cÊp mét sè cÊu tróc lÆp kh¸c gåm:

™

While condition......Wend Do…... Loop Until condition Do Until condition……. Loop ™

TÊt c¶ c¸c cÊu tróc lÆp nµy cã thÓ ®−îc biÓu diÔn víi cÊu tróc: Do While Loop vµ For Next

VSoft Group – Vinh University

Slide 49

VSoft Group – Vinh University

Controls: Check Box

Bµi thùc hµnh 2 (Buæi 4) ™

™

Bµi 1: T¹o 1 form gi¶i ph−¬ng tr×nh bËc 1.

™

Bµi 2: T¹o form ®Ó nhËp 3 sè a, b, c. KiÓm tra 3 sè ®ã t¹o thµnh c¸c c¹nh cña tam gi¸c hay kh«ng ?. NÕu ®−îc th× tÝnh diÖn tÝch, chu vi vµ hiÓn thÞ kÕt qu¶. NÕu kh«ng th× th«ng b¸o.

™

™

– KiÓm tra d÷ liÖu nhËp vµo lµ sè míi ch¹y nÕu kh«ng th× b¸o lçi.

Slide 50

Private Sub Check3_Click() If Check1.Value = 1 Then Text1.FontBold = True Else Text1.FontBold = False End If End Sub

VSoft Group – Vinh University

Bµi thùc hµnh 2 (Buæi 4 - tiÕp)

Slide 53

Option Buttons C¸c Option button cho phÐp chän 1 trong c¸c kh¶ n¨ng cã thÓ. NÕu mét Option ®−îc chän th× c¸c Option kh¸c bÞ bá chän. ™ Khi mét Option Button ®−îc chän, thuéc tÝnh value ®−îc ®Æt b»ng True, ng−îc l¹i ®−îc ®Æt b»ng False. )Option1.Value = False ’unchecked )Option1.Value = True ’checked VÝ dô: ™

Bµi 3: T¹o 1 form cã 1 textbox ®Ó nhËp ®iÓm TBC cña 1 sinh viªn. Mét text box ®Ó hiÓn thÞ xÕp lo¹i. Sau khi Ên Enter trªn text box nhËp th× hiÓn thÞ kÕt qu¶ theo ®iÒu kiÖn sau:

™

– NÕu TBC<5 th× xÕp lo¹i =“YÕu” – NÕu 5<= TBC<7 th× xÕp lo¹i =“T.b×nh”

Private Sub Option1_Click() If Option1.Value = True Then Text1.FontUnderline = False End If End Sub

– NÕu 7<=TBC<8 th× xÕp lo¹i =“Kh¸” – NÕu 8<=TBC th× xÕp lo¹i =“Giái” ™

VÝ dô:

Private Sub Check3_Click() If Check3.Value = 1 Then Text1.FontUnderline = True Else Text1.FontUnderline = False End If End Sub

– Ên Enter th× chuyÓn sang ®iÒu khiÓn kh¸c

™

C¸c Check boxes cho phÐp chän nhiÒu kh¶ n¨ng ®ång thêi cã thÓ. Khi check box ®−îc chän, thuéc tÝnh value ®−îc ®Æt b»ng 1, ng−îc l¹i ®−îc ®Æt b»ng 0. )Check1.Value = 0 ’unchecked )Check1.Value = 1 ’checked

Chó ý:

VSoft Group – Vinh University

Slide 52

Bµi 4: T¹o mét form tÝnh tæng S = 1+3+5+7+9+.. – Sö dông vßng lÆp For

Private Sub Option2_Click() If Option2.Value = True Then Text1.FontUnderline = True End If End Sub

– Sö dông Do ...while – Sö dông Do ...Until – Chó ý: T¹o 3 nót tÝnh theo 3 tr−êng hîp. VSoft Group – Vinh University

Slide 51

VSoft Group – Vinh University

Slide 54

Page 9

L ậ p tr ì nh Visual Basic 6.0 Frames generally ™

Bæ sung mét môc Combo box

Frame lµ mét khung dïng ®Ó nhãm c¸c ®iÒu khiÓn víi nhau.

VSoft Group – Vinh University

Slide 55

™

Khi ch¹y ch−¬ng tr×nh sö dông AddItem ®Ó bæ sung mét item vµo mét Combo Box. Combo1.AddItem “zero” Combo1.AddItem “one” Combo1.AddItem “two” Combo1.AddItem “three”

™

HoÆc ë thêi gian thiÕt kÕ: Bæ sung vµo List prop Slide 58

VSoft Group – Vinh University

Frames and Option buttons ™

VSoft – Vinh University

thuéc tÝnh, ph−¬ng thøc cña Combo box ‹Thuéc tÝnh

§Ó cã nhiÒu h¬n mét lùa chän trong c¸c option button trªn form, ph¶i ®Æt c¸c nhãm cÇn chän vµo mét frame.

) Combo1.Text

: Gi¸ trÞ d÷ liÖu cña Item trong ComboBox ®−îc chän.

) Combo1.ListIndex

: Tr¶ vÒ chØ sè cña hµng hiÖn thêi trong ComboBox.

) Combo1.ListCount

: Tr¶ vÒ sè phÇn tö trong mét ComboBox.

)Combo1.Locked = TRUE/FALSE : Söa/kh«ng söa vïng text. )Sorted

: ComboBox Cã thÓ ®−îc s¾p/kh«ng s¾p chØ ë thêi gian thiÕt kÕ

)Style

: KiÓu cña ComboBox (chØ thay ®æi ë thêi gian thiÕt kÕ)

‹Ph−¬ng thøc ) Combo1.Additem (Text) : Bæ sung mét item (Text) vµo d−íi cïng danh s¸ch. ) Combo1.Additem (Text, Index) : Bæ sung mét item (Text) tíi vÞ trÝ (Index). ) Combo1.RemoveItem

: Xo¸ dßng ®−îc chän hiÖn thêi tõ danh s¸ch.

) Combo1.RemoveItem (Index) : Xo¸ mét item ë vÞ trÝ (Index) trong sanh s¸ch. ) Combo1.Clear VSoft Group – Vinh University

Slide 56

: Xo¸ toµn bé danh s¸ch.

VSoft Group – Vinh University

Controls: Combo Box

Slide 59

VÝ dô vÒ COMBO BOX ™

™

Mét Combo box cã thÓ ®−îc dïng ®Ó nhËp, hiÓn thÞ & chän c¸c môc (items).

™

Mçi dßng trong combo box ®−îc xem nh− mét môc (item)

™

Mét sù kÕt hîp (combination) cña mét Text Box vµ mét List Box - gäi lµ mét Combo Box

ThiÕt kÕ Form nh− sau:

Combo1

Combo2

Text1 Khi nµo dïng Combo Box thay v× dïng List Box ?

™

Khi cÇn tiÕt kiÖm kh«ng gian trªn Form.

™

Mét combo box chøa mét textbox v× vËy cã thÓ gâ trùc tiÕp thay v× chän tõ combo box.

VSoft Group – Vinh University

Slide 57

™

Khi click combo1 th× mét danh s¸ch font xuÊt hiÖn, chän 1 font ch÷ th× dßng text cña text1 sÏ ®æi font.

™

Khi click combo2 th× mét danh s¸ch cë ch÷ xuÊt hiÖn, chän 1 cë ch÷ th× dßng text cña text1 sÏ ®æi cë ch÷.

VSoft Group – Vinh University

Slide 60

Page 10

L ậ p tr ì nh Visual Basic 6.0 X©y dùng Code

VSoft – Vinh University

thuéc tÝnh, ph−¬ng thøc cña list box ™

Thuéc tÝnh

™

List1.Text

™

List1.Selected : ChØ ra dßng cÇn chän cña list box

™

List1.ListIndex : ChØ ra dßng ®−îc chän tõ list Box iSelItem = List1.ListIndex

™

List1.ListCount : ChØ ra sè items hiÖn thêi trong List Box

™

List1.Sorted : Tù ®éng s¾p xÕp néi dung cña List Box

™

Ph−¬ng thøc

™

List1.RemoveItem : Xo¸ item chØ ra tõ List Box

™

List1.Clear : Xo¸ toµn bé c¸c dßng cña List Box

: Gi¸ trÞ d÷ liÖu cña Item trong ListBox ®−îc chän.

List1.Selected(1) = TRUE - Chän hµng 1

VSoft Group – Vinh University

Slide 61

Controls: List Box

VÝ dô vÒ List box

™

Mét list box cã thÓ ®−îc dïng ®Ó hiÓn thÞ & chän c¸c môc.

™

Mçi dßng trong list box ®−îc xem nh− mét môc (item) Mçi item cã mét chØ sè b¾t ®Çu tõ 0

™

™

ThiÕt kÕ Form nh− sau:

™

Khi click Khi click Khi click Khi click

™ ™ ™

VSoft Group – Vinh University

Slide 62

Bæ sung mét môc list box ™

™

Slide 64

VSoft Group – Vinh University

> (Command1_Click) >> (Command2_Click) < (Command3_Click) << (Command4_Click)

th× mét Item tõ List1 chuyÓn sang list2 th× c¸c Item tõ List1 chuyÓn sang list2 th× mét Item tõ List2 chuyÓn sang list1 th× c¸c Item tõ List1 chuyÓn sang list2 Slide 65

VSoft Group – Vinh University

X©y dùng Code

Khi ch¹y ch−¬ng tr×nh sö dông AddItem ®Ó bæ sung mét item vµo mét List Box. List1.AddItem “zero” List1.AddItem “One” List1.AddItem “two” List1.AddItem “three” HoÆc ë thêi gian thiÕt kÕ: Bæ sung vµo List prop

Private Sub Form_Load() List1.AddItem "Zero" List1.AddItem "One" List1.AddItem "Two" List1.AddItem "Three" End Sub

Private Sub Command2_Click() For i = 0 To List1.ListCount - 1 List1.Selected(i) = True List2.AddItem List1.Text Next List1.Clear End Sub

Private Sub Command1_Click() If List1.Text <> "" Then List2.AddItem List1.Text List1.RemoveItem List1.ListIndex End If End Sub •Command3 vµ Command4 chØ ®¶o l¹i list1 thµnh list2 vµ list2 thµnh list1

VSoft Group – Vinh University

Slide 63

VSoft Group – Vinh University

Slide 66

Page 11

L ậ p tr ì nh Visual Basic 6.0 tabstop vµ tab Index

Controls: Image

™

ë ChÕ ®é ngÇm ®Þnh, Ên phÝm TAB ®Ó chuyÓn con trá tõ ®iÒu khiÓn nµy ®Õn ®iÒu khiÓn kh¸c trong form.

™

TabStop lµ thuéc tÝnh cña ®iÒu khiÓn cho phÐp cã/kh«ng (true/false) khi Ên phÝm TAB ®Ó chuyÓn con trá ®Õn ®iÒu khiÓn ®ã. – VÝ dô: §Æt thuéc tÝnh tabstop = false cña Command Button ®Ó chÆn viÖc Ên phÝm TAB ®Ó chuyÓn con trá ®Õn ®iÒu khiÓn.

™

VSoft – Vinh University

TabIndex lµ thuéc tÝnh cña ®iÒu khiÓn x¸c ®Þnh thø tù con trá sÏ chuyÓn ®Õn khi Ên phÝm TAB.

™

Mét Image control cã thÓ hiÓn thÞ mét ¶nh d¹ng bitmap, icon, metafile,JPEG, hoÆc GIF.

™

N¹p mét ¶nh vµo Image box Image1.Picture = LoadPicture(“a:\picture1.bmp”)

™

Thuéc tÝnh Stretch ®−îc dïng ®Ó thay ®æi kÝch th−íc cña ¶nh. – NÕu stretch = true th× ¶nh võa víi kÝch th−íc cña Image Box – NÕu stretch = false th× ¶nh lÊy kÝch th−íc cña ¶nh gèc.

– VÝ dô: Mét ®iÒu khiÓn víi tabindex = 0 th× khi khëi ®éng Form con trá sÏ n»m ë ®iÒu khiÓn nµy, sau ®ã nÕu Ên phÝm TAB th× con trá sÏ chuyÓn ®Õn c¸c ®iÒu khiÓn theo thø tù tabindex =1, tabindex = 2, tabindex = 3... VSoft Group – Vinh University

Slide 67

VSoft Group – Vinh University

Slide 70

Menu trong Visual Basic

Controls: Shape

)Chän biÓu t−îng Menu Editor ™

™

Mét shape control cho phÐp b¹n ‘draw’ trªn mµn h×nh

Caption xuÊt hiÖn trªn mµn h×nh

Cã thÓ chØ ra: Shape Color FillStyle

Name xuÊt hiÖn trong code module Shortcut e.g. Ctrl+O to Open

VSoft Group – Vinh University

Slide 68

Mét line lµ mét control cho phÐp vÏ c¸c ®−êng trªn mµn h×nh.

™

Th−êng ®−îc sö dông ®Ó trang trÝ mµn h×nh.

™

BorderWidth lµ thuéc tÝnh x¸c ®Þnh ®é réng cña ®−êng.

™

BorderStyle cho phÐp thay ®æi kiÓu cña ®−êng.

VSoft Group – Vinh University

Slide 71

Menu trong Visual Basic

Controls: Line ™

VSoft Group – Vinh University

Ph©n cÊp Menu … chØ ra menu con trong mét menu Use arrows ®Ó chØnh c¸c cÊp

Slide 69

VSoft Group – Vinh University

Slide 72

Page 12

L ậ p tr ì nh Visual Basic 6.0

Thªm mét Module vµo Project

Menu items Caption &File ®−a ra File, cho phÝm t¾t sö dông ALT+ F Name PrintOption xuÊt hiÖn trong code module

™

Slide 73

Slide 76

VSoft Group – Vinh University

BiÓn tæng thÓ trong project ™

Tõ menu Project -> Add Module.

The menu Add Module option

Hµnh ®éng xÈy ra sau khi click mét menu item sÏ ®−îc thùc hiÖn n¬i nµy (FormName.Show). VSoft Group – Vinh University

VSoft – Vinh University

Cöa sæ Code Module

Cã thÓ cã mét project yªu cÇu c¸c gi¸ trÞ truyÒn tõ form

™

Mét code module chØ chøa c¸c khai b¸o, hµm, thñ tôc.

™

VÝ dô:

)

X©y dùng 1 thñ tôc trong

nµy tíi form kh¸c hoÆc yªu cÇu c¸c hµm, thñ tôc ®−îc sö dông ë nhiÒu form. ™

Lµm thÕ nµo ®Ó thùc hiÖn ®iÒu nµy ?.

™

Më mét code module vµ khai b¸o biÕn, hµm, thñ tôc cã

Code Modul cho phÐp xo¸ thuéc tÝnh text cña c¸c text

kiÓu Public.

box ë c¸c form trong Project.

™

Module nµy ph¶i ph¶i ®−îc ghi cïng víi c¸c tÖp .vbp vµ .frm.

label, textbox víi font ch÷ lµ

™

Chó ý: Mét modul cã thÓ chøa nhiÒu hµm, thñ tôc

“.vnArial”, cë ch÷ 10, mµu ®á.

VSoft Group – Vinh University

)

Slide 74

X©y dùng 1 thñ tôc ®Ó ®Æt c¸c

PhÇn khai b¸o biÕn, viÕt c¸c hµm, thñ tôc.

VSoft Group – Vinh University

Slide 77

VÝ dô

Code Modules ™

Mét code module lµ mét tËp hîp c¸c biÕn, hµm, thñ tôc kh«ng ph¶i cña c¸c ®iÒu khiÓn.

™

Trong mét code module cã thÓ khai b¸o: – Variables dïng Dim - ChØ cã ph¹m vi trong Modul

Public Sub ClearText(frm As Form) Dim Ctrl As Control For Each Ctrl In frm If TypeOf Ctrl Is TextBox Then Ctrl.Text = "" Next End Sub

– Variables dïng Public - Cã ph¹m vi toµn Project – Constants dïng Const - ChØ cã ph¹m vi trong Modul Constants dïng Public Const - Cã ph¹m vi toµn Project ™

Cã thÓ viÕt c¸c hµm, thñ tôc trong mét code module. (Chóng ta kh«ng xem xÐt ë ®©y.)

™

BiÕn, hµm, thñ tôc khai b¸o víi tõ kho¸ Public cã ph¹m vi trong toµn project, v× vËy nã cã thÓ ®−îc truy nhËp ë mäi forms, mäi modul.

VSoft Group – Vinh University

Slide 75

Public Sub SetFont(frm As Form) Dim Ctrl As Control For Each Ctrl In frm If (TypeOf Ctrl Is TextBox)or(TypeOf Ctrl Is Label) Then Ctrl.FontName = “.vnArial” Ctrl.FontSize = 10 Ctrl.ForeColor = Rgb(255,0,0) End If Next End Sub VSoft Group – Vinh University

Slide 78

Page 13

L ậ p tr ì nh Visual Basic 6.0 Gäi c¸c modul ®∙ t¹o ™

Tr−íc khi gäi

BµI tËp mÉu - qu¶n lý l−¬ng ™

§Ó qu¶n lý tiÒn l−¬ng trong c¬ quan ng−êi ta tæ chøc 1 CSDL QLLUONG.MDB trong ACCESS víi 4 b¶ng cã cÊu tróc nh− sau:

™

Table 1 cã tªn lµ DONVI dïng ®Ó l−u c¸c ®¬n vÞ thuéc c¬ quan vµ cã cÊu tróc nh− sau:

Trong Form Load

™

™

MDV

Text

5

(M· ®¬n vÞ)

TEN

Text

30

(Tªn ®¬n vÞ)

Table 2 cã tªn lµ CHUCVU dïng ®Ó l−u c¸c chøc vô vµ cã cÊu tróc nh− sau: MCV

Text

5

(M· chøc vô)

TEN

Text

30

(Tªn chøc vô)

HSPC

Number/Double

(HÖ sè phô cÊp chøc vô)

Sau khi ch¹y form

VSoft Group – Vinh University

Slide 79

T¹o mét form nh− sau:

Yªu cÇu: - NhËp c¸c dßng vµo Text1 - Combo1 chøa tªn 4 font - Combo2 chøa 4 kÝch th−íc - List1 chøa: regular, bold, italic, bold italic. - Underline - Option1, Option2 : Èn vµ hiÖn text1. - Khi chän c¸c ®iÒu khiÓn th× thu«c tÝnh v¨n b¶n trong text1 thay ®æi theo.

VSoft Group – Vinh University

™ ™

Table 3 cã tªn lµ HOSO dïng ®Ó l−u hå s¬ c¸n bé vµ cã cÊu tróc nh− sau: MDV

Text

5

MCB

Text

8

(M· c¸n bé)

HOTEN

Text

25

(Hä vµ tªn)

NGAYSINH

Date (dd/mm/yyyy)

(Ngµy sinh)

MCV

Text

(M· chøc vô)

NGAYH

Date (dd/mm/yyyy)

(Ngµy h−ëng l−¬ng)

HSL

Number (Double)

(HSL l−¬ng hiÖn t¹i)

HOPDONG

Text

(Hîp ®ång lao ®éng)

1

- Nh÷ng ng−êi ®ang thêi gian hîp ®ång ®−îc ®¸nh dÊu “x”.

Slide 80

Slide 83

VSoft Group – Vinh University

™

Table 4 cã tªn lµ LUONG dïng ®Ó l−u l−¬ng th¸ng cña c¸n bé vµ cã cÊu tróc nh− sau:

Giíi thiÖu bµi to¸n

NAM

C¬ së d÷ liÖu ACCESS

THANG

Text

2

MCB

Text

8

HSL

Number

HSPC

Number

Kü thuËt ADO – C¸c ph−¬ng thøc – C¸c thuéc tÝnh

Number

TONG

Number

™

Lµm viÖc víi DataGrid

BHXH

Number

™

VÝ dô vÒ nhËp d÷ liÖu

BHYT

Number

™

X©y dùng c¸c modul cho c¸c form nhËp

TAMUNG

Number

CHIKHAC

Number

CONNHAN

Number

™

5

Bµi tËp

VSoft Group – Vinh University

(M∙ ®¬n vÞ)

Chó ý:

Bµi 4 : CSDL ACCESs vµ ADO ™

Slide 82

VSoft Group – Vinh University

™

Bµi thùc hµnh 3 (buæi 6) ™

VSoft – Vinh University

Slide 81

VSoft Group – Vinh University

Slide 84

Page 14

L ậ p tr ì nh Visual Basic 6.0 ™

Yªu cÇu: ViÕt ch−¬ng tr×nh qu¶n lý l−¬ng gåm c¸c chøc

VSoft – Vinh University

C¬ së d÷ liÖu Access

n¨ng sau: ™

™

1. NhËp d÷ liÖu vµo c¸c b¶ng gåm:

– Chän Blank Access Database -> OK ®Ó t¹o míi CSDL.

- Chøc n¨ng nhËp, söa, xo¸ chøc vô (MCV kh«ng trïng, HSCV>0) - Chøc n¨ng nhËp, söa, xo¸ hå s¬ (MCB kh«ng trïng). ™

™

Khëi ®éng – Chän Start ->Programs -> Microsoft Access

- Chøc n¨ng nhËp, söa, xo¸ ®¬n vÞ (MDV kh«ng trïng)

– Chän th− môc vµ Gâ vµo tªn CSDL cÇn t¹o ->Create ™

XuÊt hiÖn mét cöa sæ gåm

2. T×m kiÕm hå s¬ theo Tªn ®¬n vÞ, Tªn chøc vô, Hä

– Tables: §Ó t¹o, söa, xo¸ c¸c b¶ng

tªn, Hîp ®ång

– Queries: §Ó t¹o c¸c truy vÊn (tr¶ lêi c¸c c©u hái CSDL) – Forms, Reports, Moduls, Macros dïng ®Ó lËp tr×nh.

2. Chøc n¨ng t¹o b¶ng l−¬ng th¸ng: ™

- D÷ liÖu vµo lµ THANG vµ NAM

VSoft Group – Vinh University

Slide 85

BiÕt r»ng d÷ liÖu cña c¸c cét MCB, HSL ®−îc chuyÓn vµo tõ tÖp hå s¬, HSPC ®−îc chuyÓn tõ tÖp CHUCVU. Nh÷ng c¸n bé nµo cã t¹m øng hoÆc chi kh¸c th× nhËp d÷ liÖu vµo tr−êng TAMUNG hoÆc CHIKHAC. D÷ liÖu trªn c¸c cét cßn l¹i ®−îc tÝnh nh− sau:

C¬ së d÷ liÖu trong Access: Lµ mét tËp c¸c b¶ng d÷ liÖu, c¸c query, c¸c forms,... Slide 88

VSoft Group – Vinh University

C¬ së d÷ liÖu Access ™

– §èi víi nh÷ng c¸n bé biªn chÕ:

T¹o b¶ng d÷ liÖu – Chän Tables

TONG = 290000*(HSL+HSPC)

– Chän Create Table In Designed View ®Ó t¹o b¶ng míi, xuÊt hiÖn mét b¶ng míi.

BHXH = 0.05*TONG BHYT = 0.01*TONG

‹

Field name: Gâ vµo tªn tr−êng

CONNHAN = TONG - BHXH - BHYT - TAMUNG - CHIKHAC

‹

Data Type: Chän kiÓu d÷ liÖu øng víi tr−êng

‹

Description: M« t¶ chi tiÕt tr−êng (cã hoÆc kh«ng)

‹

Field Properties: Khai b¸o thuéc tÝnh cña tr−êng.

– §èi víi nh÷ng ng−êi hîp ®ång (cét HOPDONG ®¸nh dÊu “x” trong tÖp hå s¬): CONNHAN = 500000 - TAMUNG - CHIKHAC

– Field size: §é réng cña tr−êng

3. Chøc n¨ng in Ên gåm:

– Format: §Þnh d¹ng d÷ liÖu hiÓn thÞ

- In chi tiÕt b¶ng l−¬ng cña tõng ®¬n vÞ theo tõng th¸ng

– Allow zero length: Cho phÐp rçng hay kh«ng ?

- In tæng hîp l−¬ng cña tõng th¸ng theo ®¬n vÞ. VSoft Group – Vinh University

Slide 86

C¬ së d÷ liÖu Access ™

Khëi ®éng Access

™

T¹o Table

C¬ së d÷ liÖu Access ™

– §Ó ®Æt tr−êng kho¸, chän tr−êng vµ bÊm con trá chuét vµo biÓu t−îng kho¸.

– Table1: NhËp vµo 3 b¶n ghi

– NÕu muèn bá tr−êng kho¸, chän l¹i tr−êng vµ bÊm con trá chuét vµo biÓu t−îng kho¸.

– Table 2: NhËp vµo 3 b¶n ghi (Tr−ëng khoa, P.Khoa, Giao viªn) – Table 3: NhËp vµo 5 b¶n ghi

Ghi CSDL

™

Më CSDL

§Æt tr−êng kho¸ cho b¶ng – Cã thÓ ®Æt hay kh«ng ?. Nh−ng nªn ®Æt

– Thùc hµnh t¹o 4 b¶ng

™

Slide 89

VSoft Group – Vinh University

– Trong tr−êng hîp kh«ng muèn ®Æt kho¸, khi ghi l¹i m¸y sÏ hái, chän NO. ™

Ghi b¶ng – BÊm con trá chuét vµo biÓu t−îng . – Tªn b¶ng cã thÓ ®Æt dÊu c¸ch, tiÕng viÖt nh−ng nªn ®Æt lµ mét d∙y ký tù liÒn nhau.

VSoft Group – Vinh University

Slide 87

VSoft Group – Vinh University

Slide 90

Page 15

L ậ p tr ì nh Visual Basic 6.0 C¬ së d÷ liÖu Access ™

Bæ sung Control Data ADO ™

NhËp d÷ liÖu cho b¶ng – C¸ch 1: ‹

VSoft – Vinh University

™

Trong chÕ ®é thiÕt kÕ b¶ng bÊm con trá chuét vµo biÓu t−îng ®Ó chuyªn gi÷a hai chÕ ®é: NhËp, xem, söa d÷ liÖu vµ thiÕt kÕ b¶ng.

™

Khi míi khëi ®éng, Control Data ADO kh«ng cã trong IDE cña VB. §Ó bæ sung, nh¸y chuét ph¶i vµo thanh c«ng cô, chän Components. Trong cöa sæ Components chän: Microsoft ADO Data Control 6.0 (OLEDB)

– C¸ch 2:

™

‹

§ãng b¶ng vµ trë vÒ CSDL

‹

Nh¸y ®óp chuét vµo b¶ng ®Ó më b¶ng d÷ liÖu

Söa cÊu tróc b¶ng – Trong cöa sæ CSDL chän b¶ng vµ chän môc Designed hoÆc më d÷ liÖu vµ chuyÓn sang chÕ ®é thiÕt kÕ.

Slide 91

VSoft Group – Vinh University

VSoft Group – Vinh University

C¬ së d÷ liÖu Access

Bæ sung DataGrid Control ™

™

Tho¸t Access

™

Më CSDL

Slide 94

– Chän menu File -> Exit

™ ™

– Khëi ®éng Access, xuÊt hiÖn cöa sæ

Khi míi khëi ®éng, DataGrid Control kh«ng cã trong IDE cña VB. §Ó bæ sung, nh¸y chuét ph¶i vµo thanh c«ng cô, chän Components. Trong cöa sæ Components chän: Microsoft DataGrid Control 6.0 (OLEDB)

– Chän môc Open An Existing File – Chän OK – Chän th− môc, chän tÖp CSDL – Chän Open

Slide 92

VSoft Group – Vinh University

VSoft Group – Vinh University

LËp tr×nh víi c¬ së d÷ liÖu ™

Sö dông Control Data ADO

Cã 2 c¸ch truy nhËp CSDL

™

– DAO (Data Access Object): ChØ truy nhËp víi ACCESS 97 vÒ tr−íc.

™

™

Trong gi¸o tr×nh nµy chØ ®Ò cËp ®Õn ADO

Form

VSoft Group – Vinh University

ADO

T¹o 1 form nh− sau: Cã hai c¸ch kÕt nèi ADO víi Dasabase – KÕt nèi cøng – KÕt nèi b»ng ch−¬ng tr×nh

– ADO (ActiveX Data Object): LËp tr×nh ®−îc víi mäi lo¹i nguån d÷ liÖu. ™

Slide 95

Database

Slide 93

§Ó kÕt nèi cøng, chän Adodc1 vµ trong cöa sæ Properties chän thuéc tÝnh: ConnectionString ... XuÊt hiÖn cöa sæ:

VSoft Group – Vinh University

Slide 96

Page 16

L ậ p tr ì nh Visual Basic 6.0

C¸c ph−¬ng thøc cña AdoDC1

KÕt nèi ADODC víi Database ™

VSoft – Vinh University

™

Chän Use Connection String vµ click Build. Khi ®ã xuÊt hiÖn:

C¸c ph−¬ng thøc – Adodc1.RecordSet.MoveFirst

– Chän Jet 4.0 OLEDB (CSDL lµ ACCESS). – Click nót Next>> xuÊt hiÖn..

ChuyÓn con trá b¶n ghi vÒ b¶n ghi ®Çu tiªn – Adodc1.RecordSet.MovePrevious ChuyÓn con trá ®Õn b¶n ghi tr−íc b¶n ghi hiÖn thêi – Adodc1.RecordSet.MoveNext ChuyÓn con trá b¶n ghi ®Õn sau b¶n ghi hiÖn thêi – Adodc1.RecordSet.MoveLast ChuyÓn con trá b¶n ghi ®Õn b¶n ghi cuèi cïng

Chän CSDL cÇn kÕt nèi. ™ Chän Test Connection ®Ó kiÓm tra. ™ Click nót OK, OK.

– Adodc1.Recordset.Addnew

™

Slide 97

VSoft Group – Vinh University

Thªm b¶n ghi míi

C¸c ph−¬ng thøc cña Recordset

KÕt nèi ADODC víi Database ™

Slide 100

VSoft Group – Vinh University

™

Chän Adodc1 vµ chän thuéc tÝnh RecordSource ... khi ®ã xuÊt hiÖn cöa sæ:

C¸c ph−¬ng thøc: – Adodc1.Recordset.Update CËp nhËt b¶n ghi ®∙ thªm hay ®∙ söa – Adodc1.Recordset. CancelUpdate

•Chän adCmdTable (kiÓu b¶ng)

Huû bá b¶n ghi ®∙ AddNew

•Chän b¶ng DONVI (tªn b¶ng)

– Adodc1.Recordset.Delete Xo¸ mét b¶n ghi

•Chän OK

– Adodc1.Recordset.Find “§iÒu kiÖn t×m” T×m b¶n ghi tho∙ m∙n ®iÒu kiÖn t×m, nÕu t×m xong mµ Adodc1.Eof th× kh«ng t×m thÊy, ng−îc l¹i lµ t×m thÊy.

VSoft Group – Vinh University

Slide 98

C¸c thuéc tÝnh cña Recordset

KÕt nèi l−íi DataGrid víi ADODC Chän l−íi DataGrid vµ chän thuéc tÝnh DataSource lµ Adodc1. ™ Ghi l¹i Form vµ ch¹y

™

™

Slide 101

VSoft Group – Vinh University

C¸c thuéc tÝnh: – Adodc1.RecordSet.BookMark - §Ó ®¸nh dÊu b¶n ghi hiÖn thêi: n = Data1.RecordSet.BookMark - Trë l¹i b¶n ghi ®∙ ®¸nh dÊu: Data1.RecordSet.BookMark = n - n lµ biÕn kiÓu Variant

– Adodc1.RecordSet.BOF Tr¶ vÒ True nÕu con trá b¶n ghi ë ®Çu tÖp, ng−îc l¹i lµ False

– Adodc1.RecordSet.EOF Tr¶ vÒ True nÕu con trá b¶n ghi ë cuèi tÖp, ng−îc l¹i lµ False

– Adodc1.RecordSet.RecordCount Tr¶ vÒ sè b¶n ghi trong b¶ng

VSoft Group – Vinh University

Slide 99

VSoft Group – Vinh University

Slide 102

Page 17

L ậ p tr ì nh Visual Basic 6.0 C¸c thuéc tÝnh cña Recordset – LÊy gi¸ trÞ c¸c tr−êng cña Recordset = Adodc1.RecordSet.Fields(“FieldName”) – G¸n gi¸ trÞ cña tr−êng

Hµnh ®éng trªn form ™ Nh¸y

chuét lªn nót th× ®ãng form

™ Nh¸y

chuét lªn nót

1. Hai Textbox bÞ xo¸

Adodc1.RecordSet.Fields(“FieldName”) =

2. Hai Textbox s¸ng lªn cho phÐp nhËp d÷ liÖu

Chó ý:

™

VSoft – Vinh University

3. Con trá chuyÓn vÒ txtMdv

– Cã thÓ thay tªn tr−êng b»ng sè thø tù cña tr−êng (TÝnh tõ 0)

4. Ba nót , <Edit>, bÞ mê

– VÝ dô:

5. Hai nót <Save>, s¸ng lªn

= Adodc1.RecordSet.Fields(0)

™ Nh¸y

- C¸c vÝ dô cña lÖnh sÏ ®−îc minh ho¹ trong ch−¬ng tr×nh.

chuét lªn nót <Edit>

1. Hai Textbox s¸ng lªn cho phÐp s÷a d÷ liÖu ®∙ chän trªn l−íi 2. Con trá chuyÓn vÒ txtMdv 3. Ba nót , <Edit>, bÞ mê 4. Hai nót <Save>, s¸ng lªn Slide 103

VSoft Group – Vinh University

X©y dùng form nhËp ®¬n vÞ

™ Nh¸y

Giao diÖn cña Form: Name: txtmdv Name:Datagrid1

Text = “”

DataSource = Adodc1 Name: txtten

Name:Adodc1

Text=“”

Connection String=... RecordSource=“Donvi” Name: CmdNew

Name:

CmdEdit

Caption=&New

Caption: &Edit

Caption: &Delete

Name: cmdSave

Name:

Name:

Caption=&Save

Caption: &Cancel

cmdCancel

Name:

CmdDelete

cmdClose

Caption: C&lose

VSoft Group – Vinh University

Slide 106

Hµnh ®éng trªn form

BµI tËp mÉu - qu¶n lý l−¬ng ™

VSoft Group – Vinh University

Slide 104

chuét lªn nót <Save>

1. NÕu ®∙ nh¸y nót th× KiÓm tra m∙ ®¬n vÞ cã trïng hay kh«ng ? NÕu trïng th× th«ng b¸o NÕu kh«ng trïng th× thªm b¶n ghi míi 2. NÕu ®∙ nh¸y vµo nót <Edit> th× KiÓm tra m∙ ®∙ s÷a trïng víi m∙ ®∙ cã (trõ m∙ cò) hay kh«ng ? NÕu trïng th× th«ng b¸o NÕu kh«ng trïng th× chuyÓn con trá ®Õn b¶n ghi cÇn söa 3. CËp nhËt gi¸ trÞ míi vµo b¶ng 4. Hai Textbox bÞ mê 5. Ba nót , <Edit>, s¸ng lªn 6. Hai nót <Save>, bÞ mê VSoft Group – Vinh University

Slide 107

Hµnh ®éng trªn form

Hµnh ®éng trªn FORM -

§Æt thuéc tÝnh Connection String cña ®èi t−îng Adodc1 cho kÕt nèi tíi CSDL QLLUONG.MDB

-

§Æt thuéc tÝnh RecordSource lµ b¶ng “DONVI”

™

Di chuyÓn con trá trªn l−íi, gi¸ trÞ c¸c b¶n ghi chuyÓn sang Textbox

™ Nh¸y

chuét lªn nót

1. Hai Textbox bÞ mê 2. Hai <Save>, bÞ mê 3. Ba nót , <Edit>, s¸ng lªn

)Nh¸y ®óp chuét vµo DataGrid1 )Chän ph−¬ng thøc: DataGrid1_RowColChange

™ Nh¸y

)Gâ vµo lÖnh:

chuét lªn nót

1. C¸c tr¹ng th¸I trªn Form gi÷ nguyªn

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer) On Error goto Thoat

2. Th«ng b¸o cã ch¾c ch¾n xo¸ hay kh«ng ? NÕu ®ång ý th× xo¸

txtMdv.Text = Adodc1.RecordSet.Fields(“Mdv”)

NÕu kh«ng ®«ng ý th× dõng.

txtTen.Text = Adodc1.RecordSet.Fields(“Ten”) Thoat: End Sub VSoft Group – Vinh University

Slide 105

VSoft Group – Vinh University

Slide 108

Page 18

L ậ p tr ì nh Visual Basic 6.0 Ph©n tÝch ™

X©y dùng c¸c sù kiÖn ™

Hµnh ®éng: 1. Hai nót <Save>, bÞ mê

)Trªn thñ tôc Form Load Private Sub Form_Load() Call SetControls(False) End Sub

hoÆc 1. Ba nót <Save>, s¸ng lªn 2. Ba nót , <Edit>, bÞ mê

Hµnh ®éng:

™

1. Hai Textbox txtMdv, txtTen bÞ mê

Private Sub CmdClose_Click()

1. Hai Textbox txtMdv, txtTen s¸ng lªn

Unload Me

§−îc lÆp l¹i th−êng xuyªn ë c¸c nót -> X©y dùng 1 thñ tôc ™

CÇn 1 biÕn tæng thÓ x¸c ®Þnh kÝch nót hay <Edit>.

™

Khi 1 b¶n ghi cÇn söa, ph¶i l−u vÞ trÝ cña b¶n ghi ®ã. Slide 109

X©y dùng c¸c thñ tôc, biÕn ™ Khai

End Sub

VSoft Group – Vinh University

™

Slide 112

X©y dùng sù kiÖn trªn nót )Nh¸y ®óp chuét vµo nót Private Sub CmdNew_Click()

b¸o 3 biÕn tæng thÓ trong form

NutNew = True ‘ Nót ®−îc kÝch ®óp hoÆc Enter Call SetControls(True) ‘ §Æt c¸c TextBox vµ c¸c nót Ên s¸ng lªn Call ClearTexts ‘ Xo¸ c¸c TextBox vÒ tr¾ng txtMdv.SetFocus ‘ ChuyÓn con trá ®Õn txtMdv ®Ó nhËp

Option Explicit ‘ Tõ kho¸ ®Ó mäi biÕn dïng ph¶i khai b¸o Dim NutNew As Boolean ‘ KiÓm tra ®∙ bÊm nót New hay nót Edit Dim OldMdv As String ‘ BiÕn l−u MDV tr−íc khi söa Dim CurRecno As Variant ‘ BiÕn vÞ trÝ b¶n ghi cÇn khi söa

™ X©y

X©y dùng sù kiÖn kÝch nót )Nh¸y ®óp chuét vµo nót Close

hoÆc

VSoft Group – Vinh University

X©y dùng sù kiÖn form load )Kich ®óp chuét vµo Form

2. Ba nót , <Edit>, s¸ng lªn

™

VSoft – Vinh University

dùng thñ tôc ClearTexts – xo¸ c¸c TextBox

End Sub ™

X©y dùng sù kiÖn trªn nót <Edit> )Nh¸y ®óp chuét vµo nót <Edit> Private Sub CmdEdit_Click()

Private Sub ClearTexts() Dim ctrl as Control For Each Ctrl in Me if TypeOf Ctrl is TextBox then Ctrl.Text = “” Next End Sub

NutNew = False ‘ Nót <Edit> ®uîc kÝch ®óp hoÆc Enter Call SetControls(True) ‘ §Æt c¸c TextBox vµ c¸c nót Ên s¸ng lªn txtMdv.SetFocus ‘ChuyÓn con trá ®Õn txtMdv ®Ó nhËp OldMdv = txtMdv.Text ‘ L−u m∙ cò ®Ô kiÓm tra söa m∙ hay kh«ng CurRecno = Adodc1.RecordSet.BookMark ‘L−u vÞ trÝ cÇn söa

End Sub VSoft Group – Vinh University

Slide 110

™

X©y dùng c¸c thñ tôc, biÕn ™ X©y

dùng thñ tôc SetControls - ®iÒu khiÓn c¸c textbox vµ c¸c nót s¸ng, mê.

Private Sub SetControls(Edit As Boolean) Dim Ctrl As Control For Each Ctrl In Me If TypeOf Ctrl Is TextBox Then Ctrl.Enabled = Edit Next CmdNew.Enabled = Not Edit CmdEdit.Enabled = Not Edit CmdDelete.Enabled = Not Edit CmdSave.Enabled = Edit CmdCancel.Enabled = Edit End Sub

VSoft Group – Vinh University

VSoft Group – Vinh University

Slide 111

Slide 113

X©y dùng sù kiÖn trªn nót <Save> Private Sub CmdSave_Click() If (NutNew) Or ((Not NutNew) And (OldMdv <> txtMdv.Text)) Then If Adodc1.Recordset.RecordCount > 0 Then Adodc1.RecordSet.MoveFirst Adodc1.Recordset.Find "MDV='" & Trim(txtMdv.Text) & "'" If Not Adodc1.Recordset.EOF Then MsgBox "M∙ ®∙ nhËp trïng", vbExclamation, "Th«ng b¸o..." Exit Sub End If End If If NutNew Then Adodc1.Recordset.AddNew ' Bæ sung 1 b¶n ghi míi Else Adodc1.RecordSet.BookMark = CurRecno ‘ Trë vÒ b¶n ghi cÇn söa End If Adodc1.Recordset.Fields("Mdv") = txtMdv.Text ' G¸n Mdv Adodc1.Recordset.Fields("Ten") = txtTen.Text ' Gi¸ Ten Adodc1.Recordset.Update ' CËp nhËt c¸c gi¸ trÞ ®∙ g¸n Call SetControls(False) End Sub

VSoft Group – Vinh University

Slide 114

Page 19

L ậ p tr ì nh Visual Basic 6.0 ™

X©y dùng sù kiÖn trªn nót

VSoft – Vinh University

Tãm t¾t vÝ dô

)Nh¸y ®óp chuét vµo nót Private Sub CmdCancel_Click() Call SetControls(False)

™

Mét sè nh−îc ®iÓm chÝnh khi thiÕt kÕ form nhËp ®¬n vÞ:

End Sub ™

– §−êng dÉn ®Õn CSDL ph¶i cè ®Þnh ®Õn 1 th− môc x¸c ®Þnh.

X©y dùng sù kiÖn trªn nót )Nh¸y ®óp chuét vµo nót

– Tiªu ®Ò c¸c cét cña l−íi lÊy theo tªn c¸c cét trong b¶ng

Private Sub CmdDelete_Click()

– Thñ tôc SetControls sÏ ®−îc lÆp l¹i trong c¸c Form nhËp d÷ liÖu.

If Adodc1.Recordset.RecordCount > 0 Then

– Thñ tôc ClearTexts sÏ ®−îc lÆp l¹i trong c¸c Form nhËp d÷ liÖu.

If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "C¶nh b¸o...") = vbNo Then Exit Sub Adodc1.Recordset.Delete End if

End Sub

VSoft Group – Vinh University

Slide 115

Bµi tËp vÒ nhµ ™

Slide 118

VSoft Group – Vinh University

N©ng cÊp - Thay ®æi ®−êng dÉn

X©y dùng c¸c form nhËp chøc vô ™

Thay ®æi ®−êng dÉn – Trong thuéc tÝnh Connection String cña Adodc1 xo¸ ®−êng dÉn gèc, chØ ®Æt ®−êng dÉn t−¬ng ®èi ®Õn CSDL. – Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Data\QLTL.mdb;Persist Security Info=False

§−êng dÉn t−¬ng ®èi

VSoft Group – Vinh University

Slide 116

VSoft Group – Vinh University

Slide 119

N©ng cÊp - HiÖu chØnh l−íi

Bµi 5: Modul, Query, T×m kiÕm ™

HiÖu chØnh l−íi:

™

N©ng cÊp form NhËp ®¬n vÞ

™

X©y dùng c¸c modul

™

X©y dùng form NhËp chøc vô

™

C¸c Query trong ACCESS

– Gâ vµo Caption

™

Sö dông ADODC kÕt hîp víi c©u lÖnh Select

– Gâ vµo DataField

™

X©y dùng form T×m kiÕm d÷ liÖu

– Chän Tab Layout ®Ó c¨n lÒ

™

Bµi tËp

– Chän Tab Font ®Ó ®Æt Font

– Nh¸y chuét ph¶i trªn l−íi, chän Properties … -> XuÊt hiÖn cöa sæ, chän Tab Column. – LÇn l−ît chän c¸c Column

– Chän Tab Format ®Ó ®Þnh d¹ng d÷ liÖu. – Chän Apply

VSoft Group – Vinh University

Slide 117

VSoft Group – Vinh University

Slide 120

Page 20

L ậ p tr ì nh Visual Basic 6.0 N©ng cÊp -Thªm mét Module ™

N©ng cÊp - ViÕt thñ tôc ™

ViÕt thñ tôc SetControls, thñ tôc ClearTexts dïng cho

X©y dùng thñ tôc xo¸ thuéc tÝnh Text cña c¸c Textbox, ComboBox:

mäi form nhËp. Thñ tôc ®−îc x©y dùng trong Modul. ™

Public Sub ClearTexts(frm As Form) Dim ctrl As Control For Each ctrl In frm If TypeOf ctrl Is TextBox Then ctrl.Text = "" If TypeOf ctrl Is ComboBox Then ctrl.Text = "" Next End Sub

Tõ menu Project -> Add Module.

™

LÖnh gäi thñ tôc nµy trong c¸c Form nhËp: - Èn c¸c Textbox vµ 2 nót Save, Cancel: Call SetControls(Me,False) - HiÖn c¸c Textbox vµ 2 nót Save, Cancel: Call SetControls(Me,True) - Xo¸ c¸c Textbox: Call ClearTexts(Me)

Chän Add Module Slide 121

VSoft Group – Vinh University

Slide 124

VSoft Group – Vinh University

Cöa sæ Code Module ™

VSoft – Vinh University

X©y dùng form nhËp chøc vô Name: txtmcv

Mét code module chØ chøa c¸c khai b¸o, hµm, thñ tôc.

Text = “” Name: txtten

Name:Datagrid1

Text=“”

DataSource = Adodc1

Name: txthspc

Name:Adodc1

Text=“”

Connection String=... RecordSource=“Chucvu”

Gâ vµo c¸c khai b¸o, hµm, thñ tôc

VSoft Group – Vinh University

Slide 122

Name:

CmdEdit

Caption=&New

Caption: &Edit

Caption: &Delete

Name: cmdSave

Name:

Name:

Caption=&Save

Caption: &Cancel

cmdCancel

Name:

CmdDelete

cmdClose

Caption: C&lose Slide 125

VSoft Group – Vinh University

N©ng cÊp - ViÕt thñ tôc

X©y dùng code

™

Thªm mét Modul míi, ghi lai vµ ®Æt tªn Modul lµ MyModul.

™

X©y dùng thñ tôc SetControls ®Ó Enabled/Not Enabled c¸c ®iÒu khiÓn:

Public Sub SetControls(frm as Form, Edit As Boolean) Dim ctrl As Control For Each ctrl In frm If TypeOf ctrl Is TextBox Then ctrl.Enabled = Edit End If If TypeOf ctrl Is ComboBox Then ctrl.Enabled = Edit End If Next frm.CmdNew.Enabled = Not Edit frm.CmdEdit.Enabled = Not Edit frm.CmdDelete.Enabled = Not Edit frm.CmdSave.Enabled = Edit frm.CmdCancel.Enabled = Edit End Sub VSoft Group – Vinh University

Name: CmdNew

-

§Æt thuéc tÝnh Connection String cña ®èi t−îng Adodc1 cho kÕt nèi tíi CSDL QLLUONG.MDB

-

§Æt thuéc tÝnh RecordSource lµ b¶ng “CHUCVU”

™

Di chuyÓn con trá trªn l−íi, gi¸ trÞ c¸c b¶n ghi chuyÓn sang Textbox )Nh¸y ®óp chuét vµo DataGrid1 )Chän ph−¬ng thøc: DataGrid1_RowColChange

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer) On Error goto Thoat txtMcv.Text = Adodc1.RecordSet.Fields(“Mcv”) txtTen.Text = Adodc1.RecordSet.Fields(“Ten”) txtHspc.Text = Adodc1.RecordSet.Fields(“Hspc”) Thoat: End Sub Slide 123

VSoft Group – Vinh University

Slide 126

Page 21

L ậ p tr ì nh Visual Basic 6.0 ™

X©y dùng c¸c thñ tôc, biÕn ™ Khai

‘ Tõ kho¸ ®Ó mäi biÕn dïng ph¶i khai b¸o

Dim NutNew As Boolean ‘ KiÓm tra ®∙ bÊm nót New hay nót Edit Dim OldMcv As String

‘ BiÕn l−u MDV tr−íc khi söa

Dim CurRecno As Variant ‘ BiÕn vÞ trÝ b¶n ghi cÇn khi söa

VSoft Group – Vinh University

Slide 127

™

X©y dùng sù kiÖn trªn nót )Nh¸y ®óp chuét vµo nót

X©y dùng sù kiÖn Form_Load

Private Sub CmdCancel_Click()

)Kich ®óp chuét vµo Form

Call SetControls(Me,False)

)Trªn thñ tôc Form Load Private Sub Form_Load() Call SetControls(Me,False) End Sub ™

Slide 130

VSoft Group – Vinh University

™

X©y dùng c¸c sù kiÖn

X©y dùng sù kiÖn trªn nót <Save> Private Sub CmdSave_Click() If (NutNew) Or ((Not NutNew) And (OldMcv <> txtMcv.Text)) Then If Adodc1.Recordset.RecordCount > 0 Then Adodc1.RecordSet.MoveFirst Adodc1.Recordset.Find "MCV='" & Trim(txtMcv.Text) & "'" If Not Adodc1.Recordset.EOF Then MsgBox "M∙ ®∙ nhËp trïng", vbExclamation, "Th«ng b¸o..." Exit Sub End If End If If NutNew Then Adodc1.Recordset.AddNew ' Bæ sung 1 b¶n ghi míi Else Adodc1.RecordSet.BookMark = CurRecno ‘ Trë vÒ b¶n ghi cÇn söa End If Adodc1.Recordset.Fields("Mcv") = txtMcv.Text ' G¸n Mdv Adodc1.Recordset.Fields("Ten") = txtTen.Text ' Gi¸ Ten Adodc1.Recordset.Fields(“Hspc") = txtHspc.Text ' Gi¸ Ten Adodc1.Recordset.Update Call SetControls(Me,False) End Sub

b¸o 3 biÕn tæng thÓ trong form

Option Explicit

VSoft – Vinh University

End Sub ™

X©y dùng sù kiÖn trªn nót )Nh¸y ®óp chuét vµo nót Private Sub CmdDelete_Click()

X©y dùng sù kiÖn kÝch nót

If Adodc1.Recordset.RecordCount > 0 Then

)Nh¸y ®óp chuét vµo nót Close

If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "C¶nh

Private Sub CmdClose_Click()

b¸o...") = vbNo Then Exit Sub Adodc1.Recordset.Delete

Unload Me

End if

End Sub

End Sub

VSoft Group – Vinh University

™

Slide 128

X©y dùng sù kiÖn trªn nót

T¹o query trong access

)Nh¸y ®óp chuét vµo nót Private Sub CmdNew_Click() NutNew = True Call SetControls(Me,True) Call ClearTexts(Me) txtMcv.SetFocus

End Sub ™

X©y dùng sù kiÖn trªn nót <Edit>

™

Khëi ®éng Access

™

T¹o c¸c b¶ng: DONVI, CHUCVU, HOSO

™

T¹o Query ®Ó tr¶ lêi c¸c c©u hái: – H1: Cho biÕt tªn ®¬n vÞ, hä tªn, ngµy sinh, hîp ®ång cña c¸c c¸n bé.

)Nh¸y ®óp chuét vµo nót <Edit> Private Sub CmdEdit_Click()

– H2: Cho biÕt hä tªn, ngµy sinh, tªn chøc vô, hsl, hscv, tæng hÖ sè, tæng l−¬ng cña c¸c c¸n bé.

NutNew = False Call SetControls(Me,True) txtMcv.SetFocus OldMcv = txtMcv.Text CurRecno = Adodc1.RecordSet.BookMark

– H3: Cho biÕt tªn ®¬n vÞ, hä tªn, ngµy sinh, tªn chøc vô, hsl, hscv, tæng hÖ sè, tæng l−¬ng cña c¸c c¸n bé.

End Sub

VSoft Group – Vinh University

Slide 131

VSoft Group – Vinh University

– H4: Cho biÕt hä tªn, ngµy sinh, tªn chøc vô, hsl, hscv, tæng hÖ sè, tæng l−¬ng cña c¸c c¸n bé trong ®¬n vÞ “Ban gi¸m ®èc”. Slide 129

VSoft Group – Vinh University

Slide 132

Page 22

L ậ p tr ì nh Visual Basic 6.0 T¹o query trong access ™

Trong cöa sæ Database

™

Chän Queries -> Create query in designed view, xuÊt hiÖn mét cöa sæ liÖt kª c¸c b¶ng vµ c¸c query kh¸c.

VSoft Group – Vinh University

VSoft – Vinh University

C©u lÖnh select Có ph¸p:

™

SELECT *|[Tªn c¸c cét AS ] FROM [WHERE ] [GROUP BY ] [ORDER BY ]

Slide 133

-

ý nghÜa: Dïng ®Ó trÝch chän c¸c b¶n ghi tõ trªn c¸c cét tho∙ m∙n biÓu thøc ®−a ra.

-

< Tªn c¸c b¶ng> cã thÓ lµ mét biÓu thøc kÕt nèi gi÷a c¸c b¶ng: inner join, left join, righ join trªn tr−êng kho¸.

-

NÕu cãmÖnh ®Ò GROUP BY th× c¸c cét ®−îc nhãm l¹i.

-

NÕu cã mÖnh ®Ò ORDER BY th× sÏ s¾p xÕp theo c¸c cét ®−a ra.

Slide 136

VSoft Group – Vinh University

T¹o query trong access

X©y dùng Form T×m kiÕm ™

LÇn l−ît chän c¸c b¶ng hoÆc c¸c query vµ chän Add. ™ Sau khi chän xong chän Close, xuÊt hiÖn: ™

ThiÕt kÕ form t×m kiÕm hå s¬ nh− sau: txtTendv txtTencv

Datagrid1

txtHoten txtHopdong

™

KÐo c¸c tr−êng kho¸ gi÷a c¸c b¶ng ®Æt cïng nhau ®Ó t¹o kÕt nèi.

VSoft Group – Vinh University

Slide 134

Slide 137

VSoft Group – Vinh University

T¹o query trong access

§Æt c¸c thuéc tÝnh...

LÇn l−ît kÐo c¸c tr−êng cña b¶ng vµo ®Æt ë hµng Field ®Ó t¹o c¸c tr−êng cña Query.

™

Chän ®èi t−îng Adodc1

™

§Æt thuéc tÝnh Connection String ®Õn QLLUONG.MDB

™

Chän hµng Sort trªn c¸c tr−êng cña Query ®Ó s¾p xÕp d÷ liÖu theo c¸c tr−êng.

™

Chän thuéc tÝnh RecordSource, §Æt Command Type lµ adCmdText -> OK

™

Hµng Show ®Ó chØ ra c¸c tr−êng cÇn hiÓn thÞ

™

™

Hµng Criteria ®Ó chøa c¸c ®iÒu kiÖn cÇn läc.

Chó ý: Kh«ng ®Æt thuéc tÝnh DataSource cña DataGrid1 lµ Adodc1 nh− c¸c vÝ dô tr−íc.

™

Cã thÓ bæ sung c¸c tr−êng míi dùa trªn c¸c tr−êng ®∙ cã theo c¸c biÓu thøc, vÝ dô:

™

Thùc chÊt Query lµ mét c©u lÖnh SQL.

™

TONGHS: [HSL+HSPC], TONGLUONG: [TONGHS]*290000

VSoft Group – Vinh University

Slide 135

VSoft Group – Vinh University

Slide 138

Page 23

L ậ p tr ì nh Visual Basic 6.0 X©y dùng code

VSoft – Vinh University

Bµi 6,7: ADODB.Connection

Dim sSql As String Private Sub TimKiem()

™

§èi t−îng ADODB.Connection

™

VÝ dô sö dông ADODB.Connection

"AND (CHUCVU.TEN LIKE ‘%" & txtTencv.Text & “%') " & _

™

X©y dùng c¸c modul lµm viÖc víi ADODB.Connection

"AND (HOSO.HOTEN LIKE ‘%" & txtHoten.Text & “%') " & _

™

X©y dùng ch−¬ng tr×nh qu¶n lý l−¬ng

"AND (HOSO.HOPDONG LIKE ‘%" & txtHopdong.Text & “%')"

™

Bµi tËp

sSql = "SELECT DONVI.TEN, CHUCVU.TEN,HOTEN, NGAYSINH, HOPDONG FROM " & _ "DONVI INNER JOIN (CHUCVU INNER JOIN HOSO ON CHUCVU.MCV = HOSO.MCV) " & _ "ON DONVI.MDV=HOSO.MDV WHERE (DONVI.TEN LIKE ‘%" & txtTendv.Text & “%') " & _

Adodc1.RecordSource = sSql Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 End Sub

Slide 139

VSoft Group – Vinh University

Slide 142

VSoft Group – Vinh University

Lµm viÖc víi ADODB. connection Private Sub Form_Load() Call Timkiem End Sub

Private Sub txtTencv_Change() Call TimKiem End Sub

™

Thay v× sö dông ®iÒu khiÓn Adodc ta sö dông mét ®èi t−îng ADODB.Connection. §èi t−îng nµy cho phÐp thùc hiÖn c¸c thao t¸c thªm, söa, xo¸ c¸c b¶n ghi vµ cã thÓ lµm viÖc víi Recordset hoÆc tËp lÖnh SQL.

Private Sub txtHopdong_Change() Private Sub txtTendv_Change() Call TimKiem Call TimKiem End Sub End Sub Private Sub txtHoten_Change() Call TimKiem End Sub

™

theo c¸c Provider cña CSDL kh¸c nhau, ta chØ cÇn thay chuæi kÕt nèi nµy.

Private Sub CmdKetthuc_Click()

™

Kh«ng nªn nhí chuæi kÕt nèi mµ nªn add mét form míi råi thªm ®iÒu khiÓn Adodc vµo form, sau ®ã chän thuéc tÝnh Connection

End Sub

String vµ copy chuæi nµy vµo hµm ®∙ viÕt.

Slide 140

Slide 143

VSoft Group – Vinh University

Lµm viÖc víi ADODB. connection

Bµi tËp vÒ nhµ X©y dùng c¸c form nhËp hå s¬

VSoft Group – Vinh University

Chó ý: ‹

Unload Me

VSoft Group – Vinh University

™

KÕt nèi cña ADODB.Connection lµ kÕt nèi b»ng ch−¬ng tr×nh. Tuú

Slide 141

™

T¹o mét th− môc trong æ C:\ADOPro

™

ChÐp tÖp qltl.mdb trong ch−¬ng tr×nh tr−íc vµo th− môc nµy.

™

Khëi ®éng VB vµ më mét Project míi

™

Ghi Form vµo th− môc C:\ADOPro víi tªn: ViduAdo

™

Ghi Project vµo th− môc C:\ADOPro víi tªn: Adopro

™

Më Form ®∙ ghi vµ thiÕt kÕ nh− sau:

VSoft Group – Vinh University

Slide 144

Page 24

L ậ p tr ì nh Visual Basic 6.0 vÝ dô ®Çu tiªn vÒ ado ™

™

txtMdv Enabled=False

Private Sub CmdPrevious_Click() rs.MovePrevious ' Hien thi cac ban ghi neu khong phai dau tep If Not rs.BOF Then Displayrecord ' Hien thi ban ghi hien thoi Else rs.MoveFirst ' Chuyen ve ban ghi dau tien End If End Sub

txtTen Enabled=False CmdLast

CmdPrevious CmdNext ™

LÇn l−ît viÕt c¸c nót: CmdFirst, CmdPrevious

Private Sub CmdFirst_Click() rs.MoveFirst ' Chuyen ve ban ghi dau tien Displayrecord ' Hien thi ban ghi hien thoi End Sub

T¹o 1 form

CmdFirst

VSoft – Vinh University

T¹o x©u kÕt nèi (ConnÐcttring) cña Adodc1

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AdoPro\QLTL.mdb;Persist Security Info=False

VSoft Group – Vinh University

Slide 145

LÇn l−ît viÕt c¸c nót: CmdNext, CmdLast.

VÝ dô ®Çu tiªn vÒ ado ™

Chän Project - >References -> xuÊt hiÖn cöa sæ, chän:

™

Më View code cña form thªm c¸c khai b¸o vµ thñ tôc:

™

Private Sub CmdNext_Click() rs.MoveNext 'Hien thi cac ban ghi neu khong phai cuoi tep If Not rs.EOF Then Displayrecord 'Hien thi ban ghi hien thoi Else rs.MoveLast 'Chuyen ve ban ghi cuoi cuong End If End Sub Private Sub CmdLast_Click() rs.MoveLast ' Chuyen ve ban ghi cuoi cung Displayrecord ' Hien thi ban ghi hien thoi End Sub

Dim gConn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSql As String Private Sub Displayrecord() 'Gan gia tri 2 text box cho 2 truong Mdv, Ten txtMdv.Text = rs!Mdv 'Su dung ! thay cho rs.Fields("Mdv") txtTen.Text = rs!Ten End Sub VSoft Group – Vinh University

™

Slide 146

Xo¸ ®iÒu khiÓn Adodc1, ghi l¹i Form vµ ch¹y

Slide 149

VSoft Group – Vinh University

Më Form load vµ gâ vµo:

ch−¬ng tr×nh Qu¶n lý l−¬ng

Private Sub Form_Load() 'Neu co loi thi nhay ve nhan OpenConnectionErr On Error GoTo OpenConnectionErr 'Tao doi tuong gConn Set gConn = New ADODB.Connection 'Xau ConnectString duoc copy tu xau Adodc1 gConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AdoPro\QLTL.mdb;Persist Security Info=False" gConn.Open 'Tao doi tuong rs Set rs = New ADODB.Recordset sSql = "SELECT * FROM DONVI" rs.Open sSql, gConn , adOpenDynamic rs.MoveFirst Displayrecord Exit Sub OpenConnectionErr: MsgBox (Err.Description) Unload Me End Sub VSoft Group – Vinh University

Slide 148

VSoft Group – Vinh University

™

T¹o th− môc D:\Qlluong

™

T¹o c¸c th− môc con trong th− môc nµy: – Forms: Dïng ®Ó chøa c¸c form cña ch−¬ng tr×nh – Reports: Dïng ®Ó chøa c¸c Report trong ch−¬ng tr×nh – Graphics: Dïng ®Ó chøa c¸c h×nh ¶nh trong ch−¬ng tr×nh

Slide 147

™

Khëi ®éng VB, t¹o mét Project míi

™

Ghi Project vµo th− môc D:\Qlluong víi tªn Qlluong.vbp

™

Ghi form vµo th− môc Form víi tªn: Nhapdv

™

Bæ sung vµo Project mét modul vµ ghi vµo th− môc Form víi tªn: Cacham.bas

™

§Æt tªn modul lµ MyModul

VSoft Group – Vinh University

Slide 150

Page 25

L ậ p tr ì nh Visual Basic 6.0 X©y d−ng c¸c modul

™

VSoft – Vinh University Më View code vµ thªm c¸c khai b¸o

Option Explicit Dim rsDONVI As ADODB.Recordset, sSql As String ™

Dim NutAdd As Boolean, OldMdv As String

Khai b¸o trong mudul c¸c biÕn tæng thÓ:

Dim CurRecno As Variant ‘Luu vi tri de sua

Option explicit Public MyConn as ADODB.Connection Public Dataname as string ™

™

Click ®óp chuét vµo Form, viÕt trong FormLoad:

Private Sub Form_Load()

Bæ sung vµo modul c¸c hµm nh− trong Adodc:

If Not OpenDB(App.path &”\QLTL.MDB”) Then End

– SetControls(frm as Form, Edit as Boolean) – ClearTexts(frm as Form)

Set rsDONVI = New ADODB.Recordset sSql = "Select * from DONVI order by MDV" rsDONVI.Open sSql, MyConn, adOpenDynamic, adLockOptimistic Set DataGrid1.DataSource = rsDONVI Call SetControls(Me, False) End Sub Slide 151

VSoft Group – Vinh University

Slide 154

VSoft Group – Vinh University

Ch¹y form ®Ó kiÓm tra

X©y d−ng c¸c modul ™ Bæ sung vµo modul mét hµm ®Ó më CSDL nh− sau: Public Function OpenDB(DataName As String) As Boolean Dim sConnect As String OpenDB = True On Error GoTo Thoat sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataName & ";Persist Security Info=False" Set MyConn = New ADODB.Connection MyConn.ConnectionString = sConnect MyConn.ConnectionTimeout = 10 MyConn.CursorLocation = adUseClient MyConn.Open Exit Function Thoat: MsgBox Err.Description OpenDB = False End Function

™

™

Ghi l¹i form vµ ch¹y

Nh¸y chuét lªn DataGrid, d÷ liÖu sÏ chuyÓn sang Textbox. – Trë vÒ Form, click ®óp vµo DataGrid1 chän sù kiÖn: RowColChange – §o¹n m∙:

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer) On error goto Thoat txtMdv.Text = rsDONVI.Fields("MDV") txtTen.Text = rsDONVI.Fields("TEN") Thoat: End Sub

Slide 152

VSoft Group – Vinh University

Thªm sù kiÖn Add_Click, Cancel_Click, Close_Click, Edit_Click

X©y dùng form nhËp ®¬n vÞ ™ ™

Slide 155

VSoft Group – Vinh University

Më form Nhapdv ThiÕt kÕ form nh− sau:

™

Thªm c¸c sù kiÖn

Private Sub CmdNew_Click() NutAdd = True

Private Sub CmdClose_Click() Unload Me End Sub

Call ClearTexts(Me) Call SetControls(Me, True)

txtMdv DataGrid1 txtTen

txtMdv.SetFocus End Sub

Private Sub CmdEdit_Click() NutAdd = False Call SetControls(Me, True) txtMdv.SetFocus

Private Sub CmdCancel_Click() Call SetControls(Me, False) End Sub

• C¸c nót ®Æt tªn lµ:

OldMdv = txtMdv.Text CurRecno=rsDONVI.BookMark End Sub

CmdAdd, CmdEdit, CmdDelete, CmdSave, CmdCancel, CmdClose VSoft Group – Vinh University

Slide 153

VSoft Group – Vinh University

Slide 156

Page 26

L ậ p tr ì nh Visual Basic 6.0 Thªm sù kiÖn Delete_Click

VSoft – Vinh University

X©y dùng form nhËp chøc vô

Private Sub CmdDelete_Click()

™

If Trim(txtMdv.Text) = "" Then

™

MsgBox "De nghi chon don vi”

Thªm mét form míi, ghi lªn ®Üa víi tªn Nhapcv, vµ frmNhapcv ThiÕt kÕ form nh− sau:

Exit Sub End If 'Neu co loi thi nhay den nhan Thoat On Error GoTo Thoat If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "Canh bao...") = vbNo Then

Exit Sub End If rsDONVI.Delete Call ClearTexts(Me)

• C¸c textbox ®Æt tªn lµ: txtMcv, txtTen, txtHspc

rsDONVI.Requery Thoat:

• C¸c nót ®Æt tªn lµ:

End Sub

CmdAdd, CmdEdit, CmdDelete, CmdSave, CmdCancel, CmdClose

VSoft Group – Vinh University

Slide 157

Slide 160

VSoft Group – Vinh University

Thªm sù kiÖn Save_Click

™

Më View code vµ thªm c¸c khai b¸o

Option Explicit

Private Sub CmdSave_Click() 'Neu co loi thi nhay den nhan Thoat

Dim rsCHUCVU As ADODB.Recordset, sSql As String

On Error GoTo Thoat

Dim NutAdd As Boolean ™

'Kiem tra trung ma

Click ®óp chuét vµo Form, viÕt trong FormLoad:

Private Sub Form_Load()

If (NutAdd) Or ((Not NutAdd) And (OldMdv <> txtMdv.Text)) Then If rsDONVI.RecordCount > 0 Then rsDONVI.MoveFirst

If Not OpenDB(App.path &”\QLTL.MDB”) Then End

rsDONVI.Find "MDV='" & txtMdv & "'"

Set rsCHUCVU = New ADODB.Recordset

If Not rsDONVI.EOF Then

sSql = "Select * from CHUCVU order by MCV" rsCHUCVU.Open sSql, MyConn, adOpenDynamic, adLockOptimistic

MsgBox "M∙ ®∙ trïng !", vbExclamation, "Th«ng b¸o..."

Set DataGrid1.DataSource = rsCHUCVU

Exit Sub

Call SetControls(Me, False)

End If

End Sub

End If Xem trang tiÕp theo VSoft Group – Vinh University

Slide 158

VSoft Group – Vinh University

Thªm sù kiÖn Save_Click

Ch¹y form ®Ó kiÓm tra

If NutAdd Then rsDONVI.AddNew Else rsDONVI.Bookmark = CurRecno End If rsDONVI.Fields("MDV") = txtMdv.Text rsDONVI.Fields("TEN") = txtTen.Text rsDONVI.Update Call SetControls(Me, False) Exit Sub Thoat: MsgBox Err.Description End Sub

VSoft Group – Vinh University

Slide 161

™

Ghi l¹i Form vµ ch¹y ®Ó kiÓm tra

™

Nh¸y chuét lªn DataGrid, d÷ liÖu sÏ chuyÓn sang Textbox. – Trë vÒ Form, click ®óp vµo DataGrid1 chän sù kiÖn: RowColChange – §o¹n m∙:

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)

On error goto Thoat txtMcv.Text = rsCHUCVU.Fields("MCV") txtTen.Text = rsCHUCVU.Fields("TEN") txtHspc.Text = rsCHUCVU.Fields(“HSPC") Thoat: End Sub

Slide 159

VSoft Group – Vinh University

Slide 162

Page 27

L ậ p tr ì nh Visual Basic 6.0

VSoft – Vinh University

Thªm sù kiÖn Delete_Click

Kü thuËt ®¸nh m∙ tù ®éng

Private Sub CmdDelete_Click() If Trim(txtTen.Text) = "" Then ™

Më CSDL ACCESS vµ t¹o mét b¶ng cã tªn CONTROLS_ID

™

T¹o c¸c tr−êng nh− sau:

™ ™

MsgBox "De nghi chon don vi” Exit Sub End If

– MCV

Text

5 ‘Dïng ®Ó ®¸nh m∙ chøc vô tù ®éng

– MCB

Text

8 ‘Dïng ®Ó ®¸nh m∙ c¸n bé tù ®éng

'Neu co loi thi nhay den nhan Thoat On Error GoTo Thoat If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "Canh bao...") = vbNo Then

Exit Sub

NhËp vµo c¸c gi¸ trÞ 00001, 00000001

End If

ThuËt to¸n t¹o MCV tù ®éng

rsCHUCVU.Delete

– Khi thªm 1 chøc vô míi, lÊy MCV trong b¶ng CONTROLS_ID

Call ClearTexts(Me)

– T¨ng MCV trong b¶ng CONTROLS_ID lªn 1 gi¸ trÞ

rsCHUCVU.Requery

– Khi söa b¶n ghi hay xo¸ b¶n ghi th× kh«ng thay ®æi MCV

Thoat: End Sub Slide 163

VSoft Group – Vinh University

Private Sub CmdSave_Click()

Hµm lÊy m∙

'Neu co loi thi nhay den nhan Thoat

Private Function Get_MCV() As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "SELECT MCV from CONTROLS_ID", MyConn Get_MCV = rs.Fields("MCV") Set rs = Nothing End Function ™

Slide 166

Thªm sù kiÖn Save_Click

X©y dùng hµm lÊy m∙ tù ®éng ™

VSoft Group – Vinh University

On Error GoTo Thoat If Not IsNumeric(txtHspc) Then MsgBox "HÖ sè phô cÊp ph¶i lµ sè !" txtHspc.SetFocus Exit Sub End If

Hµm cËp nhËt l¹i m∙

If NutAdd Then

Private Sub Save_MCV() Dim old_MCV As String, new_MCV As String old_MCV = Get_MCV new_MCV = Format(Val(old_MCV) + 1, "0000#")

rsCHUCVU.AddNew rsCHUCVU.Fields("MCV") = Get_MCV Call Save_MCV

MyConn.Execute "UPDATE CONTROLS_ID SET MCV ='" & new_MCV & "'"

End If

End Sub

Xem trang tiÕp theo Slide 164

VSoft Group – Vinh University

Thªm sù kiÖn Add_Click, Cancel_Click, Close_Click, Edit_Click ™

Thªm c¸c sù kiÖn

Private Sub CmdNew_Click() NutAdd = True

Unload Me End Sub Private Sub CmdEdit_Click()

txtMcv.Enabled = False

NutAdd = False

txtMcv.Text = Get_MCV

Call SetControls(Me, True)

txtTen.SetFocus End Sub Private Sub CmdCancel_Click()

Slide 167

Thªm sù kiÖn Save_Click rsCHUCVU.Fields("MCV") = txtMcv.Text rsCHUCVU.Fields("TEN") = txtTen.Text rsCHUCVU.Fields("HSPC") = Val(txtHspc.Text) rsCHUCVU.Update Call SetControls(Me, False) Exit Sub Thoat: MsgBox Err.Description End Sub

Private Sub CmdClose_Click()

Call ClearTexts(Me) Call SetControls(Me, True)

VSoft Group – Vinh University

txtMcv.Enabled = False txtTen.SetFocus

™ Ghi

End Sub

l¹i Form chøc vô vµ ch¹y

Call SetControls(Me, False) End Sub

VSoft Group – Vinh University

Slide 165

VSoft Group – Vinh University

Slide 168

Page 28

L ậ p tr ì nh Visual Basic 6.0 X©y dùng form nhËp hå s¬ ™

™

Bæ sung c¸c modul

ThiÕt kÕ Form nh− sau, ghi l¹i víi tªn Nhaphs vµo th− môc Forms

™

C¸c nót Ên cã tªn: cmdNew, cmdEdit, cmdDelete, cmdSave, cmdCancel, cmdClose

VSoft Group – Vinh University

VSoft – Vinh University

Slide 169

Bæ sung thªm hµm lÊy gi¸ trÞ cña 1 tr−êng theo tr−êng kh¸c Public Function GetField(table As String, fin As String, fout As String, vfind As String) As String Dim rs As ADODB.Recordset, sSql As String Set rs = New ADODB.Recordset sSql = "Select * from " & table & " where " & fin & " = '" & vfind & "'" rs.Open sSql, MyConn, adOpenDynamic, adLockOptimistic If rs.RecordCount > 0 Then GetField = rs.Fields(fout) Else GetField = "" End If End Function Slide 172

VSoft Group – Vinh University

Bæ sung c¸c modul

™

Më View code vµ thªm c¸c khai b¸o

Option Explicit ™

Dim rsHOSO As ADODB.Recordset, sSql As String

Më modul, thªm vµo modul t¹o Combo chän ®¬n vÞ Public Sub TaoCboDonvi(cboName As ComboBox) Dim rs As ADODB.Recordset Dim sSql As String Set rs = New ADODB.Recordset sSql = "SELECT * FROM DONVI ORDER BY TEN" rs.Open sSql, MyConn, adOpenDynamic, adLockOptimistic cboName.Clear If rs.RecordCount = 0 Then Exit Sub rs.MoveFirst Do While Not rs.EOF cboName.AddItem rs.Fields("TEN") rs.MoveNext Loop Set rs = Nothing End Sub

VSoft Group – Vinh University

Dim NutAdd As Boolean ™

Click ®óp chuét vµo Form, viÕt trong FormLoad:

Private Sub Form_Load() If Not OpenDB(App.path &”\QLTL.MDB”) Then End Set rsHOSO = New ADODB.Recordset sSql = "Select * from HOSO order by MCB" rsHOSO.Open sSql, MyConn, adOpenDynamic, adLockOptimistic

Set DataGrid1.DataSource = rsHOSO Call SetControls(Me, False) Call TaoCboDonvi(CboDonvi) Call TaoCboChucvu(CboChucvu) End Sub Slide 170

Bæ sung c¸c modul

Slide 173

VSoft Group – Vinh University

™

Nh¸y chuét lªn DataGrid, d÷ liÖu sÏ chuyÓn sang Textbox

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer) ™

On Error GoTo Thoat

Më modul, thªm vµo modul t¹o Combo chän chøc vô Public Sub TaoCboChucvu(cboName As ComboBox) Dim rs As ADODB.Recordset Dim sSql As String Set rs = New ADODB.Recordset sSql = "SELECT * FROM CHUCVU ORDER BY TEN" rs.Open sSql, MyConn, adOpenDynamic, adLockOptimistic cboName.Clear If rs.RecordCount = 0 Then Exit Sub rs.MoveFirst Do While Not rs.EOF cboName.AddItem rs.Fields("TEN") rs.MoveNext Loop Set rs = Nothing End Sub

VSoft Group – Vinh University

CboDonvi.Text = GetField("DONVI", "MDV", "TEN", rsHOSO!MDV) txtHoten.Text = rsHOSO!HOTEN txtNgaysinh.Text = rsHOSO!NGAYSINH CboChucvu.Text = GetField("CHUCVU", "MCV", "TEN", rsHOSO!MCV) txtNgayH.Text = rsHOSO!NGAYH txtHsl.Text = rsHOSO!HSL If rsHOSO!HOPDONG = "X" Then optHopdong.Value = True Else optBienche.Value = True End If Thoat: End Sub Slide 171

VSoft Group – Vinh University

Ghi vµ ch¹y Form ®Ó kiÓm tra !!! Slide 174

Page 29

L ậ p tr ì nh Visual Basic 6.0

Thªm sù kiÖn Save_Click

X©y dùng hµm lÊy m∙ tù ®éng ™

VSoft – Vinh University

Private Sub CmdSave_Click()

Hµm lÊy m∙

On Error GoTo Thoat

Private Function Get_MCB() As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "SELECT MCB from CONTROLS_ID", MyConn Get_MCB = rs.Fields("MCB") Set rs = Nothing End Function

'Phan bien luan du lieu tu bo sung them '... If NutAdd Then rsHOSO.AddNew rsHOSO.Fields("MCB") = Get_MCB Call Save_MCB

™

End If

Hµm cËp nhËt l¹i m∙

Private Sub Save_MCB() Dim old_MCB As String, new_MCB As String old_MCB = Get_MCB new_MCB = Format(Val(old_MCB) + 1, “0000000#")

rsHOSO.Fields("MDV") = GetField("DONVI", "TEN", "MDV", CboDonvi.Text) rsHOSO.Fields("HOTEN") = txtHoten.Text rsHOSO.Fields("NGAYSINH") = txtNgaysinh.Text rsHOSO.Fields("MCV") = GetField("CHUCVU", "TEN", "MCV", CboChucvu.Text)

MyConn.Execute "UPDATE CONTROLS_ID SET MCB ='" & new_MCV & "'"

Xem trang tiÕp theo

End Sub

Slide 175

VSoft Group – Vinh University

Thªm sù kiÖn Add_Click, Cancel_Click, Close_Click, Edit_Click ™

Thªm c¸c sù kiÖn

Private Sub CmdNew_Click() NutAdd = True

Thªm sù kiÖn Save_Click rsHOSO.Fields("NGAYH") = txtNgayH.Text rsHOSO.Fields("HSL") = txtHsl.Text If optHopdong.Value = True Then rsHOSO.Fields("HOPDONG") = "X" Else rsHOSO.Fields("HOPDONG") = " " End If rsHOSO.Update Call SetControls(Me, False) Exit Sub Thoat: MsgBox Err.Description End Sub

Private Sub CmdClose_Click() Unload Me End Sub

Call ClearTexts(Me) Call SetControls(Me, True) CboDonvi.SetFocus End Sub

Private Sub CmdEdit_Click() NutAdd = False Call SetControls(Me, True) CboDonvr.SetFocus

Private Sub CmdCancel_Click() Call SetControls(Me, False)

Slide 178

VSoft Group – Vinh University

End Sub

End Sub ™

VSoft Group – Vinh University

Slide 176

Ghi l¹i Form Hå s¬ vµ ch¹y Slide 179

VSoft Group – Vinh University

Thªm sù kiÖn Delete_Click

Bæ sung thªm hµm ..

Private Sub CmdDelete_Click() If Trim(txtHoten.Text) = "" Then

™

MsgBox "De nghi chon can bo” Exit Sub

Bæ sung thªm hµm khi chän ®¬n vÞ nµo, hiÖn thÞ danh s¸ch c¸n bé cña ®¬n vÞ ®ã. – Nh¸y ®óp con trá chuét vµo CboDonvi vµ chän Cbodonvi_Click()

End If 'Neu co loi thi nhay den nhan Thoat On Error GoTo Thoat

Private Sub CboDonvi_Click() Dim vMDV As String vMDV = GetField("DONVI", "TEN", "MDV", CboDonvi.Text) rsHOSO.Filter = "MDV='" & vMDV & "'" End Sub

If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "Canh bao...") = vbNo Then

Exit Sub End If rsHOSO.Delete Call ClearTexts(Me) rsHOSO.Requery ™

Thoat:

Ghi l¹i form Nhaphs vµ ch¹y

End Sub VSoft Group – Vinh University

Slide 177

VSoft Group – Vinh University

Slide 180

Page 30

L ậ p tr ì nh Visual Basic 6.0

C©u lÖnh SQL – ADO víi SQl

X©y dùng tæng thÓ Thªm mét form míi, ghi vµo th− môc d:\qlluong\forms víi tªn qlluong.frm ™ §Æt tªn form lµ frmQlluong ™ Thªm c¸c menu nh− sau: ™

NhËp d÷ liÖu

TÝnh l−¬ng th¸ng

- NhËp ®¬n vÞ

- TÝnh l−¬ng th¸ng

- NhËp chøc vô

- In l−¬ng th¸ng

KÕt thóc

TËp lÖnh SQL lµ mét gi¶i ph¸p kh¸c lµm viÖc víi CSDL. TËp lÖnh ®−îc chia lµm 2 d¹ng c¬ b¶n: C¸c lÖnh lµm viÖc víi b¶n ghi

T¹o b¶ng [, field2 type [(size)] [NOT NULL] [index2] [, ...]) Tªn b¶ng cÇn t¹o

field1, field2: Tªn tr−êng, Ýt nhÊt ph¶I cã 1 tr−êng

Slide 181

type:

KiÓu d÷ liÖu

size:

KÝch th−íc cña tr−êng. Slide 184

VSoft Group – Vinh University

X©y dùng tæng thÓ

SQL

Chän menu Project - Add form míi, ghi l¹i víi tªn Login, ®Æt tªn frmLogin, thiÕt kÕ form d¹ng:

™

Xo¸ b¶ng DROP TABLE table table : Lµ tªn b¶ng cÇn xo¸

Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cmdOK_Click() txtDatabaseName.Text = App.Path & "\" & txtDatabaseName.Text If not OpenDB(txtDatabaseName.Text) Then End FrmQlluong.Show Me.Hide End Sub ™

C¸c lÖnh lµm viÖc víi cÊu tróc

‹

table:

ViÕt code ®Ó gäi menu tíi c¸c Form (mét sè form cÇn thiÕt kÕ tiÕp).

™

‹

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1]

- T×m kiÕm hå s¬

VSoft Group – Vinh University

™

™

- NhËp hå s¬

™

VSoft – Vinh University

™

Thªm cét ALTER TABLE table ADD field type [(size)] [NOT NULL]

Më c¸c form: frmNhapdv, frmNhapcv, frmNhaphs vµ xo¸ c¸c ®o¹n më CSDL trong Form_load.

™

table:

Tªn b¶ng cÇn t¹o cét

field:

Tªn tr−êng cÇn t¹o

type:

KiÓu d÷ liÖu

size:

KÝch th−íc cña tr−êng.

Xo¸ cét ALTER TABLE table DROP field

VSoft Group – Vinh University

Slide 182

Slide 185

VSoft Group – Vinh University

x©y dùng tæng thÓ

SQL ™

Chän form cÇn ch¹y lµ frmLogin ™ Biªn dÞch ch−¬ng tr×nh thµnh . exe ™ Ch¹y ch−¬ng tr×nh. ™

ChÌn bµn ghi – ChÌn mét b¶n ghi: INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2[, ...]) – ChÌn nhiÒu b¶n ghi tõ b¶ng hoÆc query: INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] FROM tableexpression target : field1, field2 : value1, value2 :

VSoft Group – Vinh University

Slide 183

VSoft Group – Vinh University

Tªn b¶ng hoÆc truy vÊn (query). Tªn cña c¸c tr−êng. Gi¸ trÞ chÌn vµo c¸c tr−êng. Gi¸ trÞ value1 øng víi field1, value2 øng víi field2,… Slide 186

Page 31

L ậ p tr ì nh Visual Basic 6.0

Bµi 8: Data Report – Active report

SQL ™

Xo¸ b¶n ghi DELETE FROM table WHERE criteria

™

ThiÕt kÕ Data Report

table

™

KÕt hîp Data Report víi DataEnvironment

™

KÕt nèi Report víi ch−¬ng tr×nh

™

ThiÕt kÕ Report víi Active Report

™

KÕt nèi Active Report víi ch−¬ng tr×nh

™

Bµi tËp

: Tªn b¶ng

criteria : ®iÒu kiÖn cÇn xo¸ ™

Söa b¶n ghi UPDATE table SET field1= [, field2 = ][,…] [WHERE criteria]

™

VSoft – Vinh University

table:

Tªn b¶ng

field1, field2:

Tªn truêng cÇn cËp nhËt.

Value1, value2:

C¸c gi¸ trÞ cÇn cËp nhËt

criteria :

®iÒu kiÖn c¸c b¶n ghi cÇn söa

Chän b¶n ghi SELECT *|[Tªn c¸c cét] FROM [WHERE ]

VSoft Group – Vinh University

Slide 187

Sö dông c©u lÖnh SQL ™

thiÕt kÕ report dïng Datareport ™

§Ó sö dông c©u lÖnh SQL (trõ lÖnh Select) ta viÕt: – MyConn.Excuted Trong ®ã MyConn lµ ®èi t−îng ADODB.Connection nh− m« t¶ trong bµi t¹p mÉu. – VÝ dô: Xo¸ mét b¶n ghi cã MDV =‘01’ MyConn.Execute “DELETE FROM DONVI WHERE MDV=’01’”

™

Slide 190

VSoft Group – Vinh University

™

ThiÕt kÕ report sö dông DataReport Trong menu Project chän Add Data Report

Bµi tËp vÒ nhµ – Söa Form nhËp ®¬n vÞ sö dông c©u lÖnh SQL thay vÌ dïng c¸c lÖnh cña ADO – Söa Form nhËp chøc vô sö dông c©u lÖnh SQL thay vÌ dïng c¸c lÖnh cña ADO

VSoft Group – Vinh University

Slide 188

Slide 191

VSoft Group – Vinh University

™

X©y dùng Form t×m kiÕm ™

XuÊt hiÖn

2.C¸c thµnh phÇn cña 1 Report – – – – – –

Bµi tËp vÒ nhµ: X©y dùng form t×m kiÕm, söa, xo¸

™

Report Header: HiÓn thÞ 1 lÇn ë ®Çu b¸o c¸o Report Footer: HiÓn thÞ 1 lÇn ë cuèi b¸o c¸o Page Header: HiÓn thÞ t¹i ®Çu mçi trang Page Footer: HiÓn thÞ t¹i cuèi mçi trang Detail Section: HiÓn thÞ c¸c dßng d÷ liÖu Mét hay nhiÒu Group headers hay Group Footers hiÓn thÞ t¹i ®Çu vµ cuèi cña mét ph©n ®o¹n nhãm.

C¸c ®iÒu kiÓn t¹o report

T¹o c¸c dßng th«ng b¸o Thªm c¸c biÕn

ChÌn h×nh ¶nh

VÏ ®o¹n th¼ng

VÏ h×nh ch÷ nhËt, h×nh trßn

TÝnh to¸n

VSoft Group – Vinh University

Slide 189

VSoft Group – Vinh University

Slide 192

Page 32

L ậ p tr ì nh Visual Basic 6.0

VSoft – Vinh University

KÕt hîp DataEnvironment víi Report Chän menu View - Data View Windows, trªn mµn h×nh xuÊt hiÖn:

™

T¹o mét kÕt nèi CSDL míi

Bæ sung mét kÕt nèi tíi CSDL

Chän bæ sung mét kÕt nèi tíi CSDL. ™ Trªn cöa sæ Project Properties xuÊt hiÖn: ™

Slide 193

VSoft Group – Vinh University

Chó ý r»ng trong cöa sæ Data View xuÊt hiÖn connection1.

™

Chän dÊu + ë Table ®Ó xuÊt hiÖn c¸c b¶ng.

™

KÐo b¶ng cÇn in tõ cöa sæ Data View sang connection1 cña cöa sæ DataEnvironment. Slide 196

VSoft Group – Vinh University

Sau khi t¹o xong DataEnvironment, cã thÓ sö dông nã ®Ó thiÕt kÕ cho Report. ™ Më hai cöa sæ: ™

KÕt hîp DataEnvironment víi Report ™

™

Trªn mµn h×nh xuÊt hiÖn:

1- Cöa sæ Report 2- Cöa sæ DataEnvironment

™

Chän biÓu t−îng Properties ®Ó t¹o kÕt nèi víi CSDL. Khi ®ã xuÊt hiÖn cöa sæ míi. Slide 194

VSoft Group – Vinh University

KÕt nèi Dataenvironment víi Database

VSoft Group – Vinh University

KÐo vµ th¶ c¸c tr−êng cÇn in tõ cöa sæ DataEnvironment vµo phÇn Detail section cña Report. ™ Trang trÝ report: bæ sung c¸c thµnh phÇn ™

– Chän Jet3.51 OLEDB (CSDL lµ ACCESS). – Click nót Next>> xuÊt hiÖn..

– – – – – – ™ ™

Chän CSDL cÇn kÕt nèi. ™ Chän Test Connection ®Ó kiÓm tra. ™ Click nót OK. Slide 195

Report Header Report Footer Page Header Page Footer KÎ b¶ng Bæ sung c¸c c«ng thøc tÝnh to¸n, c¸c Group, ...

Ghi l¹i Report vµ DataEnvironment. Trong cöa sæ Project Properties chän Report vµ ®Æt c¸c thuéc tÝnh: – DataSource : Lµ DataEnvironment võa t¹o – DataMember : Lµ tªn b¶ng trong DataEnvironment

™

VSoft Group – Vinh University

Slide 197

™

KÕt nèi Report víi Form – T¹o 1 nót trong form. – §Ó gäi Report gâ: ReportName.Show

VSoft Group – Vinh University

Slide 198

Page 33

L ậ p tr ì nh Visual Basic 6.0

ThiÕt kÕ Form in danh s¸ch

thiÕt kÕ report dïng ActiveReport ™ ™

Yªu cÇu cµi phÇn mÒm ActiveReport Trong menu Project chän More ActiveX Designed -> Data Dynamics ActiveReport.

™

™ ™ Slide 199

VSoft Group – Vinh University

™

Page Header: HiÓn thÞ t¹i ®Çu mçi trang ™ Page Footer: HiÓn thÞ t¹i cuèi mçi trang ™ Detail Section: HiÓn thÞ c¸c dßng d÷ liÖu ™ §Ó bæ sung Report Header, Report Footer nh¸y chuét ph¶i gi÷a Report chän Ýnsert -> Report Header/Footer

Slide 202

Khai b¸o mét biÕn tæng thÓ cña Form Option Explicit Dim rsDANHSACH As ADODB.Recordset

™

ViÕt mét hµm t×m kiÕm nh− sau: Private Sub Timkiem() Dim sSql As String, Dieukien As String, Sapxep As String If cboDonvi.ListIndex = 0 Then Dieukien = "" Else Dieukien = "WHERE DONVI.TEN ='" & cboDonvi.Text & "'" End If If optChucvu.Value = True Then Sapxep = " ORDER BY DONVI.MDV, CHUCVU.MCV" Else Sapxep = " ORDER BY HOSO.HSL" End If

– Report Header: HiÓn thÞ 1 lÇn ë ®Çu b¸o c¸o – Report Footer: HiÓn thÞ 1 lÇn ë cuèi b¸o c¸o

§Ó bæ sung mét hay nhiÒu Group headers hay Group Footers, nh¸y chuét ph¶i gi÷a Report chän Ýnsert -> Group Header/Footer

Slide 200

Slide 203

VSoft Group – Vinh University

C¸c ®iÒu kiÓn t¹o report

ThiÕt kÕ Form in danh s¸ch sSql = "SELECT DONVI.TEN AS TENDV, CHUCVU.TEN AS TENCV,HOTEN,NGAYSINH, NGAYH,HSL,HSPC,HOPDONG FROM DONVI INNER JOIN (CHUCVU INNER JOIN HOSO ON CHUCVU.MCV = HOSO.MCV) ON DONVI.MDV = HOSO.MDV " & Dieukien & Sapxep

T¹o c¸c dßng th«ng b¸o Thªm c¸c biÕn ChÌn h×nh ¶nh

C¸c Option ®Æt tªn lµ: optChucvu, optHsl Combo ®Æt tªn lµ cboDonvi, nót in ®Æt tªn lµ CmdIn

ThiÕt kÕ Form in danh s¸ch

™

VSoft Group – Vinh University

ThiÕt kÕ Form in danh s¸ch c¸n bé

VSoft Group – Vinh University

C¸c thµnh phÇn cña ActiveReport

™

VSoft – Vinh University

Set rsDANHSACH = New ADODB.Recordset

VÏ ®o¹n th¼ng

rsDANHSACH.Open sSql, MyConn, adOpenDynamic, adLockOptimistic

VÏ h×nh ch÷ nhËt, h×nh trßn

Set DataGrid1.DataSource = rsDANHSACH End Sub

™

Nh¸y ®óp vµo CboDonvi vµ chän sù kiªn CboDonvi_Cick() Private Sub cboDonvi_Click() Call Timkiem

Sö dông ADO

End Sub

VSoft Group – Vinh University

Slide 201

VSoft Group – Vinh University

Slide 204

Page 34

L ậ p tr ì nh Visual Basic 6.0 ThiÕt kÕ Form in danh s¸ch ™

Hoµn thiÖn bµi to¸n Chøc n¨ng tÝnh l−¬ng

™

Nh¸y ®óp vµo form, chän Form_load

– ThiÕt kÕ form 1:

Private Sub Form_Load() If Not OpenDB(App.Path & "\QLTL.MDB") Then End Call TaoCboDonvi(cboDonvi) cboDonvi.AddItem "[TÊt c¶...]", 0 cboDonvi.ListIndex = 0 optChucvu.Value = True Call Timkiem End Sub ™

VSoft – Vinh University

txtthang.Text = Month(Date) txtnam.Text = Year(Date) End Sub Private Sub cmdchon_Click() frmtluong.Show frmlthang.Hide

Nh¸y ®óp chuét vµo optChucvu, optHsl

End Sub

Private Sub optChucvu_Click() Call Timkiem End Sub Private Sub optHsl_Click() Call Timkiem End Sub VSoft Group – Vinh University

Private Sub Form_Load()

™

Hai textbox cã tªn lµ: – txtThang – txtNam

™

Private Sub cmdhuy_Click() Unload Me

Hai command button:

End Sub

– CmdChon – CmdHuy Slide 205

Slide 208

VSoft Group – Vinh University

Bæ sung thªm Report

KÕt hîp ADO víi l−íi listview ™

•ThiÕt kÕ Report nh− sau, chó ý kÐo ADO vµo phÇn Detail

ThiÕt kÕ form nh− sau:

™

§Ó cã ®−îc listView1, nh¸y chuét ph¶i lªn thanh c«ng cô, chän Component vµ trong cöa sæ Component chän:

™

KÐo biÓu t−îng ListView vµo form.

Microsoft Window common control 6.0

VSoft Group – Vinh University

Slide 206

KÕt hîp Report víi Form ™

§Æt thuéc tÝnh cho ListView1

Nh¸y ®óp chuét vµo nót cmdIn, viÕt lÖnh Private Sub cmdIn_Click() frmInds.DataControl1.Recordset = rsDANHSACH frmInds.Restart frmInds.Field2.DataField = "HOTEN" frmInds.Field3.DataField = "NGAYSINH" frmInds.Field4.DataField = "CHUCVU" frmInds.Field5.DataField = "HSL" frmInds.Field6.DataField = "HSPC" frmInds.Field7.DataField = "HOPDONG" frmInds.Field8.DataField = "TENDV" If rsDANHSACH.RecordCount > 0 Then rsDANHSACH.MoveFirst frmInds.Show End Sub

VSoft Group – Vinh University

Slide 209

VSoft Group – Vinh University

™

Nh¸y chuét ph¶i lªn ListView1 chän Properties

™

Chän Tab General

™

Chon View: 3 - lvwReport

™

Chän c¸c Check box: – HideSelection – LabelWrap – Enabled – FullRowSelect – GridLines

Slide 207

VSoft Group – Vinh University

Slide 210

Page 35

L ậ p tr ì nh Visual Basic 6.0 §Æt thuéc tÝnh cho ListView1 ™

Chän Tab Column Headers

™

LÇn l−ît chän Insert Column

™

Mçi lÇn chän Insert Column khai b¸o dßng Text xuÊt hiÖn trªn mçi cét, ®é réng cña c¸c cét. C¸c cét ®¸nh chØ sè tõ 1.

™

Chó ý: Bµi nµy chÌn 2 cét, tiªu ®Ò c¸c cét lµ: MDV vµ Tªn ®¬n vÞ.

™

Ch¹y form ®Ó kiÓm tra ™

Ghi l¹i form vµ ch¹y

™

Nh¸y chuét lªn ListView1, d÷ liÖu sÏ chuyÓn sang Textbox. – Trë vÒ Form, click ®óp vµo ListView1 chän sù kiÖn: ItemClick. – §o¹n m∙: Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem) txtMdv.Text = Item.Text txtTen.Text = Item.SubItems(1) Set ListView1.DropHighlight = Item End Sub

Chän xong, Click OK

Slide 211

VSoft Group – Vinh University

Thªm sù kiÖn Add_Click, Cancel_Click, Close_Click, Edit_Click

§Ó chuyÓn d÷ liÖu tõ 1 ADODB.RecordSet thùc hiÖn nh− sau: Dim itmX As ListItem Do While Not rs.EOF Set itmX = ListView1.ListItems.Add(, "E" & rs!MDV, rs!MDV) itmX.SubItems(1) = rs!Ten rs.MoveNext C¸c tr−êng cÇn chuyÓn lªn ListView1 Loop

™

Trong ®ã rs ®−îc khai b¸o lµ 1 ADODB.RecordSet. VÝ dô, chÌn ®o¹n ch−¬ng tr×nh trªn ngay sau ®o¹n:

Chó ý: Form nµy sö dông c¸c hµm trong code modul ®∙ viÕt tr−íc.

End Sub

Dim NutAdd As Boolean Dim OldMdv As String

Private Sub CmdClose_Click() Unload Me

Thªm c¸c sù kiÖn

End Sub Private Sub CmdEdit_Click()

NutAdd = True

NutAdd = False

Call ClearFields(Me)

Call SetControls(Me, True)

Call SetControls(Me, True)

txtMdv.SetFocus

txtMdv.SetFocus

Slide 212

Private Sub CmdCancel_Click() Call SetControls(Me, False)

Option Explicit

End Sub

Click ®óp chuét vµo Form, viÕt trong FormLoad:

Private Sub Form_Load() Dim DatabaseName As String Dim rs As ADODB.Recordset, itmX As ListItem, sSql As String DatabaseName = App.Path & "\QLTL.MDB" If Not OpenDB(DatabaseName) Then Unload Me Exit Sub End If 'Neu co loi thi nhay den nhan ErrHandler On Error GoTo ErrHandler Set rs = New ADODB.Recordset sSql = "Select * from DONVI order by MDV" rs.Open sSql, gConn, adOpenForwardOnly, adLockReadOnly Do While Not rs.EOF Set itmX = ListView1.ListItems.Add(, "E" & rs!MDV, rs!MDV) itmX.SubItems(1) = rs!Ten rs.MoveNext Loop rs.Close Set rs = Nothing Call SetControls(Me, False) Exit Sub ErrHandler: MsgBox "Error: " & Err.Number & ", " & Err.Description End Sub

Thªm khai b¸o

Private Sub CmdAdd_Click()

VSoft Group – Vinh University

™

™

™

Dim rs as ADODB.RecordSet Set rs = New ADODB.Recordset sSql = "Select * from DONVI" rs.Open sSql, gConn, adOpenDynamic ™

Slide 214

VSoft Group – Vinh University

ChuyÓn d÷ liÖu lªn listview ™

VSoft – Vinh University

OldMdv = txtMdv.Text End Sub

Slide 215

VSoft Group – Vinh University

Thªm sù kiÖn Delete_Click Më c¬ së d÷ liÖu

Private Sub CmdDelete_Click() If Trim(txtMdv.Text) = "" Then MsgBox "De nghi chon don vi" Exit Sub End If 'Neu co loi thi nhay den nhan ErrHandler On Error GoTo ErrHandler

ChuyÓn lªn ListView1

If MsgBox("Dong y xoa khong ?", vbQuestion + vbYesNo, "Canh bao...") = vbNo Then Exit Sub gConn.Execute "DELETE * from DONVI where MDV = '" & txtMdv.Text & "'" ListView1.ListItems.Remove ListView1.SelectedItem.Index

'remove from list

Call ClearFields(Me) Exit Sub ErrHandler: MsgBox "Error: " & Err.Number & ", " & Err.Description End Sub

VSoft Group – Vinh University

Slide 213

VSoft Group – Vinh University

Slide 216

Page 36

L ậ p tr ì nh Visual Basic 6.0

VSoft – Vinh University

Thªm sù kiÖn Save_Click Private Sub CmdSave_Click() Dim rs As ADODB.Recordset, sSql As String Dim itmX As ListItem 'Neu co loi thi nhay den nhan ErrHandler

The End.

On Error GoTo ErrHandler 'Kiem tra trung ma If (NutAdd) Or ((Not NutAdd) And (OldMdv <> txtMdv.Text)) Then Set rs = New ADODB.Recordset sSql = "Select * from DONVI where MDV = '" & txtMdv.Text & "'" rs.Open sSql, gConn ', adOpenForwardOnly, adLockReadOnly If Not rs.EOF Then MsgBox ("Ma da trung") Exit Sub End If End If

Xem trang tiÕp theo VSoft Group – Vinh University

Slide 217

Slide 220

VSoft Group – Vinh University

Thªm sù kiÖn Save_Click

ThiÕt kÕ Report dïng Crystal Report

If NutAdd Then

Crystal Report kh«ng ®−îc cµi tù ®éng vµo VB mµ ph¶i cµi ®Æt riªng. ™ Sau khi cµi ®Æt xong Crystal Report, nã ®−îc khëi ®éng tõ menu: Add - Ins -> Report Designer. ™ Tõ menu File cña Crystal Report chän New ®Ó t¹o Report míi, trªn mµn h×nh xuÊt hiÖn. ™

sSql = "INSERT into DONVI (MDV, TEN) values ('" & txtMdv.Text & "','" & txtTen.Text & "')" gConn.Execute sSql Set itmX = ListView1.ListItems.Add(, "E" & txtMdv.Text, txtMdv.Text) itmX.SubItems(1) = txtTen.Text Set ListView1.SelectedItem = ListView1.ListItems(ListView1.ListItems.Count) Set ListView1.DropHighlight = ListView1.ListItems.Item(ListView1.ListItems.Count) Else sSql = "Update DONVI Set MDV = '" & txtMdv.Text & "', TEN = '" & txtTen.Text & "' where MDV = '" & OldMdv & "'"

™

gConn.Execute sSql Set itmX = ListView1.ListItems.Item("E" & txtMdv.Text) itmX.SubItems(1) = Trim(txtTen.Text) End If

Chän Standard vµ thùc hiÖn theo c¸c chØ dÉn.

Call SetControls(Me, False) Exit Sub ErrHandler: MsgBox "Error: " & Err.Number & ", " & Err.Description End Sub VSoft Group – Vinh University

Slide 218

KÕt qu¶ cña Form

Sö dông biÕn trong Report ™

™

Slide 221

VSoft Group – Vinh University

Thªm mét Form míi vµ t¹o nh− sau:

Ghi l¹i Form vµ ch¹y Form Name: rp1

Name: CmdIn

™

Name: CmdKetthuc

Chó ý: – §Ó cã ®−îc biÓu t−îng Crytal Report, nh¸y chuét ph¶i lªn thanh c«ng cô, chän Component -> Chän Crytal Report. – KÐo vµ th¶ biÓu t−îng Crytal Report vµo form.

™

VSoft Group – Vinh University

Slide 219

Môc ®Ých: T¹o mét report ®Ó in hå s¬, khi chän ®¬n vÞ trªn CboDonvi th× tªn ®¬n vÞ ®−îc ®−a vµo report vµ chØ danh s¸ch c¸n bé trong ®¬n vÞ ®−îc in.

VSoft Group – Vinh University

Slide 222

Page 37

L ậ p tr ì nh Visual Basic 6.0 ™

Sö dông biÕn trong Report ™

Nh− vËy Tªn ®¬n vÞ ®−a vµo trong Report chÝnh lµ gi¸ trÞ cña biÕn (§−îc lÊy lµ CboDonvi.Text).

™

ChØ danh s¸ch c¸n bé trong ®¬n vÞ ®−îc in chÝnh lµ läc c¸c b¶n ghi trong b¶ng HOSO theo 1 ®iÒu kiÖn.

™

Tr−íc tiªn t¹o mét report ®Ó in hå s¬ gåm c¸c tr−êng: Mdv, Mcb, Hoten, Ngaysinh, HSL, Hopdong.

™

™

C¸ch thùc hiÖn ®−a gi¸ trÞ cña biÕn vµo report.

Nh¸y ®óp chuét vµo nót In hå s¬ vµ viÕt: Private Sub CmdIn_Click() rp1.Formulas(0) = "vTendv = '" & CboDonvi.Text & "'" rp1.Formulas(1) = "vMdv = '" & mdv & "'" rp1.PrintReport End Sub

– VÝ dô: Gâ tªn biÕn lµ vTendv, chän OK khi ®ã xuÊt hiÖn mét cöa sæ. Chän Accept vµ ®−a biÕn vµo vÞ trÝ cÇn ®Æt.

Chó ý: Tªn biÕn trong Report ®−îc ®Æt gièng nh− quy t¾c ®Æt tªn biÕn trong Form.

VSoft Group – Vinh University

Nh¸y ®óp vµo CboDonvi, chän sù kiÖn Click vµ viÕt: Private Sub CboDonvi_Click() Data1.Recordset.FindFirst "Ten = '" & CboDonvi.Text & "'" If Not Data1.Recordset.EOF Then mdv = Data1.Recordset.Fields("MDV") Else mdv = "" End If End Sub

– T¹o mét biÕn: Nh¸y chuét vµo biÓu t−îng [x+2] hoÆc chän Insert -> Formula Field... vµ gâ vµo tªn biÕn.

™

VSoft – Vinh University

Tªn biÕn trong Report Slide 223

VSoft Group – Vinh University

Tªn biÕn trong Form Slide 226

Läc b¶n ghi trong crytal ™ ™

C¸c b¶n ghi trong 1 b¶ng cã thÓ ®uîc läc ra ®Ó in. C¸ch thùc hiÖn: – T¹o mét biÕn: Nh¸y chuét vµo biÓu t−îng [x+2] hoÆc chän Insert -> Formula Field... vµ gâ vµo tªn biÕn, chän OK, xuÊt hiÖn cöa sæ, Accept. VÝ dô tªn biÕn gâ vµo lµ vmdv. – Chän menu Report -> Select reports Expert -> XuÊt hiÖn cöa sæ. Trong cöa sæ chän Show Formula >>>, tiÕp tôc chän Formula Editor. – Trong cöa sæ Formula Text: ‹ §−a con trá ®Õn tr−êng cÇn chän (tr−êng HOSO.MDV), nh¸y ®óp. ‹ Gâ vµo to¸n tö so s¸nh (=, >,<,<>,...) (ë ®©y lµ to¸n tö =). ‹ §−a con trá ®Õn tªn biÕn võa t¹o (vMdv), nh¸y ®óp. C«ng thøc xuÊt hiÖn d¹ng: {HOSO.MDV} = {@vMdv} ‹ Chän Check ®Ó kiÓm tra c«ng thøc ®∙ chän. – Chän Accep trë vÒ cöa sæ tr−íc – Chän OK.

VSoft Group – Vinh University

Slide 224

§−a report vµo Form ™

§Ó ®¹t ®−îc môc ®Ých, nh¸y ®óp vµo Form: – Khai b¸o 1 biÕn mdv trong form. – Trong thñ tôc Form_Load() viÕt ®o¹n ch−¬ng tr×nh chuyÓn tªn ®¬n vÞ lªn CboDonvi: Private Sub Form_Load() Data1.DatabaseName = App.Path & "\qltl.mdb" Data1.RecordSource = "DONVI" Data1.Refresh Do While Not Data1.Recordset.EOF CboDonvi.AddItem Data1.Recordset.Fields("TEN") Data1.Recordset.MoveNext Loop End Sub

VSoft Group – Vinh University

Slide 225

Page 38