[改造DVBBS83]升级你的7.1SQL版到8.3SQL版

mccad

动网的7.1版开放了SQL版本供大家免费使用，但现在已经是8.3版本了，怎么才能也用8.3版本的SQL版。由于8.3版本比较新，D版肯定是找不到的，但D版的8.2版是找得到的，所以大家可以升级到8.3版的SQL后，用8.2版的储存过程，再经过以下改造就可以用了。1.dispbbs.asp改成：

2. If IsSqlDataBase=1 And IsBuss=1 And Skin=0 Then
3. 'mccad edit 将8.3存储方式改成8.2，因为8.3SQL版还没有D版出现  
4.   Set Cmd = Dvbbs.iCreateObject("ADODB.Command")
5.   Set Cmd.ActiveConnection=conn
6.   Cmd.CommandText="dv_dispbbs"
7.   Cmd.CommandType=4
8.   Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)
9.   Cmd.Parameters.Append cmd.CreateParameter("@rootid",3)
10.   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)
11.   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)
12.   Cmd.Parameters.Append cmd.CreateParameter("@totalusetable",200,1,20)
13.   Cmd("@boardid")=Dvbbs.BoardID
14.   Cmd("@rootid")=ReplyID
15.   Cmd("@pagenow")=G_CurrentPage
16.   Cmd("@pagesize")=G_ItemsPerPage
17.   Cmd("@totalusetable")=TotalUsetable
18.   Set Rs=Cmd.Execute
19.   If Not Rs.EoF Then
20.    G_ItemList=Rs.GetRows(-1)
21.   Else
22.    Dvbbs.AddErrCode(29)
23.   End If
24.   Rs.close()
25.   Set Rs=Nothing
26.   Set Cmd =  Nothing
27. '  Dim mypage
28. '  Set mypage=new Pager
29. '  'If Not IsObject(Conn) Then ConnectionDatabase
30. '  mypage.getconn=conn '得到数据库连接
31. '  mypage.pagesize=G_ItemsPerPage '定义分页每一页的记录数
32. '  mypage.TableName=TotalUsetable '要查询的表名
33. '  mypage.Tablezd=sqlfields
34. '  mypage.KeyName="announceid"
35. '  mypage.OrderType=0
36. '  mypage.PageWhere=sqlfieldswhere
37. '  mypage.GetStyle =1
38. '  Set Rs=mypage.getrs()
39. '  If Not Rs.EoF Then
40. '   G_ItemList=Rs.GetRows(-1)
41. '  Else
42. '   Rs.close():Set Rs=Nothing:Dvbbs.AddErrCode(29)
43. '  End If
44. '  Rs.close():Set Rs=Nothing
45. '  'Set Cmd =  Nothing
46. Else

If IsSqlDataBase=1 And IsBuss=1 And Skin=0 Then 'mccad edit 将8.3存储方式改成8.2，因为8.3SQL版还没有D版出现     Set Cmd = Dvbbs.iCreateObject("ADODB.Command")   Set Cmd.ActiveConnection=conn   Cmd.CommandText="dv_dispbbs"   Cmd.CommandType=4   Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)   Cmd.Parameters.Append cmd.CreateParameter("@rootid",3)   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)   Cmd.Parameters.Append cmd.CreateParameter("@totalusetable",200,1,20)   Cmd("@boardid")=Dvbbs.BoardID   Cmd("@rootid")=ReplyID   Cmd("@pagenow")=G_CurrentPage   Cmd("@pagesize")=G_ItemsPerPage   Cmd("@totalusetable")=TotalUsetable   Set Rs=Cmd.Execute   If Not Rs.EoF Then    G_ItemList=Rs.GetRows(-1)   Else    Dvbbs.AddErrCode(29)   End If   Rs.close()   Set Rs=Nothing   Set Cmd =  Nothing '  Dim mypage '  Set mypage=new Pager '  'If Not IsObject(Conn) Then ConnectionDatabase '  mypage.getconn=conn '得到数据库连接 '  mypage.pagesize=G_ItemsPerPage '定义分页每一页的记录数 '  mypage.TableName=TotalUsetable '要查询的表名 '  mypage.Tablezd=sqlfields '  mypage.KeyName="announceid" '  mypage.OrderType=0 '  mypage.PageWhere=sqlfieldswhere '  mypage.GetStyle =1 '  Set Rs=mypage.getrs() '  If Not Rs.EoF Then '   G_ItemList=Rs.GetRows(-1) '  Else '   Rs.close():Set Rs=Nothing:Dvbbs.AddErrCode(29) '  End If '  Rs.close():Set Rs=Nothing '  'Set Cmd =  Nothing Else

2.index.asp改成：

2. If IsSqlDataBase=1 And IsBuss=1 Then
3. 'mccad edit 将8.3存储方式改成8.2，因为8.3SQL版还没有D版出现  
4.   Set Cmd = Dvbbs.iCreateObject("ADODB.Command")
5.   Set Cmd.ActiveConnection=conn
6.   Cmd.CommandText="dv_TopicList"
7.   Cmd.CommandType=4  
8.   Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)
9.   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)
10.   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)
11.   Cmd.Parameters.Append cmd.CreateParameter("@topicmode",3)
12.   Cmd.Parameters.Append cmd.CreateParameter("@inConditions",200,1,250)
13.   Cmd.Parameters.Append cmd.CreateParameter("@inOrder",3)
14.   Cmd.Parameters.Append cmd.CreateParameter("@inSort",3)
15.   Cmd.Parameters.Append cmd.CreateParameter("@totalrec",3,2,4)
16.   
17.   Cmd("@boardid")=Dvbbs.BoardID
18.   Cmd("@pagenow")=Page
19.   Cmd("@pagesize")=Cint(Dvbbs.Board_Setting(26))
20.   Cmd("@topicmode")=TopicMode
21.   Cmd("@inConditions")=SQLQuery
22.   Cmd("@inOrder")=OrderId
23.   Cmd("@inSort")=SortId
25.   Set Rs=Cmd.Execute
26.   If Not Rs.EoF Then
27.    Topic=Rs.GetRows(-1)
28.   Else
29.    Topic=Null
30.   End If
31.   Rs.close()
32.   Set Rs=Nothing
33.   Count = Cmd("@totalrec")
34.   Set Cmd =  Nothing
35.   'Dim mypage
36.   'Set mypage=new Pager
37.   'mypage.getconn=conn '得到数据库连接
38.   'mypage.pagesize=Cint(Dvbbs.Board_Setting(26)) '定义分页每一页的记录数
39.   'mypage.TableName="Dv_Topic" '要查询的表名
40.   'mypage.Tablezd=sqlfields
41.   'mypage.KeyName=OrderField
42.   'mypage.OrderType=OrderType
43.   'mypage.PageWhere=sqlfieldswhere
44.   'mypage.GetStyle =1
45.   'Set Rs=mypage.getrs()
46.   'If Not (Rs.EoF And Rs.Bof) Then
47.   ' Topic=Rs.GetRows(-1)
48.   'Else
49.   ' Topic=Null
50.   'End If
51.   'Count = mypage.int_totalRecord
52.   'Rs.close()
53.   'Set Rs=Nothing
54. Else

If IsSqlDataBase=1 And IsBuss=1 Then 'mccad edit 将8.3存储方式改成8.2，因为8.3SQL版还没有D版出现     Set Cmd = Dvbbs.iCreateObject("ADODB.Command")   Set Cmd.ActiveConnection=conn   Cmd.CommandText="dv_TopicList"   Cmd.CommandType=4     Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)   Cmd.Parameters.Append cmd.CreateParameter("@topicmode",3)   Cmd.Parameters.Append cmd.CreateParameter("@inConditions",200,1,250)   Cmd.Parameters.Append cmd.CreateParameter("@inOrder",3)   Cmd.Parameters.Append cmd.CreateParameter("@inSort",3)   Cmd.Parameters.Append cmd.CreateParameter("@totalrec",3,2,4)      Cmd("@boardid")=Dvbbs.BoardID   Cmd("@pagenow")=Page   Cmd("@pagesize")=Cint(Dvbbs.Board_Setting(26))   Cmd("@topicmode")=TopicMode   Cmd("@inConditions")=SQLQuery   Cmd("@inOrder")=OrderId   Cmd("@inSort")=SortId   Set Rs=Cmd.Execute   If Not Rs.EoF Then    Topic=Rs.GetRows(-1)   Else    Topic=Null   End If   Rs.close()   Set Rs=Nothing   Count = Cmd("@totalrec")   Set Cmd =  Nothing   'Dim mypage   'Set mypage=new Pager   'mypage.getconn=conn '得到数据库连接   'mypage.pagesize=Cint(Dvbbs.Board_Setting(26)) '定义分页每一页的记录数   'mypage.TableName="Dv_Topic" '要查询的表名   'mypage.Tablezd=sqlfields   'mypage.KeyName=OrderField   'mypage.OrderType=OrderType   'mypage.PageWhere=sqlfieldswhere   'mypage.GetStyle =1   'Set Rs=mypage.getrs()   'If Not (Rs.EoF And Rs.Bof) Then   ' Topic=Rs.GetRows(-1)   'Else   ' Topic=Null   'End If   'Count = mypage.int_totalRecord   'Rs.close()   'Set Rs=Nothing Else

3.dv_dispbbs 储存过程:

1. CREATE PROCEDURE [dv_Dispbbs]
2. @boardid int=0,
3. @rootid int=0,
4. @pagenow int=1,     
5. @pagesize int=1,
6. @totalusetable nvarchar(20)='dv_bbs1'
7. AS
8. set nocount on
9. declare @int_top int
10. declare @int_begin int
11. declare @str_sql nvarchar(500)
13. if @pagenow>1
14. begin
15.   select @int_top=(@pagenow-1)*@pagesize
16.   SET @str_sql ='SELECT @int_begin = Max(announceid ) FROM (Select Top '+str(@int_top)+'  announceid From '+@totalusetable+'  where  RootID='+str(@rootid)+' and Boardid='+str(@boardid)+' Order By Announceid ) as t'
17.   
18.   exec sp_executesql @str_sql,N'@int_begin int output ',@int_begin output
20.   set @str_sql='select AnnounceID,UserName,Topic,dateandtime,body,Expression,ip,RootID,signflag,isbest,PostUserid,layer,isagree,GetMoneyType,IsUpload,Ubblist,LockTopic,GetMoney,UseTools,PostBuyUser,ParentID,FlashId From '+@totalusetable+' where RootID='+str(@rootid)+' and announceid>'+str(@int_begin)+'  and Boardid='+str(@boardid)+' Order By Announceid'
21.   set rowcount @pagesize
22.   exec sp_executesql @str_sql
23.   set nocount off
24.   return
25. end
26. else
27. begin
28. set rowcount @pagesize
29. set @str_sql='Select AnnounceID,UserName,Topic,dateandtime,body,Expression,ip,RootID,signflag,isbest,PostUserid,layer,isagree,GetMoneyType,IsUpload,Ubblist,LockTopic,GetMoney,UseTools,PostBuyUser,ParentID,FlashId From '+@totalusetable+' where  RootID='+str(@rootid)+' and Boardid='+str(@boardid)+' Order By Announceid'
30. exec sp_executesql @str_sql
31. return
32. end
33. GO

复制代码

4.dv_TopicList储存过程：CREATE PROCEDURE [dv_TopicList]
@boardid int=1,
@pagenow int=1, --当前页数            
@pagesize int=1, --定义每页面帖子数目
@topicmode int=0, --专题
@inConditions varchar(250)=' ',
@inOrder int=0,
@inSort int=0,
@totalrec int output --SET @TotalRec=@@ROWCOUNT
AS
SET nocount on
DECLARE @int_topnum int
DECLARE @int_timenum int
DECLARE @var_times varchar(5000)
DECLARE @OrderField varchar(20)
DECLARE @SortStr varchar(5)
DECLARE @strSQL nvarchar(4000)
DECLARE @Compare varchar(1)
DECLARE @nRet int
Declare @Compare1 nvarchar(20)
IF @inOrder=0
SET @OrderField='LastPostTime'
ELSE IF @inOrder=1
SET @OrderField='TopicId'
ELSE IF @inOrder=2
SET @OrderField='hits'
ELSE IF @inOrder=3
SET @OrderField='child'
ELSE
SET @OrderField='LastPostTime'

IF @inSort=0
BEGIN
SET @SortStr='DESC'
SET @Compare = '<'
Set @Compare1='Min'
END
ELSE
BEGIN
SET @SortStr='ASC'
SET @Compare = '>'
Set @Compare1='Max'
END


IF @pagenow>1
IF @topicmode>0
  BEGIN
   SELECT @int_timenum=(@pagenow-1) * @pagesize
   --SET ROWCOUNT @int_timenum
   SET @strSQL='SELECT @var_times ='+@Compare1+'(' + @OrderField  + ')  FROM (Select Top '+str(@int_timenum)+' ' + @OrderField + ' From Dv_Topic WHERE mode=@3 And boardID=@2 AND istop = 0 ' + @inConditions + 'ORDER BY ' + @OrderField + ' ' + @SortStr +') as t'  
   EXEC sp_executesql @strSQL,N'@var_times varchar(5000) output,@2 int,@3 int',@var_times output,@2=@boardID,@3=@topicmode

   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM dv_topic WHERE mode=@3 AND boardID=@2 AND istop = 0 AND ' + @OrderField + @Compare + ' @1 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@1 varchar(5000),@2 int,@3 int',@1=@var_times,@2=@boardID,@3=@topicmode

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE mode=@3 AND boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output , @2 int,@3 int',@nRet output ,@2=@boardID,@3=@topicmode
   SELECT @totalrec=@nRet

   SET nocount OFF
   RETURN
  END
ELSE --@topicmode
  BEGIN
   SELECT @int_timenum=(@pagenow-1) * @pagesize
   --SET ROWCOUNT @int_timenum
   --SET @strSQL='SELECT @var_times=' + @OrderField + '  FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions +' ORDER BY ' + @OrderField + ' ' + @SortStr
   SET @strSQL='SELECT @var_times ='+@Compare1+'(' + @OrderField  + ')  FROM (Select Top '+str(@int_timenum)+' ' + @OrderField + ' From Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions +' ORDER BY ' + @OrderField + ' ' + @SortStr +') as t'  
   EXEC sp_executesql @strSQL,N'@var_times varchar(5000) output,@2 int',@var_times output,@2=@boardID

   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM dv_topic WHERE boardID=@2 AND istop = 0 AND ' + @OrderField + @Compare + ' @1 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@1 varchar(5000),@2 int',@1=@var_times,@2=@boardID

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output ,@2 int',@nRet output , @2=@boardID
   SELECT @totalrec=@nRet

   SET nocount OFF
   RETURN
  END
ELSE --pagenow
IF @topicmode>0
  BEGIN
   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM Dv_topic WHERE mode=@3 AND boardID=@2 AND istop =
0 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@2 int,@3 int',@2=@boardID,@3=@topicmode

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE mode=@3 And boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output,@2 int,@3 int',@nRet output,@2=@boardID,@3=@topicmode
   SELECT @totalrec=@nRet
  END
ELSE --topicmode
  BEGIN
   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM Dv_topic WHERE boardID=@2 AND istop=0 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@2 int',@2=@boardID

   SET @strSQL='SELECT @nRet=COUNT(TopicID) FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output,@2 int',@nRet output,@2=@boardID
   SELECT @totalrec=@nRet
  END

/****** 对象:  StoredProcedure [dv_disp]    脚本日期: 02/26/2008 15:59:30 ******/
SET ANSI_NULLS ON
GO

vbangle

官方来是8.3SQL牐优化了不少存储过程么，可能还有增加

boyhong

严重关注中，您在如下部分的重色部分，8.3版的SQL从哪儿来呢？可以下载到吗？：

动网的7.1版开放了SQL版本供大家免费使用，但现在已经是8.3版本了，怎么才能也用8.3版本的SQL版。

由于8.3版本比较新，D版肯定是找不到的，但D版的8.2版是找得到的，所以大家可以升级到8.3版的SQL后，用8.2版的储存过程，再经过以下改造就可以用了。

boyhong

在您的提示之下，今天下午我成功升级为8.2sql版，嘿嘿~~~无比感谢，，若能得到您的进一步指点升级为8。3那就完美了~~~

mccad

实际上SQL版与MSACCESS版是一样的程序，只是用的数据库不是而已。

anyc100

学习了~~

chaodna

风树

chaodna 发表于 2011-1-4 00:07

谢谢分享！        

pengfei2010

回帖是一种美德！感谢楼主的无私分享 谢谢