////Command can't receive Recordset,OutputParameters,ReturnParameter
from a Stored Procedure at the same time ,Then you can modi the stored procedure to: CREATE PROCEDURE AuthorTitleByRoyalty @percentage int
AS
select a.au_lname, a.au_fname, t.title
from authors a
, titleauthor ta
, titles t
,count(*) numrows
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and ta.royaltyper = @percentage
order by a.au_lname, a.au_fname//////In vb you can modi to :
Set cmd = New Command
cmd.ActiveConnection = mConn
cmd.CommandText = "AuthorTitleByRoyalty"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("percentage", adInteger _
, adParamInput, , Val(lstRoyalty.Text))
cmd.Parameters.Append param
Debug.Print
listWorks.ListItems.Clear
Set rs = cmd.Execute
Do Until rs.EOF
Set NewItem = listWorks.ListItems.Add(, , rs("au_lname") _
& "," & rs("au_fname"))
NewItem.SubItems(1) = rs("Title")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set NewItem = Nothing
////if you have some questions ,please contact me here
from a Stored Procedure at the same time ,Then you can modi the stored procedure to: CREATE PROCEDURE AuthorTitleByRoyalty @percentage int
AS
select a.au_lname, a.au_fname, t.title
from authors a
, titleauthor ta
, titles t
,count(*) numrows
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and ta.royaltyper = @percentage
order by a.au_lname, a.au_fname//////In vb you can modi to :
Set cmd = New Command
cmd.ActiveConnection = mConn
cmd.CommandText = "AuthorTitleByRoyalty"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("percentage", adInteger _
, adParamInput, , Val(lstRoyalty.Text))
cmd.Parameters.Append param
Debug.Print
listWorks.ListItems.Clear
Set rs = cmd.Execute
Do Until rs.EOF
Set NewItem = listWorks.ListItems.Add(, , rs("au_lname") _
& "," & rs("au_fname"))
NewItem.SubItems(1) = rs("Title")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set NewItem = Nothing
////if you have some questions ,please contact me here
具体就是这一句
NewItem.SubItems(1) = rs("Title")
但存储过程里明显有
select a.au_lname, a.au_fname, t.title
from authors a
, titleauthor ta
, titles t
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and ta.royaltyper = @percentage
order by a.au_lname, a.au_fname
at the same time
rs("Title")出现属性无效的错误,because there is no Recordset at all
修改你的SP為如下:
CREATE PROCEDURE AuthorTitleByRoyalty @percentage int, @numrows int OUTPUT
AS
SET NOCOUNT ON
select a.au_lname, a.au_fname, t.title
from authors a
, titleauthor ta
, titles t
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and ta.royaltyper = @percentage
order by a.au_lname, a.au_fname SELECT @numrows = @@ROWCOUNT if @numrows = 0
return 0
else return 1
SET NOCOUNT OFF
GO
--保証調試OK.
加了﹐listWork.Add (,,"Name")
listWork.Add (,,"Text")
就OK了。 你 再試試。