ASP :: Stored Procedure Outputs!

Stored Procedure
if session timeout end, normaly i make some sql changes and redirecting to login page...
but if user closed the browser i cannot made those sql changes.

is it possiable, and how, to write a stored procedure in asp that will see that the timeout of session("x") ended and will do this changes from the server side?

Trying To Use A Stored Procedure
I'm trying to access a stored procedure on our AS400 that is written in RPG. I know that the stored procedure works (I've called it from an RPG program on the AS400), and I know that the connection to the AS400 is good, because this same ASP page is reading data via SQL. However, when I try to call the stored procedure, I can't tell if anything is actually happening. All that I know is that the end result is not being changed, so I suspect that the procedure isn't being called correctly.

My stored procedure is used to encrypt/decrypt data. It has 3 parameters - a 1 character flag telling it whether to encrypt or decrypt, a 25 character field for the plaintext data, and a 50 character field for the encrypted data. The last two parameters are used for either input or output, depending on the value of the first
flag (if the flag is "E", then the plaintext is used as input and the program fills the encrypted data field. If the flag is "D", then vice - versa).

Here is the code where I call my stored procedure. I'm trying to decrypt data from the queryString and place it in some program variables. I'm new to ASP, and can't find any good examples for my situation, so I pieced this together from what examples I found
online. Can anyone tell me what I'm missing?

Sql Stored Procedure
I have a problem with input parameter which has Decimal DataType. Stored procedure works but it rounds all
values, i.e 5.555 input becomes 6 and 1.3 input becomes 1.
In table QTY has data type decimal(5) - precision(8) scale(3).
Please, suggest what's wrong with this:

newqty = Request.Form("quantity")
..........
cmd.Parameters.Append(cmd.CreateParameter("qty", adDecimal, adParamInput, 5, newqty))
cmd.Parameters("qty").Precision = 8
cmd.Parameters("qty").NumericScale = 3

Stored Procedure
I am currently using Microsoft access database 2000. I am trying to create a stored procedure in my database. How do i do that?
example:

"CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " & _
"inSupplierID LONG, inCategoryID LONG) " & _
"AS INSERT INTO Products (ProductName, SupplierID, CategoryID) " & _
"Values (inProductName, inSupplierID, inCategoryID);""CREATE PROC procProductsUpdateItem(inProductID LONG, " & _
" inProductName VARCHAR(40)) " & _
"AS UPDATE Products SET ProductName = inProductName " & _
" WHERE ProductID = inProductID;"

Stored Procedure And LIKE
how to use LIKE in a Stored Procedure ?I can use it as "where tbl LIKE @string"but it doesn't work truly.how can I use % ' & ?you know, we use LIKE in classic asp and sql as '%"&YYY&"%'.but how can it be used in Stored Procedure ?

Stored Procedure
there something that i need to clarify . can we use stored procedure to find the difference between two tables and shown it out in html table ? can anyone giv a bone on how to do it or if there any link that show examples on this ?

Stored Procedure In ASP
I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02.

Insert some other info in another table (tblX03) With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?

Stored Procedure
I have a stored procedure that I am executing with this SQL

osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo = 37,
@Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604, 605, 606,
607, 608, 609, 610, 611, 612, 613, 616'

the @Schedules are ID fields in a table that are integers. I would like to do an Update blah, Where ScheduleID in (@Schedules) instead of looping. How can I do this? I have to sent the values to the sproc as text, right? Where do I covert to the int data type and how?

#include Vs. Stored Procedure
I have a query that selects from multiple tables and is complicated, thus making the number of code lines very big. I'd like to separate it from the page that displays the query results.

I have been told that a stored procedure will do the trick, but I've just come across another option, 'include file'. I'm used to programming in ColdFusion so I am familiar with the concept of including files.

Stored Procedure In Oracle
I'm trying to access a stored procedure in oracle using asp/jscript.

I get an error message complaining about the parameters;
[Oracle][ODBC][Ora]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'UPDATE_BAKERY_USER' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

A Response.write returns this:
CommandText: { call BUNGE.UPDATE_BAKERY_USER(?, ?, ?) }

The stored procedure changes a users password, so the parameters are:
plogin
pold_password
pnew_password

Can anyone please help me verify my code?

Code:....

Calling A Stored Procedure
I am new to asp, basically new to everything. but I am trying to create a log table and and trouble calling my stored procedure from my asp page, see code:

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'sp_logsession'
/app/lib.asp, line 71

i am using the following code to execute it in my asp page:

SET objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("deConn")

Execute sp_logsession(browser,timenow,remoteaddress,idsess ion,refer)

is there anyone who could help me to get this working?

Call Stored Procedure In ASP.NET 2.0
I would like to know if anyone knows how to execute a stored procedure from ASP.NET 2.0. I'm using the NorthWind database and I'm trying to execute the "CustOrderHist" stored procedure. The error I get is "Incorrect syntax near 'CustOrderHist'. "

Public Function GetCustomerOrderHistory(ByVal customerid As String) As
SqlDataReader
Dim conn As New SqlConnection(conString)
Dim cmd As New SqlCommand("CustOrderHist", conn)
cmd.Parameters.AddWithValue("@CustomerID", customerid)
conn.Open()
Dim dtr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return dtr
End Function

Running An SQL Stored Procedure With ADO
I am having a problem running an sql stored procedure with ADO/ASP. If I hard code a select statement, the code works, but when I try to use a stored procedure it bombs. Here is my code:

THIS WORKS:

sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601', '20030605'"

'Note: I already did my connection object above
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn

THIS DOES NOT:

'my stored procedure is LC_Top50_ByCost sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"

set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MyWeb/T10_Cost.asp, line 202

Line 202 is the first line that I try to access the data in the
recordset.

Calling A Stored Procedure
I'm trying to run a stored procedure and access data to read data. I'm getting an error with the ObjComm.CommandText when it's calling the Stored procedure.

ADODB.Command error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.....

How Do I Call A Stored Procedure With ASP?
How do I call a Stored Procedure with ASP?

My Stored Procedure Won't Load
I'm new to asp but not new to web programming. I've been a cold fusion developer for a few years now, but due to a job changes I've had to learn asp. I'm running into a problem calling my stored procedure. The procedure runs fine in Query Analyer so I know that works, but when I try to call it using asp I get:

ADODB.Commanderror '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/ce/etracker/aIssue.asp, line 7

Here is my code.....

ASP/ Stored Procedure Query
I'm not sure if you can do this but what I want to do it execute a stored procedure but query it with dynamic ASP data. Basically,

exex StoredProc where id = 2

I am using a set of dynamic data and I truly belive looping trought the dataset would be too much overhead on the server.

Using ORDER BY In A Stored Procedure
I keep getting an error from the following SP snippet

"Line 11: Incorrect syntax near 'BY'."

It runs fine without the ORDER BY clause.
[color=blue][color=green][color=darkred]
>>>>>>>>>>>>>>>[/color][/color][/color]
[' + @user + ']
WHERE
[' + @cal + '].clientID = [' + @user1 + '].userID
AND [' + @cal + '].newID = ' + @userID + ' ORDER BY [' + @cal +
'].myAppointment'
EXEC (@sql)
GO[color=blue][color=green][color=darkred]
>>>>>>>>>>>>>>>[/color][/color][/color]

Any suggestions?

Problem With Stored Procedure
I am using a simple stored procedure that just searches for the cities whos name starts with the keyword enterd. But the below code dosnt seems to work. any ideas.

I am using SQL Server 2000

Code:
CREATE PROCEDURE va_search_location
@keyword varchar(50)
AS
Select * from tblcities where CityName like '@keyword%'
GO

Getting Return Value Of Stored Procedure
I have the Following Code, that Executes a Stored Procedure and Attempt to
read a Returned Integer Value from the StoredProc.

But It gives Error ...

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/C4U/DBOutputParameterTest.asp, line 25

Can some one Points Out the Problem: Code:

Execute Stored Procedure In ASP
I want to exexute stored procedure in ASP, but it has error "Microsoft
VBScript compilation (0x800A0401) Expected end of statement" on line (1).

The stored procedure "sp_emp" contain "select * from Employee;"

<%
Dim objRS, sqlStmt
set objRS = Server.CreateObject("ADODB.Recordset")
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.open strConnect
Set objRS = conn.Execute "sp_emp" '<========(1) execute stored
procedure
//etc...
%>

Run Stored Procedure In Background
I have a stored procedure that gets called from an ASP page. I had a requirement to adjust the stored procdure to have a delay, so now my browser waits for the stored procedure to complete before it returns the page and value I am looking for.

Is it possible to launch the stored proc from the ASP, but have it run in the background per say and return the value immediatley to the browser, cause the browser portion to not appear to be delayed?

Call To Stored Procedure
Is this the correct way to read a variable from a stored procedure

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_Supplier"

Hi i'd like to know what this line in a asp page does ?

cmd.Parameters.Append cmd.CreateParameter("Supplier",adVarChar,adParamInput,10,sChangeSupplier)

Passing Stored Procedure
I have a Sub that runs a stored procedure and another Sub to display the results of the stored procedure. How can I pass that sp to the Sub?

Executing Stored Procedure In Asp Page
I have written a stored procedure and I am trying to execute it inside my ASP page. So here is my code. It wont work at all, like when I try and open the page It times outs.

<%
dim sqlrs, rs2
sqlrs = "exec SP_GET_KPI_TABLE_HEADERS"
set rs2 = server.createobject("ADODB.Recordset")
RS2.open SQLrs, CMS, 2, 2
%> Code:

Returning Recordsets From A Stored Procedure
(IIS 6.0, SQL Server 2000)

I have a block of code that populates a recordset from a stored procedure.
The problem is, the recordset seems to be forward only (which would be OK),
but can't jump with the "AbsolutePage" property (which isn't OK)

How do I define the recordset that will allow this? Code:

Passing A Date To A Stored Procedure...
I've created a stored procedure to to display a listing of events based on the user's input. when the user submits the date, i get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '/'.

this is my reference in my asp page: ....

.ASP, MTS Transactions, And Stored Procedure I/O Variables
I've got a webpage that calls some stored procedures with input variables.
The procedures return recordsets and also some output variables. We're
trying to get the values of the output variables. I've done this using the
method I found in MSDN, as shown in the code below. The problem is that we
believe doing it this way involves the use of the Microsoft Transaction
Server (IIS transaction server). Is this true? (The SQL Server and IIS
Server are on different machines and, I believe, a firewall separates them.)

I can't see why this method would use MTS. The problem is that in the past,
we have had problems with MTS stopping for no reason, which causes the
websites to not work. So my boss doesn't want to use any ASP code that
relies on MTS. Not using the stored procedure output variables makes things
way more complicated. So does this code really invoke MTS? And if it does,
is there a way to obtain SP output variables without using MTS?

I am running SQL Server 7.0 with the latest service pack and IIS version 4.
We do have an IIS version 5 server we can use if that matters. Code:

Pass Image To SQL Stored Procedure
I would like to upload an image (got File.Binary from ASPUpload) with a
stored procedure.

It works with a normal recordset with new/update and in ASP.NET, but in
Classic ASP, I can't find the right constant in adovbs.inc to use for the
parameter:

Command.Parameters.Append Command.CreateParameter("@FileBlob", ?????,
adParamInput, , File.Binary)

Anyone?

Stored Procedure Call Not Executing
I have a Stored Procedure (SP) being called by my ASP page, but nothing is being executed. The SQL Profiler shows a RPC execute, but the actual SP is not executing as nothing is being inserted.

If I run an EXEC in Query Analyzer, with the same data being passed, it works fine and returns my OUTPUT. The SQL Profiler shows all the hits to the database then too. Code:

Oracle Stored Procedure APPEND
I'm trying to run a stored procedure on an Oracle database. I currently get this error message:

Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/live/verify_3.asp, line 24

My code is as follows: ....

@@Row Count From Stored Procedure Returnes -1
I have a very simple stored procedure:

ALTER PROCEDURE [energya_admin].[proc_Customer_CheckIfNew]
@last_name nchar(100)
AS
BEGIN
SET NOCOUNT ON;

SELECT Last_Name, Address, City
FROM v2_Customers
WHERE v2_Customers.Last_Name = @last_name

SELECT @@RowCount
END

now in the ASP page i want to get that RowCount number, so:....

Handle Stored Procedure Timeout From ASP
I have an ASP page calling a stored procedure. I have set the connection and command timeouts and I have error checking so I can trap a timeout from the stored proc (as detailed here)

[Link...]

This works if the stored proc just contains a "WAITFOR DELAY", but doesn't seem to throw an error when the stored proc contains long-running sql. Anyone have any ideas?

Passing Variables To Stored Procedure
I have an asp search page that passes the variables to a stored procedure for sql server. whenever i have a blank variable (like searching for last name only), i get this error:

Line 1: Incorrect syntax near ','.

This is my code for the page:

<%
lastname=Trim(Request.QueryString("LastName"))
firstname=Trim(Request.QueryString("FirstName"))
strSQL = "EXEC sp_HCDAIS_BanSearch "& lastname & ", " & firstname
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn
%>

........

Passing Parameters To Stored Procedure
I have several parameters that I need to pass into a stored procedure. Each parameter holds a comma delimited list.

I tried splitting the parameters and executing the stored procedure doing the following:

Dim CmtIdLoop
CmtIdLoop = Split(Request.Form("commentsID"),", ")
revwStatLoop = Split(Request.Form("review_status"),", ")
abrstatLoop = Split(Request.Form("abr_status"),", ")
commentsLoop = Split(Request.Form("comments"),", ")
For l = 0 to Ubound(CmtIdLoop)
s = CmtIdLoop(l)
t = revwStatLoop
u = abrstatLoop
v = commentsLoop .....

Stored Procedure And Output Parameters
I have developed a Stored Procedure on SQL Server 7 this is it:

ALTER PROCEDURE TestOutput
@FieldReturnedint=0 OUTPUT
AS
select @FieldReturned = 12
--print @FieldReturned
return 1001

and made a test ASP page to call this procedure on ASP page

<%
Dim CommObj, ParmObj, rsResults, strColumns
set CommObj = Server.CreateObject("ADODB.Command")
set rsResults = Server.CreateObject("ADODB.Recordset")
strColumns = 1

'rsResults.Open "TestOutput " & strColumns ,Conn

CommObj.ActiveConnection = Conn
CommObj.CommandText = "TestOutput"
CommObj.CommandType = adCmdStoredProc
'CommObj.Parameters.Refresh

'CommObj.Parameters.Append CommObj.CreateParameter("@Data", adInteger,adParamInputOutput)

'set ParmObj = CommObj.CreateParameter("@FieldReturned", adInteger, adParamOutput,, 1)
'CommObj.Parameters.Append ParmObj
Set ParamOut=CommObj.CreateParameter("@FieldReturned", adInteger,adParamInputOutput ","0")
CommObj.Parameters.Append ParamOut
'Set ParamRet=CommObj.CreateParameter ("Return",adInteger ,adParamReturnValue )
'CommObj.Parameters.Append ParamRet
'CommObj.Prepared = true

set rsResults = CommObj.Execute()
'strColumns = CommObj.Parameters("@FieldReturned").Value 'CStr(ParmObj) '

Response.Write "Returned Columns:[ " & CommObj.Parameters(0).Value & " ]"
%>

yet.......it does not type the output parameter......

it does not give an exception...just empty value for the output parameter

What do you think the problem is....?

Choose Recordset In Stored Procedure?
I have a SQL 2000 stored procedure with several recordsets. Is there a way to query a specific recordset in a stored procedure, or will I have to loop through each recordset?

Use Of Varchar(max) - Stored Procedure Parameter
I need to use varchar(max) for my out parameter (@resultText) from the 4000 length it is currently using. When i change the length to -1 or drop it altogether i get an error, how can i fix this issue? Code:

Stored Procedure Null Value Problem
I have a stored procedure that inserts data into my SQL Server Database. Some of the fields might be NULL sometimes, so I have set allow null to the appropriate coloums in my database.

But when I submit my form I get an error as follows...

COM Error Number -2147217900 (0x80040E14)
File Name /includes/contests.asp
Line Number 201
Brief Description [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function 'Contests_InsertRegistrants' expects parameter '@CustomQuestion5', which was not supplied.
Code:

Stored Procedure Parameters Generator
there used to be a script by Bob Barrows which generated the vbscript for
a stored procedure. The web site isn't there any more. Can anyone point me
somewhere else?

Call Oracle Stored Procedure
I'm trying to call a stored procedure(package) from asp/vb using following
code but I got the error message. What's the correct syntax for oracle stored procedured? Code:

Stored Procedure Parameters (text)
I have a stored procedure that takes parameter @description with datatype set to text.

In my ASP I have the following:

..Parameters.Append
..CreateParameter("@description",adVarChar,adParamInput,255,varDesc))

I've seen other examples of SPs with adVarChar set to 1000 and up! Is this the way it should be done or is there a matching parameter type I should be using?

Crystal Report: Stored Procedure
I've created a SQL Server stored procedure that uses a temporary table
(#tempTable) to run some sub-queries within the main SELECT statement.
This works perfectly fine within the Crystal Designer environment
(version 9.0). However, when I run this report via an ASP web page the
fields that are using the temporary table show up blank, while the
other fields show up fine that are not referencing this temporary
table. Why does it work fine in the design environment but not on the
ASP web page? I can provide more details if necessary.

Execute A Stored Procedure From ASP Page
I have written a stored procedure that take 5 - 8 minutes to fully execute..
I wrote this routine as a stored procedure because I started to create all
the functionality via ASP but I kept getting time out errors on my ASP page.

The problem is that when I call the stored procedure from the ASP page the
server waits for the procedure to complete before returning the ASP page..
Is there any way to avoid this? I would like the user to be able to trigger
the stored procedure and have the page returned right away..

Can't Call Oracle 9i Stored Procedure From ASP
I'm trying to call a stored procedure(package) from ASP/VB using following code. But I got the error message. Code:

Stored Procedure, Could Not Open 'http....'
I am running a stored procedure from an asp page. After the sp runs, it should export into Excel. However, I get an error 99% of the time that says: Could not open 'http://...... ' (name of the report and its location). Could it be timing out? I am not sure that would be (but it could be) as I am not getting that error message. Microsoft Excel is also saying it could be:

-->file name or path does not exist
-->The file is being used by another program
-->The workbook you are trying to save has the same name as a currently open workbook.

None of these seem to be true. Is anyone familiar with this type of error? What code can I offer for someone to take a look at it? Would you need to see the ASP portion or more than likely, the SQL portion? Any help to get this working would be appreciated.

UPDATE -- I can get the stored procedure to run inside of Analyzer, as it takes nearly 7 minutes to render the report. How can I alter the asp to not time out or to not give the error mentioned above?

Calling Stored Procedure In ASP Script
i am getting an error when i try to call a SP in ASP script. The code is as follows..

set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
conn.Open "DSN=Test;UID=sa;PWD=nmsglobal;"
Set cmd.ActiveConnection = conn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc

It gives me an error for the last line saying tht adCmdStoredProc= empty. if nebody knows the solution.

Reading Linked Server Stored Procedure
I've got a stored procedure which returns a select query on a linked server. This works fine if i call within SQL and Query Analyser but if i try and execue to return data to a ASP recordset i get the ridiculous

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Am i missing something really basic like a simple setting or is it just not possible.

Heres my ASP code: ...

Error While Calling Stored Procedure From Asp Page
I am trying to run an example code from a book. However I am getting the following error message:

Number: -2147217900
Description: Syntax error or access violation

Source: Microsoft OLE DB Provider for SQL Server

SQLState: 42000

NativeError: 0

The following is the code that is being used in the asp page:....