Re: Stored Procedures & Parameters Insert Problem

On 14 Mar, 11:23, "Bob Barrows [MVP]" <reb01...@xxxxxxxxxxxxxxx>
wrote:
incubusau...@xxxxxxxxx wrote:
Hi Bob,

Well, I thought I had tried experimenting with the Command's Prepared
property... I have just gone back to the code, after having a doubt
in the back of my mind, and tried changing the Prepared property to
True, prior to executing the loop, and it worked.

Although I am glad that this has worked, I cannot understand why.
Does this mean that only compiled stored procedures will check for new
parameter values upon each execution?

I've never had to use that property, but I remembered reading somewhere that
it could help in this situation. Frankly, I don't remember why (somebody
jump in please?). I will go see if Sceppa has anything to say about it in
his "Programming ADO" book and get back to you.

I suspect you would get the desired result if you re-instantiated the
Command object before running the procedure. Also, I'm wondering if using an
implicit Command object would resolve it, like this:

For lngTestCount = 1 To 20
adoDBConn.sp_InsertCount lngTestCount
Next

Given that I primarily use vbscript which has nothing but Variants, I use
this technique a lot in my ASP apps. The only time I explicitly create a
Command object is if I have a SQL Server procedure with output parameters,
or I want to read the Return value, neither of which is available with Jet
procedures.

--
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"

Hi Bob,

Using the implicit command object, as you suggested, does also resolve
the problem.

I have Googled for further information on the Prepared property, but
not come up with anything solid. The following Microsoft article
suggests (to me, at least), that for performance reasons (and no other
reason), the Prepared property should be set to True if the same
command is to be executed more than three or four times:

ADO and SQL Server (SQL Server 2000)
Executing Prepared Statements
http://msdn2.microsoft.com/en-us/library/aa905910(SQL.80).aspx

Even if I don't fully understand why my code didn't work correctly
with the default Prepared=False setting, at least I now know a
suitable resolution.


Regards,

Mike

.

Relevant Pages

  • Re: SQL for SP
    ... I'm executing this inside of BeginTrans-CommitTrans along with many other ... My weak knowledge of SQL Server doesn't allow me to understand why in case ... With VB I'm more comfortable and can catch the failure. ... >> Instead of using this SP for Command object with CommandType ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL for SP
    ... I'm executing this inside of BeginTrans-CommitTrans along with many other ... My weak knowledge of SQL Server doesn't allow me to understand why in case ... With VB I'm more comfortable and can catch the failure. ... >> Instead of using this SP for Command object with CommandType ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL for SP
    ... I'm executing this inside of BeginTrans-CommitTrans along with many other ... My weak knowledge of SQL Server doesn't allow me to understand why in case ... With VB I'm more comfortable and can catch the failure. ... >> Instead of using this SP for Command object with CommandType ...
    (microsoft.public.vb.database)
  • Re: SQL for SP
    ... I'm executing this inside of BeginTrans-CommitTrans along with many other ... My weak knowledge of SQL Server doesn't allow me to understand why in case ... With VB I'm more comfortable and can catch the failure. ... >> Instead of using this SP for Command object with CommandType ...
    (microsoft.public.vb.database.ado)