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

Posted on 2004-09-24
Medium Priority
Last Modified: 2012-06-21
I have a rather difficult question...at 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.
Question by:ShaymusBane2
LVL 16

Expert Comment

ID: 12148329
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)

Accepted Solution

mcp111 earned 1500 total points
ID: 12148341
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)));

Expert Comment

ID: 12153901
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!

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 12161414
For all: Sorry there hasn't been any follow ups by me...I work Mon-Fri...so 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 procedure...it 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 error...so 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'!!!

Expert Comment

ID: 12162660
good resource on asp is www.learnasp.com.
Instead of CInt just use Int(...) in Jscript

Author Comment

ID: 12162715
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 :(

Author Comment

ID: 12170502
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 though...so 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?

Author Comment

ID: 12228895
mcp111 was CLOSE to the answer...it 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 into...in 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 this...one 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 well...at least it works!!!!

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question