我使用ADO中的COMMAND\PARAMETER\RECORDSET对象组合,去获取存储过程返回的记录集。但是我发现我使用PARAMETER对象输入参数的时候,程序获取这些参数的情况不稳定。
我大概写一下该存储过程如下:
CREATE PRODUCURE sp_xxx()
@A VARCHAR(50)
ASSELECT * FROM TABLE1 WHERE FIELD1 = (@A)这个时候我用ADO传入参数
Private Function GetProcedureInfo()
On Error GoTo HandleErr
Dim cmdProcedure As ADODB.Command
Dim rstSelect As ADODB.Recordset
Set cmdProcedure = Nothing
Set cmdProcedure = New ADODB.Command cmdProcedure.Parameters.Append cmdProcedure.CreateParameter(, adVarChar, adParamInput, 50, "DBOM") '注意,此处"DBOM" '刷新COMMAND对象
cmdProcedure.ActiveConnection = g_adoPPDM cmdProcedure.CommandText = "sp_xxx" cmdProcedure.CommandType = adCmdStoredProc
'
'刷新纪录集
Set rstSelect = Nothing
Set rstSelect = New ADODB.Recordset
Set rstSelect = cmdProcedure.Execute()
'rstSelect.MoveFirst
If Not rstSelect.EOF Then
Set cmdProcedure = Nothing
Else
Set cmdProcedure = Nothing
End If
Exit Function
HandleErr:
MsgBox Err.Description
End Function可以获取到数据但是我一旦在存储过程中增加了insert 语句的话,上面函数中注释的那条语句中,"DBOM",就需要更改为"'dbom'",不然程序会报错。
但是,如此一写后,就不能取出数据了。请教各位,有什么解决方式。
我大概写一下该存储过程如下:
CREATE PRODUCURE sp_xxx()
@A VARCHAR(50)
ASSELECT * FROM TABLE1 WHERE FIELD1 = (@A)这个时候我用ADO传入参数
Private Function GetProcedureInfo()
On Error GoTo HandleErr
Dim cmdProcedure As ADODB.Command
Dim rstSelect As ADODB.Recordset
Set cmdProcedure = Nothing
Set cmdProcedure = New ADODB.Command cmdProcedure.Parameters.Append cmdProcedure.CreateParameter(, adVarChar, adParamInput, 50, "DBOM") '注意,此处"DBOM" '刷新COMMAND对象
cmdProcedure.ActiveConnection = g_adoPPDM cmdProcedure.CommandText = "sp_xxx" cmdProcedure.CommandType = adCmdStoredProc
'
'刷新纪录集
Set rstSelect = Nothing
Set rstSelect = New ADODB.Recordset
Set rstSelect = cmdProcedure.Execute()
'rstSelect.MoveFirst
If Not rstSelect.EOF Then
Set cmdProcedure = Nothing
Else
Set cmdProcedure = Nothing
End If
Exit Function
HandleErr:
MsgBox Err.Description
End Function可以获取到数据但是我一旦在存储过程中增加了insert 语句的话,上面函数中注释的那条语句中,"DBOM",就需要更改为"'dbom'",不然程序会报错。
但是,如此一写后,就不能取出数据了。请教各位,有什么解决方式。
cmdProcedure.ActiveConnection = g_adoPPDM cmdProcedure.CommandText = "sp_xxx" cmdProcedure.CommandType = adCmdStoredProc
改成:
cmdProcedure.ActiveConnection = g_adoPPDM cmdProcedure.CommandType = adCmdStoredProc cmdProcedure.CommandText = "sp_xxx" cmdProcedure.Parameters("@A").value="DBOM"
看看,能不能用
以上这段也需要更换否?
我更换了,然后程序执行到
cmdProcedure.Parameters("@A").value="DBOM"
出错提示“项目中不存在·····”
也就是说,Parameters对象中没有这个子对象。
请教各位高手,如何解决?
cmdProcedure.CommandType = adCmdStoredPro
cmdProcedure.CommandText = "sp_xxx('DBOM'")Set rstSelect = cmdProcedure.Execute()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "insert_users"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text))
cmd.Parameters.Append param
Set rs = cmd.Execute