Visual Basic 6.0 Óë SQL Server µÄÁ½ÖÖÁ¬½Ó
VBͨ¹ýADOÁ¬½ÓSQL ServerÊý¾Ý¿â¿ÉÒÔ·ÖΪÓÐÔ´Êý¾Ý¿âÁ¬½ÓºÍÎÞÔ´Êý¾Ý¿âÁ¬½Ó£¬Çø±ðËüÃÇ¿´ÊÇ·ñʹÓÃÁËDSN£¨Êý¾ÝÔ´Ãû³Æ£©À´¾ö¶¨¡£
1 ÎÞÔ´Êý¾Ý¿âÁ¬½Ó
Æäʵ£¬“ÎÞÔ´Êý¾Ý¿â”Á¬½ÓÒ²²¢²»ÊÇ˵²»ÐèÒªÊý¾ÝÔ´£¬Èç¹ûûÓÐÊý¾ÝÔ´ÔòÁ¬½ÓÊÇÖ½ÉÏ̸±ø£¬Ö»ÊDz»ÐèÒªÅäÖÃODBCÊý¾ÝÔ´£¬ÀûÓÃADO¾Í¿Éͨ¹ý´úÂë½øÐÐÁ¬½Ó¡£
1.1 ADO(ActiveX Data Object)
ÔÚVisual Basic6.0ÖУ¬Êý¾Ý·ÃÎʽӿÚÓÐÈýÖÖ£ºActiveXÊý¾Ý¶ÔÏó£¨ADO£©¡¢Ô¶³ÌÊý¾Ý¶ÔÏó£¨RDO£©ºÍÊý¾Ý·ÃÎʶÔÏó£¨DAO£©¡£ÕâÈýÖÖ½Ó¿ÚµÄÿһÖÖ¶¼·Ö±ð´ú±í¸Ã¼¼ÊõµÄ²»Í¬·¢Õ¹½×¶Î£¬×îеÄÊÇADO£¬Ëü±ÈRDOºÍDAO¸ü¼Ó¼òµ¥£¬¸ü¼ÓÁé»î¡£¶ÔÓÚй¤³Ì£¬Ó¦Ê¹ÓÃADO×÷ΪÊý¾Ý·ÃÎʽӿڡ£
ADO ÊÇMicrosoft Êý¾Ý¿âÓ¦ÓóÌÐò¿ª·¢µÄ½Ó¿Ú£¬Êǽ¨Á¢ÔÚOLE DBÖ®Éϵĸ߲ãÊý¾Ý¿â·ÃÎʼ¼Êõ¡£Ëü·â×°ÁËOLE DBËùÌṩµÄ½Ó¿Ú£¬±ÈÆðOLE DBÌṩÕߣ¬ADOµÄ½Ó¿Ú¿ÉÒÔʹ³ÌÐòÔ±ÔÚ¸ü¸ß¼¶±ðÉϽøÐÐÊý¾Ý½»»¥¡£ADO¼¼Êõ²»½ö¿ÉÒÔÓ¦ÓÃÓÚ¹ØϵÊý¾Ý¿â£¬Ò²¿ÉÒÔÓ¦ÓÃÓڷǹØϵÊý¾Ý¿â¡£¿ÉÒÔÓÃͳһµÄ·½·¨¶Ô²»Í¬µÄÎļþϵͳ½øÐзÃÎÊ£¬´ó´ó¼ò»¯Á˳ÌÐò±àÖÆ£¬Ôö¼ÓÁ˳ÌÐòµÄ¿ÉÒÆÖ²ÐÔ¡£
1.2 ½¨Á¢ÎÞÔ´Êý¾Ý¿âÁ¬½Ó
ÔÚÁ¬½ÓÊý¾Ý¿âÇ°£¬Ê×ÏÈÒªÔÚVisual Basic 6.0²Ëµ¥ÖГ¹¤³Ì”—“ÒýÓÔÖÐÑ¡ÔñMicrosoft ActiveX Data Objects 2.6 Library ºÍMicrosoft ActiveX Data Objects Recordset 2.6 LibraryÕâÁ½¸ö×é¼þ¡£
ÔËÓÃADO¶ÔÏóÄ£Ð͵ÄÖ÷ÒªÔªËØ£ºConnection£¨Á¬½Ó£©ÖеÄConnectionStringÊôÐÔ½øÐÐÁ¬½Ó, ConnectionStringΪ¿É¶ÁдstringÀàÐÍ£¬Ö¸¶¨Ò»¸öÁ¬½Ó×Ö·û´®£¬¸æËßADO ÈçºÎÁ¬½ÓÊý¾Ý¿â¡£
ʵÀý£ºÒÔ½¨Á¢µÇ½ϵͳΪÀý£¬½éÉÜÎÞÔ´Êý¾Ý¿âÁ¬½Ó¡£ÔÚMicrosoft SQL Server 2000 Öн¨Á¢Ò»¸öStudent µÄÊý¾Ý¿â£¬ÔÚStudentÊý¾Ý¿âÖн¨Á¢login_user(name,password)µÄÊý¾Ý±í£¬Microsoft SQL Server·þÎñÆ÷µÄÃûΪdata_server¡£´úÂëÈçÏ£º
Option Explicit
Public LoginSucceeded As Boolean
'ÒýÓÃMicrosoft ActiveX Data Objects 2.6 Library
'¶¨ÒåÁ¬½Ó¶ÔÏóºÍ¼Ç¼¼¯
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
'¶ÔÈ¡Ïû°´Å¥µ¥»÷ʼþµÄÏìÓ¦
Private Sub cmdCancel_Click()
'ÉèÖÃÈ«¾Ö±äÁ¿Îª false
'²»Ìáʾʧ°ÜµÄµÇ¼
LoginSucceeded = False
Me.Hide
End Sub
'¶ÔµÇ¼°´Å¥µ¥»÷ʼþµÄÏìÓ¦
Private Sub cmdOK_Click()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'½¨Á¢ÎÞÔ´Êý¾Ý¿âÁ¬½Ó
conn.ConnectionString="driver={sql server};serve
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£ unionÖ»Êǽ«Á½¸ö½á¹ ......
SQL LEFT JOIN ¹Ø¼ü×Ö
SQL LEFT JOIN ¹Ø¼ü×Ö
LEFT JOIN ¹Ø¼ü×Ö»á´Ó×ó±í (table_name1) ÄÇÀï·µ»ØËùÓеÄÐУ¬¼´Ê¹ÔÚÓÒ±í (table_name2) ÖÐûÓÐÆ¥ÅäµÄÐС£
LEFT JOIN ¹Ø¼ü×ÖÓï·¨
SELECT column_name(s)
from table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
×¢ÊÍ£ºÔÚÄ ......
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)
¡¡¡¡7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣠......
×î½üÔÚ×ömysqlµÄÐÔÄÜÓÇ»¯£¬×öµ½¶à±íÁ¬½Ó²éѯ£¬±È½ÏÍ·ÌÛ£¬¿´ÁËһЩjoinµÄ×ÊÁÏ£¬ÖÕÓڸ㶨£¬ÕâÀï·ÖÏí³öÀ´£¡
ÍâÁª½Ó¡£ÍâÁª½Ó¿ÉÒÔÊÇ×óÏòÍâÁª½Ó¡¢ÓÒÏòÍâÁª½Ó»òÍêÕûÍⲿÁª½Ó¡£
ÔÚ from ×Ó¾äÖÐÖ¸¶¨ÍâÁª½Óʱ£¬¿ÉÒÔÓÉÏÂÁм¸×é¹Ø¼ü×ÖÖеÄÒ»×éÖ¸¶¨£º
&n ......