Which is the better approach?

[Bob Barrows [MVP]]

Re: Which is the better approach?

Rob Meade wrote:[color=blue]
> "Bob Barrows [MVP]" wrote ...
>[color=green]
>> What provider are you using? You're using SQL Server, right? I've
>> never used the MSDASQL provider (ODBC) with SQL Server, but I assume
>> that can handle multiple resultsets ...
>> If you're using SQLOLEDB, well, I've never run into this error
>> message ...[/color]
>
> My connection string stuff goes like this:
>
> Set objConnection = Server.CreateOb ject("ADODB.Con nection")
> objConnection.O pen "Provider=SQLOL EDB;Data Source=TITUS;Us er
> ID=<userid>;Pas sword=<password >;Initial Catalog=Parasol Training"
> Set objCommand = Server.CreateOb ject("ADODB.Com mand")
> Set RS = Server.CreateOb ject("ADODB.Rec ordset")
> objCommand.Comm andText = SQL
> objCommand.Comm andType = adCmdText
> Set objCommand.Acti veConnection = objConnection[/color]

Nothing to do with your problem, but I'm not sure why you are bothering with
an explicit Command object here - you aren't passing any parameters that I
can see. And why use adCmdText when calling a stored procedure? Use
adCmdStoredProc in this case.

Oh! Are you concatenating the parameter values into the SQL variable? IMO,
that's a bad idea. See the text at the end of this message for my reasoning.

[color=blue]
> RS.Open objCommand,,adO penKeySet, adLockOptimisti c
>
> I don't really know what the adOpenKeySet/ adLockOptimisti c stuff
> does (I never really have) -[/color]

Time to learn :-)

http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcursortypeenum.asp

http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnextrec.asp

I probably would not use a keyset cursor in this situation. Actually I would
not use anything but either a server-side forward-only cursor (most likely)
or a client-side static cursor (rare - only if I need bookmark support -
http://msdn.microsoft.com/library/en...robookmark.asp - very
rare) in ASP. I am never intending to have a cursor open long enough to care
about changes made by other users, which is the main reason for using
dynamic or keyset cursors.
[color=blue]
> could it be anything to do with that?[/color]

I don't think so, but I've never tested it. Try testing using a simple
forwardonly cursor:

Set RS = Server.CreateOb ject("ADODB.Rec ordset")
objConnection.N ameOfProcedure Parm1,...,ParmN ,RS
if not rs.eof then arCourses = rs.GetRows
Set rs=rs.NextRecor dset
if not rs.eof then arFeatures=rs.G etRows
etc.

[color=blue]
>[color=green]
>> Well, you could use GetRows to put the first one into an array ...
>> nothing says you HAVE to loop through a recordset, does it?[/color]
>
> Wouldn't I have the same problem though Bob? ie, populating
> "anything" from a recordset it cant get?[/color]

I was not addressing your inability to use NextRecordset. I was addressing
your assertion that you needed a second recordset object.

-------------------------------------------------------------------------------------------
There are several ways to pass parameter values to stored procedures:


1. Use the technique described here: http://www.aspfaq.com/show.asp?id=2201


Personally, I don't like this technique since:
a. You have to worry about preventing hackers from injecting SQL into your
code (there are ways to prevent this - see the SQL Injection FAQ at
www.sqlsecurity.com)


b. You have to correctly delimit your parameter values, just as if you were
creating a dynamic SQL statement (actually, that is exactly what you are
doing here). You also have to correctly handle string values that contain
literal characters that are normally used as delimiters. While I've done
this enough times so that it is second nature to me now, in the beginning
this was the largest stumbling block to my learning how to create strings
containing dynamic SQL statements.


c. There is some performance-impairing overhead involved with both the
concatenation of the SQL statement that ultimately runs the stored
procedure, and the preparation of the statement on the SQL Server box, which
happens before the statement is actually executed.


d. It forces you to return data only by recordsets: no output or return
values can be used with this technique. Recordsets require substantial
resources, both on the SQL Server which has to assemble the resultset and
pass it back to the client, and on the web server which has to marshal the
resultset and transform it into an ADO recordset. This is a lot of overhead
when we're talking about returning one or two values to the client.


However, a lot of people do like this technique because:
a. They have no problem knowing when and how to concatenate delimiters into
the SQL statement, and how to handle string parameters that contain literal
characters that are normally used as delimiters
b. They have taken the necessary steps to prevent SQL Injection
c. You can assign the statement to a variable and, if there's an error
during the debug process, you can response.write the variable to see the
actual statement being sent to the SQL Server. If the statement has been
created correctly, you can copy and paste it from the browser window into
Query Analyzer and further debug it
d. They are aware of the performance hit, and consider it to be too minor to
worry about. (To be fair, in many cases, this perfomance hit is relatively
minor)


The alternatives I prefer completely eliminate objection b from above.


1. If you have output parameters, or you are interested in using the Return
value from your procedure, use an explicit ADO Command object. Now, this can
be tricky, especially if you do it the correct way (manually create the
Parameters collection using CreateParameter instead of using
Parameters.Refr esh which involves an extra time-consuming trip to the
database). However, there are many stored procedure code generators out
there that vastly simplify this process, including the one I wrote which is
available here:

http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

2. The technique I use most often is the "procedure-as-connection-method"
technique. With ADO 2.5 and higher, stored procedures can be called as if
they were native methods of the connection object, like this:
conn.MyProcedur e parmval1,...,pa rmvalN
This completely avoids the need to worry about delimiters, literal or
otherwise. Plus it turns out that this technique also causes the procedure
to be executed in a very efficient manner on the SQL Server box.

You can also use this technique if your procedure returns a recordset:
set rs=server.creat eobject("adodb. recordset")
'optionally, set the cursor location and type properties
conn.MyProcedur e parmval1,...,pa rmvalN, rs

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"