[转帖]医药行业进销存系统论文=３=（VB+SQL Sever 2000）
database programming of visual basicvisual basic数据库编程
visual basic作为应用程序的开发“利器”也表现在数据库应用程序的开发上，它良好的界面和强大的控件功能使数据库编程变得简单多了。但即便如此，数据库应用程序的开发仍然算得上是vb编程中的难点，这是因为你不仅要熟悉vb中关于数据库编程方面的知识（当然这是十分简单的）还要了解数据库的知识。所以我们先介绍一下数据库的基本知识，算是学习数据库编程前的热身运动吧！
一、热身运动
首先需要声明是，我们这里介绍的数据库知识都是指的关系数据库。所谓关系数据库就是将数据表示为表的集合，通过建立简单表之间的关系来定义结构的一种数据库。
不管表在数据库文件中的物理存储方式如何，它都可以看作一组行和列，与电子表格的行和列类似。在关系数据库中，行被称为记录，而列则被称为字段。下面是一个客户表的例子。
表1 客户表
客户号 姓名 地址 城市 街道 邮编
1723 doe john 1234 fifth avenue new york ny 1004
3391 smith mary 9876 myrtle levee boston ma 6078
3765 blasé mortimer 2296j river road peoria il 7011
此表中每一行是一个记录，它包含了特定客户的所有信息，而每个记录则包含了相同类型和数量的字段：客户号、姓名等等。
表 是一种按行与列排列的相关信息的逻辑组，类似于工作单表。
字段 数据库表中的每一列称作一个字段。表是由其包含的各种字段定义的，每个字段描述了它所含有的数据。创建一个数据库时，须为每个字段分配一个数据类型、最大长度和其它属性。字段可包含各种字符、数字甚至图形。
记录 各个客户有关的信息存放在表的行，被称为记录。一般来说，数据库表创建时任意两个记录都不能相同。
键 键就是表中的某个字段（或多个字段），它（们）为快速检索而被索引。键可以是唯一的，也可以是非唯一的，取决于它（们）是否允许重复。唯一键可以指定为主键，用来唯一标识表的每行。例如，在前面的例子中，客户标识号 (客户号) 是表的主键，因为客户号唯一地标识了一个客户。
关系 数据库可以由多个表组成，表与表之间可以以不同的方式相互关联。例如，客户数据库还可以有一个包含某个客户的所有定单的表。它只用“客户号”字段来引用该定单的客户，而不在定单表中的每项重复所有客户信息，如下表所示:
表2 定货表
定货 客户号 日期 内容 数量
14764 3391 2/23/94 27 $22.95
14932 3391 3/17/94 46 $9.57
15108 8765 2/15/96 27 $22.95
在这个表中，客户号字段引用了客户表中的 客户号字段，从而把定单和客户联系起来了。可以看到，客户 3391 (mary smith) 在 94 年 2 月 23 日订购了 27 项，在 94 年 3 月 17 日订购了 46 项。用来建立关系的键叫做外部键，因为它与“外部”表（客户表）的主键关联。
一对多和多对多关系 上表中的关系类型叫做一对多关系，因为一个客户可以发出多个定单，而某个特定的定单只能是一个客户所发。也可以建立多对多的关系。例如，列出所有可以销售的项（存货）的盘存表:
表3 存盘表
内容 描述 供应商 费用 盘存
27 straw hat garden supply co. $14.00 50
46 garden gloves garden supply co. $4.50 75
102 hanging floral industries $6.00 137
从盘存表中，可以看到在客户和存货项之间存在多对多的关系。也就是说，一个客户可以订购多个存货项，而一个存货项又能够被多个客户订购。多对多关系是通过两个独立的一对多关系来定义的，公共的“多”表包含了两个其它表的外部键。在该例中，定货s 表与 盘存 表（通过 “内容”）与 customer 表（通过 客户号）都相关联。通过这三个表，我们可以看到，mary smith (客户号 3391) 订购了 straw hat (“内容” 27) 和garden gloves (“内容” 46)，而 mary smith (客户号 3391) 和 mortimer blaselflatz (客户号 8765) 都订购了straw hat (“内容” 27)。如果把客户表和盘存表的相关字段与 定货表的“定货”字段联结起来，建立一个“关联”表，那么这个关系就更清楚了。
表4 关联表
定货号 客户号 姓名 内容 描述
14764 33391 smith mary 27 straw hat
14932 33391 smith mary 46 garden gloves
15168 8765 blaselfatz mortimer 27 straw hat
规范化 数据库设计者的任务就是组织数据，而组织数据的方法，应能消除不必要的重复，并为所有必要信息提供快速查找路径。为了达到这种目标而把信息分离到各种独立的表中去的过程，叫做规范化。
规范化是用许多指定的规则和不同级别的范式来进行规范的复杂过程。该过程的研讨已超出了本文的范围。但是，大多数简单数据库的规范化可以用下面简单的经验规则来完成:包含重复信息的表必须分成独立的几个表来消除重复。
例如，使学生和课程对应的学生数据库，包含了下表所示的信息。
表 5
学生 课程 描述 教授
1 4 introduction to physiology dawson
2 3 applied basketweaing carruth
3 1 physics for short-cooks adams
4 2 introduction to physiology damsons
如果有选学了十二门课程的 1000 名学生，每门课程的说明和教师将显示100多次─ 对选了那门课程的每个学生都要重复一次。要避免这种低效率，应当把表分成两个独立的表来规范化，一个用来表示学生，另一个用来表示课程。
现在表被规范化了，所以，要改变特定课程的课程描述或“数据”，只要改变一个记录就可以了。
以上是关于数据库的基本知识，这是学习数据库编程所必须的。虽然数据库技术作为一门学科，其深度和广度不是这点篇幅能描述的，但作为入门和简单数据库编程应该是足够了。
好了，下面我们就可以开始练练了。我们经常遇到数据库系统是登记系统，不管你是在单位，或是参加什么组织，登记是免不了的，而且它的结构比较简单，我们就以一个登记系统为例吧。分析一下该系统所涉及到的数据。
二、磨刀不误砍柴功
对于登记，要跟踪的信息包括：
● 姓名 ● 性别
● 籍贯 ● 年龄
● 出生年月 ● 单位
● 地址 ● 邮政编码
● 电话 ● 传真
当然，可以简单地创建一个表，使得上述的每个数据项对应一个字段。
现在需要给表指派主键,用以唯一标识每一条记录,在登记表中分别添加登记号作为唯一键,这样就保证数据库中的任两条记录都不同了。
对数据库做出以上分析后，我们就可以开始建立数据库了。
三、建营扎寨
在这里我们学习怎样建立数据库，首先需要确定要建立数据库的类型。在visual basic中通过数据访问控件或数据访问对象（dao）可以访问下列数据库：
1． jet数据库，即microsoft access
2． isam数据库，如：dbase，foxpro等
3． odbc数据库，凡是遵循odbc标准的客户/服务器数据库。如：microsoft sql server、oracle
一般来说，如果要开发个人的小型数据库系统，用access数据库比较合适，要开发大、中型的数据库系统用odbc数据库更为适宜。而dbase和foxpro数据库由于已经过时，除非特别的情况，否则不要使用。在我们的例子中，当然选用access数据库了。建立access数据库有两种方法：一是在microsoft access中建立数据库。点击“新建”按钮就可以建立新的表了（如图1）。这里我们主要介绍第二种方法：使用可视化数据管理器，不需要编程就可创建数据库。可视化数据管理器是一个非常有用的应用程序，它是vb企业版和专业版附带的，在目录\..\devstudio\vb\samples\visdata下。
点击菜单“文件”项下“新建”子项“microsoft access”子项的“版本7.0 mdb”项。在弹出窗口中输入新建数据库的名称“登记”，出现下面图3所示窗口:
要生成新的表，右键单击数据库窗口弹出菜单，然后选择“新表”命令，在随后出现的“表结构”对话框中建立所要的字段。每次向表中加入新的字段，单击“增加字段”按钮，会出现图4 的“增加字段”对话框。
“增加字段”对话框中的选项如表10所示，根据字段的类型，有些选项是无效的，无法读取。
在我们建立的登记数据库中，各个字段的类型如表11。
要注意的是，由于字段登记号用来唯一标志记录的，因此，它不能由用户输入。所以在定义该字段时需要定义为long数据类型，“自动生成字段”项有效，并选中这一项。这样当用户每输入一条新记录时，系统就会在该字段上自动输入一个与其它记录不同的值。
在access数据库中，关键字是用索引实现的，作为编程人员在对表类型的记录集编程时，只需调用索引名。在查询时，rushmore技术自动用索引信息优化查询。完成表定义后，点击“增加索引”按钮，在窗口中右边有三个选项，添加索引对话框选项。
当然，学会数据库的建立也并非一朝一夕的事，读者不妨多练习一下。下面你就可以运行vb开始我们的编程了。
四、千里相会
visual basic 数据库应用程序有三个部分，如图7所示。
用户程序是程序员开发的，也是我们即将用vb来编写的部分。数据库引擎是数据库驱动程序，使用它程序员可以用统一的格式访问各种数据库，不管这个数据库是本地的 visual basic 数据库，还是所支持的其它任何格式的数据库格式，所使用的数据访问对象和编程技术都是相同的。数据库则是我们上面完成的部分。从这个结构可以看出用户与正在访问的特定数据库无关。那我们在用vb编写数据库程序时，就需要使程序能够访问指定的数据库。
如果是简单的数据库应用，可以使用 data 控件来执行大部分数据访问操作，而根本不用编写代码。与 data 控件相捆绑的控件自动显示来自当前记录的一个或多个字段的数据。
致 谢
附 录
进货订单信息设置窗体(frmdh)的数据显示与删除的代码如下：
option explicit
public txtsql as string
dim mrc as adodb.recordset
private sub cmdadd_click()
gintdhmode = 1
frmdh1.show 1
end sub
private sub cmddelete_click()
dim txtsql as string
dim intcount as integer
dim mrc as adodb.recordset
dim msgtext as string
if msglist.rows > 1 then
if msgbox("真的要删除编号为" & trim(msglist.textmatrix(msglist.row, 1)) & "的进货单记录吗？", vbokcancel + vbexclamation, "警告") = vbok then
intcount = msglist.row
txtsql = "delete from dh where dh_no ='" & trim(msglist.textmatrix(intcount, 1)) & "'"
set mrc = executesql(txtsql, msgtext)
unload frmdh
frmdh.txtsql = "select dh_no,in_date,ywman,gfdm,wzdm,cxend,sl,in_danj,i_zk,i_zke,bz from dh"
frmdh.show
end if
end if
end sub
private sub cmdmodify_click()
dim intcount as integer
if frmdh.msglist.rows > 1 then
gintdhmode = 2
intcount = msglist.row
if intcount > 0 then
frmdh1.txtsql = "select * from dh where dh_no ='" & trim(msglist.textmatrix(intcount, 1)) & "'"
frmdh1.show 1
else
msgbox "警告", vbokonly + vbexclamation, "请首先选择需要修改的纪录！"
end if
end if
end sub
private sub form_load()
showtitle
showdata
end sub
private sub form_resize()
if me.windowstate <> vbminimized and fmainform.windowstate <> vbminimized then
'边界处理
if me.scaleheight < 10 * lbltitle.height then
exit sub
end if
if me.scalewidth < lbltitle.width + lbltitle.width / 2 then
exit sub
end if
'控制控件的位置
lbltitle.top = lbltitle.height
lbltitle.left = (me.width - lbltitle.width) / 2
msglist.top = lbltitle.top + lbltitle.height + lbltitle.height / 2
msglist.width = me.scalewidth - 200
msglist.left = me.scaleleft + 100
msglist.height = me.scaleheight - msglist.top - 1500
frame2.top = msglist.top + msglist.height + 50
frame2.left = me.scalewidth / 2 - 3000
end if
end sub
public sub formclose()
unload me
end sub
private sub showdata()
dim j as integer
dim i as integer
dim msgtext as string
set mrc = executesql(txtsql, msgtext)
with msglist
.rows = 1
do while not mrc.eof
.rows = .rows + 1
for i = 1 to mrc.fields.count
if not isnull(trim(mrc.fields(i - 1))) then
select case mrc.fields(i - 1).type
case addbdate
.textmatrix(.rows - 1, i) = format(mrc.fields(i - 1) & "", "yyyy-mm-dd")
case else
.textmatrix(.rows - 1, i) = mrc.fields(i - 1) & ""
end select
end if
next i
mrc.movenext
loop
end with
mrc.close
end sub
'显示grid表头
private sub showtitle()
dim i as integer
with msglist
.cols = 12
.textmatrix(0, 1) = "进货单编号"
.textmatrix(0, 2) = "日期"
.textmatrix(0, 3) = "业务员"
.textmatrix(0, 4) = "供应商代码"
.textmatrix(0, 5) = "物资代码"
.textmatrix(0, 6) = "有效期限"
.textmatrix(0, 7) = "数量"
.textmatrix(0, 8) = "进货单价"
.textmatrix(0, 9) = "折扣"
.textmatrix(0, 10) = "总金额"
.textmatrix(0, 11) = "备注信息"
'固定表头
.fixedrows = 1
'设置各列的对齐方式
for i = 0 to 10
.colalignment(i) = 0
next i
'表头项居中
.fillstyle = flexfillrepeat
.col = 0
.row = 0
.rowsel = 1
.colsel = .cols - 1
.cellalignment = 4
'设置单元大小
.colwidth(0) = 1000
.colwidth(1) = 1000
.colwidth(2) = 2000
.colwidth(3) = 1000
.colwidth(4) = 1000
.colwidth(5) = 1000
.colwidth(6) = 1000
.colwidth(7) = 1000
.colwidth(8) = 1000
.colwidth(9) = 1000
.colwidth(10) = 1000
.colwidth(11) = 2000
.row = 1
end with
end sub
private sub msglist_mouseup(button as integer, shift as integer, x as single, y as single)
'右键弹出
if button = 2 and shift = 0 then
end if
end sub
上述代码很好的展示了该窗体的实现和删除的功能。 还有英文的 database programming of visual basic
visual basic conduct and actions applied the " sharp weapon" of development of the procedure also expresses on the database development of applying the procedure, it good interface with mighty control a function makes database plait the distance mulched become simple. but even if such, the database applies the development of the procedure to still calculate top is knowledge( certainly this is very simple of) that vb plait distance inside of a little bit difficult, this is because you not only want to acquaint with the vb inside to weave the distance concerning the database still understand the knowledge of the database. so we introduce first the basic knowledge of the database, be regarded as the warm-up exercise that study database weave distance front!
one, warm-up exercise
needing first avowal is, relation database that database knowledge that we here introduce all point. the so-called relation database is to means data as that the form gather, passing to establish simple form an a kind of database for of relation to defining construction.
i ignore the watch at how saving way in physics in the document in database is, it can see to make an a line for with row, with electronics form is similar with the row. in relation database, the line were called the record, but the row then is called word segment. the underneath is an example of customer's form.
table 1 customer form
customer's number name address city street postal code
1723 doe john 1234 fifth avenue new york ny 1004
3391 smith mary 9876 myrtle levee boston ma 6078
3765 blasé mortimer 2296j river road peoria il 7011
this form inside each an all in formations for is a record, it including particular customer, but each record then included the same type with the word segment of the quantity: customer's number, name etc.
form is logic set that a kind of related information that press a line of arranging with row, similar in single form in work.
each row of the word a database form inside calls a word segment. watch is from every kind of word a definition of its containment of, each word a data for describing its implying. while creating to set up a database, the beard assign for each word segment a the piece belongs to the sex with the other according to the type, biggest length. the word segment can include every kind of word sign, arithmetic figure even sketch.
an information of relevant customer deposits in the line of the form, is called record. by any large, arbitrarily two records for database form to create set up can't be same.
key be a certain word segment( or several words segment) of the form inside, it() for fast inspect but drive index. the key can be unique, and also can then the right and wrong is unique, being decided by it() whether admission repetition. unique key can specify for main key, using each one that come to unique marking form. for example, in anterior example, customer the marking number( customer's number) is the main key of the form, because customer's number was uniquely the marking a customer.
the relation database can be constituted by several forms, form and form can in different way mutually connection. for example, customer's database can also have the form of all order forms of a certain customer in a containment. it only use a customer that an each repetition all customer's information for quoting from that order form, but not in order form, is shown as watch below:
table 2 order the form
order customer's number date contents quantity
14764 3391 2/23/94 27 $22.95
14932 3391 3/17/94 46 $9.57
15108 8765 2/15/96 27 $22.95
in this form, customer the number word segment quoted from the customer's customer in the form number word segment, from but contact the order form with the customer. can see, customer 3391( mary smith) ordered on in february of 94 23 27 item, ordered on in march of 94 17 46 item. use to be called the exterior key to the key that establish the relation, because of it with the main key connection of the " exterior" form( customer's form).
a rightness of many relate to last relation in the form type more to be called a rightness of much relating to with many rightness, a certain and particular order form can be a customer a hair because a customer can send out several order forms, .can also establish many a relation for munchies. for example, the inventory form of the item( stock) that list the all selling:
table 3 inventory form
contents describe supplier expenses inventory
27 straw hat garden supply co. $14.00 50
46 garden gloves garden supply co. $4.50 75
102 hanging floral industries $6.00 137
from the inventory form inside, can see to exists with the stock an at the customer many a relation for mulches. too is to say, a customer can order several stocks item, but a stock item is again can drive several customers order. many right nesses much relates to is to pass two independence a rightness of to much relate to define of, the public" many" form included the exterior key of two other forms. in that example, order goods the s form to is all and mutually with the form of customer( pass customer's number) with inventory form( pass the " contents") the connection. pass these three forms, we can see, the mary smith( customer's number 3391) ordered the straw hat(" contents"27) with the garden gloves(" contents"46), but the mary smith( customer's number 3391) ordered the straw hat with mortimer blaselflatz ( customer's number 8765) all.(" contents"27)if orders goods customer's form with the related word segment of the inventory form and form of" order goods" the word a coupling gets up, establishing a" connection" form, so this relation clearer.
table 4 connection form
order customer's number name contents describe
14764 33391 smith mary 27 straw hat
14932 33391 smith mary 46 garden gloves
15168 8765 blaselfatz mortimer 27 straw hat
the norm turns the database design of mission be method that the data of buildup, but the data of buildup, should can dissolve otiose repetition, and for have the necessary information offering to check to seek the path quickly. for attaining this kind of target but separate information to the process gone to in every kind of independent form, be called the norm turn.
it is complicated process to use many appointed rules to proceed the norm with the type of the different class that norm turn. that process studies and discuss already beyond the reach of textual scope. but, the norm of the simple database in majority turn and can use the simple experience in underneath rule completes: include the form of the information of repetition must be divided into independent a few forms dissolve repetition.
for example, make student with course to should of student's database, included the next the information that watch show.
table 5
student course describe professor
1 4 introduction to physiology dawson
2 3 applied basketweaing carruth
3 1 physics for short-cooks adams
4 2 introduction to physiology damsons
if have chosen to learn 12 curricular 1000 students, the each curricular elucidation will show with the teacher 100 many- to choose that curricular and each student repeat once. to avoid this kind of low efficiency, should to turn the dichotomous and independent form in form to the norm, an is used to mean the student, the another uses mean the course, such as table 6, table 7 show.
table 6
student course
1 4
2 3
3 1
4 4
table 7
course describe professor
1 physics for short- cooks adams
2 counterculture sociology beckley
3 applied basketweaing carruth
4 introduction to physiology damsons
now the form was turned by the norm, so, to changes the particular and curricular course describe or" data", as long as change a record all right.
is a basic knowledge concerning database above, this is what study database plait distance must. although the database technique is used as a course, its depth is not this space and can describe with the wide degree of, be used as the primer with simple database plait the distance should be all right already.
like, underneath we can start practicing. we usually meet the database system is to registers the system, ignoring you are in the unit, or attend what buildup, the register is to cans not help of, and it of the construction is more simple, we take a register system as an example. the analysis is once a data for involving.
two, understanding the elements
for the register, want the on the trail of information include:
● name ● sex
● native place ● age
● date of birth ● unit
● address ● postal service codes
● telephone ● fax
certainly, can create to set up a form in brief, make above each piece according to item to should a word segment.
need to point the form the main key in parties now, in order to unique marking each a record, increase register number conduct and actions unique key respectively in register form, guarantee a two records like this all different.
to the database after making an above analysis, we can start establishing the database.
three, set up the firm in camp
here we study to establish the database how, needing to make sure the type that to establish the database first. pass in visual basic the data interview control piece or data interview object ( dao) can visit the following database:
1. the database of jet, namely microsoft access
2. the database of isam, such as: dbase, etc. of foxpro
3. the database of odbc, every follow the standard of odbc customer/ server database. such as: microsoft sql server, oracle
by any large, if develop the personal small scaled database system, use access database comparison accommodation, to develop the big and medium-sized database system uses the database of odbc fester. but dbase with the database of foxpro because of already dated, unless special circumstance, do not use otherwise. in our example, chose to use the database of access certainly. establish the database of access contain two kinds of methods: while establishing the database in microsoft access. click" new set up" button can establish the new form.( such as figure 1)here we main introduction the second method: the usage can see to turn the data management machine, do not need to weave the distance and then can create to set up the database. can see to turn the data management machine is the applied procedure of a great use, it is a vb business enterprise version is supplementary with the profession version, in the catalogue\..\ devastation\ vb\ samples\ the visdata is next, its interface is as follows the diagram.
click the menu" document" item is next" new set up" son item" microsoft access" son item of" edition 7.0 mdb" item. input the new setting up the name of the database in flick window way open" register", appear the underneath figure 3 show the window way:
want the born and new form, the single shot in right key database window way flicks the menu open, then the choice" new form" ordinaries, establishing in" watch construction" dialog box appeared later on a word for wanting segment. the every time faces to join in the form new word segment, single shot" increment word segment" button, will appear the figure 4 of" increment word segment" dialog box.
"increment word a type for" options in the dialog box, such as table,10 showing, according to word segment. in our the register database that establish, the type of the each word segment, such as table11.
want to notice of is, because the word segment registers the number be used unique marking record of, therefore, it can't be inputted by customer. so while defining the word's segment need to be defined for long data type, the " automatic born word segment" item is valid, and picks out this item. be the customer like this to input to record with other the different value every input a new record, system would on the word's segment automatically.
in access database, the key word realizes with the index of, conduct and actions plait distance personnel at gather to weave the distance to the record of the form type, needs to adjust to use index. while searching, the technique of rushmore uses the index information the excellent turning the search automatically. after completing the form definition, click" increment index" button, flick such aspen figure 5 show the window way.
in window way the right side contain three optioned, its meaning, such as table,12.
increase the index dialog box options
after completing such as figure 6.
certainly, the academic association database establishes too not a dynasty the business of the one night, the reader no harm much practices once. underneath you can circulate the vb started our plait distance.
four, a long distance meets
applied procedure in the database of visual basic contain three parts, such as figure 7 show.
customer procedure is part that procedure member development of, and also is we will soon weave to write with the vb. the database engine is a database to drive the procedure, using its procedure member can use united format interview every kind of database, whether this database is a native the database of visual basic, or an other the database format of any format for supporting, a data for using interview object is similar to wove distance technique is all. the database is then the part that we up complete. can see from this construction the customer have nothing to do with at the particular database that visit. that we at use the vb the plait write the database procedure, need to can make the procedure visit the appointed database.
if is a simple database applied, can use the data control piece to carry out big and parts of data interview operate, but basic need not plait writes the code. control an a data for or several words segment for controlling mutually and bindingly first piece the automatic manifestation comes from the front record with the data. [EM03][EM03][EM03] 哇英文的都有无敌了！！ 看看i 谢谢~~~~~~~~
页:
[1]