I 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:
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?
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.
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 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: .....
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 want to call a recordset so you can edit the details and then obviously pass the parameters onwards into the db. However, the user guide just says this:
To process other fields in the form, use the Form collection of the upload object the same way you would use the Form collection of the Request object. For example, if your form had a text element named UserName, your processing code would include: Code:
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?
View Replies View RelatedI'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: ....
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
%>
........
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 .....
I want to know the Cookie values are stored in which location?
how can i recall the Cookie value?
is it possible to store the cookie value to the Particular location?
if i stored the cookie value to the particular location, any possiblities to conflict the location of other cookie values...
I am attempting to pass null as the input value from a series of
textboxes if the user does not input a value prior to submit. To try and do
this, I am using a vbscript function on this asp as follows:
CommentsAdd = IIf(Request.Form("Comments")="",NULL,Request.Form("Comments"))
I was hoping this would convert those emptystring textboxes to null.
Then, these variables are input in an update stored procedure called from
the same asp upon submit. This handles ntext and like fields in the table I
am updating, but a null value passed in this way for a numeric field chokes.
The error on the asp is something like, "unable to convert value of type
text to numeric". Can't null be passed as a parameter value when the field
type is numeric?
How does one pass null for a parameter's value in a stored procedure on an
asp? Am I missing something here?
I'm trying to pass parameters from an ASP (VBScript) web page to a Stored Procedure in SQL Server. I know how to do the Stored procedure but it's passing the parameter from the web page that is stumping me.
View Replies View Related