Executing SQL Stored Proc And Returning XML Through ADO In ASP Page ??
I need to return XML from a SQL Stored Procedure which I will execute through ASP ADO code. Stored Procedure as below:
CREATE PROCEDURE pr_GetJobInfo2
@JobType int,
@CntryID int,
@JobTitle varchar(8000) OUTPUT
AS
SELECT @JobTitle = ltrim(rtrim(JobTitle))
FROM dbo.JOBS
WHERE JobType = @JobType
AND CntryID = @CntryID
FOR XML AUTO
[code]
Now the output parameter (@JobTitle) cannot be assigned to FOR XML, so what must I do ??? My ASP code reads as: ....
View Replies
ADVERTISEMENT
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:
View Replies
View Related
I am trying to execute the following stored procedure from my asp page The asp page is passing a value "@Year" to the stored procedure ut seems to fall over with a declaration problem. Here is the stored procedure Code:
View Replies
View Related
I need to also add an AND after the where like
WHERE (Id = @CId)
and MAX(end) < NOW()
CREATE PROCEDURE dbo.[sp_rsgroup]
@Id int
AS
SELECT ISNULL(Group, '') AS GroupCalc, MIN(start) AS START, COUNT(Id) AS Count
FROM Table
WHERE (Id = @CId)
GROUP BY ISNULL(Group, '')
ORDER BY start
GO
View Replies
View Related
What is the simplest way to open up a stored query in Access - I'v got this far but I need to open: qry_Listings.
set cnn = Server.CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/listings.mdb") '//This one is for Access
2000/2002
cnn.Open strCon
View Replies
View Related
as I'm in the process of performance tuning, I'm trying to receive a NUMBER type array from a Stored Procedure.I made a VARRAY OF NUMBER in my stored procedure. I'm not able to receive this in a Function in ASP.
View Replies
View Related
I would like to call my stored proc and invoke the WITH RECOMPILE option. I can't figure out what to set in ADO to make that happen. how to make this work? BTW, I _don't_ want to create the stored proc using the WITH RECOMPILE.
View Replies
View Related
I need to open a Recordset with a dynamic cursor from a Stored Proc. Does anyone have the syntax?
View Replies
View Related
I am trying to pass a VBScript Date variable as value to an ADO adDate
parameter of a stored procedure (which runs in an SQL Server).
If I add the parameter like this:
oCmd.Parameters.Append oCmd.CreateParameter( "@p_Date", adDate,
adParamInput, , dParam)
where oCmd is of type ADODB.Command and dParam is a VBScript variable of
type Date
then when trying to execute the stored procedure I get the following error:
Optional feature not implemented
I was able to get round this by passing a string value in an adVarChar
parameter, but it would be easier and more readable with adDate and
Date-type variables. Is this latter possible?
View Replies
View Related
Is it possible to call a stored procedure asynchronously from an ASP page?
We would like to be able to execute a proc and have the page continue to
process without having to wait for the proc to finish. I know you can make
a dll and call it from there, but we'd rather just be able to keep it simple
and call it from the page if we can.
View Replies
View Related
I am using SQL SERVER 2005 and ASP.
Would anyone be kind enough to give me a solution to the problem below please?
If for example in the stored procedure looks like this:
-- Initialize the SQL Server XML stored procedures
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xmlString
Insert into @TableBook
SELECT * FROM OPENXML (@xmlDoc, 'books/book') WITH
(
bookNameVARCHAR(100) 'name',
authorVARCHAR(100) 'author'
)
The question is supposing there is a node called 'Surname' but I am not adding 'Surname' to 'Author'. If I want to check 'Surname' value DOES exists and I want to replace it with 'Author' or if 'Surname' does not exist, just use 'Author' node instead.
I am not sure how you can use IF statement to check a node and put the value into SET @surname and then put something like author VARCHAR(100) '@surname' OR author VARCHAR(100) '@Author'. Just like switching it around.
View Replies
View Related
I am trying to run the following t-sql stored proc from my VBScript page
Create Procedure getUserInfo
(
@login nvarchar(8)
)
AS
USE proj_dashboard
SELECT f_forename, f_surname, f_profile_id
FROM t_users
WHERE f_login = @login
basically I wasnt to pull in the login of the user pass it to the proc and pull out the info for that user. I use the following sql statement Code:
View Replies
View Related
I've got a problem with an ASP page which calls a stored proc. The sybase stored procedure returns numeric values as shown: ...
View Replies
View Related
I've created a Stored Procedure which adds a new record and updates some more records and then returns the primary key for the added record.
The SP seems to work OK, but I'm having problems getting at the returned key in my ASP code:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
A common error, but in this case I can't see why... I output the SQL instruction that is sent to the DB and have run it in Query Analyser and it runs OK and returns a single row, with a single column - the new primary key...
But when I try and access this in ASP, I get the error message....
View Replies
View Related
Does anyone know how ASP calls a stored procedure in ORACLE ? I want the stored procedure to return a recordset bact to the ASP environment.
Can a stored procedure in ORACLE return a recordset to ASP. If so can you direct me to the necessary syntax that can be useful for this ?
View Replies
View Related
Is it possible to add a stored proc to a table adapter and execute the proc
inside an ASP button click event?
I have a table adapter called Claimers and a sp called AddWatch. On the
onclick event I would like to call the table adapter and execute the sp.
Note that the sp does not return records. Code:
View Replies
View Related
set rs = Server.CreateObject("ADODB.Recordset")
objConn.usp_RetrieveCategories rs
Method 2:
set rs = objConn.Execute("usp_RetriveCategories")
Which method is considered to efficient. Is it method 1 or method 2?.
The stored procedures returns no values.
View Replies
View Related
i have this script that loops through the variable in request.form("values") and based on this executes a Stored proceedure. Stored proceedure is in the include file. (Which basically does an insert) To make it neat
However, the loop is successful. But the SP only inserts into my db once.
What could be wrong here ?
Code:
<%aTest = Split(request.form("values"), ",")
For i = 0 to UBound(aTest)
%>
<!--#include file="../../../scripts/objects/send_sms.asp" -->
<%Next%>
View Replies
View Related
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:
View Replies
View Related
(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:
View Replies
View Related
I have a file called Audit.asp that I want to execute via command line from a batch file (bulk.bat)How can I do this?
View Replies
View Related
Is there a way to determine the filepath of an ASP script being executed, like so? First file contains:
<%
For Each x In arrNuggets
Server.Execute "/nuggets/" & x & "/default.asp"
Next
%>
The executed file, default.asp, needs to be able to figure out what it's parent folder name is.
Request.ServerVariables("SCRIPT_NAME") only returns the name of the first initial page loaded, and nothing about the page currently being executed.
View Replies
View Related
Is there a function in ASP that can stop the remainder of the page from executing?
View Replies
View Related
i have a form that action is set to itself, on the same page i m having the insert statement, executed when i submit that form, but when the user refresh that page the query is executed and submit the null values in the table.
<form name=form1 action=form1.asp method=post>
<input type........
<input type.......
<input type=submit value....>
<input type=hidden name='insert' value=1>
</form>
<%
if request.form("insert") = 1 then
Execute the insert query
End If
%>
View Replies
View Related
I am trying to have my asp page direct the user to the page that called the current page, but the only code that I have found is on the MIcorsoft MSDN Knowledge base and it won't work.
View Replies
View Related
I’ve been racking my brains trying to figure out why when I call a record set back with a SELECT * it only brings back some of the fields. It use to be connecting to an access 2000 DB and worked fine but I’ve converted it over to SQL. I’ve installed SQL server DEV edition and imported the data. The desktop is running XP.
Has anyone got any ideas why it only picks some of the fields in the record set and not others? O I’ve tried timeout = 0 on the connection already..
View Replies
View Related
I have very limited asp knowledge so I just use the built in dreamweaver functions at work. I've done an update recordset before but this one as me baffled.
When trying to modify the article and update the database the upodate doesn't work, the page if succesful should redirect to the news admin. I've searched for about the last hour but no joy. The code is as follows: Code:
View Replies
View Related
---------------------------------------------
The Execute method allows you to call another ASP page from inside an ASP page. When the called ASP page completes its tasks, you are then returned to the calling ASP page. The overall effect is very similar to a function or subroutine call. Any text or output from the called ASP page will be displayed on the calling ASP page. The Execute method is a more useful alternative to using server-side includes.
In contrast, the Transfer method allows you to transfer from one ASP page to another without returning to the calling ASP page.
There is one mandatory argument.
---------------------------------------------
Fair enough does what I need it to. However, I've run into a glitch. I understand the point about it being like calling a sub which means anything set within the executed page will be considered local and so anything set within it won't work in the calling page. What I don't understand is why the executed page can't read objects which have been set in the calling page before the execute???
View Replies
View Related
What is a faster/better coding practice?
Method 1:
Code:
Sub myFunction(byRef x)
x = x + 1 ' do something with x
End Sub
x = 7
myFunction(x)
response.write x ' shows 8
Method 2:
Code:
Function myFunction(x)
myFunction = x + 1 ' do something with x
End Function
x = 7
response.write myFunction(x) ' shows 8
Also discuss considerations when there are more than one variables that need to be changed.
View Replies
View Related
I've started using stored procedures but I have a problem. My stored proc works fine but when I want to use it to list results on several pages I get an error. My previous code:
View Replies
View Related
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..
View Replies
View Related
I have a stored procedure named as
usp_CheckLogin with two parameters
as @usID, @Password
also values of these parameters are to be extracted using Request.Form from
the fields in the forms. Now How do I call this stored procedure from an ASP
Page using a Connection object in Server Tags <% %>. Also this stored
procedure returns a Numeric value as "RETURN_VALUE". please give me the
syntax. This stored Procedure is a part of Session Management module.
View Replies
View Related
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:....
View Replies
View Related