关于用vb执行sql存储过程并获取返回值

[smile111]

sql存储过程如下：
create procedure prc_a @HousingMode varchar(8),@RoomClass varchar(16),
@StartTime datetime,@LeaveTime datetime,@RoomPay money output
as
declare @price money,@time int
begin
if @HousingMode='计时方式'
begin
select @price=[ClassHourPrice] from [RoomClassInfo] where [RoomClass]=@RoomClass
set @time=datediff(hour,@StartTime,@LeaveTime)
end
if @HousingMode='计天方式'
begin
select @price=[ClassDayPrice] from [RoomClassInfo] where [RoomClass]=@RoomClass
set @time=datediff(day,@StartTime,@LeaveTime)
end

set @RoomPay=@price*@time
end


vb代码：
Private Sub Command7_Click()
'关于类模块，模块里的一些定义太杂这里就不写，我再下面会有注释来交代
Dim cmm As New ADODB.Command
Dim RoomClass As New ADODB.Parameter
Dim StartTime As New ADODB.Parameter
Dim LeaveTime As New ADODB.Parameter
Dim HouseMode As New ADODB.Parameter
Dim RoomPay As New ADODB.Parameter

Dim strInput As String
strInput = cmMode.Text 'cmMode是一选择下拉框

'hotel是名为Top的类的实例,room是名为Room的类（也是top类的属性),StartTime是Room类属性,数据类型是date,Dt1是一个时间控件,此处赋值,下同)
Hotel.room.StartTime = CDate(Dt1.Value)
Hotel.room.LeaveTime = CDate(DT2.Value)


Call OpenCon '连接数据库

Set cmm = New ADODB.Command

Set cmm.ActiveConnection = mycon
Set HouseMode = cmm.CreateParameter("HouseMode", adVarChar, adParamInput, 8, strInput)
Set RoomClass = cmm.CreateParameter("RoomClass", adVarChar, adParamInput, 16, Hotel.room.RoomClass)
Set StartTime = cmm.CreateParameter("StartTime", adDBTimeStamp, adParamInput, , Hotel.room.StartTime)
Set LeaveTime = cmm.CreateParameter("LeaveTime", adDBTimeStamp, adParamInput, , Hotel.room.LeaveTime)
Set RoomPay = cmm.CreateParameter("RoomPay", adInteger, adParamOutput)


cmm.Parameters.Append RoomClass
cmm.Parameters.Append StartTime
cmm.Parameters.Append LeaveTime
cmm.Parameters.Append HouseMode
cmm.Parameters.Append RoomPay


cmm.CommandText = " prc_a "

cmm.CommandType = adCmdStoredProc

Set myres = New ADODB.Recordset
Set myres = cmm.Execute()
TxtMoney.Text = RoomPay.Value 'txtMoney.Text 是一个文本框

End Sub

执行这段代码报错：
实时错误
将数据类型varchar转换为datetime时出错。

请问如何解决？小弟先向各位看帖达人道谢了。

[smile111]

在这之前,我写过一个与上很类似的,但只有1个输入参数，1输出参数，运行成功，代码如下：



sql代码:

create procedure prc_d @HousingMode varchar(8),@RoomPay money output
as
begin
if @HousingMode='计时方式'
begin
select @RoomPay=[ClassHourPrice] from [RoomClassInfo] where [RoomClass]='普通'

end
if @HousingMode='计天方式'
begin

select @RoomPay=[ClassDayPrice] from [RoomClassInfo] where [RoomClass]='普通'

end


end

vb代码：
Private Sub Command7_Click()

Dim cmm As New ADODB.Command
Dim a As New ADODB.Parameter
Dim b As New ADODB.Parameter
Dim str As String
Call OpenCon
Set cmm = New ADODB.Command

Set cmm.ActiveConnection = mycon

str = cmMode.Text
Set a = cmm.CreateParameter("a", adVarChar, adParamInput, 8, str)


Set b = cmm.CreateParameter("b", adInteger, adParamOutput)


cmm.Parameters.Append a
cmm.Parameters.Append b

cmm.CommandText = " prc_d "

cmm.CommandType = adCmdStoredProc

Set myres = New ADODB.Recordset
Set myres = cmm.Execute()
TxtMoney.Text = b.Value

End Sub

[aohan]

create procedure prc_a @HousingMode varchar(8),@RoomClass varchar(16),
@StartTime datetime,@LeaveTime datetime,@RoomPay money output
as
中的@StartTime datetime


改为 @StartTime varchar(20)

用字符串往里边传应该没有问题

[smile111]

改为@StartTime varchar(20),运行报错：
实时错误
无效使用NULL

ps:在查询分析器里执行存储过程没一点问题，问题应该是出在vb的代码，请高人指点.

[zkzj]

会不会是vb里参数类型定义错误

[w3k]

这个问题很容易，我前天才用到sql存储过程并获取返回值！效率高！

QQ：59804834