Re: Calling stored procedure from ASP with ADO

Stephen wrote:
I have a stored procedure as follows:
CREATE PROCEDURE ClaimDetails
(
@RANum NVARCHAR(10),
@PlanID NVARCHAR(9),
@AdjCode NVARCHAR(5)
)
AS


You should make it a standard practice to add "SET NOCOUNT ON" here.
It's got nothing to do with your problem this time, but will probably
prevent problems in the future.

SELECT Claim.BillingProviderID, LineItem.PIKey, LineItem.EIMLineNum,
<snip>


I am trying to call it and pass parameters and then return the
resulting recordset. The following code on my ASP page does not work:

set conTmp = server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")

You have no output parameters and don't seem to be interested in the
Return parameter, so you do not need an explicit Command object.

set rsTmp = Server.CreateObject("ADODB.Recordset")

'connection string is valid and set in global.asa
conTmp.open Application("EIMRemit_ConnectionString")

cmd.ActiveConnection = conTmp
This should be:
Set cmd.ActiveConnection = conTmp

cmd.CommandText = "ClaimDetails"
cmd.CommandType = adCmdStoredProc

Here is the first problem right here:
cmd.Parameters.Append cmd.CreateParameter("RANum", adVarChar, _
adVarChar is used for varchar parameters, not nvarchar.

Here is the second problem:
adParamReturnValue)
These are adParamInput parameters, not adParamReturnValue

cmd.Parameters.Append cmd.CreateParameter("PlanID", adVarChar, _
adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("AdjCode", adVarChar, _
adParamReturnValue)

cmd("RANum") = Request.QueryString("RA") 'RA number (string)

Always validate user inputs. It's very dangerous to directly use Request
collection values without validating them first.

cmd("PlanID") = Request.QueryString("PID") 'Provider number (string)
cmd("AdjCode") = Request.QueryString("Code") 'code (string)

set rstmp = cmd.Execute()


While you can certainly use an explicit Command object for this, I find
it much simpler to use the procedure-as-connection-method technique,
like this:

dim ra, pid, code
ra = Request.QueryString("RA")
'validate ra here
pid= Request.QueryString("pid")
'validate pid here
code= Request.QueryString("code")
'validate code here

' if everything is valid, then:
conTmp.ClaimDetails ra,pid,code, rstmp
if not rstmp.eof then
....

If you are married to the idea of using the explicit Command object, you
might find this code generator helpful (the price is right: free):
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

Here is some more reading material:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages