ERROR! I think my SQL is mis-coded...Can't pull values in ASP...HELP!

  • 12 years ago

    Hello!

    I am trying to use the following stored procedure:

    CREATE PROCEDURE sp_CostFind
    @QID int,
    AS 
    BEGIN 
     
    IF (SELECT a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID + a.PID = b.AID + b.PID) and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date) IS NOT NULL  
       BEGIN
       PRINT 'AID to PID COST EXISTS'
       (SELECT a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID + a.PID = b.AID + b.PID) and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date)
       END 
    ELSE
       IF (SELECT a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID + a.CID = b.AID + b.CID)and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date) IS NOT NULL  
          BEGIN  
          PRINT 'AID to CID COST EXISTS'
          SELECT a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID + a.CID = b.AID + b.CID)and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date    
          END  
       ELSE  
              IF (SELECT top 1 a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID = b.AID) and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date) IS NOT NULL  
             BEGIN  
             PRINT 'AID COST EXISTS'
             SELECT top 1 a.cost from files.dbo.costs a, clix b, futures c WHERE (a.AID = b.AID) and b.clixID = c.clixID and c.futuresID = @QID AND b.Entry_Date between a.begin_date and a.end_date ORDER BY c.prospect_ID DESC  
             END  
             ELSE  
                BEGIN  
                PRINT 'NO COST EXISTS, ASSIGNING ZERO VALUE'
                SELECT TOP 1 cost = 0 from files.dbo.costs
             END
    END
    GO



























    I then attempt to execute the proc and assign the value to a variable in ASP:

     sSQL = "EXECUTE sp_CostFind 431"
    Set rsEmails = Server.CreateObject("adodb.RecordSet")
    rsEmails.Open sSQL, healthdbConnect, 3, 1
    future_cost = rsEmails("cost")
    response.write(future_cost)



    When I load the page to test it (it works when complied in I get the following error:

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    Can anyone tell me (if you can understand my cockamay coding) what the reason is why I am not able to retireve the "cost" value out of the above SP? BTW, when I remove all decisions, the straight select statements by themselves all work fine in both SQL Query Analyzer and ASP.  Addtionally, the exec proc statement (if I compile in Query Analyzer) returns the value I need...

    Thank you all so much! This is mission-critical and very important!

     

    JSweet

  • 12 years ago
    Hi,

    Welcome to Developer Fusion!!!  This may be way off the mark but "Item cannot be found in the collection" seems to suggest that it is being referenced incorrectly, instead of:
    SELECT a.cost from files.dbo.costs a 

    Try:
    SELECT a.cost AS cost from files.dbo.costs a

    Because you are referencing cost, not a.cost, in the following:
    sSQL = "EXECUTE sp_CostFind 431"
    Set rsEmails = Server.CreateObject("adodb.RecordSet")
    rsEmails.Open sSQL, healthdbConnect, 3, 1
    future_cost = rsEmails("cost")
    response.write(future_cost)












Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why. Programmers combine theory and practice: Nothing works and they don't know why.”