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

> 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

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

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

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

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.

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

Forgot to post the link to the other topic heading I have this same posted on...

https://www.experts-exchange.com/questions/21144232/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 :(

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.

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?

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

Also post your stored procedure if it has changed.

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.

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

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?

Whoops...typo...in the stored procedure, I am accepting varchar(200) for notes...not (30)...

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?

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?

See my solution on the other board...for the effort though, 500 pts to acperkins

Thanks again for helpin out!