我用的是SQL7,VB6,ADO2.0,AD02.1,ADO2.7,我在SQL中写了个存储过程,存储过程的前面是DELETE,UPDATE,INSERT,CREATE等语句后面用SELECT来返回记录集,
但在客户端用VB6+ADO调用这个存储过程,返回的记录集竞然是NOTHING,我试过了很多方法,但死活不能将存储过程中SELECT语句的结果返回,
请问大家有什么办法可以帮小弟一把???
但在客户端用VB6+ADO调用这个存储过程,返回的记录集竞然是NOTHING,我试过了很多方法,但死活不能将存储过程中SELECT语句的结果返回,
请问大家有什么办法可以帮小弟一把???
Dim lcmwuqi As New ADODB.Command
Dim rs As New ADODB.RecordSet
On Error GoTo inerr
'If lcmwuqi Is Nothing Then
'Set lcmwuqi = New ADODB.command
With lcmwuqi
.ActiveConnection = conn
.CommandText = "p_NewStudentTest"
.CommandType = adCmdStoredProc
End With
'End If
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("学生ID", adInteger, adParamInput, 4, CInt(stuID))
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("考试科目", adVarChar, adParamInput, 30, tbTextName.Text)
lcmwuqi.Parameters.Append lcmwuqi.CreateParameter("成绩", adVarChar, adParamInput, 6, tbText.Text)
set rs = lcmwuqi.Execute
(
SNO CHAR(10) CONSTRAINT PK_SNO PRIMARY KEY NOT NULL,
SNAME NVARCHAR(10) NOT NULL,
SEX TINYINT DEFAULT(0) NULL
)
GO
INSERT INTO STUDENTS VALUES('2003000001', '2003000001', 0)
INSERT INTO STUDENTS VALUES('2003000002', '2003000002', 0)
INSERT INTO STUDENTS VALUES('2003000003', '2003000003', 0)
INSERT INTO STUDENTS VALUES('2003000004', '2003000004', 1)
INSERT INTO STUDENTS VALUES('2003000005', '2003000005', 1)
GO
CREATE PROCEDURE STU_PROC(
@SEX TINYINT = NULL,
@CNT INTEGER OUTPUT)
AS
SET NOCOUNT OFF
IF @SEX IS NULL
SELECT * FROM STUDENTS
ELSE
SELECT * FROM STUDENTS WHERE SEX = @SEX
SET @CNT = @@ROWCOUNT
SET NOCOUNT ON
GO
Private mCnn As ADODB.Connection
Private mCmd As ADODB.Command
Private mRst As ADODB.RecordsetPrivate Sub Command1_Click()
On Error GoTo ErrHandler
Dim prm As ADODB.Parameter
Set mCmd = New ADODB.Command
mCmd.ActiveConnection = mCnn
mCmd.CommandText = "STU_PROC"
mCmd.CommandType = adCmdStoredProc
Set prm = mCmd.CreateParameter("Sex", adTinyInt, adParamInput)
mCmd.Parameters.Append prm
Set prm = mCmd.CreateParameter("Cnt", adInteger, adParamOutput)
mCmd.Parameters.Append prm
Set mRst = mCmd.Execute
Debug.Print mRst(0).Name, mRst(1).Name
Do Until mRst.EOF
Debug.Print mRst(0).Value, mRst(1).Value
mRst.MoveNext
Loop
mRst.Close
Debug.Print mCmd.Parameters("Cnt").Value & " Row(s) affected"
Exit Sub
ErrHandler:
MsgBox Err.Description
End SubPrivate Sub Form_Load()
On Error GoTo ErrHandler
Set mCnn = New ADODB.Connection
mCnn.ConnectionString = _
"Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"Initial Catalog=CITIC_DB;" & _
"Data Source=(LOCAL)"
mCnn.Open UserID:="wgy", Password:="wgy"
Exit Sub
ErrHandler:
MsgBox Err.Description
End SubPrivate Sub Form_Unload(Cancel As Integer)
On Error Resume Next
mRst.Close
mCnn.Close
Set mCmd = Nothing
Set mRst = Nothing
Set mCnn = Nothing
End Sub
程序是这样:
CREATE PROCEDURE test
@dwbh CHAR(7),
@S1 CHAR(7) OUTPUT
AS
--给要返回值起个别名就可以了
Set @S1=MAX(ID)
select @s1 as s1
private sub refreshlist()
dim rs as recordset
rs.open "declare @s1 varchar(10) exec test,@s1 output("s1")
text1=rs("s")
end sub 或者
CREATE PROCEDURE test(@dwbh CHAR(7)) AS
--给要返回值起个别名就可以了
select dwbh as s1,dwsj as s2,dwzt as s3 from dwjbda where dwbh=@dwbh
下面是vb的过程
private sub refreshlist()
dim rs as recordset
set rs=mconn.execute("test",,adcmdstoredproc)
txt.text=rs("s1")
end sub
@SEX TINYINT = NULL,
@CNT INTEGER OUTPUT)
AS
SET NOCOUNT OFF
DELETE STUDENTS
INSERT INTO STUDENTS VALUES('2003000001', '2003000001', 0)
INSERT INTO STUDENTS VALUES('2003000002', '2003000002', 0)
INSERT INTO STUDENTS VALUES('2003000003', '2003000003', 0)
INSERT INTO STUDENTS VALUES('2003000004', '2003000004', 1)
INSERT INTO STUDENTS VALUES('2003000005', '2003000005', 1)
IF @SEX IS NULL
SELECT * FROM STUDENTS
ELSE
SELECT * FROM STUDENTS WHERE SEX = @SEX
SET @CNT = @@ROWCOUNT
SET NOCOUNT ON
是 SET NOCOUNTALTER PROCEDURE STU_PROC(
@SEX TINYINT = NULL,
@CNT INTEGER OUTPUT)
AS
SET NOCOUNT ON -- 原来是 SET NOCOUNT OFF(不好意思,写错了)
BEGIN TRANSACTION
DELETE STUDENTS
INSERT INTO STUDENTS VALUES('2003000001', '2003000001', 0)
INSERT INTO STUDENTS VALUES('2003000002', '2003000002', 0)
INSERT INTO STUDENTS VALUES('2003000003', '2003000003', 0)
INSERT INTO STUDENTS VALUES('2003000004', '2003000004', 1)
INSERT INTO STUDENTS VALUES('2003000005', '2003000005', 1)
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION IF @SEX IS NULL
SELECT * FROM STUDENTS
ELSE
SELECT * FROM STUDENTS WHERE SEX = @SEX
SET @CNT = @@ROWCOUNT
SET NOCOUNT OFF -- 原来是 SET NOCOUNT ON(不好意思,写错了)
你好!
非常多谢你的指点,我的问题终于解决了,这个问题我去年就有了,现在我的程序都写将近十万行,就是遇到这个问题时拐弯抹角地实现,比如在SQL中建个表,在存储过程中把结果写入这个表,然后在VB中先调用存储过程,接着再马上读取表中的数据,效率非常低,这可能有访问冲突,我一直到没有注意SET NOCOUNT ON 这条语句的作用,现在我数月以来的问题逆刃而解,多亏有你的帮助!
我要给你分了,但我不知如何给,请告诉如何操作?我来CSDN真的不是很久,平时只是看别人的贴子,自己很少参与!
为了表示我的感激之情,再说一次:多谢!!!