intBeginMonth = Month(beginSendTime) '起始时间月
intEndMonth = Month(endSendtime) '结束时间月
intYear=Year(beginSendTime) '既然没有跨年那么取一个日期的年份就可以
sql=""
For i = intBeginMonth To intEndMonth '将表合并为一个表
sql = sql&"select * from [table"&CStr(intYear)&CStr(i)&"]"
If i<>intEndMonth Then
sql = sql & " union all " '当为最后一条的时候不加上union all
End If
Next
Response.write sql&"<br>"
'将合并后的sql作为一个大表加上where
strSql = "select * from ("&sql&")a where a.sendtime>='"&beginSendTime&"' and a.sendtime<='"&endSendtime&"' "
Response.write strSql
For i = intBeginMonth To intEndMonth'将表合并为一个表
sql = "select * from [table"&CStr(intYear)&CStr(intBeginMonth)&"]"
If intBeginMonth<>intEndMonth Then
sql = sql & " union all "'当为最后一条的时候不加上union all
End If
Next
'将合并后的sql作为一个大表加上where
strSql = "select * from ("&sql&")a where a.sendtime>='"&beginSendTime&"' and a.sendtime<='"&endSendtime&"' "
rs.open strSql,conn,1,1
-------------------------------------------------------------------------------
我还是决定用你提供的上面这种方法。。。
正在调试中。。
If intBeginMonth<>intEndMonth Then
sql = sql & " union all "'当为最后一条的时候不加上union all
End If
sql = sql & " union all "...这句,怎么没法把所有的SQL追加成一个大的sqkl表呢。。
对的,是由于数据量大的原因所以做了分表处理。。。。
---------------------------------
看到了这个我上边的示例恐怕不能满足你。你看看这个吧
---------------------------------------------
1、最简单的如下
Dim objConn
Set objConn = Server.CreateObject("ADOBD.Connection")
objConn.Open Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objConn.Execute "exec sp_AddHit"
没有参数,没有返回,没有错误处理,就是这个了
3、返回记录的
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADOBD.Connection")
Set objRs = Server.CreateObject("ADOBD.Recordset")
objConn.Open Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objRs.Open objConn, "exec sp_ListArticles '1/15/2001'"
'Loop through recordset and display each article
4、使用Command对象操作
Dim objConn
Dim objCmd
'Instantiate objects
Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
conn.Open Application("ConnectionString")
With objCmd
.ActiveConnection = conn 'You can also just specify a connection string here
.CommandText = "sp_InsertArticle"
.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'objCmd.CommandType = 2 为视图
'objCmd.CommandType = 4 为存储过程
'Execute the function
'If not returning a recordset, use the adExecuteNoRecords parameter option
.Execute, , adExecuteNoRecords
link_id = .Parameters("@link_id")
End With
5、存储过程的代码
Create PROCEDURE dbo.sp_InsertArticle
(
@columnist_id int,
@url varchar(255),
@title varchar(99),
@description text
@link_id int OUTPUT
)
AS
BEGIN
INSERT INTO dbo.t_link (columnist_id,url,title,description)
VALUES (@columnist_id,@url,@title,@description)
SELECT @link_id = @@IDENTITY
END
6.调用视图
<%
Set Dataconn = Server.CreateObject("ADODB.Connection") '建立连接对象
Dataconn.Open "DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft (R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
Set cmdTemp = Server.CreateObject("ADODB.Command") '建立命令对象
Set rst= Server.CreateObject("ADODB.Recordset") '建立记录集对象
cmdTemp.CommandText = "Customers "
cmdTemp.CommandType = 2
Set cmdTemp.ActiveConnection = DataConn
rst.Open cmdTemp, , 1, 3 '生成查询结果
%>
7.使用事务处理
①.Asp内嵌的事务支持
例子:
use_transaction_1.asp
<%
'Asp中使用事务
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "course_dsn","course_user","course_password"
conn.begintrans '开始事务
sql="delete from user_info"
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3
if conn.errors.count>0 then '有错误发生
conn.rollbacktrans '回滚
set rs=nothing
conn.close
set conn=nothing
response.write "交易失败,回滚至修改前的状态!"
response.end
else
conn.committrans '提交事务
set rs=nothing
conn.close
set conn=nothing
response.write "交易成功!"
response.end
end if
%>
②.数据库级的事务
i.创建存储过程
CREATE PROCEDURE [user_info_2]
(@user_name varchar(40),@password varchar(20))
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
delete from user_info where user_name=@user_name and password=@password
COMMIT TRANSACTION
SET XACT_ABORT OFF
ii.在Asp中调用
use_transaction_2.asp
<%
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "course_dsn","course_user","course_password"
For i = intBeginMonth To intEndMonth '将表合并为一个表
sql = "select * from [table"&CStr(intYear)&CStr(intBeginMonth)&"]"
If intBeginMonth<>intEndMonth Then
sql = sql & " union all " '当为最后一条的时候不加上union all
End If
Next
'将合并后的sql作为一个大表加上where
strSql = "select * from ("&sql&")a where a.sendtime>='"&beginSendTime&"' and a.sendtime<='"&endSendtime&"' "
rs.open strSql,conn,1,1