调用存储过程中出现的有关问题
调用存储过程中出现的问题
我写了一个存储过程在数据库中，其中两参数传入，一个参数传出，返回记录集装入datagrid，但datagrid总也装不进数据，存储过程应该没问题，在SQL查询分析器中执行正常，请各位给看一下：
存储过程参数定义：@sfid varchar(18),@year int,@fhbz int output
VB中调用代码如下：
Dim SQL As String
Dim Rs As New ADODB.Recordset
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
Dim RsCmd As New ADODB.Command
Dim dwID As String
Dim SfCode As String
Dim ghj As Integer
Dim MsgString As String
Private Sub Command1_Click()
Set Rs = CreateObject("ADODB.RECORDSET")
Set RsCmd = CreateObject("ADODB.COMMAND")
Set con = New ADODB.Connection
con.Open GetConnStr
con.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
SQL = "select z_ylbxh,z_xm,tbsj=convert(char(10),z_tbsj,120),xb=case z_xbbm when 0 then '女' else '男' end,z_grxh,csrq=convert(char(10),z_csrq,120),z_tbdw_id,z_gzze,z_jfjs,rylb=case z_rylb when 0 then '在职' when 1 then '退休' else '其它' end,z_sfid from grzhxx00 where z_ylbxh='" & Trim(Text1.Text) & "'"
Set rst = SelectSQL(SQL, MsgString)
If rst.EOF Then
MsgBox "您所查询的信息不存在，请加以确定！", 0 + 16 + 0, "注意！"
Text1.Text = ""
Text1.SetFocus
Exit Sub
Else
dwID = Trim(rst.Fields(6))
For i = 0 To 6
Label4(i).Caption = Trim(rst.Fields(i))
Next i
Label4(8).Caption = rst.Fields(7)
Label4(9).Caption = rst.Fields(8)
Label4(10).Caption = rst.Fields(9)
SfCode = Trim(rst.Fields(10))
SQL = "Select z_dwmc from tbdwxx00 where z_dwid='" & dwID & "'"
Set rst = SelectSQL(SQL, MsgString)
If rst.EOF Then
Label4(7).Caption = ""
Else
Label4(7).Caption = Trim(rst.Fields(0))
End If
End If
RsCmd.ActiveConnection = con
RsCmd.Parameters.Append RsCmd.CreateParameter("@sfid", adChar, adParamInput, 18)
RsCmd.Parameters.Append RsCmd.CreateParameter("@year", adInteger, adParamInput)
RsCmd.Parameters.Append RsCmd.CreateParameter("@fhbz", adInteger, adParamOutput)
RsCmd.CommandText = "TaiYuan_grdzdjs"
RsCmd.CommandType = adCmdStoredProc
RsCmd.Parameters.Refresh
RsCmd.Parameters("@sfid").Value = SfCode
RsCmd.Parameters("@year").Value = Combo1.Text
Set Rs = RsCmd.Execute
Set DataGrid1.DataSource = Rs
DataGrid1.Refresh
Label5.Caption = SfCode
Label6.Caption = Combo1.Text - 1
End Sub
其中的基本信息导入也没问题，就是最后的存储过程装不进，为何，还请各位指教！
------解决方案--------------------
你的表是怎么样的？
用command obj很烦的。
------解决方案--------------------
存储过程中
CREATE PROCEDURE ....
....
AS
SET NOCOUNT ON --设置不返回计数
操作代码
SET NOCOUNT OFF
我写了一个存储过程在数据库中，其中两参数传入，一个参数传出，返回记录集装入datagrid，但datagrid总也装不进数据，存储过程应该没问题，在SQL查询分析器中执行正常，请各位给看一下：
存储过程参数定义：@sfid varchar(18),@year int,@fhbz int output
VB中调用代码如下：
Dim SQL As String
Dim Rs As New ADODB.Recordset
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
Dim RsCmd As New ADODB.Command
Dim dwID As String
Dim SfCode As String
Dim ghj As Integer
Dim MsgString As String
Private Sub Command1_Click()
Set Rs = CreateObject("ADODB.RECORDSET")
Set RsCmd = CreateObject("ADODB.COMMAND")
Set con = New ADODB.Connection
con.Open GetConnStr
con.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
SQL = "select z_ylbxh,z_xm,tbsj=convert(char(10),z_tbsj,120),xb=case z_xbbm when 0 then '女' else '男' end,z_grxh,csrq=convert(char(10),z_csrq,120),z_tbdw_id,z_gzze,z_jfjs,rylb=case z_rylb when 0 then '在职' when 1 then '退休' else '其它' end,z_sfid from grzhxx00 where z_ylbxh='" & Trim(Text1.Text) & "'"
Set rst = SelectSQL(SQL, MsgString)
If rst.EOF Then
MsgBox "您所查询的信息不存在，请加以确定！", 0 + 16 + 0, "注意！"
Text1.Text = ""
Text1.SetFocus
Exit Sub
Else
dwID = Trim(rst.Fields(6))
For i = 0 To 6
Label4(i).Caption = Trim(rst.Fields(i))
Next i
Label4(8).Caption = rst.Fields(7)
Label4(9).Caption = rst.Fields(8)
Label4(10).Caption = rst.Fields(9)
SfCode = Trim(rst.Fields(10))
SQL = "Select z_dwmc from tbdwxx00 where z_dwid='" & dwID & "'"
Set rst = SelectSQL(SQL, MsgString)
If rst.EOF Then
Label4(7).Caption = ""
Else
Label4(7).Caption = Trim(rst.Fields(0))
End If
End If
RsCmd.ActiveConnection = con
RsCmd.Parameters.Append RsCmd.CreateParameter("@sfid", adChar, adParamInput, 18)
RsCmd.Parameters.Append RsCmd.CreateParameter("@year", adInteger, adParamInput)
RsCmd.Parameters.Append RsCmd.CreateParameter("@fhbz", adInteger, adParamOutput)
RsCmd.CommandText = "TaiYuan_grdzdjs"
RsCmd.CommandType = adCmdStoredProc
RsCmd.Parameters.Refresh
RsCmd.Parameters("@sfid").Value = SfCode
RsCmd.Parameters("@year").Value = Combo1.Text
Set Rs = RsCmd.Execute
Set DataGrid1.DataSource = Rs
DataGrid1.Refresh
Label5.Caption = SfCode
Label6.Caption = Combo1.Text - 1
End Sub
其中的基本信息导入也没问题，就是最后的存储过程装不进，为何，还请各位指教！
------解决方案--------------------
你的表是怎么样的？
用command obj很烦的。
------解决方案--------------------
存储过程中
CREATE PROCEDURE ....
....
AS
SET NOCOUNT ON --设置不返回计数
操作代码
SET NOCOUNT OFF