Stored Procedure only partially works??? ASP-> SQL Server

I have a rather difficult least everyone around here makes it sound like it is...that and they have no solutions for me.

I have a SQL Server 2000 database setup, and am trying to call a stored procedure (sp_updateInvcID) from an ASP page.  I am going to cross post this in the ASP forum as well, as I'm not certain where the problem lies (ie. database problem or asp problem).

Table assets
asst_id identity
asss_id decimal(17)
invc_id decimal(17)
mnfc_id decimal(17)
modl_id decimal(17)
ascr_id decimal(17)
serial_number varchar(30)
asset_number varchar(30)

There are more columns, but these are the ones I am working with.  

      @asstID int,
      @invcID int,      
      @ascrID int,
      @assetNum varchar(30),
      @serial varchar(30)
      update assets set asss_id=4, invc_id=@invcID, serial_number=@serial,
      asset_number=@assetNum, received_dt=getDate(), paying_ascr_id=@ascrID
      where asst_id=@asstID

ASP JScript to call stored Procedure
function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
      cmd = getStoredProc("sp_updateInvcID");
      cmd.Parameters.Append(cmd.CreateParameter("@asstID", 3, 1, 7, asstID));
      cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 1, 7, invcID));      
      cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 3, 1, 7, ascrID));            
      cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
      cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));            

function getStoredProc(name) {
      conn = getConn();
      cmd = Server.CreateObject("ADODB.Command");
      cmd.ActiveConnection = conn;
      cmd.CommandText = name;
      cmd.CommandType = 4;
      cmd.Prepared = true;
      return cmd;

function getConn() {
      conn = Server.CreateObject("ADODB.Connection");
      conn.Open = Application("conn");
      return conn;

ASP JScript which makes function call
try {
   updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial);
} catch (e) {
  Session("Error") = e.message;

I have tested all the variables in the try / catch block, and they are all of the appropriate types (ie. strings for assetNum and serial, and integers for the rest).

Here is where things get 'interesting' :(  The way the app works, you may end up updating the invcID of a number of records.  I use a for loop to go through each of the records, calling the same function for each of the records.  For small numbers of records (1-5) the function works MOSTLY fine...but I'll get to that in a second.

For large numbers of function calls, the application fails saying "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Further, for the 1-5 rows in the table that WERE effected, the asss_id is NOT updated!  I've been struggling with this one for a while now, and asked a lot of people a lot of questions...any further information / suggestions anyone can give are GREATLY appreciated at this point.  

Further info
IIS 5.0 with appropriate updates / patches
MDAC 2.whatever it's up to
Connection to database is using:
"Provider=sqloledb; Data Source=;User ID=READ;Password=READ" (** Permissions on the database have been set for the read user to execute this stored procedure**)

If anyone needs further information, let me know, and I'll try as best I can to provide it.
Who is Participating?
Partha MandayamTechnical DirectorCommented:
In Jscript variables are always variant.

Try converting to the proper datatypes as follows

 cmd.Parameters.Append(cmd.CreateParameter("@asstID", 3, 1, 7, CInt(asstID)));
You can test if it's the stored proc, by catching the error, saving the parameter values and running the stored proc with those values from query analyzer.

You should have the stored proc parameters declared as the exact same types as in your table though it shouldn't matter too much in this case.
     @invcID int,    
     @ascrID int,
should be typed decimal(17)
1. Check to see if you have any unique keys on Table assets that are denying your changes.

2. When you do a lot of updates, you create a lot of connections. Instead, consider writing a connection pool object or just using one connection. I see that you aren't destroying the connection, just leaving it to die on its own. My experiences with MS products lend me to spend a LOT of time cleaning up open objects that MS 'says' they're going to take care of for me. HA!

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ShaymusBane2Author Commented:
For all: Sorry there hasn't been any follow ups by me...I work will check all these suggestions out today...

RobertRFreeman:  I've ran the stored procedure using the query analyzer, and it works.  I've also used the debug tool in query analyzer to step through the stored works fine as well.  So I'm FAIRLY positive it's not the stored procedure that's causing the error.

mcp111: the CInt() function is VBScript, isnt it?  I've cast the appropriate parameters into 'new String()' in order to comply with the varchar element in SQL server, but I'm not sure how to cast into an int explicitly in JScript....that being said, however, I have dozens of other stored procedure calls passing the id values, which work flawlessly.  And this stored procedure call does work...just not 100%.  As I said, it updates MOST things (invcID, assetNum, serial, etc.) just not asss_id.  When I tried using syntax like CInt, it errored saying an object was expected, and gave a VBScript that avenue sounds like it'll open up more difficultly, as I'd have to rewrite most of the app to VBScrip in order to do the type casting you suggested.

ala frosty: 1) There are a bunch of 'not null' column constraints on the assets table, and asss_id is one of them...all I'm trying to do though, is update an existing value?  I'll have to check in a bit about whether there is a default value on the column on insert...but I don't think that should cause any problems when doing an update on existing data?  I'll check to see if all my relational links on Asset Status (asss_id) table are correct and let you know in a bit though.

2) I'm not that familiar with creating 'objects' in ASP :(  Asp isn't a 'native' programming language for me...I'm a JSP / Java programmer mostly, so I'm kinda winging this ;)  I've read that IIS 5.0 can be set to do connection pooling though?  (I'm familiar with Tomcat, not IIS :(  )  Do you have links to sites that reference how to do connection pooling with ASP?  I'll gladly give that a go, as I know the benefits of utilizing a connection pool.  I just tried to close the connection after each iteration, and it still causes the same error...although I agree, MS isn't like Java....gotta love the 'garbage collector'!!!
Partha MandayamTechnical DirectorCommented:
good resource on asp is
Instead of CInt just use Int(...) in Jscript
ShaymusBane2Author Commented:
Forgot to post the link to the other topic heading I have this same posted on...

Maybe something someone has posted there will tweak someone's memory?  I know I'm at a loss on this one :(
ShaymusBane2Author Commented:
After reading over ala frosty's question regarding the unique constraints on the assets table, I started 'digging' into some of the other things related to Assets.

The DBA has written the following trigger on assets though, which I was unaware of:

CREATE TRIGGER asset_number_trig ON dbo.ASSETS
  if update(ASSET_NUMBER)
    if ( select count(*)
           from assets a, inserted i
          where i.ASSET_NUMBER is not null and
                a.ASSET_NUMBER = i.ASSET_NUMBER )  <>
      ( select count(*)
        from inserted
        where asset_number is not null)                                              
      rollback transaction
      raiserror( 'A duplicate asset number has been detected.  Transaction failed.',11,1)

I was told that this is what he's using to get around one of the 'features' of SQL server 2000...The original application was hosted in Oracle.  The asset_number column allowed multiple instances of 'null' as a unique column...what this yields, is that null!=null.  Apparantly, SQL server doesn't follow SQL standard (can't recall the specifics).   In SQL server, null = null.  So you cannot declare a column unique, and have multiple null values.  The business procedure of the application which the database is for, must have multiple null values allowable for asset_number.

Long winded update, but there it is.  I've already removed the trigger and tried the same procedure call with the same results the trigger doesn't SEEM to be the issue...but I don't know enough about SQL triggers to say whether this is the case or not.  

Any further thoughts?
ShaymusBane2Author Commented:
mcp111 was CLOSE to the WAS a data type that was incorrect...but it was the return value datatype!

I think that something like what this individual found is what I ran the case of invc_id, I am calling a stored procedure to create the invoice, and return the @@identity of that new invoice.  I then use this value to add all the assets to that invoice.  The trouble seems to be that invc_id is NOT actually an integer!

I did a SQL trace, and saw that my query was using a LOT of cpu cycles when trying to process of the guys at work said that can happen when SQL is doing conversions...

Even though invc_id printed out LIKE a number when I did Response.Write(invc_id), it wasn't in fact an integer!  So I performed this, and now it works fine!

invcID = parseInt(invcID);

Goofy thing!  **laugh**  Ah least it works!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.