CREATE PROCEDURE [dbo].[tse]
@keyword varchar(20)=null, '定义查询的关键字
as
if exists(select * from web where title=@keywordelse )
select '有'
else
select '没有'
GO
@keyword varchar(20)=null, '定义查询的关键字
as
if exists(select * from web where title=@keywordelse )
select '有'
else
select '没有'
GO
@keyword varchar(20)=null, '定义查询的关键字
as
select * from web where title=@keywordelse
GO
CREATE PROCEDURE [dbo].[tse]
@keyword varchar(20)=null
as
select * from web where title=@keyword
GO
set rs=server.createobject("adodb.recordset")
sql="exec tse '"&keyword&"',"&choose&"" '用exec执行tse存储过程,把keyword,choose给存储 过程传递参数
rs.open sql,conn,1,1
----------
这个出不来?
@keyword varchar(20)=null, '定义查询的关键字
as
select * from web where title=@keywordelse
GO
asp:dim cmd,cn,rs
set cn = server.createobect("adodb.connection")
cn.open "provider=sqloledb;uid=...;pwd=...;database=.."
set cmd=server.createobject("adodb.command")
cmd.activeconnection = cn
cmd.commadtype = ...
找个ado手册抄下就是了,不想打了。 google搜asp调用sqlserver存储过程也可 以。
@keyword varchar(20)=null, '定义查询的关键字
as
select * from web where title=@keyword
GO 你的过程只有输入参数没有输出参数。'如果有adovbs.inc可以写常量名,这里我直接写参数值。
cmd.activeconnection = cn
cmd.commandtype = 4
dim pm1
set pm1=cmd.CreateParameter("@keyword",200,1,长度)
cmd.parameters.append pm1
cmd("@keyword")=值
set rs=cmd.execute
set cmdfind=server.createobject("adodb.command")
cmdfind.activeconnection=conn
cmdfind.commandtype=4
cmdfind.commandtext="ds"
set mypara=cmdfind.createparameter("@num",200,1,"num")
cmdfind.parameters.append mypara
set mypara=cmdfind.createparameter("@count",3,2,"count")
cmdfind.parameters.append mypara
cmdfind.execute
%>
这样写的话,提示
Provider 错误 '80020005' 类型不匹配。
@id INT,
@count INT OUTPUT
AS
BEGIN
SELECT @count = @id +1
END
<%
Dim Conn
Set Conn = Server.CreateObject("Adodb.Connection")
Conn.Open "Provider=SqlOleDb;UID=fc;Pwd=xxx;Database=db1"
Dim cmd
Set cmd = Server.CreateObject("Adodb.Command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "p"Dim pm1,pm2
Set pm1=cmd.CreateParameter("@id",3,1,32)
cmd.Parameters.Append(pm1)
cmd("@id") = 1
Set pm2 = cmd.CreateParameter("@count",3,2,32)
cmd.Parameters.Append(pm2)cmd.Execute()
Dim result
result = cmd("@count")
Response.Write(result)
Conn.close()conn = null
cmd = null
%>页面输出 2因为我的过程不返回记录集,所以asp代码中未用recordset接收cmd.execute的结果。
<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996-1998 Microsoft Corporation. All Rights Reserved.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBook = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4'---- ExecuteOptionEnum Values ----
Const adRunAsync = &H00000010
Const adAsyncExecute = &H00000010
Const adAsyncFetch = &H00000020
Const adAsyncFetchNonBlocking = &H00000040
Const adExecuteNoRecords = &H00000080'---- ConnectOptionEnum Values ----
Const adAsyncConnect = &H00000010'---- ObjectStateEnum Values ----
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001
Const adStateConnecting = &H00000002
Const adStateExecuting = &H00000004
Const adStateFetching = &H00000008'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139'---- FieldAttributeEnum Values ----
Const adFldMayDefer = &H00000002
Const adFldUpdatable = &H00000004
Const adFldUnknownUpdatable = &H00000008
Const adFldFixed = &H00000010
Const adFldIsNullable = &H00000020
Const adFldMayBeNull = &H00000040
Const adFldLong = &H00000080
Const adFldRowID = &H00000100
Const adFldRowVersion = &H00000200
Const adFldCacheDeferred = &H00001000
Const adFldKeyColumn = &H00008000'---- EditModeEnum Values ----
Const adEditNone = &H0000
Const adEditInProgress = &H0001
Const adEditAdd = &H0002
Const adEditDelete = &H0004'---- RecordStatusEnum Values ----
Const adRecOK = &H0000000
Const adRecNew = &H0000001
Const adRecModified = &H0000002
Const adRecDeleted = &H0000004
Const adRecUnmodified = &H0000008
Const adRecInvalid = &H0000010
Const adRecMultipleChanges = &H0000040
Const adRecPendingChanges = &H0000080
Const adRecCanceled = &H0000100
Const adRecCantRelease = &H0000400
Const adRecConcurrencyViolation = &H0000800
Const adRecIntegrityViolation = &H0001000
Const adRecMaxChangesExceeded = &H0002000
Const adRecObjectOpen = &H0004000
Const adRecOutOfMemory = &H0008000
Const adRecPermissionDenied = &H0010000
Const adRecSchemaViolation = &H0020000
Const adRecDBDeleted = &H0040000'---- GetRowsOptionEnum Values ----
Const adGetRowsRest = -1'---- PositionEnum Values ----
Const adPosUnknown = -1
Const adPosBOF = -2
Const adPosEOF = -3'---- enum Values ----
Const adBookCurrent = 0
Const adBookFirst = 1
Const adBookLast = 2'---- MarshalOptionsEnum Values ----
Const adMarshalAll = 0
Const adMarshalModifiedOnly = 1'---- AffectEnum Values ----
Const adAffectCurrent = 1
Const adAffectGroup = 2
Const adAffectAll = 3
Const adAffectAllChapters = 4'---- ResyncEnum Values ----
Const adResyncUnderlyingValues = 1
Const adResyncAllValues = 2'---- CompareEnum Values ----
Const adCompareLessThan = 0
Const adCompareEqual = 1
Const adCompareGreaterThan = 2
Const adCompareNotEqual = 3
Const adCompareNotComparable = 4'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterPredicate = 4
Const adFilterConflictingRecords = 5'---- SearchDirectionEnum Values ----
Const adSearchForward = 1
Const adSearchBackward = -1'---- PersistFormatEnum Values ----
Const adPersistADTG = 0
Const adPersistXML = 1'---- StringFormatEnum Values ----
Const adStringXML = 0
Const adStringHTML = 1
Const adClipString = 2'---- ConnectPromptEnum Values ----
Const adPromptAlways = 1
Const adPromptComplete = 2
Const adPromptCompleteRequired = 3
Const adPromptNever = 4'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &Hc
Const adModeShareDenyNone = &H10'---- IsolationLevelEnum Values ----
Const adXactUnspecified = &Hffffffff
Const adXactChaos = &H00000010
Const adXactReadUncommitted = &H00000100
Const adXactBrowse = &H00000100
Const adXactCursorStability = &H00001000
Const adXactReadCommitted = &H00001000
Const adXactRepeatableRead = &H00010000
Const adXactSerializable = &H00100000
Const adXactIsolated = &H00100000'---- XactAttributeEnum Values ----
Const adXactCommitRetaining = &H00020000
Const adXactAbortRetaining = &H00040000'---- PropertyAttributesEnum Values ----
Const adPropNotSupported = &H0000
Const adPropRequired = &H0001
Const adPropOptional = &H0002
Const adPropRead = &H0200
Const adPropWrite = &H0400'---- ErrorValueEnum Values ----
Const adErrInvalidArgument = &Hbb9
Const adErrNoCurrentRecord = &Hbcd
Const adErrIllegalOperation = &Hc93
Const adErrInTransaction = &Hcae
Const adErrFeatureNotAvailable = &Hcb3
Const adErrItemNotFound = &Hcc1
Const adErrObjectInCollection = &Hd27
Const adErrObjectNotSet = &Hd5c
Const adErrDataConversion = &Hd5d
Const adErrObjectClosed = &He78
Const adErrObjectOpen = &He79
Const adErrProviderNotFound = &He7a
Const adErrBoundToCommand = &He7b
Const adErrInvalidParamInfo = &He7c
Const adErrInvalidConnection = &He7d
Const adErrNotReentrant = &He7e
Const adErrStillExecuting = &He7f
Const adErrOperationCancelled = &He80
Const adErrStillConnecting = &He81
Const adErrNotExecuting = &He83
Const adErrUnsafeOperation = &He84'---- ParameterAttributesEnum Values ----
Const adParamSigned = &H0010
Const adParamNullable = &H0040
Const adParamLong = &H0080太长,略去部分,机器下都有
%>
200代表varchar,3代表int
你参数未赋值。
@num varchar
as
DECLARE @count as int
set @count=
( select number from aa where number=@num )
return @count
GO输入页
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>
<body>
<p> </p>
<p>
<%
dim num ,f
num=replace(trim(request("shu"))," ","")
f=len(num)
if f<18 then
response.Write "<script language='javascript'>alert('您输入的位数少于18位,请重新输入!');window.location.href='../index.asp';</script>"
response.End
else if f>20 then
response.Write "<script language='javascript'>alert('您输入的位数多于20位,请重新输入!');window.location.href='../index.asp';</script>"
response.End
end if
end if
%><%
set conn=server.createobject("adodb.connection")
conn.connectionstring="driver={sql server};server=CNC-0P4MK7F13AN;uid=liang;pwd=123456;database=cha;"
conn.open
Dim cmd
Set cmd = Server.CreateObject("Adodb.Command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "ds"Dim pm1,pm2
Set pm1=cmd.CreateParameter("@num",200,1,32)
cmd.Parameters.Append(pm1)
cmd("@num") = num
Set pm2 = cmd.CreateParameter("@count",3,2,32)
cmd.Parameters.Append(pm2)
cmd.Execute()
Dim result
result = cmd("@count")
Response.Write(result)
Conn.close()conn = null
cmd = null
%></p>
</body>
</html>提示Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]为过程或函数 ds 指定的参数太多。 /cha/zhao.asp,行 39
您帮忙弄一下吧,改ASP调用吧.
以前没接触过存储过程,谢谢您