Rob said:
...
My connection string stuff goes like this:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=TITUS;User
ID=<userid>;Password=<password>;Initial Catalog=ParasolTraining"
Set objCommand = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
objCommand.CommandText = SQL
objCommand.CommandType = adCmdText
Set objCommand.ActiveConnection = objConnection
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.
RS.Open objCommand,,adOpenKeySet, adLockOptimistic
I don't really know what the adOpenKeySet/ adLockOptimistic stuff
does (I never really have) -
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-us/ado270/htm/mdprobookmark.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.
could it be anything to do with that?
I don't think so, but I've never tested it. Try testing using a simple
forwardonly cursor:
Set RS = Server.CreateObject("ADODB.Recordset")
objConnection.NameOfProcedure Parm1,...,ParmN,RS
if not rs.eof then arCourses = rs.GetRows
Set rs=rs.NextRecordset
if not rs.eof then arFeatures=rs.GetRows
etc.
Wouldn't I have the same problem though Bob? ie, populating
"anything" from a recordset it cant get?
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.Refresh 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.MyProcedure parmval1,...,parmvalN
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.createobject("adodb.recordset")
'optionally, set the cursor location and type properties
conn.MyProcedure parmval1,...,parmvalN, rs
HTH,
Bob Barrows