怎样在vb data控件中实现 多表个显示

来源：百度知道 编辑：UC知道 时间：2024/05/17 03:39:49
我想在1个控件中将数据库中2个有关系的表的以一张表的形式显示出来，
以下的代码总提示from语句出错

Set sturs = New ADODB.Recordset
cmdclear.Enabled = False

sturs.CursorLocation = adUseClient
sturs.Open "select stu_num,stu_name,stu_birth,stu_sex,stu_id," & _
"stu_nation,stu_addr,stu_indate,stu_class from student union" & _
"select xy_type from xuejileixing union" & _
"select zy_name,zy_from from zhuanye ", mycon, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = sturs
DataGrid1.Refresh
刚才的错误属于格式和算法错误，union后没加空格
改成下面这样 还是不行

sturs.Open "SELECT stu_num,stu_name,stu_birth,stu_sex,stu_id,stu_nation," & _
"stu_addr, stu_indate, stu_class, xy_type, zy_name, zy_from " & _
"From student, xuejilqixing, zhuanye WHERE student.stu_xuejileihao*=xuejiqixing.xj_num AND student.stu_zhuanyehao*=zhuanye.zy_num", mycon, adOpenDynamic, adLockOptimistic
提示语法错

"select stu_num,stu_name,stu_birth,stu_sex,stu_id," & _
"stu_nation,stu_addr,stu_indate,stu_class from student union" & _
"select xy_type from xuejileixing union" & _
"select zy_name,zy_from from zhuanye ",
语句不对。
可以这样：
"SELECT stu_num,stu_name,stu_birth,stu_sex,stu_id,stu_nation,stu_addr,stu_indate,stu_class,xy_type,zy_name,zy_from
FROM student,xuejilqixing,zhuanye
WHERE student.stu_num*=xuejiqixing.stu_num AND student.stu_num*=zhuanye.stu_num"
这里有一点，就是WHERE条件中的，因为你说几个表是有联系的，那么要主表student中的主键与两个副表中的外键相等，所以WHERE语句中的条件要改成自己表中的相应主键、外键名称。【*= 】符号右外连接的意思，以*所在边的表为主体，其他表的内容添加在主表的右侧，副表中不存在的值以NULL填充。

还有，如果几个表中有相同名称的列名的话，要注意在前面加上表名限制，以防止出错。如：student和zhuanye都有stu_num，那么在SELECT中要写上SELECT student.stu_num,...

把*号去掉就可以了。在我自己的机器上刚试过，三个表在同一个Datagrid1中显示所有的数据。VB中的语法好象和SQLSever2003上的有一点不一致。

你这样
dim sql as string
sql="select stu_num,stu_name,stu_birth,stu_sex,stu_