Stored proc returns data in SQL debugger but not when using DAAB RSS

• • Reply
  

  Nocturnal

  Contributor

  2850 Points

  570 Posts

  Stored proc returns data in SQL debugger but not when using DAAB

  Mar 22, 2005 08:32 PM|LINK

  Hello, I'm working in a DNN project. As you may know it recommends you use DAAB and a provider model to access the underlying database. In my case I need to have a stored procedure that only adds a new record if no match can be found for the supplied data. To illustrate I've created a a trivial table: Item_ID : int (identity, PK) Name_VC : varchar(50) The stored procedure looks like this: ALTER PROCEDURE dbo.TT_DBTryPlusAdd @Name_VC varchar(50) AS SELECT * FROM TT_DBTry WHERE [Name_VC] = @Name_VC IF @@ROWCOUNT = 0 -- no record was found, insert a new one BEGIN INSERT INTO TT_DBTry ( [Name_VC] ) VALUES ( @Name_VC ) SELECT * FROM TT_DBTry WHERE [Item_ID] = @@IDENTITY END RETURN When I run this stored proc from within VS.NET 2003 and enter a new value for Name_VC I get a message like this: Running dbo."TT_DBTryPlusAdd" ( @Name_VC = asdasdasda ). No rows affected. (1 row(s) affected) Item_ID Name_VC ----------- -------------------------------------------------- 5 asdasdasda No more results. (1 row(s) returned) @RETURN_VALUE = 0 Finished running dbo."TT_DBTryPlusAdd". If however I run this DAAB command: -- CType(SqlHelper.ExecuteReader(ConnectionString, "TT_DBTryPlusAdd", GetNull(name_VC)), IDataReader) -- I get an empty datareader. If I pass a Name_VC that already exists I get the data from the existing record just fine. Any ideas what's going on? Thanks in advance for replying.
  SquadEngine for game squad and clan hosting
  GravityPoint for all other group portal hosting
  TungstenTech: DNN Site