Question

VB系统中调用存储过程的写法

VB系统中使用
dim AAWDatabase As Database
Set AADatabase = DBEngine.OpenDatabase("JJW", False, False, "ODBC;DSN=AADSN;DATABSE=AA;UID=;PWD=")
连接ODBC数据源连接

读取数据时
mstrSQL="Select * from person where name='"&name&"' and age="&age&" and sex="&sex

Set RSDynaset = AADatabase.CreateDynaset(mstrSQL)

我现在想把这种直接读取SQL语句改成调用存储过程的方法，输入参数(name,age,sex),输出的为查询结果.

我写成这样错误的:

With AADatabase
.CommandText = "P_Search_MSSQLX_SQL" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = True '要求将SQL命令先行编译
.Parameters.Append .CreateParameter("@name", 3, adParamInput, name)
.Parameters.Append .CreateParameter("@age", 2, adParamInput, age)
.Parameters.Append .CreateParameter("@sex", 2, adParamInput, sex)

End With
Set mDynaset = AADatabase.Execute(1)

===========感觉肯定错误===========

代码应该怎么写,请问？谢谢

Answer 1

呵呵，不用那么麻烦的
mstrSQL="P_Search_MSSQLX_SQL '" & name & "'," & age & "," & sex
参数顺序必须与存储进程中参数顺序和数据类型严格保持一致，可选参数可以留空直接逗号

或者
mstrSQL="P_Search_MSSQLX_SQL name='" & name & "',age=" & age & ",sex=" & sex
第二种方法可以打乱输入参数顺序

Answer 2

Dim Cmd As New ADODB.Command
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "SendCar"
Cmd.Parameters.Append Cmd.CreateParameter("@DetectNo", adVarChar, adParamInput, 50, ADetect)
Cmd.Parameters.Append Cmd.CreateParameter("@CardNum", adVarChar, adParamInput, 50, Acardnum)
Cmd.Parameters.Append Cmd.CreateParameter("@Detecttype", adVarChar, adParamInput, 50, AdetectType)
Cmd.Parameters.Append Cmd.CreateParameter("@Detectitem", adVarChar, adParamInput, 50, ADetectItem)
Cmd.Parameters.Append Cmd.CreateParameter("@DetectAxiss", adVarChar, adParamInput, 50, AxisList)
Cmd.Parameters.Append Cmd.CreateParameter("@Loginid", adInteger, adParamInput, 50, LoginID)
Cmd.ActiveConnection = Conn
Cmd.Execute
Set Cmd = Nothing

其中Conn是你打开的数据库