sql相关.txt

较为详细的介绍了asp自定义的各种函数,方便asp的各种开发.

Set GetExcelRecordset = GetOtherRs( "Excel", FileName, SQL, 2 ) 
End Function 

'--------------------------------------------------- 
Function GetExcelStaticRecordset( FileName, SQL ) 
Set GetExcelStaticRecordset = GetOtherRs( "Excel", FileName, SQL, 3 ) 
End Function 

'--------------------------------------------------- 
Function GetTextRecordset( Directory, SQL ) 
Set GetTextRecordset = GetOtherRs( "Text", Directory, SQL, 2 ) 
End Function 

'--------------------------------------------------- 
Function GetTextStaticRecordset( Directory, SQL ) 
Set GetTextStaticRecordset = GetOtherRs( "Text", Directory, SQL, 3 ) 
End Function 

'--------------------------------------------------- 
Function GetSQLServerRecordset( conn, source ) 
Dim rs 

Set rs = Server.CreateObject("ADODB.Recordset") 
rs.Open source, conn, 2, 2 
Set GetSQLServerRecordset = rs 
End Function 

'--------------------------------------------------- 
Function GetSQLServerStaticRecordset( conn, source ) 
Dim rs 

Set rs = Server.CreateObject("ADODB.Recordset") 
rs.Open source, conn, 3, 2 
Set GetSQLServerStaticRecordset = rs 
End Function 

'--------------------------------------------------- 
Function GetConnection( Param ) 
Dim conn 

On Error Resume Next 
Set GetConnection = Nothing 
Set conn = Server.CreateObject("ADODB.Connection") 
If Err.Number <> 0 Then Exit Function 

  conn.Open Param 
  If Err.Number <> 0 Then Exit Function 
  Set GetConnection = conn 
End Function 

'--------------------------------------------------- 
Function GetMdbRs( FileName, Source, Cursor, Password ) 
  Dim conn, rs 

  On Error Resume Next 
  Set GetMdbRs = Nothing 
  If Len(Password) = 0 Then 
      Set conn = GetMdbConnection( FileName ) 
  Else 
      Set conn = GetSecuredMdbConnection( FileName, Password ) 
  End If 
  If conn Is Nothing Then Exit Function 

  Set rs = Server.CreateObject("ADODB.Recordset") 
  If Err.Number <> 0 Then Exit Function 

  rs.Open source, conn, Cursor, 2 
  If Err.Number <> 0 Then Exit Function 
  Set GetMdbRs = rs 
End Function 

'--------------------------------------------------- 
Function GetOtherRs( DataType, Path, SQL, Cursor ) 
  Dim conn, rs 
  On Error Resume Next 
  Set GetOtherRs = Nothing 

  Select Case DataType 
      Case "Dbf" 
        Set conn = GetDbfConnection( Path ) 
      Case "Dbc" 
        Set conn = GetDbcConnection( Path ) 
      Case "Excel" 
        Set conn = GetExcelConnection( Path ) 
      Case "Text" 
        Set conn = GetTextConnection( Path ) 
  End Select 
  If conn Is Nothing Then Exit Function 

  Set rs = Server.CreateObject("ADODB.Recordset") 
  If Err.Number <> 0 Then Exit Function 

  rs.Open SQL, conn, Cursor, 2 
  If Err.Number <> 0 Then Exit Function 
  Set GetOtherRs = rs 
End Function 

'--------------------------------------------------- 
Function GetSQLServerRs( Computer, UserID, Password, Db, source, Cursor ) 
  Dim conn, rs 

  On Error Resume Next 
  Set GetSQLServerRs = Nothing 
  Set conn = GetSQLServerConnection( Computer, UserID, Password, Db ) 
  If conn Is Nothing Then Exit Function 

  Set rs = Server.CreateObject("ADODB.Recordset") 
  If Err.Number <> 0 Then Exit Function 

  rs.Open source, conn, Cursor, 2 
  If Err.Number <> 0 Then Exit Function 
  Set GetSQLServerRs = rs 
End Function 
%> 
使用方法是——复制下来存成一个文件，然后用#Include “文件名”就可以调用里面的子程序了。 
有什么问题可以一起探讨！！！                                  

随机记录的取得，适用于sql数据库中
SELECT * FROM table ORDER BY NEWID()

[推荐]删除全部数据的最快捷的方法
TRUNCATE TABLE
删除表中的所有行，而不记录单个行删除操作。 

语法
TRUNCATE TABLE name

参数
name

是要截断的表的名称或要删除其全部行的表的名称。

注释
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。 

TRUNCATE TABLE 不能用于参与了索引视图的表。

示例
下例删除 authors 表中的所有数据。

TRUNCATE TABLE authors


袖珍数据库连接查询手册
下面将简单介绍一下几种ADO连接方式：ODBC DSN,ODBC DSN-Less, 
OLE DB Provider,和"MS Remote" Provider.
1。ODBC DSN连接
I.DSN
    oConn.Open "DSN=AdvWorks;" & _ 
        "UID=Admin;" & _ 
        "PWD=;"
注意：从MDAC2.1开始就不能够在使用这样的方式了，就是只把DSN文件名放在ConnectString中
      你必须同时使用DSN，UID，PWD标志。例如下面的方式在MDAC 2.1中将会出错：
    oConn.Open "AdvWorks"

II.File DSN
    oConn.Open "FILEDSN=\somepath\mydb.dsn;" & _ 
        "UID=Admin;" & _
        "PWD=;"
III.ODBC DSN-Less Connections
a)ODBC Text Driver
    oConn.Open _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=\somepath\;" & _
        "Extensions=asc,csv,tab,txt;" & _
        "Persist Security Info=False"
注意：需要在SQL语句中指定使用到的文件名。例如：
    oRs.Open "Select * From customer.csv", _
        oConn, adOpenStatic, adLockReadOnly, adCmdText
b)ODBC Driver for Access
    i)普通安全模式：
        oConn.Open _
            "Driver={Microsoft Access Driver (*.mdb)};" & _ 
            "Dbq=\somepath\mydb.mdb;" & _
            "Uid=Admin;" & _
            "Pwd=;"
    ii)如果使用了System database:
        oConn.Open _
            "Driver={Microsoft Access Driver (*.mdb)};" & _ 
            "Dbq=\somepath\mydb.mdb;" & _
            "SystemDB=\somepath\mydb.mdw;", _
            "admin", ""
c)ODBC Driver for SQL Server
    i)普通安全模式
        oConn.Open "Driver={SQL Server};" & _ 
            "Server=carl2;" & _
            "Database=pubs;" & _
            "Uid=sa;" & _
            "Pwd=;"
    ii)使用信任安全模式：
        oConn.Open "Driver={SQL Server};" & _ 
            "Server=carl2;" & _
            "Database=pubs;" & _
            "Uid=;" & _
            "Pwd=;"
    注意：要使用空白的Uid和Pwd           
d)ODBC Driver for Oracle
    i)使用现有的Oracle ODBC Driver from Microsoft:
        oConn.Open _
            "Driver={Microsoft ODBC for Oracle};" & _
            "Server=OracleServer.world;" & _
            "Uid=demo;" & _
            "Pwd=demo;"
    ii)使用老版本的Oracle ODBC Driver from Microsoft:
        oConn.Open _
            "Driver={Microsoft ODBC Driver for Oracle};" & _
            "ConnectString=OracleServer.world;" & _
            "Uid=demo;" & _
            "Pwd=demo;"
IIII)使用微软的OLE DB Data Link Connections方式Data Link File (UDL)
a)使用绝对路径
    oConn.Open "File Name=\somepath\pubs.udl;" 
b)使用相对路径
    oConn.Open "File Name=pubs.udl;" 
V)OLE DB Provider Connections方式
a)OLE DB Provider for ODBC Databases
    i)Access (Jet):
        oConn.Open _
            "Provider=MSDASQL;" & _ 
            "Driver={Microsoft Access Driver (*.mdb)};" & _
            "Dbq=\somepath\mydb.mdb;" & _
            "Uid=Admin;" & _
            "Pwd=;"
    ii)SQL Server:
        oConn.Open _
            "Provider=MSDASQL;" & _  
            "Driver={SQL Server};" & _
            "Server=carl2;" & _
            "Database=pubs;" & _
            "Uid=sa;" & _
            "Pwd=;"
b)OLE DB Provider for Microsoft Jet  (Access)
    i)普通安全模式：
        oConn.Open _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=\somepath\mydb.mdb;" & _ 
            "User Id=admin;" & _
            "Password=;"
    ii)如果使用了System database:
        oConn.Open _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=\somepath\mydb.mdb;" & _ 
            "Jet OLEDB:System Database=system.mdw;", _
            "admin", "" 
    注意：当使用OLE DB Provider4.0版本是，需要把MDB和MDW文件转换成4.0的数据库格式
    iii)如果MDB需要一个数据库密码的话：
        oConn.Open _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=\somepath\mydb.mdb;" & _ 
            "Jet OLEDB:Database Password=MyDbPassword;", _
            "admin", ""
c)OLE DB Provider for Excel Spreadsheet: 
        oConn.Open _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=\somepath\expenses.xls;" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";" 
    注意： "HDR=Yes"表示在第一行中是行标题，在provider中将不把第一行包括入recordset中
d)OLE DB Provider for SQL Server
    i)普通安全模式：
        oConn.Open "Provider=sqloledb;" & _ 
            "Network Library=DBMSSOCN;" & _
            "Data Source=carl2;" & _
            "Initial Catalog=pubs;" & _
            "User Id=sa;" & _
            "Password=;"
    ii)使用信任安全模式：
        oConn.Open "Provider=sqloledb;" & _
            Network Library=DBMSSOCN;" & _
            "Data Source=carl2;" & _
            "Initial Catalog=pubs;" & _
            "Trusted_Connection=yes;"
    注意："Network Library=DBMSSOCN"声明OLE DB使用TCP/IP替代Named Pipes.
e)OLE DB Provider for Oracle
    oConn.Open "Provider=msdaora;" & _
        "Data Source=OracleServer.world;" & _ 
        "User Id=sa;" & _
        "Password=;"
(VI)Remote OLE DB Provider Connections方式（就是我一直在研究的RDS方式哦，呵呵。）：
a)MS Remote - Access (Jet)
    i)ODBC DSN:
        oConn.Open "Remote Server=http://carl2;" & _ 
            "Remote Provider=MSDASQL;" & _
            "DSN=AdvWorks;" & _
            "Uid=admin" & _
            "Pwd=;" 
    ii)OLE DB Provider:
        oConn.Open "Provider=MS Remote;" & _ 
            "Remote Server=http://carl2;" & _
            "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=\somepath\mydb.mdb;", _
            "admin", ""
    iii)OLE DB Provider自定义事务对象
        oConn.Open "Provider=MS Remote;" & _ 
            "Remote Server=http://carl2;" & _
            "Handler=MSDFMAP.Handler;" & _
            "Data Source=MyAdvworksOLEDBConnectTag;"
b)MS Remote - SQL Server
    i)ODBC DSN:
        oConn.Open "Remote Server=http://carl2;" & _ 
            "Remote Provider=MSDASQL;" & _
            "Network Library=DBMSSOCN;" & _
            "DSN=Pubs;" & _
            "Uid=sa" & _
            "Pwd=;" 
    ii)OLE DB Provider
        oConn.Open "Provider=MS Remote;" & _ 
            "Remote Server=http://carl2;" & _
            "Remote Provider=SQLOLEDB;" & _
            "Network Library=DBMSSOCN;" & _
            "Data Source=carl2;" & _
            "Initial Catalog=pubs;" & _
            "User ID=sa;" & _
            "Password=;"