大家看看这段关于游标的错在哪里

create procedure [save]
as
declare @who varchar(20)
declare @word varchar(1000)
declare @s varchar(100)
declare @roomid int(4)
declare @thetime datetimedeclare   abc  cursor  for
select  distinct(who)  from  chttmp_1
open   abc
fetch next from  abc  into @who
    WHILE @@FETCH_STATUS = 0
    begin
      declare   ab  cursor  for select roomid,who+'对'+towho+content as content1 from chttmp_1 where (who=@who or towho=@who)
      open ab
      fetch next from ab into @roomid,@s
      while @@FETCH_STATUS = 0
      begin
         set @word = @word + @s
fetch next from ab into @s
      end
      close ab
      deallocate ab
     insert into chat_record(roomid,who,content,thetime) values(@roomid,@who,@word,@thetime)
     fetch next from  abc  into @who
   End
      close abc
      deallocate abc

我把它改成这样的了
CREATE PROCEDURE save1
AS
declare @who varchar(20)
declare @word varchar(1000)
declare @s varchar(100)
declare @roomid intdeclare   abc  cursor  for
select  distinct(who)  from  chttmp_1
open   abc
fetch next from  abc  into @who
    WHILE @@FETCH_STATUS = 0
    begin
      declare   ab  cursor  for select roomid,who+towho+content as content1 from chttmp_1 where (who=@who or towho=@who)
      open ab
      fetch next from ab into @roomid,@s
      while @@FETCH_STATUS = 0
      begin
         set  @word = @word + @s
fetch next from ab into @s
      end
      close ab
      deallocate ab
     insert into chat_record(roomid,who,content) values(@roomid,@who,@word)
     fetch next from  abc  into @who
   End
      close abc
      deallocate abc
GO然后在asp中执行：conn.Execute("Exec save1")
结果返回
错误类型：
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cursorfetch: 在 INTO 列表中声明的变量数目必须与所选择的列数目匹配。
/209/save.asp, 第 4 行
并且数据库中content中没有数据！什么也没有插入

declare   ab  cursor  for select roomid,who+towho+content as content1 from chttmp_1 where (who=@who or towho=@who)
选择了两个字段
而 fetch next from  abc  into @who 只赋值给了一个字段
  应为fetch next from ab into @roomid,@s

1.
create procedure [save]
as
declare @who varchar(20)
declare @word varchar(1000)
declare @s varchar(100)
declare @roomid int(4)
declare @thetime datetimeSET NOCOUNT ON --ADD THIS STATEMENTS2.THIS MAY BE YOU asp script problem.pls give you asp code about call this sp .

我的asp代码
<%
opendatabase conn
conn.Execute("Exec save1")
Closedatabase conn
%>

Sub OpenDataBase (connection)
dim dblocation
dim strconn
strconn = "driver={SQL Server};server=192.168.0.45;database=invest;uid=qdinfo;pwd=qdinfotech"
Set connection = Server.CreateObject("ADODB.Connection")
on error resume next
connection.open strConn
If connection.errors.count> 1 then
Session("Openerror")="打开数据数出现错误<br>" & connection.errors(0).description & " <br>" & Session("dbc")
else
Session("Openerror")=""
end if
End Sub

I see you code,I find the way you asp call the MSSQL sp is incorrect!
you can see below and try again
and
also you can see:
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=133244Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordsetcn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.OpenCmd.ActiveConnection = cn
Cmd.CommandText = "sp_who"
Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute
Debug.Print rs(0)
rs.Close

将存储过程改成下面那样后，一切就没有问题了，但是就是有一个字段写不进去就是
： insert into chat_record(roomid,who,content) values(@roomid,@who,@word)
content字段总是为null,请高手帮着看看
declare @s varchar(100)
declare @roomid intdeclare   abc  cursor  for
select  distinct(who)  from  chttmp_1
open   abc
fetch next from  abc  into @who
    WHILE @@FETCH_STATUS = 0
    begin
      declare   ab  cursor  for select roomid,who+towho+content as content1 from chttmp_1 where (who=@who or towho=@who)
      open ab
      fetch next from ab into @roomid,@s
      while @@FETCH_STATUS = 0
      begin
         set  @word = @word + @s
set @s = " "
fetch next from ab into @roomid, @s
      end
      close ab
      deallocate ab
     insert into chat_record(roomid,who,content) values(@roomid,@who,@word)     fetch next from  abc  into @who
   End      close abc
      deallocate abc
GO

declare @s varchar(100)
declare @roomid intdeclare   abc  cursor  for
select  distinct(who)  from  chttmp_1
open   abc
fetch next from  abc  into @who
    WHILE @@FETCH_STATUS = 0
    begin
      declare   ab  cursor  for select roomid,who+towho+content as content1 from chttmp_1 where (who=@who or towho=@who)
      open ab
      fetch next from ab into @roomid,@s
      while @@FETCH_STATUS = 0
      begin
         set  @word = isnull(@word,'') + isnull(@s,'')
set @s = ' '
fetch next from ab into @roomid, @s
      end
      close ab
      deallocate ab
     insert into chat_record(roomid,who,content) values(@roomid,@who,@word)     fetch next from  abc  into @who
   End      close abc
      deallocate abc
GO

楼主,你可以在SQLSERVER里先DEBUG一下,看看@work的值是否为空,也可以加一个DEBUG语句,比如:
declare @debug int
selet @debug=0fetch next from ab into @roomid,@s
      while @@FETCH_STATUS = 0
      begin
         selsct  @word = @word+@s
         if @word is null and @debug=1
            begin
                  print '@word is null'
                  return -1
             end
          fetch next from ab into @roomid, @s
      end
      close ab
      deallocate ab

请按大力的修改即可！
加上ISNULL（）函数。

set @word = isnull(@word,'') + isnull(@s,'')
set @s = ' '

调试易

大家看看这段关于游标的错在哪里

解决方案 »