CF or StoredProc problem?

[Taco Fleur]

Hi all,

I have the following problem that is driving me nuts, if anyone could shed some light 
on it, I would appreciate it.

To view the webpages; go to 
http://www.coldfusionist.com/forum/dsp_category.cfm?category=15
click on login and use username: test123 password: test123
It should take you back again to 
http://www.coldfusionist.com/forum/dsp_category.cfm?category=15 after you logged in.
Then click on "New thread"
Then post a new thread.

The problem is that when the thread is inserted and you are being redirected to the 
page that should show the new thread you just inserted, it does not show up! Untill 
sometimes many seconds later..
I have been battling with this problem for a while now.

The code for the stored proc that performs the insert is:

CREATE PROCEDURE spForumThreadInsert 
(
 @categoryID INTEGER, 
 @header VARCHAR(200), 
 @threadBody VARCHAR(8000), 
 @someoneID INTEGER, 
 @startDate SMALLDATETIME = NULL, 
 @expiryDate SMALLDATETIME = NULL, 
 @bAnswerRequired BIT = 0, 
 @bAutoApprove BIT = NULL, 
 @bQuoteThread BIT = NULL, 
 @bAllowRating BIT = NULL, 
 @bHideModerator BIT = NULL, 
 @newThreadID INTEGER = NULL OUTPUT, 
 @bApprovalRequired BIT = NULL OUTPUT, 
 @bError BIT = NULL OUTPUT
)
AS

SET @bError = 0

BEGIN TRANSACTION

 DECLARE @bApproved BIT

 SET @bApprovalRequired = (SELECT approvalRequired FROM tblForumCategory WHERE 
(pkCategoryID = @categoryID))
 
 IF @bApprovalRequired = 0
 BEGIN
  SET @bApproved = 1
 END
 ELSE
 BEGIN
  SET @bApproved = 0
 END

 IF @startDate IS NULL
  SET @startDate = GETDATE()

 INSERT INTO  tblForumThread
    (fkCategoryID, 
    fkSomeoneID, 
    threadHeader, 
    threadBody, 
    allowRating, 
    showthreadModerator, 
    answer, 
    fkStatusID, 
    startDate, 
    expiryDate)
 VALUES  (@categoryID, 
    @someoneID, 
    @header, 
    @threadBody, 
    COALESCE(@bAllowRating, '0'), 
    COALESCE(@bHideModerator, '0'), 
    CASE @bAnswerRequired
     WHEN 1 THEN -1              -- Create the right input, i.e. -1 = answer required, 
0 = no answer required and greater than 0 = the id of the answer
     ELSE 0
    END, 
    @bApproved, 
    @startDate, 
    @expiryDate)
 -- Check if there are any errors
 IF NOT (@@ERROR = 0)
 BEGIN
  SET @bError = 1
 END
 SET @newThreadID = SCOPE_IDENTITY()
 
 -- If approval is not required
 IF @bApprovalRequired <> 1
 BEGIN
  -- Update category statistics, i.e. thread count
  UPDATE tblForumCategory
  SET  threadCount = threadCount + 1
  WHERE (pkCategoryID = @categoryID)
  -- Check if there are any errors
  IF NOT (@@ERROR = 0)
  BEGIN
   SET @bError = 1
  END
 END

IF @bError = 1
BEGIN
 -- Rollback transaction if there was an error
 ROLLBACK TRANSACTION
END
ELSE
BEGIN
 -- Commit transaction if there was no error
 COMMIT TRANSACTION
END

IF @bError = 0
BEGIN
 -- Update count of records added to full-text search tables
 UPDATE tblSystemVariable
 SET  variableInteger = variableInteger + 1, 
   variableDate = GETDATE()
 WHERE (variableName = 'fullTextCount')
END
GO

I checked the value of the variable @bError and it is definately 0
I removed the TRANSACTION from the code, and still got the same problem.
I checked the db after the insert and the new row is present immediately after then 
form is submittes, however it will not show up on the page that should show the new 
thread!

The code that should retrieve the new thread is:

<cfstoredproc procedure="spForumThread" datasource="#request.mainDSN#">
 <cfprocparam type="In" cfsqltype="CF_SQL_TINYINT" value="2" null="no">
 <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" maxlength="10" null="yes">
 <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#val(url.thread)#" 
maxlength="10" null="no">
 <cfprocresult resultset="1" name="qForumThread">
 <cfprocresult resultset="2" name="qForumThreadReplies">
</cfstoredproc>

The funny thing is, it does not happen on every insert, sometimes it happens after 1 
insert, and sometimes after 5 or more inserts, so there is no pattern either.

If anyone has any thoughts on this I would appreciate it.

TIA
Taco Fleur


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4