Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Excel VBA ʵÏÖSQLÊý¾Ý¶ÁÈ¡K3ÈËÔ±ÐÅÏ¢

Private Sub CommandButton1_Click()
Worksheets("Sheet2").Select
    Cells.Select
     Selection.Delete Shift:=xlUp
     Range("A1").Select
'Çå³ýÔÚExcelÖеÄÊý¾Ý,È·±£µ¼ÈëÐÅÏ¢²»³öÏÖÓëÔ­ExcelÊý¾Ý½øÐеþ¼Ó
Dim cnnConnect As Object
Dim rstRecordset As Object
    Columns("A:C").Select
    Range("A1").Activate
    Selection.Delete Shift:=xlToLeft
Set cnnConnect = CreateObject("ADODB.connection")
Set rstRecordset = CreateObject("ADODB.Recordset")
cnnConnect.Open "Provider=SQLOLEDB;" & _
    "Data Source=10.11.1.4;" & _
    "User ID=finWork;Password=finwork;"
rstRecordset.Open _
    Source:="Select code, name, email from hm_employees where status=1", _
    ActiveConnection:=cnnConnect
'½¨Á¢Êý¾Ý¿âÁ¬½Ó,SourceΪÊý¾Ý¿âÊý¾Ý·þÎñÆ÷IP,¼°Á¬½ÓÓû§ÃûÓëÃÜÂë,ÔÚʵÏÖʱȷ±£Óû§¶ÔK3Êý¾Ý¿âÓжÁȡȨÏÞ
With ActiveSheet.QueryTables.Add( _
        Connection:=rstRecordset, _
        Destination:=Range("A1"))
    .Name = "Contact List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With
'µ¼ÈëK3Êý¾Ýµ½ExcelÖÐ,
Range("A1").Value = "¹¤ºÅ"
Range("B1").Value = "ÐÕÃû"
Range("C1").Value = "Email"
    ActiveWorkbook.Worksheets("Sheet2").QueryTables


Ïà¹ØÎĵµ£º

SQLϰÌâ1

µÚÒ»Ì⣺
Ϊ¹ÜÀíÒµÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í£º
S(S#,SN,SD,SA)S#,SN,SD,SA·Ö±ð´ú±íѧºÅ£¬Ñ§Ô±ÐÕÃû£¬ËùÊôµ¥Î»£¬Ñ§Ô±ÄêÁä
C(C#,CN)C#,CN·Ö±ð´ú±í¿Î³Ì±àºÅ£¬¿Î³ÌÃû³Æ
SC(S#,C#,G) S#,C#,G·Ö±ð´ú±íѧºÅ£¬ËùÑ¡µÄ¿Î³Ì±àºÅ£¬Ñ§Ï°³É¼¨
(1)ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆÎª’˰ÊÕ»ù´¡’µÄѧԱѧºÅºÍÐÕÃû?
(2) ʹ ......

SqlÐÔÄÜÓÅ»¯

 
²Ù×÷·ûÓÅ»¯
IN ²Ù×÷·û
ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ×¶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵쬴ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£¬Èç¹ûת»»³ ......

SQLÊÖ¹¤×¢ÉäÔ­Àí

     ¹ØÓÚSQL×¢Èë(SQL Injection)µÄ·½·¨Æäʵ¶¼ºÜÆÕ±éºÍʹÓ㬹éÄÉÆðÀ´Ò²ºÜ·½±ã¡£Ò»°ã“ºÚ¿Í”ʹÓõÄÊÇÏֳɵŤ¾ßÈç“WEBÅÔ×¢¡¢°¢DÍøÂ繤¾ß°ü¡¢½ÌÖ÷XXX”µÈÕâЩ¶¼ÊǼ¯³ÉÁË
һЩ³£ÓõÄsql×¢ÈëÓï¾ä¡£ÏÂÃæÎÒ½«½éÉÜÈçºÎʹÓÃÊÖ¹¤×¢ÈëMYSQL,MSSQLÊý¾Ý¿â.
    Ò»°ã©¶´²ú ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ