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

• • Reply

  14 years ago

  by JSweet

  Jamy Sweet

  , United States

  Joined 14 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

  Report abuse
• • Reply

  14 years ago

  by sync_or_swim

  Rob Bickel

  S. Wales, United Kingdom

  Joined 15 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)
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse