Fine-tune/improve Parametized query in asp?

I am trying to improve the robustness and elegance of my parametized sql
statements in ASP 3.0 as they get passed to the sql server SP.

Could anyone tell me if there are weaknessess in the way I have written the
following code? I have included both the asp code and the sql stored
proceducre to tie things togoether....I appreciate any advice on this. It
basically is a application to manage static news stories on our site by
tracking and organising the meta data in a table.

Many thanks for you comments.
ASP PARAMERT QUERY
----------------------------
If oRS.eof then
'// SAFE TO INSERT STORY....

CREATE Procedure spr_AddStory

oCmd.Parameters .append oCmd.CreatePara meter("StoryTit le", adVarChar,
adParamInput,10 0,pStoryTitle)
oCmd.Parameters .append oCmd.CreatePara meter("StoryURL ", adVarChar,
adParamInput,15 0,pStoryURL)
oCmd.Parameters .append oCmd.CreatePara meter("StoryBlu rb", adVarChar,
adParamInput,12 00, PStoryURL)
oCmd.Parameters .append oCmd.CreatePara meter("StoryBro kerID int", adInteger,
adParamInput,4, pStoryBrokerID)
oCmd.Parameters .append oCmd.CreatePara meter("StoryCom panyID int", adInteger,
adParamInput,4, pStoryCompanyID )
oCmd.Parameters .append oCmd.CreatePara meter("StoryCat egoryID int",
adInteger, adParamInput,4, pStoryCategoryI D)
oCmd.Parameters .append oCmd.CreatePara meter("StoryDep tID int", adInteger,
adParamInput,4, pStoryDeptID)
oCmd.Parameters .append oCmd.CreatePara meter("StoryKey word1", adVarChar,
adParamInput,50 ,pStoryKeyword1 )
oCmd.Parameters .append oCmd.CreatePara meter("StoryKey word2", adVarChar,
adParamInput,50 ,pStoryKeyword2 )
oCmd.Parameters .append oCmd.CreatePara meter("StoryKey word3", adVarChar,
adParamInput,50 ,pStoryKeyword3 )
oCmd.Parameters .append oCmd.CreatePara meter("RelatedU RL1", adVarChar,
adParamInput,15 0,pRelatedURUL1 )
oCmd.Parameters .append oCmd.CreatePara meter("RelatedU RL2", adVarChar,
adParamInput,15 0,pRelatedURUL1 )
oCmd.Parameters .append oCmd.CreatePara meter("RelatedU RL3", adVarChar,
adParamInput,15 0,pRelatedURUL1 )
oCmd.Parameters .append oCmd.CreatePara meter("StoryDat e datetime,
oCmd.Parameters .append oCmd.CreatePara meter("StoryIma geURL", adVarChar,
adParamInput,15 0,pStoryImageUR L)
oCmd.Parameters .append oCmd.CreatePara meter("StoryBLN int", adInteger,
adParamInput,4, pStoryBLN)

set oReturn = oCmd.CreatePara meter("u_id", adInteger, adParamOutput)
oCmd.Parameters .append oReturn
oCmd.execute()
'//RESULT...
if oReturn.value=-1 then
Response.write "FAILURE: Insert statement could not be carried out"
response.end

else

Response.write "SUCCESS: Insert statement was successfull"
End if
SQL SERVER STORED PROCEDURE
-------------------------------------------
CREATE Procedure spr_AddStory

@StoryTitle varchar(100),
@StoryURL varchar(150),
@StoryBlurb varchar(1200),
@StoryBrokerID int,
@StoryCompanyID int,
@StoryCategoryI D int,
@StoryDeptID int,
@StoryKeyword1 varchar(50),
@StoryKeyword2 varchar(50),
@StoryKeyword3 varchar(50),
@RelatedURL1 varchar(150),
@RelatedURL2 varchar(150),
@RelatedURL3 varchar(150),
@StoryDate datetime,
@StoryImageURL varchar(150),
@StoryBLN int
AS

INSERT INTO Story (StoryTitle, StoryURL, StoryBlurb, StoryBrokerID
,
StoryCompanyID, StoryCategoryID , StoryDeptID, StoryKeyword1, StoryKeyword2,
StoryKeyword3, RelatedURL1, RelatedURL2, RelatedURL3, StoryDate,
StoryImageURL, StoryBLN)
VALUES
(@StoryTitle,@S toryURL,@StoryB lurb,@StoryBrok erID,@StoryComp anyID,@StoryCat egoryID,@StoryD eptID,@StoryKey word1,@StoryKey word2,@StoryKey word3,
@RelatedURL1,@R elatedURL2,@Rel atedURL3,@Story Date,@StoryImag eURL,@StoryBLN)

GO

[Bob Barrows [MVP]]

ja***@catamaran co.com wrote:

I am trying to improve the robustness and elegance of my parametized
sql statements in ASP 3.0 as they get passed to the sql server SP.

Could anyone tell me if there are weaknessess in the way I have
written the following code?
Perhaps it would help if you tell us what problem you're trying to solve.
Since you need to read the value of the return parameter, this is the most
efficient way to execute your procedure.

A less efficient way, which is never recommended, is to use
cmd.Parameters. Refresh
instead of building the Parameters collection yourself. But since this
requires an extra trip to the database, it is not something you want to do.
I have included both the asp code and the sql stored
proceducre to tie things togoether....I appreciate any advice on
this. It basically is a application to manage static news stories on our
site
by tracking and organising the meta data in a table.

Many thanks for you comments.
ASP PARAMERT QUERY
----------------------------
If oRS.eof then
'// SAFE TO INSERT STORY....

CREATE Procedure spr_AddStory
? Is this a copy/paste error?

<snip> set oReturn = oCmd.CreatePara meter("u_id", adInteger, adParamOutput)
oCmd.Parameters .append oReturn

The Return parameter needs to be the FIRST parameter appended to the
Parameters collection. And the direction constant should be
adParamReturnVa lue, not adParamOutput. Output parameters are different from
Return parameters. Read this to see the difference:
http://groups-beta.google.com/group/...935bd7c531d82b
I'm not clear about why you need to read the Return parameter value. If
there is an error during the insert, it will be passed back to the
Connection object which will raise a vbscript error. Without the requirement
to read the return parameter value, your procedure can be executed more
simply (and efficiently) by:

on error resume next
conn.spr_AddSto ry,pStoryTitle, ..., pStoryBLN
if err<>0 and conn.errors.cou nt=0 then
'success
else
'failure
end if

See http://tinyurl.com/jyy0

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Wow, Bob...I have other questions relating to your last thread but for the
moment I just want to focus on this app of yours:

http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

Whoa...that is cool!

This is what it spat out for me (See below)...but... I still trying to figure
out whether or not to use either a:

1. Return value
2. Output value

I bascially just want to confirm that the insert statement is done correctly
and possibly return the unique identifier...wh ich is the correct route?
Dim cmd, param

Set cmd=server.Crea teObject("ADODB .Command")
With cmd
.CommandType=ad cmdstoredproc
.CommandText = "spr_AddSto ry"
set .ActiveConnecti on=cnSQL
set param = .createparamete r("@RETURN_VALU E", adInteger,
adParamReturnVa lue, 0)
.parameters.app end param
set param = .createparamete r("@StoryTitle" , adVarChar, adParamInput, 100,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryURL" , adVarChar, adParamInput, 150,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryBlurb" , adVarChar, adParamInput, 1200,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryBroker ID", adInteger, adParamInput, 0,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryCompan yID", adInteger, adParamInput,
0, [put value here])
.parameters.app end param
set param = .createparamete r("@StoryCatego ryID", adInteger, adParamInput,
0, [put value here])
.parameters.app end param
set param = .createparamete r("@StoryDeptID ", adInteger, adParamInput, 0,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryKeywor d1", adVarChar, adParamInput,
50, [put value here])
.parameters.app end param
set param = .createparamete r("@StoryKeywor d2", adVarChar, adParamInput,
50, [put value here])
.parameters.app end param
set param = .createparamete r("@StoryKeywor d3", adVarChar, adParamInput,
50, [put value here])
.parameters.app end param
set param = .createparamete r("@RelatedURL1 ", adVarChar, adParamInput, 150,
[put value here])
.parameters.app end param
set param = .createparamete r("@RelatedURL2 ", adVarChar, adParamInput, 150,
[put value here])
.parameters.app end param
set param = .createparamete r("@RelatedURL3 ", adVarChar, adParamInput, 150,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryDate ", adDBTimeStamp, adParamInput, 0,
[put value here])
.parameters.app end param
set param = .createparamete r("@StoryImageU RL", adVarChar, adParamInput,
150, [put value here])
.parameters.app end param
set param = .createparamete r("@StoryBLN" , adInteger, adParamInput, 0, [put
value here])
.parameters.app end param
.execute ,,adexecutenore cords
end with

[Bob Barrows [MVP]]

ja***@catamaran co.com wrote:

Wow, Bob...I have other questions relating to your last thread but
for the moment I just want to focus on this app of yours:

http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

Whoa...that is cool!

Thanks. You have the source code so you can customize it if you don't like
the names I used for the variables, etc.
This is what it spat out for me (See below)...but... I still trying to
figure out whether or not to use either a:

1. Return value
2. Output value

I bascially just want to confirm that the insert statement is done
correctly and possibly return the unique identifier...wh ich is the
correct route?

I did not see where you were returning a unique identifier in your original
procedure. Are you planning to rewrite the procedure? Are you asking for
help with that?

Because the value being returned is data, rather than a status or error
code, I would use an output parameter. Some would recommend returning it via
a SELECT statement, but I'm a little biased against using a recordset when I
have no need for cursor functionality.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

on error resume next
conn.spr_AddSto ry,pStoryTitle, ..., pStoryBLN

Well that's just wrong. it should say:

conn.spr_AddSto ry pStoryTitle, ..., pStoryBLN

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

conn.spr_AddSto ry pStoryTitle, ..., pStoryBLN

Ok. I have revamped my original code to include your comments and direction.
However, I am picking up the following error:

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/catamaranco/story/Process.asp, line 63

....which refers to this line: .CommandType=ad cmdstoredproc

'// INCOMING FORM ITEMS
'-----------------------

pStoryTitle = "My First Story"
pStoryURL = "http://www.catamarans. com/newsletter/staley/2005/07/"
pStoryBlurb = http://www.catamarans.com/newsletter/staley/2005/07/
pStoryBrokerID = "After a year of searching, Lei Ellen and Paul Beck found
their dreamboat. The Becks' new yacht is a beautiful '02 Voyage 440."
pStoryCompanyID = 1
pStoryCategoryI D = 1
pStoryDeptID = 1
pStoryKeyword1 = "Norseman"
pStoryKeyword2 = "43"
pStoryKeyword3 = "Sailing"
pStoryDate = "#05/23/2005#"
pStoryImageURL = "http://www.catamarans. com/images/SWCUA.jpg"
pStoryBLN = 1
PStoryModel="La goon"
pStorySize="60"
pYear ="1999"


'//INSERT FORM VARIABLES...
'--------------------------
Dim cmd, param

Set cmd=server.Crea teObject("ADODB .Command")

With cmd
.CommandType=ad cmdstoredproc '//Line 63
.CommandText = "spr_AddSto ry"

Set GetConnection = "driver={SQ L
Server};server= _.maximumasp.co m;DB=_pw;UID=V0 32U10DUW;PWD=_p w"

set .ActiveConnecti on=cnSQL
set param = .createparamete r("@RETURN_VALU E", adInteger, adParamOutput, 0)
.parameters.app end param
set param = .createparamete r("@StoryTitle" , adVarChar, adParamInput, 100,
pStoryTitle)
.parameters.app end param
set param = .createparamete r("@StoryURL" , adVarChar, adParamInput, 150,
pStoryURL)
.parameters.app end param
set param = .createparamete r("@StoryBlurb" , adVarChar, adParamInput, 1200,
pStoryBlurb)
.parameters.app end param
set param = .createparamete r("@StoryBroker ID", adInteger, adParamInput, 0,
pStoryBrokerID)
.parameters.app end param
set param = .createparamete r("@StoryCompan yID", adInteger, adParamInput,
0, pStoryCompanyID )
.parameters.app end param
set param = .createparamete r("@StoryCatego ryID", adInteger, adParamInput,
0, pStoryCategoryI D)
.parameters.app end param
set param = .createparamete r("@StoryDeptID ", adInteger, adParamInput, 0,
pStoryDeptID)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d1", adVarChar, adParamInput,
50, pStoryKeyword1)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d2", adVarChar, adParamInput,
50, pStoryKeyword2)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d3", adVarChar, adParamInput,
50, pStoryKeyword3)
.parameters.app end param
set param = .createparamete r("@StoryModel" , adVarChar, adParamInput, 150,
pStoryModel)
.parameters.app end param
set param = .createparamete r("@StorySize ", adVarChar, adParamInput, 10,
pStorySize)
.parameters.app end param
set param = .createparamete r("@StoryYear ", adVarChar, adParamInput, 10,
pStoryYear)
.parameters.app end param
set param = .createparamete r("@StoryDate ", adDBTimeStamp, adParamInput, 0,
pStoryDate)
.parameters.app end param
set param = .createparamete r("@StoryImageU RL", adVarChar, adParamInput,
150, pStoryImageURL)
.parameters.app end param
set param = .createparamete r("@StoryBLN" , adInteger, adParamInput, 0,
pStoryBLN)
.parameters.app end param
.execute ,,adexecutenore cords
end with

on error resume next
conn.spr_AddSto ry
StoryTitle,Stor yURL,StoryBlurb ,StoryBrokerID, StoryCompanyID, StoryCategoryID ,StoryDeptID,St oryKeyword1,Sto ryKeyword2,Stor yKeyword3,Story Date,StoryImage URL,StoryBLN,St oryModel,StoryS ize,StoryYear
if err<>0 and conn.errors.cou nt=0 then
response.write "success"
else
response.write "failure"
end if

[Aaron Bertrand [SQL Server MVP]]

Where do you define adcmdstoredproc ?

http://www.aspfaq.com/2112
<ja***@catamara nco.com> wrote in message
news:%2******** **********@TK2M SFTNGP14.phx.gb l...

conn.spr_AddSto ry pStoryTitle, ..., pStoryBLN

Ok. I have revamped my original code to include your comments and
direction. However, I am picking up the following error:

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/catamaranco/story/Process.asp, line 63

...which refers to this line: .CommandType=ad cmdstoredproc

'// INCOMING FORM ITEMS
'-----------------------

pStoryTitle = "My First Story"
pStoryURL = "http://www.catamarans. com/newsletter/staley/2005/07/"
pStoryBlurb = http://www.catamarans.com/newsletter/staley/2005/07/
pStoryBrokerID = "After a year of searching, Lei Ellen and Paul Beck found
their dreamboat. The Becks' new yacht is a beautiful '02 Voyage 440."
pStoryCompanyID = 1
pStoryCategoryI D = 1
pStoryDeptID = 1
pStoryKeyword1 = "Norseman"
pStoryKeyword2 = "43"
pStoryKeyword3 = "Sailing"
pStoryDate = "#05/23/2005#"
pStoryImageURL = "http://www.catamarans. com/images/SWCUA.jpg"
pStoryBLN = 1
PStoryModel="La goon"
pStorySize="60"
pYear ="1999"


'//INSERT FORM VARIABLES...
'--------------------------
Dim cmd, param

Set cmd=server.Crea teObject("ADODB .Command")

With cmd
.CommandType=ad cmdstoredproc '//Line 63
.CommandText = "spr_AddSto ry"

Set GetConnection = "driver={SQ L
Server};server= _.maximumasp.co m;DB=_pw;UID=V0 32U10DUW;PWD=_p w"

set .ActiveConnecti on=cnSQL
set param = .createparamete r("@RETURN_VALU E", adInteger, adParamOutput, 0)
.parameters.app end param
set param = .createparamete r("@StoryTitle" , adVarChar, adParamInput, 100,
pStoryTitle)
.parameters.app end param
set param = .createparamete r("@StoryURL" , adVarChar, adParamInput, 150,
pStoryURL)
.parameters.app end param
set param = .createparamete r("@StoryBlurb" , adVarChar, adParamInput,
1200, pStoryBlurb)
.parameters.app end param
set param = .createparamete r("@StoryBroker ID", adInteger, adParamInput,
0, pStoryBrokerID)
.parameters.app end param
set param = .createparamete r("@StoryCompan yID", adInteger, adParamInput,
0, pStoryCompanyID )
.parameters.app end param
set param = .createparamete r("@StoryCatego ryID", adInteger, adParamInput,
0, pStoryCategoryI D)
.parameters.app end param
set param = .createparamete r("@StoryDeptID ", adInteger, adParamInput, 0,
pStoryDeptID)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d1", adVarChar, adParamInput,
50, pStoryKeyword1)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d2", adVarChar, adParamInput,
50, pStoryKeyword2)
.parameters.app end param
set param = .createparamete r("@StoryKeywor d3", adVarChar, adParamInput,
50, pStoryKeyword3)
.parameters.app end param
set param = .createparamete r("@StoryModel" , adVarChar, adParamInput, 150,
pStoryModel)
.parameters.app end param
set param = .createparamete r("@StorySize ", adVarChar, adParamInput, 10,
pStorySize)
.parameters.app end param
set param = .createparamete r("@StoryYear ", adVarChar, adParamInput, 10,
pStoryYear)
.parameters.app end param
set param = .createparamete r("@StoryDate ", adDBTimeStamp, adParamInput,
0, pStoryDate)
.parameters.app end param
set param = .createparamete r("@StoryImageU RL", adVarChar, adParamInput,
150, pStoryImageURL)
.parameters.app end param
set param = .createparamete r("@StoryBLN" , adInteger, adParamInput, 0,
pStoryBLN)
.parameters.app end param
.execute ,,adexecutenore cords
end with

on error resume next
conn.spr_AddSto ry
StoryTitle,Stor yURL,StoryBlurb ,StoryBrokerID, StoryCompanyID, StoryCategoryID ,StoryDeptID,St oryKeyword1,Sto ryKeyword2,Stor yKeyword3,Story Date,StoryImage URL,StoryBLN,St oryModel,StoryS ize,StoryYear
if err<>0 and conn.errors.cou nt=0 then
response.write "success"
else
response.write "failure"
end if

[Bob Barrows [MVP]]

ja***@catamaran co.com wrote:

conn.spr_AddSto ry pStoryTitle, ..., pStoryBLN

Ok. I have revamped my original code to include your comments and
direction. However, I am picking up the following error:

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another.
/catamaranco/story/Process.asp, line 63

...which refers to this line: .CommandType=ad cmdstoredproc

What Aaron said. The constant needs do be defined somewhere. Where are you
defining the other constants you're using (adParam..., etc)?
Are you using the type library? Maybe you're using the wrong one.

<snip>

Set GetConnection = "driver={SQ L
Server};server= _.maximumasp.co m;DB=_pw;UID=V0 32U10DUW;PWD=_p w"

Why are you using the ODBC driver rather than the native OLE DB provider
(SQLOLEDB)? See www.connectionstrings.com or www.carlprothman.net for the
SQLOLEDB connection string to use.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Ok, that was stupid of me. Thank you.

I am now picking up a connection error:

'//INSERT FORM VARIABLES...
'--------------------------
Dim cmd, param

Set cmd=server.Crea teObject("ADODB .Command")

With cmd
.CommandType=ad cmdstoredproc
.CommandText = "spr_AddSto ry"

cnSQL = "driver={SQ L
Server};server= MAXSQL008.maxim umasp.com;DB=V0 32U10DUW;UID=V0 32U10DUW;PWD=cU 3QmtgDyzWJ"
'//Line 69
Microsoft VBScript runtime (0x800A01A8)
Object required: 'cnSQL.ActiveCo nnection'
/catamaranco/story/Process.asp, line 69

[Aaron Bertrand [SQL Server MVP]]

> Microsoft VBScript runtime (0x800A01A8)

Object required: 'cnSQL.ActiveCo nnection'
/catamaranco/story/Process.asp, line 69

Sheesh. Are you grabbing bits and fragments of code from all over the place
and just throwing them together? The following is a string and should not
be part of a SET assignment:

Set GetConnection = "driver={SQ L
Server};server= _.maximumasp.co m;DB=_pw;UID=V0 32U10DUW;PWD=_p w"

I assume you meant

Set cnSQL = CreateObject("A DODB.Connection ")
cnSQL.open "driver={SQ L
Server};server= _.maximumasp.co m;DB=_pw;UID=V0 32U10DUW;PWD=_p w"
' then...
Set cmd.ActiveConne ction = cnSQL

conn is the traditional name for a connection object in ASP, and you'll find
that most people use it (any other name is slightly more cumbersome to
read/analyze, IMHO) and you should certainly be using SQLOLEDB instead of
the SQL Server driver, see http://www.aspfaq.com/2126