insert then select not working

• jdeere

  SSC Rookie

  Points: 42

  More actions

  September 8, 2011 at 1:21 pm

  #244439

  I am trying to sort out if this is an sql or asp problem

  The below procedure worked fine with sql 2000 and IIS6

  It is not working correctly(detail below) with sql express and IIS7

  I have a stored procedure....

  ALTER PROCEDURE [dbo].[sp_newcart]

  AS

  begin

  insert into cart (date_created)values (getdate())

  select max(cart_id) from cart

  end

  (the cart_id auto increments 1)

  when calling from asp I cannot get the max(id)..

  I get " Item cannot be found in the collection corresponding to the requested name or ordinal"

  though it inserts ok

  When I comment out the insert... it will return the current max(id) fine

  Executing the procedure within sql server manager works ok

  any direction?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 8, 2011 at 4:32 pm

  #1378950

  The problem is likely to be that the insert is returning a row count that is "confusing" your code.

  Usually a "SET NOCOUNT ON;" in your stored procedure will fix that, but in this case I would consider

  using the OUTPUT clause to return the cart_id - this will fix the problem and make the code multi-user safe.

  ALTER PROCEDURE [dbo].[sp_newcart]

  AS

  INSERT INTOcart (date_created)

  OUTPUT INSERTED.cart_id

  VALUES (getdate());

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]