Fine-tune/improve Parametized query in asp?

[Guest]

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 Barrows [MVP]]

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?

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

 

[Guest]

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 Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

on error resume next
conn.spr_AddStory,pStoryTitle, ..., pStoryBLN

Well that's just wrong. it should say:

conn.spr_AddStory pStoryTitle, ..., pStoryBLN

 

[Guest]

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 Bertrand [SQL Server MVP]]

Where do you define adcmdstoredproc ?

http://www.aspfaq.com/2112

 

[Bob Barrows [MVP]]

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.

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

 

[Guest]

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 Bertrand [SQL Server MVP]]

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

 

[Guest]

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