比如: CREATE proc p_t @a varchar(2), @b varchar(10) output as set nocount on if @a='0' set @b= ltrim(@a)+'a' else set @b= ltrim(@a)+'b' return GO直接执行存储过程: declare @c varchar(10) declare @d varchar(2) set @c='2' exec p_t @c,@d output print @d --------结果:2b调用: com.ActiveConnection = con com.CommandText = "p_t" com.CommandType = adCmdStoredProcSet pa = com.CreateParameter("a", adBigInt, adParamInput, 2) pa.Value = "1" com.Parameters.Append pa Set pa = com.CreateParameter("@b", adVarChar, adParamOutput, 10) com.Parameters.Append pa com.Execute Debug.Print com("@b")------结果:2b
Set pa = com.CreateParameter("a", adBigInt, adParamInput, 2)中的adBigInt改成adVarchar
Option Explicit Dim con As New ADODB.Connection Dim com As New ADODB.Command Dim rs As New ADODB.Recordset Dim pa As ADODB.Parameter Private Sub Command1_Click() con.ConnectionString = "driver={sql server};server=(Local);database=test;uid=sa;pwd=sunway" con.Opencom.ActiveConnection = con com.CommandText = "p_t" com.CommandType = adCmdStoredProcSet pa = com.CreateParameter("a", adBigInt, adParamInput, 2) pa.Value = "1" com.Parameters.Append pa Set pa = com.CreateParameter("@b", adVarChar, adParamOutput, 10) com.Parameters.Append pa com.Execute Debug.Print com("@b") End Sub
Microsoft VBScript 编译器错误 错误 '800a0401' 语句未结束 /datafill/xx.asp,行 3 Dim con As New ADODB.Connection --------^
你肯定不行,这是vb的 你的脚本要 set con=server.createobject("adodb.connection")
我的存储过程: _________ CREATE proc p_datafill@filldate datetimeasdeclare @stamptime datetime set @stamptime=getdate()update shenp set 状态说明=@stamptime where ........ if @@ROWCOUNT >0 begin insert into shenp_b ................ insert into shenpjg_b .............. insert into shenp_v_b .............. delete shenpjg where ............... delete shenp where ................. RETURN 0 end else RETURN 1
GO
最好是把你的存储过程改成返回参数的: CREATE proc p_datafill@filldate datetime, @returnV int outputas set nocount on--------declare @stamptime datetime set @stamptime=getdate()update shenp set 状态说明=@stamptime where ........ if @@ROWCOUNT >0begininsert into shenp_b ................ insert into shenpjg_b .............. insert into shenp_v_b .............. delete shenpjg where ............... delete shenp where ................. set @returnV=0------- end else set @returnV=1------ return------GO
create proc pr @filldate datetime, @returnV int outputas set nocount on--------declare @stamptime datetime set @stamptime=getdate()if @@ROWCOUNT >0beginset @returnV=0------- end else set @returnV=1------ return------GO命令已成功完成。
这个放一边儿,到现在也没有一段完全实用的调用的ASP代码!
if request(filldate)="" then filldate="2006-10-10" end if
数据类型用adVarchar (200)之后报的错如下: _______________________ 2006-10-10 Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]将数据类型 varchar 转换为 int 时出错。
CREATE proc p_t
@a varchar(2),
@b varchar(10) output
as
set nocount on
if @a='0'
set @b= ltrim(@a)+'a'
else
set @b= ltrim(@a)+'b'
return
GO直接执行存储过程:
declare @c varchar(10)
declare @d varchar(2)
set @c='2'
exec p_t @c,@d output
print @d --------结果:2b调用:
com.ActiveConnection = con
com.CommandText = "p_t"
com.CommandType = adCmdStoredProcSet pa = com.CreateParameter("a", adBigInt, adParamInput, 2)
pa.Value = "1"
com.Parameters.Append pa
Set pa = com.CreateParameter("@b", adVarChar, adParamOutput, 10)
com.Parameters.Append pa
com.Execute
Debug.Print com("@b")------结果:2b
Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim pa As ADODB.Parameter
Private Sub Command1_Click()
con.ConnectionString = "driver={sql server};server=(Local);database=test;uid=sa;pwd=sunway"
con.Opencom.ActiveConnection = con
com.CommandText = "p_t"
com.CommandType = adCmdStoredProcSet pa = com.CreateParameter("a", adBigInt, adParamInput, 2)
pa.Value = "1"
com.Parameters.Append pa
Set pa = com.CreateParameter("@b", adVarChar, adParamOutput, 10)
com.Parameters.Append pa
com.Execute
Debug.Print com("@b")
End Sub
--------^
你的脚本要
set con=server.createobject("adodb.connection")
_________
CREATE proc p_datafill@filldate datetimeasdeclare @stamptime datetime
set @stamptime=getdate()update shenp set 状态说明=@stamptime where ........
if @@ROWCOUNT >0 begin insert into shenp_b ................
insert into shenpjg_b ..............
insert into shenp_v_b ..............
delete shenpjg where ...............
delete shenp where .................
RETURN 0
end
else
RETURN 1
GO
CREATE proc p_datafill@filldate datetime,
@returnV int outputas
set nocount on--------declare @stamptime datetime
set @stamptime=getdate()update shenp set 状态说明=@stamptime where ........
if @@ROWCOUNT >0begininsert into shenp_b ................
insert into shenpjg_b ..............
insert into shenp_v_b ..............
delete shenpjg where ...............
delete shenp where .................
set @returnV=0-------
end
else
set @returnV=1------
return------GO
CmdSP.Parameters.Appedn CmdSP.CreateParameter("@filldate",3,1,10,filldate)
数据是这样的:2006-10-10的形式,要如何改("@filldate",3,1,10,filldate)这里?
<%Server.ScriptTimeout = 999999 %>
<%
DIM CmdSP,MyPara
filldate=trim(request("filldate"))
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=123456;Database=SMS"
CmdSP.CommandText = "p_datafill"
CmdSP.CommandType = 4
CmdSP.Prepared = true
'声明值
Set Mypara=CmdSP.CreateParameter("filldate",134,1,8,filldate)'注意:日期格式要对
CmdSP.Parameters.Append MyParaSet Mypara = CmdSP.CreateParameter("RETURN",3,2,4)
CmdSP.Parameters.Append MyPara
CmdSP.Execute
'取得返回值
DIM retValue
retValue = CmdSP(0) if retValue=0 then
response.write "<br>数据处理完毕,请关闭当前页!"
else
response.write "<br>没有找到需要进行处理的数据!"
end ifSet CmdSP = nothing
%>
chuifengde兄改的存储过程无法建立,提示需要声明@returnV变量,并且SET处有错误!
@filldate datetime,
@returnV int outputas
set nocount on--------declare @stamptime datetime
set @stamptime=getdate()if @@ROWCOUNT >0beginset @returnV=0-------
end
else
set @returnV=1------
return------GO命令已成功完成。
filldate="2006-10-10"
end if
定义filldate类型 (格式是:2006-10-10)
与存储过程中定义的@filldate datetime有冲突,但是不知道要如何写定义ASP调用页中的filldate的数据类型
不知这样讲能否明白?
_______________________
2006-10-10
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]将数据类型 varchar 转换为 int 时出错。
CmdSP.Parameters.Appedn CmdSP.CreateParameter("@filldate",3,1,10,filldate)
改成
CmdSP.Parameters.Appedn CmdSP.CreateParameter("@filldate",3,1,10,Cdate(filldate))
adDBDate 日期值 (yyyymmdd) (DBTYPE_DBDATE)。
adDBTime 时间值 (hhmmss) (DBTYPE_DBTIME)。
adDBTimeStamp 时间戳(yyyymmddhhmmss 加 10 亿分之一的小数)(DBTYPE_DBTIMESTAMP). Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 1357和133均可一试.