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.CreateParameter("StoryTitle", adVarChar,
adParamInput,100,pStoryTitle)
oCmd.Parameters.append oCmd.CreateParameter("StoryURL", adVarChar,
adParamInput,150,pStoryURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBlurb", adVarChar,
adParamInput,1200, PStoryURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBrokerID int", adInteger,
adParamInput,4,pStoryBrokerID)
oCmd.Parameters.append oCmd.CreateParameter("StoryCompanyID int", adInteger,
adParamInput,4,pStoryCompanyID)
oCmd.Parameters.append oCmd.CreateParameter("StoryCategoryID int",
adInteger, adParamInput,4,pStoryCategoryID)
oCmd.Parameters.append oCmd.CreateParameter("StoryDeptID int", adInteger,
adParamInput,4,pStoryDeptID)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword1", adVarChar,
adParamInput,50,pStoryKeyword1)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword2", adVarChar,
adParamInput,50,pStoryKeyword2)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword3", adVarChar,
adParamInput,50,pStoryKeyword3)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL1", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL2", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL3", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("StoryDate datetime,
oCmd.Parameters.append oCmd.CreateParameter("StoryImageURL", adVarChar,
adParamInput,150,pStoryImageURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBLN int", adInteger,
adParamInput,4,pStoryBLN)

set oReturn = oCmd.CreateParameter("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,
@StoryCategoryID 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,@StoryURL,@StoryBlurb,@StoryBrokerID,@StoryCompanyID,@StoryCategoryID,@StoryDeptID,@StoryKeyword1,@StoryKeyword2,@StoryKeyword3,
@RelatedURL1,@RelatedURL2,@RelatedURL3,@StoryDate,@StoryImageURL,@StoryBLN)

GO

Bob
Fri Jul 08 10:36:13 CDT 2005

jason@catamaranco.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.CreateParameter("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
adParamReturnValue, not adParamOutput. Output parameters are different from
Return parameters. Read this to see the difference:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b


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_AddStory,pStoryTitle, ..., pStoryBLN
if err<>0 and conn.errors.count=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"

jason
Fri Jul 08 11:22:56 CDT 2005

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/index.asp?pi=links&hp=links.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...which is the correct route?
Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spr_AddStory"
set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@StoryTitle", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@StoryURL", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@StoryBlurb", adVarChar, adParamInput, 1200,
[put value here])
.parameters.append param
set param = .createparameter("@StoryBrokerID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryCompanyID", adInteger, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@StoryCategoryID", adInteger, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@StoryDeptID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword1", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword2", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword3", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@RelatedURL1", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@RelatedURL2", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@RelatedURL3", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@StoryDate", adDBTimeStamp, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryImageURL", adVarChar, adParamInput,
150, [put value here])
.parameters.append param
set param = .createparameter("@StoryBLN", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

Bob
Fri Jul 08 12:34:01 CDT 2005

jason@catamaranco.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/index.asp?pi=links&hp=links.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...which 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
Fri Jul 08 12:35:12 CDT 2005

Bob Barrows [MVP] wrote:
> on error resume next
> conn.spr_AddStory,pStoryTitle, ..., pStoryBLN

Well that's just wrong. it should say:

conn.spr_AddStory 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"

jason
Fri Jul 08 13:20:48 CDT 2005


>
> conn.spr_AddStory 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=adcmdstoredproc

'// 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
pStoryCategoryID = 1
pStoryDeptID = 1
pStoryKeyword1 = "Norseman"
pStoryKeyword2 = "43"
pStoryKeyword3 = "Sailing"
pStoryDate = "#05/23/2005#"
pStoryImageURL = "http://www.catamarans.com/images/SWCUA.jpg"
pStoryBLN = 1
PStoryModel="Lagoon"
pStorySize="60"
pYear ="1999"




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

Set cmd=server.CreateObject("ADODB.Command")

With cmd
.CommandType=adcmdstoredproc '//Line 63
.CommandText = "spr_AddStory"

Set GetConnection = "driver={SQL
Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"

set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger, adParamOutput, 0)
.parameters.append param
set param = .createparameter("@StoryTitle", adVarChar, adParamInput, 100,
pStoryTitle)
.parameters.append param
set param = .createparameter("@StoryURL", adVarChar, adParamInput, 150,
pStoryURL)
.parameters.append param
set param = .createparameter("@StoryBlurb", adVarChar, adParamInput, 1200,
pStoryBlurb)
.parameters.append param
set param = .createparameter("@StoryBrokerID", adInteger, adParamInput, 0,
pStoryBrokerID)
.parameters.append param
set param = .createparameter("@StoryCompanyID", adInteger, adParamInput,
0, pStoryCompanyID)
.parameters.append param
set param = .createparameter("@StoryCategoryID", adInteger, adParamInput,
0, pStoryCategoryID)
.parameters.append param
set param = .createparameter("@StoryDeptID", adInteger, adParamInput, 0,
pStoryDeptID)
.parameters.append param
set param = .createparameter("@StoryKeyword1", adVarChar, adParamInput,
50, pStoryKeyword1)
.parameters.append param
set param = .createparameter("@StoryKeyword2", adVarChar, adParamInput,
50, pStoryKeyword2)
.parameters.append param
set param = .createparameter("@StoryKeyword3", adVarChar, adParamInput,
50, pStoryKeyword3)
.parameters.append param
set param = .createparameter("@StoryModel", adVarChar, adParamInput, 150,
pStoryModel)
.parameters.append param
set param = .createparameter("@StorySize", adVarChar, adParamInput, 10,
pStorySize)
.parameters.append param
set param = .createparameter("@StoryYear", adVarChar, adParamInput, 10,
pStoryYear)
.parameters.append param
set param = .createparameter("@StoryDate", adDBTimeStamp, adParamInput, 0,
pStoryDate)
.parameters.append param
set param = .createparameter("@StoryImageURL", adVarChar, adParamInput,
150, pStoryImageURL)
.parameters.append param
set param = .createparameter("@StoryBLN", adInteger, adParamInput, 0,
pStoryBLN)
.parameters.append param
.execute ,,adexecutenorecords
end with

on error resume next
conn.spr_AddStory
StoryTitle,StoryURL,StoryBlurb,StoryBrokerID,StoryCompanyID,StoryCategoryID,StoryDeptID,StoryKeyword1,StoryKeyword2,StoryKeyword3,StoryDate,StoryImageURL,StoryBLN,StoryModel,StorySize,StoryYear


if err<>0 and conn.errors.count=0 then
response.write "success"
else
response.write "failure"
end if

Aaron
Fri Jul 08 13:25:37 CDT 2005

Where do you define adcmdstoredproc ?

http://www.aspfaq.com/2112


<jason@catamaranco.com> wrote in message
news:%23hkSIn%23gFHA.2072@TK2MSFTNGP14.phx.gbl...
>
>>
>> conn.spr_AddStory 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=adcmdstoredproc
>
> '// 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
> pStoryCategoryID = 1
> pStoryDeptID = 1
> pStoryKeyword1 = "Norseman"
> pStoryKeyword2 = "43"
> pStoryKeyword3 = "Sailing"
> pStoryDate = "#05/23/2005#"
> pStoryImageURL = "http://www.catamarans.com/images/SWCUA.jpg"
> pStoryBLN = 1
> PStoryModel="Lagoon"
> pStorySize="60"
> pYear ="1999"
>
>
>
>
> '//INSERT FORM VARIABLES...
> '--------------------------
> Dim cmd, param
>
> Set cmd=server.CreateObject("ADODB.Command")
>
> With cmd
> .CommandType=adcmdstoredproc '//Line 63
> .CommandText = "spr_AddStory"
>
> Set GetConnection = "driver={SQL
> Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
>
> set .ActiveConnection=cnSQL
> set param = .createparameter("@RETURN_VALUE", adInteger, adParamOutput, 0)
> .parameters.append param
> set param = .createparameter("@StoryTitle", adVarChar, adParamInput, 100,
> pStoryTitle)
> .parameters.append param
> set param = .createparameter("@StoryURL", adVarChar, adParamInput, 150,
> pStoryURL)
> .parameters.append param
> set param = .createparameter("@StoryBlurb", adVarChar, adParamInput,
> 1200, pStoryBlurb)
> .parameters.append param
> set param = .createparameter("@StoryBrokerID", adInteger, adParamInput,
> 0, pStoryBrokerID)
> .parameters.append param
> set param = .createparameter("@StoryCompanyID", adInteger, adParamInput,
> 0, pStoryCompanyID)
> .parameters.append param
> set param = .createparameter("@StoryCategoryID", adInteger, adParamInput,
> 0, pStoryCategoryID)
> .parameters.append param
> set param = .createparameter("@StoryDeptID", adInteger, adParamInput, 0,
> pStoryDeptID)
> .parameters.append param
> set param = .createparameter("@StoryKeyword1", adVarChar, adParamInput,
> 50, pStoryKeyword1)
> .parameters.append param
> set param = .createparameter("@StoryKeyword2", adVarChar, adParamInput,
> 50, pStoryKeyword2)
> .parameters.append param
> set param = .createparameter("@StoryKeyword3", adVarChar, adParamInput,
> 50, pStoryKeyword3)
> .parameters.append param
> set param = .createparameter("@StoryModel", adVarChar, adParamInput, 150,
> pStoryModel)
> .parameters.append param
> set param = .createparameter("@StorySize", adVarChar, adParamInput, 10,
> pStorySize)
> .parameters.append param
> set param = .createparameter("@StoryYear", adVarChar, adParamInput, 10,
> pStoryYear)
> .parameters.append param
> set param = .createparameter("@StoryDate", adDBTimeStamp, adParamInput,
> 0, pStoryDate)
> .parameters.append param
> set param = .createparameter("@StoryImageURL", adVarChar, adParamInput,
> 150, pStoryImageURL)
> .parameters.append param
> set param = .createparameter("@StoryBLN", adInteger, adParamInput, 0,
> pStoryBLN)
> .parameters.append param
> .execute ,,adexecutenorecords
> end with
>
> on error resume next
> conn.spr_AddStory
> StoryTitle,StoryURL,StoryBlurb,StoryBrokerID,StoryCompanyID,StoryCategoryID,StoryDeptID,StoryKeyword1,StoryKeyword2,StoryKeyword3,StoryDate,StoryImageURL,StoryBLN,StoryModel,StorySize,StoryYear
>
>
> if err<>0 and conn.errors.count=0 then
> response.write "success"
> else
> response.write "failure"
> end if
>

Bob
Fri Jul 08 13:50:51 CDT 2005

jason@catamaranco.com wrote:
>> conn.spr_AddStory 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=adcmdstoredproc
>
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={SQL
> Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
>

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"

jason
Fri Jul 08 14:03:26 CDT 2005

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.CreateObject("ADODB.Command")

With cmd
.CommandType=adcmdstoredproc
.CommandText = "spr_AddStory"

cnSQL = "driver={SQL
Server};server=MAXSQL008.maximumasp.com;DB=V032U10DUW;UID=V032U10DUW;PWD=cU3QmtgDyzWJ"
'//Line 69


Microsoft VBScript runtime (0x800A01A8)
Object required: 'cnSQL.ActiveConnection'
/catamaranco/story/Process.asp, line 69

Aaron
Fri Jul 08 14:10:34 CDT 2005

> Microsoft VBScript runtime (0x800A01A8)
> Object required: 'cnSQL.ActiveConnection'
> /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={SQL
Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
>>

I assume you meant

Set cnSQL = CreateObject("ADODB.Connection")
cnSQL.open "driver={SQL
Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
' then...
Set cmd.ActiveConnection = 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

jason
Fri Jul 08 15:25:58 CDT 2005

Apologies....I do seem to struggle with connection strings. I tend to stick
to one method and get a wee bit confused when tweaking.

I have it working now....thank you for your help.

- Jason
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%238gKrC$gFHA.3936@tk2msftngp13.phx.gbl...
>> Microsoft VBScript runtime (0x800A01A8)
>> Object required: 'cnSQL.ActiveConnection'
>> /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={SQL
> Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
>>>
>
> I assume you meant
>
> Set cnSQL = CreateObject("ADODB.Connection")
> cnSQL.open "driver={SQL
> Server};server=_.maximumasp.com;DB=_pw;UID=V032U10DUW;PWD=_pw"
> ' then...
> Set cmd.ActiveConnection = 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
>