[This question is directed at Bob Barrows, but perhaps someone else knows an
answer. In any case it is good information for the group.]

I really like Bob's "stored procedure as method of Connection Object"
technique. It is convenient, compact, and concise, and simplifies protection
from SQL injection.

HOWEVER, I cannot figure out a way to pass a null value to an INT parameter
when I use this. Null string parameters are fine. The error reads:

    Microsoft OLE DB Provider for SQL Server error '80040e07'
    Operand type clash: text is incompatible with int

As you can imagine, "text is incompatible with int" gets my attention. I am
explicitly passing a null (not the string "null"). I am using JScript on the
web server and connecting to SQL Server 2000. An example:

    var CN = Server.CreateObject("ADODB.Connection"),
        PCTagNumber = +Request.Form("PCTagNumber").Item || null,
        DeskLocation = Request.Form("DeskLocation").Item || null

    CN.Open(CNString)
    CN.Inventory_Update(HR.UserID,PCTagNumber,DeskLocation)

Assume the stored procedure looks something like:

    CREATE PROCEDURE dbo.Inventory_Update(
        @UserID VARCHAR(50),
        @TagNumber INT,
        @DeskLocation VARCHAR(255)
    ) AS ...

In the above case, a null tag number produces the error, while a null desk
location results in a SQL NULL passed to the parameter @DeskLocation.

Is there a way for me to pass a null integer here, or is this a limitation
of Bob's technique?

I wrote:
Not looking good for me:

    "To execute a stored procedure, issue a statement where
     the stored procedure name is used as if it were a method
     on the Connection object, followed by any parameters.
     ADO will make a "best guess" of parameter types."

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

Dave Anderson wrote:
Yes, I am having the same issue.  I can only come up with two workarounds:
1.
Declare the parameter with a default so it is optional, making it the last
parameter to be declared, then
if (PCTagNumber==null)
    CN.Inventory_Update(HR.UserID,DeskLocation)
else
    CN.Inventory_Update(HR.UserID,DeskLocation,PCTagNumber)

Drawbacks
    - I only succeeded in omitting the last argument
    - It doesn't help with more than one nullable parameter

2.
Use a "magic" number instead of null, testing for that value in the
procedure
Drawbacks
    - do I need to enumerate them?

I even tried using the parameter marker technique, which suffered the same
fate (I somewhat expected that, suspecting that the same stuff was happening
behind the scenes to make both techniques work).

Oh well, I guess it's back to the explicit Parameters collection for these
situations (where int parameters are nullable)

Bob

Problem Logging into ASP Web using Interdev on XP Home.
HELP!---Stored Procedure in ASP
ASP and Session Variables
Classes & Collections and some related issue
ASP rookie trying to make registration page