Solved

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

Posted on 2004-09-24
8
412 Views
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.  

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.
0
Comment
Question by:ShaymusBane2
8 Comments
 
LVL 16

Expert Comment

by:RobertRFreeman
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.
ie.
     @invcID int,    
     @ascrID int,
should be typed decimal(17)
0
 
LVL 6

Accepted Solution

by:
mcp111 earned 500 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)));
...etc
0
 
LVL 7

Expert Comment

by:ala_frosty
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!

0
 

Author Comment

by:ShaymusBane2
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'!!!
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

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

Author Comment

by:ShaymusBane2
ID: 12162715
Forgot to post the link to the other topic heading I have this same posted on...

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21144240.html

Maybe something someone has posted there will tweak someone's memory?  I know I'm at a loss on this one :(
0
 

Author Comment

by:ShaymusBane2
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
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?
0
 

Author Comment

by:ShaymusBane2
ID: 12228895
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!!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now