How Do You Pass input parameters to a Stored Procedure Using an ASP Input Screen?

Question

How Do You Pass input parameters to a Stored Procedure Using an ASP Input Screen?

~RobertGulledge

Old Hand

Points: 325
More actions
February 24, 2004 at 12:34 pm

#107431

I am a newbie to SQL and ASP programming. I want to create an ASP program that will allow users to input two pieces of data
into an SQL database from Internet Explorer.
My first program takes the two pieces of information and assigns them temporary variable names (Firstname) (LastName)
My second program needs to be able to call a stored procedure that will take the temporary variables and insert them into a new row in a table.
Here is my connectivity code in ASP:
<%
' Set up the DB Connection and the Recordset
Set Cm = Server.CreateObject("ADODB.Command")
Set Rs = Server.CreateObject("ADODB.RecordSet")
Cm.ActiveConnection = "Driver={SQL Server};Server=xxx.xxx.xxx.xxx;Database=WOSC"
Here is my SQL code in ASP
' Set up the Table
   sSQL = "EXEC prAdditem @Firstname @Lastname"
   Cm.CommandText = sSQL
   Cm.CommandType = 1
   Set Rs = Cm.Execute
%>
My Stored Procedure (prAdditem) is:
CREATE PROCEDURE prAddItem (@Firstname nvarchar, @LastName nvarchar) AS
INSERT INTO WOCS.dbo.Students (Firstname, Lastname) VALUES ('"& @Firstname @Lastname"')
Can someone help me out?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

I don't have time right now to look up the actual syntax but what you need to do is use the "Parameters" collection and add it to the command object.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Andy Ognenoff SSC Enthusiast Points: 174 More actions · Answer 2

Why the recordset object? It doesn't appear your SP returns any records to it? I would just use the Command Object and do as Gary has suggested and use the Parameters Collection of the command object.

Plus you get an added bonus of a little more security if you set your command object to be a CommandType of Stored Procedure rather than the dynamic SQL that you currently have.

Here is a nice little primer on how to use the parameters collection of the ADO Command Object with Stored Procedures:

http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

-Andy

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Three things to add to that article.

1. States "(and of course, if this is all you're doing, then don't forget the 5th/6th lines of code -- CLOSE the connection and set it to NOTHING!)"

Only close the connection and do not set to NOTHING if the site is going to be used frequently. The reason is when you set the object to nothing it is destroyed completely and connection pooling will not occurr. If not then if called again IIS will find the existing connection in the pool and opening it is faster because of this. After a period of time the connection will naturally expire from the pool anyway so it is not a concern to cleanup, unless you are not going to use again for long periods of time (next couple of hours or beyond).

2. If returning a recordset and have no need to keep the table locked make sure you set the Conection object CursorType to Client Side.

conn.CursorType = adUseClient

3. If you are using numeric tpyes there are additional parameter items to set for precision and scale. If you have fields that will not be populated you still have to create them in order of creation and set to default value or refresh the command object and set each one by ordinal position (sorry no example code on hand). And if you do set an item conditionally after creating the parameter and doing you coniditonal check you do

.parameter("paramName") = value

before you call the execute method.

Otherwise article covers most all items.

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 4

Question regarding Antares686's comment regarding adUseClient. If this is an ASP app reading from SQL Server, what would adUseClient do for you? Since ASP is all server-side, wouldn't all cursors be on the server? Does IIS and/or SQL do anything special with adUseClient versus the default?

Antares686 SSC Guru Points: 125444 More actions · Answer 5

First adUseClient offloads all the recordset to the memory of the calling application location (in the case of IIS it is the IIS server).

adUseServer retains much of the recordset in memory on the server side (or SQL side). If same box then it doesn't matter. If not then putting as much off on the client when possible will help the SQL Server out but if the recordset is too large for the client you may have no choice but to use adUseServer or leave alone since that is the default. More times than not the data is read back to the client for dispaly only, in that case using adUseClient will help performance SQL Side and show a major difference in loading of an ASP page. We have had several pages that do complex large queries take several minutes for the page to load because of load on the SQL Server (1gb of ram and cannot request more) due to loads and the fact there is a half gig of mostly unsed memory on our IIS server using adUseClient returns in seconds. You may still need to test both ways but for returning quick sets of data without more than display need you will generally get best performance from a client side cursor. However here are some good links (ones with cautions included).

http://www.midrangeserver.com/mgo/mgo121703-story01.html

http://www.sqlservercentral.com/columnists/awarren/introductiontoadopart2recordsets_printversion.asp

Andy Ognenoff SSC Enthusiast Points: 174 More actions · Answer 6

I have to disagree with the comment that you should always use a CursorLocation of adUseClient if returning a recordset. The cursor location and type is totally dependent on what you want to do with the records in the recordset.

You have to realize that if you use a Cursor location of adUseClient then the resultant CursorType needs to be adOpenStatic(which is way more resource intensive than a forward-only cursor type). If you need all the functionality of that type then by all means go for it. Here would be some reasons to use a CursorLocation of adUseClient:

You need to be able to move forward, backward, and set bookmarks in the recordset.
You need to use an ADO filter on the recordset.
You need an accurate count of the records in the recordset without having to move to the last record first.

If these things don't apply to your particular application, why incur the overhead of using adOpenStatic when a forward-only cursor would do the job?

I can agree that client side cursors can help with certain applications but to make a general statement that client side cursors improve performance is a little bit misleading.

~RobertGulledge Old Hand Points: 325 More actions · Answer 7

Thanks for the info, but I am afraid it is over my head.

What I need is some simple information (the simpler the better) that shows me the correct way to:

take input information (LastName, FirstName, SSN) that is passed from an ASP input file......

Convert it to variables that a Stored Procedure can recognize when it is called from within a second ASP file......(like "EXEC AddInfo........")

Andy Ognenoff SSC Enthusiast Points: 174 More actions · Answer 8

Ok...sorry to get off on a tangent. First, does your stored procedure need to return anything back to your second ASP page or is it just doing an insert then moving on?

What are the datatypes for your First Name, Last Name, and SSN as well as the size of those datatypes and I will try to write a parameterized command object for you.

- Andy

PhilM99 SSCrazy Points: 2214 More actions · Answer 9

'This approach uses the stored proc as a connection method.

Dim Conn ' As ADODB.Connection

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Driver={SQL Server};Server=xxx.xxx.xxx.xxx;Database=WOSC"

'You probably should really edit these Request variables first for validity

conn.prAdditem Request("Firstname"), Request("Lastname") 'or whatever your form variables are called

conn.Close

Set conn = Nothing

'Note in your stored proc you can return the number of rows affected which provides a good check to see if it worked as required.

Andy Ognenoff SSC Enthusiast Points: 174 More actions · Answer 10

As you've probably already found out, there are a lot of different ways to do things in ASP and ADO. Somethings to consider when choosing which way to do it is the possibility of SQL injection attacks as well as performance. Using a parameterized ADO Command Object reduces the risk of SQL injection attacks (note: doesn't mitigate the risk completely) much better than executing the command using a dynamic SQL string. Of course you can use a dynamic SQL string, it just isn't recommended, but a lot of people use them anyways because as you can probably see, its whole lot easier to write.

You'd want to validate any string variables you're pulling into this code, but I think this would do the trick for you given what you've already said about what your purpose is. Things change if you need to return records back from the stored procedure, especially if you are using OUTPUT parameters.

<%

Dim strConnectionString, cnSQLServer, cmdAddItem

'Change SERVERNAME, DBNAME, USERID, and PWD

strConnectionString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=USERID; Password=PWD; Network Library=dbmssocn;"

'Create Connection Object - this is useful if you have multiple recordsets

' or commands because you can reuse the same object

SET cnSQLServer = Server.CreateObject("ADODB.Connection")

cnSQLServer.Open strConnectionString

'Create the Command Object

SET cmdAddItem = Server.CreateObject("ADODB.Command")

cmdAddItem.ActiveConnection = cnSQLServer ' uses already opened Connection Object

cmdAddItem.CommandText = "prAddItem" ' this is the name of your SP

cmdAddItem.CommandType = adCmdStoredProc ' set the type to be a stored procedure

'this separates creating the parameter from setting the value of the parameter just for clarity

Dim cmdParam1, cmdParam2

Set cmdParam1 = cmdAddItem.CreateParameter("@Firstname", adVarChar, adParamInput)

cmdAddItem.Parameters.Append cmdParam1

cmdParam1.Value = strFirstName 'this is where you put your variable name you stored your First Name in

Set cmdParam2 = cmdAddItem.CreateParameter("@Lastname", adVarChar, adParamInput)

cmdAddItem.Parameters.Append cmdParam2

cmdParam1.Value = strLastName 'this is where you put your variable name you stored your Last Name in

'since you aren't returning any records with this SP, this is how you execute the Command Object

cmdAddItem.Execute, , adExecuteNoRecords

'then clean stuff up

SET cmdAddItem = Nothing

cnSQLServer.Close

SET cnSQLServer = Nothing

%>

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 11

Try something like this:

firstname = xxxxx ' however you set your local variable, such as from Request.Form

lastname = xxxxx ' ditto

Set cm = Server.CreateObject("ADODB.Command")

cm.ActiveConnection = "Driver....."

cm.CommandType = 4 ' adCmdStoredProc

cm.CommandText = "prAddItem"

cm.Parameters("@Firstname") = firstname

cm.Parameters("@Lastname") = lastname

cm.Execute

You'll see other examples where folks create Parameter objects and then append them to the Command object, but I've been doing SP parameters this way for years and it works like a champ. Of course, I also do comprehensive validity checking of my values before I try to pass 'em. Hope this helps...

~RobertGulledge Old Hand Points: 325 More actions · Answer 12

The stored procedure does not need to return anything....just insert the information into a new row (Note: there will be other fields in the row which will not be filled at this time)

Datatypes for all three variables is nvarchar (50)

Thanks for your help!

~RobertGulledge Old Hand Points: 325 More actions · Answer 13

Great Code!

But when I try it, I get the following error:

Microsoft OLE DB Provider for SQL Server (0x80004005)

Invalid connection string attribute

/wocsdata/form_ac.asp, line 12

Here is the way I have the code in ASP:

<%

Dim strConnectionString, cnSQLServer, cmdAddItem

'Change SERVERNAME, DBNAME, USERID, and PWD

strConnectionString = "Provider=SQLOLEDB; Data Source=xxx.xxx.xxx.xxx; Initial Catalog=WOCS; UserID=sa; Password=; Network Library=dbmssocn;"

'Create Connection Object - this is useful if you have multiple recordsets

' or commands because you can reuse the same object

SET cnSQLServer = Server.CreateObject("ADODB.Connection")

cnSQLServer.Open strConnectionString

'Create the Command Object

SET cmdAddItem = Server.CreateObject("ADODB.Command")

cmdAddItem.ActiveConnection = cnSQLServer

' uses already opened Connection Object

cmdAddItem.CommandText = "prAddItem"

' this is the name of your SP

cmdAddItem.CommandType = adCmdStoredProc

' set the type to be a stored procedure

'this separates creating the parameter from setting the value of the

'parameter just for clarity

Dim cmdParam1, cmdParam2, cmdParam3

Set cmdParam1 = cmdAddItem.CreateParameter("@Firstname", adVarChar, adParamInput)

cmdAddItem.Parameters.Append cmdParam1

cmdParam1.Value = strFirstName

'this is where you put your variable name you stored your First Name in

Set cmdParam2 = cmdAddItem.CreateParameter("@Lastname", adVarChar, adParamInput)

cmdAddItem.Parameters.Append cmdParam2

cmdParam1.Value = strLastName

'this is where you put your variable name you stored your Last Name in

Set cmdParam3 = cmdAddItem.CreateParameter("@SSN", adVarChar, adParamInput)

cmdAddItem.Parameters.Append cmdParam3

cmdParam1.Value = strSSN

'this is where you put your variable name you stored your SSN in