[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-09-24
19
Medium Priority
?
538 Views
Last Modified: 2008-01-09
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
19 Comments
 
LVL 8

Expert Comment

by:a_twixt_in_the_tale
ID: 12146354
> Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
asLockOptimistic?

> the asss_id is NOT updated
pardone moi if im wrong. is the procedure ok.

:)
Don
0
 

Author Comment

by:ShaymusBane2
ID: 12147264
The procedure works fine in the SQL analyzer if I run it...and if I copy and paste the 'sql' part out of the stored procedure and run it in SQL analyzer it also works....it's only when I'm calling the store procedure from ASP that it does the partial work.

As for the adLockOptimistic...is that a concern when opening a connection for executing a stored procedure with no return value?

I had been lead to believe that utilizing different locking mechanisms and cursor types is only important for returning recordsets?  This is what I use...

function getRecordSet() {
      conn = getConn();
      rs = Server.CreateObject("ADODB.Recordset");
      rs.CursorLocation = 3; // adUseClient
      rs.CursorType = 3; //adOpenStatic
      rs.ActiveConnection = conn;
      return rs;
}

Not that I'm using this in this instance, but this is what I've got setup for recordset creation and it works exactly how I want it....but if you could elaborate further on the reference to adLockOptimistic and whether or not this would apply for stored procedure calls, that would be greatly appreciated...
0
 

Author Comment

by:ShaymusBane2
ID: 12147461
I just updated the getConn() function to reflect the following, based on your suggestion

function getConn() {
      conn = Server.CreateObject("ADODB.Connection");
      conn.Mode = 3; // adModeReadWrite
      conn.CursorLocation = 2; // adUseServer
      conn.Open = Application("conn");
      return conn;
}

It still returns / causes the same "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." error...
0
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.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 12157246
The only obvious problem I can see is that you have defined integers to be 7 bytes, when in fact they are 4 bytes.  So try your code as follows:

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 3, 1, 4, asstID));
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 1, 4, invcID));    
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 3, 1, 4, ascrID));          
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

However, I can see how this error could have crept in:  The corresponding columns for the parameters @invcID and @ascrID are decimal (although this last may have a typo as "paying_ascr_id" probably should be "ascr_id").

Also, make sure none of the parameters have a null value.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12157254
>>but if you could elaborate further on the reference to adLockOptimistic and whether or not this would apply for stored procedure calls<<
You are right, recordsets (and adLockOptimistic) are irrelevant with stored procedures that do not return resultsets.
0
 

Author Comment

by:ShaymusBane2
ID: 12161105
acperkins, thanks for the comments, and for clarifying the adLockOptimistic query...

I'll give changing the integers to 4 a try, but I had them at 5 length before and it was returning an error because some of the identifiers I was passing were 5 charcters or longer (ie. id=12345, 123456).  I had read this parameter as indicating the number of 'spaces' the value would contain?  I didn't realize it referred to bytes...

Sorry for no follow ups over the weekend, but I'm a mon-fri worker ;)  So for anyone tracking this thread, activity will be picking up now.
0
 

Author Comment

by:ShaymusBane2
ID: 12161506
acperkins...just tried changing to '4' in place of the '7' I had...similar error occurs...although when I dig deeper into this error, it relates more to the parameters I'm sending it being 'too big' for the place holders I've allocated.  

I took this to mean that it's trying to fit four bit 5 and 6 bit characters into a 4 bit placeholder...?  When I encountered this same error (different than the original problem) in regards to a 'notes' field, all I did was increase the place holder to fix it...ie. for a notes field that allows 200 characters...

cmd.Parameters.Append(cmd.CreateParameter(@notes, 200, 1, 201, notes));

This fixed the problem...
0
 

Author Comment

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

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21144232.html

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

Expert Comment

by:Anthony Perkins
ID: 12165098
Integers are always 4 bytes regardless if the value is 1, 2, 3, ... 9 or 10 digits.

If you feel that my other points and I quote:
<quote>
However, I can see how this error could have crept in:  The corresponding columns for the parameters @invcID and @ascrID are decimal (although this last may have a typo as "paying_ascr_id" probably should be "ascr_id").

Also, make sure none of the parameters have a null value.
</quote>

Are addressed than I have no idea.
0
 

Author Comment

by:ShaymusBane2
ID: 12170654
acperkins: I've altered the data type to integer in the database, and witnessed the same error.  I've also changed the ASP to utilize the decimal type.  The error remains in both cases.  And paying_ascr_id isn't a typo...there are two columns (managing_ascr_id and paying_ascr_id) that assets has...the specific procedure is only working with the paying reference.  From doing Response.write() statements all over the code, all of the parameters are populated with values as well.  I've taken those same values, and using Query Analyzer, ran the stored procedure with those same values, and they work 100%.  I've used the query debug tool with the values to see what occurs, and it seems to work as well.  

After reading over ala frosty's post in the other thread, 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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12173246
>>I've also changed the ASP to utilize the decimal type.<<
Post your code.  If you are in fact using Decimal (you were using integer in your original code) you must specify the Precision and Scale.  

>>And paying_ascr_id isn't a typo<<
Since you had not included it in your table structure I could only assume it was a typo. And yes, I read "There are more columns, but these are the ones I am working with."

>>I've taken those same values, and using Query Analyzer, ran the stored procedure with those same values, and they work 100%.<<
This is directly unrelated to SQL Server and has to do with how you have defined the Stored Procedure in ASP

>>In SQL server, null = null.<<
In SQL Server Null is never equal to Null.  Just try this:
If Null = Null
  Print 'Null = Null'
Else
  Print 'Null <> Null'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12173258
Also post your stored procedure if it has changed.
0
 

Author Comment

by:ShaymusBane2
ID: 12174897
Sorry if my responses sounded 'short'...it was far from my intent to do so...I greatly appreciate the time and effort you are giving me with this...here are the items you requested

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 14, 1, 7, asstID)); //adDecimal = 14;
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 14, 1, 7, invcID)); //adDecimal = 14;    
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 14, 1, 7, ascrID)); //adDecimal = 14;      
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

I also tried doing the alternate, ie. change the assets table to take integers, rather than decimal values, with a precision matching that listed in the SQL Enterprice Manager.

asst_id identity
asss_id integer(7)
invc_id integer(7)
mnfc_id integer(7)
modl_id integer(7)
ascr_id integer(7)
serial_number varchar(30)
asset_number varchar(30)

I'm not sure I follow your comment about Query Analyzer not being related...our DBA had me run execute commands on my stored procedure, then view the database to make sure that the values I was submitting were actually being updated in the database.  Using the Response.Write() statements, I took the values that were being generated, and 'cut and paste' them into the corresponding parameter locations, to perform the stored procedure test.  This is what I meant when I said I'd used Query Analyzer...I'm more familiar with Oracle, so to use the Oracle equivalent, I ran the stored procedure using SQL Plus, to make sure that the stored procedure was doing what it was supposed to...

The comment I made about 'null = null' refers to the Microsoft white paper published in 2000 which talks about migrating and oracle application into SQL Server 2000.  

Under the topic of 'Primary Keys and Unique Columns' in the following link, is where I encountered the situation I was talking about...if you do a find for the quoted text, it covers the specifics in the fourth paragraph.

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

The business logic for the application requires asset_number to be unique, but it also requires you to be able to add assets into the database without entering in any asset_number.  The trigger was what the DBA implemented to recreate this functionality.

The stored procedure I ended up using looked as follows:

CREATE PROCEDURE sp_updateInvcID
     @asstID decimal(17,0),
     @invcID decimal(17,0),    
     @ascrID decimal(17,0),
     @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

I got the precision values to try / use from the assets table.

To clarify, I tried changing the ASP code to mirror the database datatypes, removing the possibility of the implicit datatype conversion being at fault.  I also tried changing the database to mirror the ASP code for the same reason.  The result turned out to be the same in both cases.  

I'm waiting on a more detailed analysis of all the relational joins for the assets table, as per the suggestion from ala frosty to check for constraints being at fault...but so far I havn't been able to get access to the database or the dba today...I will try again tomorrow.

Once again, I will apologize if my previous comments came across as being short...perhaps I've read into your comments something that wasn't there in that regards, but I would like to stress how much I do appreciate any and all suggestions you are providing me with this problem.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12176042
>>Sorry if my responses sounded 'short'...<<
Not my intention.  I trust that I did not give you that impression.

>>The comment I made about 'null = null'<<
I understand now what you are saying.  What I was getting at is that:
If Null Is Null        -- Is true

If Null = Null        -- Is false

>>perhaps I've read into your comments something that wasn't there in that regards<<
I believe you did.

>>I'm not sure I follow your comment about Query Analyzer not being related<<
Simply put:  The Stored Procedure can run flawlessly in SQL Query Analyzer, but fail from ASP, because it has not been declared correctly.  This is just as true for Oracle, as well.

But to your question.  As I mentioned previously, in order to use decimal values you must define the precision and scale for each parameter. So while I am not advocating that you use decimal(17,0), since you are now using decimal parameters you must define the precision of 17 and the scale of 0 (this last may not be entirely necessary, but I have never used a decimal with a scale of 0, prefering to use integers, instead)

As an aside, and for the record:   Integer data type would be sufficient if your maximum size is 2,147,483,647 or bigint if it is
9,223,372,036,854,775,807 and still use less bytes than decimal(17, 0) which uses 9 bytes.

So your ASP code (with the current stored proc and assuming it works correctly in SQL Analyzer) should look like this (untested):

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 14, 1, 9, asstID)); //adDecimal = 14;
     cmd.Parameters("@asstID").Precision = 17;
     cmd.Parameters("@asstID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 14, 1, 9, invcID)); //adDecimal = 14;    
     cmd.Parameters("@invcID").Precision = 17;
     cmd.Parameters("@invcID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 14, 1, 9, ascrID)); //adDecimal = 14;      
     cmd.Parameters("@ascrID").Precision = 17;
     cmd.Parameters("@ascrID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

Notice, how I have also changed the size to the correct 9 bytes.  Incidently, Decimal (18, 0) also uses 9 bytes.

I hope this helps.  If not please advise error message that occurred and any changes to the code.
0
 

Author Comment

by:ShaymusBane2
ID: 12193265
K...this is just getting frustrating now!

acperkins: I've blanket changed all of the decimal values in the database to integers...reverted all my asp code to integers, changed the precision of my stored procedure parameters to 4 as you recommended....and I'm STILL getting the same error?!?!

Whilst banging my head against the monitor, I happened to see something (in Access of all things) that I thought kinda 'odd'...when i'm working at my desk, rather than on the server,  I use MS Access to view the data, run simple queries, etc. etc...one of the things I noticed though, is the identity field for asset status (asss_id) comes up as a 'Long Integer' autonumber when I view the table design view...is it possible that THIS is why I'm getting all these errors???  Is it possible that the autonumber requires something different?  I'm truly at my wits end on this one :(

The same goes for invc_id...more background here...I have a stored procedure to create an invoice that is called before the aforementioned problematic stored procedure is called...

sp_createInvoice
--------------------
CREATE PROCEDURE sp_createInvoice
     @invoice varchar(30),
     @notes varchar(30),
     @invcID int output
 AS
     insert into invoices (invoice, notes) values (@invoice, @notes)
     select @invcID = @@identity
GO

ASP JScript to call stored Procedure
------------------------------------------
function createInvoice(invoice, notes) {
      cmd = getStoredProc("sp_createInvoice");
      cmd.Parameters.Append(cmd.CreateParameter("@invoice", 200, 1, 30, invoice));
      cmd.Parameters.Append(cmd.CreateParameter("@notes", 200, 1, 200, notes));
      cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 2, 4));
      cmd.Execute();      
      return cmd.Parameters("@invcID");
} // *** Done and tested ***

ASP JScript which makes function call
--------------------------------------------
try {
   var invcID = receiveContract(cotrID, invoice, notes);
} catch (e) {
   Session("Error") = e.message;
   Response.Redirect("/miams/error/error.asp");
}

Is it possible that invcID is the problem?  Due to it being an autonumber or something??  As I said before, asss_id doesn't update either, but the asset status table (asss_id) also uses autonumber...?  Any thoughts?
0
 

Author Comment

by:ShaymusBane2
ID: 12193302
Whoops...typo...in the stored procedure, I am accepting varchar(200) for notes...not (30)...
0
 

Author Comment

by:ShaymusBane2
ID: 12194278
http://support.microsoft.com/?kbid=165156

Found this link...here are the results for the first stored procedure..

sp_updateInvcID
-------------------
PARAMETER NAME    DATA-TYPE    DIRECTION    DATA-SIZE
@RETURN_VALUE     3                   4                  0
@asstID                   3                   1                  0
@invcID                   3                   1                  0
@ascrID                   3                   1                  0
@assetNum             200                 1                 30
@serial                   200                 1                 30

Is it normal for the data-size to be 0?  I've ran this on a few of my stored procedures and they all claim a 0 data-size?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12196809
Yes, you can set integer size to 0.  But I suspect that is not the problem.  Even after changing it to 0 you still get the same problem, don't you?
0
 

Author Comment

by:ShaymusBane2
ID: 12228916
See my solution on the other board...for the effort though, 500 pts to acperkins

Thanks again for helpin out!
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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