Link to home
Start Free TrialLog in
Avatar of ShaymusBane2
ShaymusBane2

asked on

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

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.  

sp_updateInvcID
--------------------
CREATE PROCEDURE sp_updateInvcID
      @asstID int,
      @invcID int,      
      @ascrID int,
      @assetNum varchar(30),
      @serial varchar(30)
 AS
      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
GO

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));            
      cmd.Execute();      
}

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;
  Response.Redirect("/webapp/error/error.asp");
}            

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.
Avatar of RobertRFreeman
RobertRFreeman
Flag of United States of America image

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.
ie.
     @invcID int,    
     @ascrID int,
should be typed decimal(17)
ASKER CERTIFIED SOLUTION
Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ala_frosty
ala_frosty

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!

Avatar of ShaymusBane2

ASKER

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'!!!
good resource on asp is www.learnasp.com.
Instead of CInt just use Int(...) in Jscript
Forgot to post the link to the other topic heading I have this same posted on...

https://www.experts-exchange.com/questions/21144240/Stored-Procedure-only-partially-works-ASP-SQL-Server.html

Maybe something someone has posted there will tweak someone's memory?  I know I'm at a loss on this one :(
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
FOR INSERT, UPDATE, DELETE
AS
  if update(ASSET_NUMBER)
  begin
    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)                                              
    begin
      rollback transaction
      raiserror( 'A duplicate asset number has been detected.  Transaction failed.',11,1)
    end
  end

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?
mcp111 was CLOSE to the answer...it WAS a data type that was incorrect...but it was the return value datatype!

http://p2p.wrox.com/archive/beginning_asp/2002-03/18.asp

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