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.
Just a quick question. I am using Sql Server 2000 and calling stored procedures from asp.
At this stage i am forgoing using the ADO command object and am simply create dynamic like sql statement for store procedure execution.
My question is, I find the code below quite messy, particularly when it comes to checking for option parameters using if statements. Is their a better way to write this, keeping in mind i have to check in a value exists before adding the appropriate parameter
to the stored procedure call.
Is there are more elegant way?....
A while back people were looking into stored procedures and code to list which ones exist
Does anyone know the code to list all the stored procedures in a my sql database?
could anyone provide me with some simple example code of asp classic
calling adodb? specifically, i'd like the code to:
- call a couple of stored procedures that have optional parameters
- share a connection over the two calls
If I have a html table with, lets say three columns, and I have to fill each column with different data from database, could I use a stored procedures to write three separate sql queries?All I need than is to create a loop for each column.
I have never tried before writing a code with more than one sql query, and that isn't working out for me this time. I've read about this stored procedures. I don't know if that's the answer.
Can anyone gives me an example how to use stored procedures in ASP. I know how to create views but he must I call a stored procedure ?
View Replies View RelatedIn order to access return values and output parameters from a stored procedure, the recordset must first be closed. Is this correct? If so, can someone please shed some more light on ADO, stored procedures, their parameters and recordsets. Specifically, I've seen some code that accesses the output parameters and return values by switching to the next recordset.
[vbs]
Set adoRS = adoRS.NextRecordset
[/vbs]
and then some other funny stuff ...
[vbs]
Response.Write "<p>Return value = " & CmdSP.Parameters("RETURN_VALUE").Value & "</p>"
[/vbs]
Is a second recordset always created when you have output parameters and/or return values? And what are these properties of the parameter object? How do you guys work with ADO?
I am trying to send some emails that are stored in a SQL database to a variable within my ASP page. Once the emails have been gathered and stored in the variable (as a string of emails) I want to send an email to those emails using that variable.
I'm trying to accomplish this by using a stored procedure.
Here is how my SP is set up in sql enterprise manager:
Code: ....
I am trying to use a stored procedure created in SQL Server 2005 and execute it in ASP. How can I return the entire recordset from the stored procedure the way I can do it with a recordset?
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = Conn
' Specify the name of the stored procedure you wish to call
cmd.CommandText = "sp_Org_Structure"
'Form variable to pass as a paramter to the SP
strUI = 4655
sql = "EXECUTE sp_Org_Structure " & strUI
response.write sql
Secondly, instead of hard-coding the name of the SP in the sql statement, how I call the 'cmd.CommandText' from within this SQL Statement.
I get the error:
Microsoft OLE DB Provider for SQL Servererror '80040e37'
Invalid object name 'compmatrix_divcontrols'. /compliancematrix/qocdx.asp, line 13
my asp code begins with: ....
i have some stored procedures written in asp can i use the same stored
procedures in my .net web application.
I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just about all you can imagine within the product. I just personally rewrote/reformatted close to 150 of them myself. Nothing too fancy, mostly a lot of formatting. We have a little down time between Q/A and fixing any bugs they find so I decided to test the security of the site with Cross-Site Scripting and SQL injection. Because SP's are used in pretty much every aspect of the site, the site isnt vunerable to the simple Injection stuff like '
OR 1 = 1 . Instead I've had to try several things which I will get to in a minute. As I have access to all of these SP's and the dB in which they reside, I could easly look at the code and determine what variables are neede to pass in. I've been trying to break the site to give up a database name so I can proceed to check the system tables etc etc - much like a regular hacker would (meaning not cheating by looking at the code). So, I've started my attack by downloading a local version of the login page, changing a few variables and injected somthing like:
'SELECT * FROM INFORMATION_SCHEMA.TABLES
Initially, the statement is passed into the form fields and wont return anything other than a JS popup error. I altered the statement intentionally to break the page
SELECT * FROM INFORMATION_SCHEMA.TA'
and it produces an error giving me the name of the query and its location when I plug the name of the query in the url i.e http://theurl.com/query_folder/query_name/ it fires me off to another folder containing an include file of some sort. When I run that include it gives me the name of a pretty important variable. Apparetly its a variable that determins what dB I should point to.
Now, one of the issues I have is that everything is a stored procedure so trying to hack it is a little more difficult ....
Is there any way of viewing a list of stored procedures from a database.for example i state i which database i am looking at and it returns a list of stored procedures.
View Replies View RelatedHThe aspfaq.com seems to really push stored procedures, and I hear the same advice here all the time. So I want to take the advice.
Is it possible to create and practically maintain, delete, use, etc. stored procedures soley from asp (i.e., no GUI or console- like being hosted on Brinkster)?
The tutorial on aspfaq.com mentions that stored procedures can be created from asp code- how? Do you just send the stored procedure you'd type into the GUI with oConn.execute() instead?
I use stored procedures in my asp using the connection object. I validate any inputs to protect myself from SQL injection. Why is it, or isn't it better to use the command object? I have used the command object with parameters and the coding was a pain.
Comments?? I realize this is an open ended question but I am trying to improve my skills/code if need be.
I am working with a sql server developer to create reports. He writes the stored procedures I do the web piece to display them on web.
At the moment I am having problems getting the record set from the stored procedure to display properly.
what I get is: (the table is repeated on purpose - I want to consolidate the IRS01 to single line [second table])
Appropriate vs Reimbursed FTE's for 2005 .....
I'm having trouble calling a stored procedure. For some reason I keep getting this error.
--------------------------------------------------------------------
ADODB.Command error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
---------------------------------------------------------------------
Here is the code: .....
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 RelatedI am currently trying to pass a value from a url to a sqldatasource control. There seems to be a lack of information on how to do this. I would greatly appreciate the help.
I am passing a variable "dept" through the url (i.e. products.aspx?dept=23) and trying to insert it into the Where part of my SQL statement.
The stored procedure is defined as follows: Code:
I use dynamic SQL queries in my sites. I know this is bad, but I grew up using Access as a data source and learned bad habits.
One of my MsSQL Server databases has just been hacked, despite me stripping common words/punctuation from the SQL statements - which I thought would prevent injection attack.
What I currently do is this (using three functions to strip out HTML, quotation marks and T-SQL code)
Code: .....
Is SQL injection an issue with SP's?
View Replies View RelatedI have two stored procedures:
1. LoadFile - Loads data from a file to a table...which can take up to 10 minutes if the file is large enough. While loading, it periodically updates a record in a "load summary" table with the total number of records to load, the number of records currently loaded and the status of the load.
2. GetLoadSummary - Gets the record from the "load summary" table.
My hope was that in ASP, I could call LoadFile within one FRAME and then on a timer within another FRAME call GetLoadSummary to create a progress bar for the user.My problem is that when I call LoadFile, I can't seem to execute any other stored procedures until it finishes.
I am calling some stored procedures from ASP. These strored procedures have to deal with lots of deletes and updates. So i have thought of implementing transaction commits and rollbacks.
But if a rollback occurs in these stored procedures, i want to get a value back to asp page, based on this value i will run the next stored procedure.
It loops in order to get data in different, sequential date ranges. I.E. from 9/1/2000 - 10/1/2000 then 10/1/2000 - 11/1/2000 etc etc etc. It calls SPs using the 2 dates and an integer used for companyid reference.
Let's just do this for 2 SP's (there are like 6 on the page.) One SP has 3 params, one has only 2.
Now, the first iteration of the loop, it works. (because I'm response.writiting out the dates it's using to verify they are ok. The second time through I get the following error when I try to execute the following ASP: .....
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%>
When some of the SQL Server stored procedures I have written are called via
my Classic ASP page I have written I get the following error in the cell that
is supposed to be retrieving a single result:
"ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/Default.asp, line 130"
I have no idea why this could be occuring. Some of the other stored
procedures work just fine. Any one have any ideas?
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:
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:
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: ....
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 RelatedIf I need to use a procedure call when clicking on a form button of an "ASP" page instead of submitting the form.
<Input type="Button" name="btnInsert" value="Insert" OnClick="btnInsert_Click()">
<%
Sub btnInsert_Click()
' I need to open a table and insert the data I collected in a form
EndSub
%>
When clicking I received an error "Object Expected". What is wrong in the code.
Should I inclose the btnInsert_Click() within the <Script> tag?
I'm pretty new to ASP so this may seem like a stupid question but
is there anyway I can use something like an OnClick or OnChange
sub procedure similar to the ones you can use with VBS or JS?
I have a VB script Sub() that is used by several ASP pages. How do I make it
globally available rather than having to replicate the source code in each
ASP page that uses it?