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±íʾ½µÐòÅÅÁÐ
......
--SQL ²é¿´±¾ÖܵÄÐÇÆÚÒ»ºÍÐÇÆÚÌì
/*
ϵͳµÄʱ¼äĬÈÏ°ÑÐÇÆÚÌì×÷Ϊһ¸öÐÇÆڵĵÚÒ»Ì죬µ«ÊÇÎÒÃÇÖйúÏ°¹ß°ÑÐÇÆÚÒ»×÷Ϊһ¸öÐÇÆڵĵÚÒ»Ì죬Òò´ËÈç¹ûµ±ÌìÊÇÐÇÆÚÌìʱÐèÒªÅжÏÒ»ÏÂ
*/
DECLARE @MondayTime varchar(10)
DECLARE @SundayTime varchar(10)
IF(datepart(weekday,getdate())-2 < 0)
BEGIN
SET @MondayTi ......
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£ unionÖ»Êǽ«Á½¸ö½á¹ ......
SQL JOIN
SQL join ÓÃÓÚ¸ù¾ÝÁ½¸ö»ò¶à¸ö±íÖеÄÁÐÖ®¼äµÄ¹Øϵ£¬´ÓÕâЩ±íÖвéѯÊý¾Ý¡£
Join ºÍ Key
ÓÐʱΪÁ˵õ½ÍêÕûµÄ½á¹û£¬ÎÒÃÇÐèÒª´ÓÁ½¸ö»ò¸ü¶àµÄ±íÖлñÈ¡½á¹û¡£ÎÒÃǾÍÐèÒªÖ´ÐÐ join¡£
Êý¾Ý¿âÖеıí¿Éͨ¹ý¼ü½«±Ë´ËÁªÏµÆðÀ´¡£Ö÷¼ü£¨Primary Key£©ÊÇÒ»¸öÁУ¬ÔÚÕâ¸öÁÐÖеÄÿһÐеÄÖµ¶¼ÊÇΨһµÄ¡£ÔÚ±íÖУ¬Ã¿¸öÖ÷¼üµÄ ......
´æ´¢¹ý³Ì
Óŵ㣺
1.ÓÉÓÚÓ¦ÓóÌÐòËæ×Åʱ¼äÍÆÒƻ᲻¶Ï¸ü¸Ä£¬Ôöɾ¹¦ÄÜ£¬T£SQL¹ý³Ì´úÂë»á±äµÃ¸ü¸´ÔÓ£¬StoredProcedureΪ·â×°´Ë´úÂëÌṩÁËÒ»¸öÌ滻λÖá£
2.Ö´Ðмƻ®£¨´æ´¢¹ý³ÌÔÚÊ×´ÎÔËÐÐʱ½«±»±àÒ룬Õ⽫²úÉúÒ»¸öÖ´Ðмƻ®££ ʵ¼ÊÉÏÊÇ Microsoft SQL ServerΪÔÚ´æ´¢¹ý³ÌÖлñÈ¡ÓÉ T-SQL Ö¸¶¨µÄ½á¹û¶ø±ØÐë²ÉÈ¡µÄ²½ÖèµÄ¼Ç¼ ......