Forum problem

Topics: Help
Oct 7, 2007 at 4:51 PM
Edited Oct 7, 2007 at 4:53 PM
Website is at 95%.
Still some little problems.

I've made some forums, problem is that nobody can make a new thread.

Error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FKMembersThreadsThreads". The conflict occurred in database "SQL2005380404clubsitedb", table "dbo.Threads", column 'threadid'.
The statement has been terminated. _italics

When I turn back to the forum the post is visible. But you can't read the message (divide to zero error).
I've tried with several users. But it won't work.
Can I do something with the DB? Or some code I have to change?

Thanks in advance,
Steve
Oct 9, 2007 at 4:59 PM
Please, is there nobody who has a solution for this problem?
I don't know what to do at this moment.
Almost all other problems are solved. I've looked on other websites and found the same problem, but no solution.
The users i've used were registrated by the website and not by the ASP.NET configuration tool. Is there something I can change at my code or database?
It works on my local computer. Not on Discountasp.net

Steve
Oct 22, 2007 at 10:54 PM


SteveStunt wrote:
Please, is there nobody who has a solution for this problem?
I don't know what to do at this moment.
Almost all other problems are solved. I've looked on other websites and found the same problem, but no solution.
The users i've used were registrated by the website and not by the ASP.NET configuration tool. Is there something I can change at my code or database?
It works on my local computer. Not on Discountasp.net

Steve


OK, here is what I did:

Change the AddMessage stored procedure to this:
ALTER PROCEDURE dbo.AddMessage
@parentmsgid int,
@memberid uniqueidentifier,
@body ntext,
@subject nvarchar(255),
@admin bit,
@topicid int
AS
declare @threadid int

IF @parentmsgid = 0
BEGIN
INSERT INTO Threads (lastpostdate, lastpostby, topicid, admin) values(GetDate(), @memberid, @topicid, @admin)
SELECT @threadid = @@Identity /* SCOPE_IDENTITY()*/
END
else
BEGIN
SELECT @threadid = threadid FROM Messages WHERE id = @parentmsgid
UPDATE Threads SET lastpostdate = GetDate(), lastpostby = @memberid WHERE threadid = @threadid
END

INSERT INTO Messages
(memberid, subject, body, threadid, parentmsgid, answer)
VALUES (@memberid,@subject,@body,@threadid,@parentmsgid, 0)

/RETURN @threadid/
SELECT @threadid



and in app_code/BLL/forum.vb

Public Shared Function Add(ByVal msg As ForumMessage, ByVal AdminOnly As Boolean) As Integer
msg.displayname = GetCachedForumUser(msg.memberid).DisplayName()
ClearCache(msg.memberid)
Dim threadid As Integer = SPs.AddMessage(msg.parentmsgid, msg.memberid, msg.body, msg.subject, AdminOnly, msg.topicid).ExecuteScalar()

msg.threadid = threadid
msg.postdate = Now
ForumNotification.Notify(msg)
StartModeration(msg)
Return threadid
End Function
Oct 26, 2007 at 10:19 PM
Great! Superb!
Everything works right now.
Thank you very much.!!

SteveStunt
Dec 18, 2007 at 11:58 AM
Hi

I've had the same problem, and looked at the solution. I'm puzzled,

/RETURN @threadid/ This generates a syntax error
SELECT @threadid I don't understand the reason for the SELECT here, don't we need a return at the end of the store procedure?

I must admit I don't fully understand the original issue and my SQL is probably not too strong. so I'm probably missing something obvious.
Dec 19, 2007 at 1:01 AM
Steve Stunt

What Club starter kit are you using? 2.0 Final or 3 Beta 1?
I am having the same problem.

LHR
Dec 19, 2007 at 11:33 AM
I'm using 3 beta 1.

I have noticed that when I login as Administrator everything seems to work fine, where as if I'm logged in as a guest or other user then I get the issue. currently looking into that..........watch this space......but if you've got a fix that'd be better.

H.
Jan 1, 2008 at 11:52 AM
Edited Jan 1, 2008 at 11:56 AM


henrykb wrote:
Hi

I've had the same problem, and looked at the solution. I'm puzzled,

/RETURN @threadid/ This generates a syntax error
SELECT @threadid I don't understand the reason for the SELECT here, don't we need a return at the end of the store procedure?

I must admit I don't fully understand the original issue and my SQL is probably not too strong. so I'm probably missing something obvious.



Sorry I haven't been on for a while...
Looks like the forum editor stripped out some text..."/RETURN @threadid/ " should be a SQL comment: "slash asterick RETURN @threadid asterick slash",

so instead of doing a RETURN of @threadid, do SELECT @threadid so the result is returned as a result set...this appears to be a difference betwwen SQLExpress on a local machine vs. SQL Server on a server (but it was my understanding they are the same engine, but none the less, the original code does not work when running against SQL Server).
Jan 1, 2008 at 5:30 PM
Hi thanks for that, got it working now.

Feb 14, 2008 at 11:28 PM
Edited Feb 14, 2008 at 11:36 PM
It is so frustrating. I don't know why we are using Execute method.

Following statement generates incorrect threadid. the storeProc is so simple. When I uses profiler, it returns correct value. I think Execute method does not handle the return value.

threadid = SPs.AddMessage(msg.parentmsgid, msg.memberid, msg.body, msg.subject, AdminOnly, msg.topicid).Execute()

Even though I change the "Return @threadid" into "SELECT @threadid" at the end of store proc, I'm getting the same
result. Am I doing missing something?

Woop, It works now. I forgot to replace "Execute" with ExecuteScalar.
Feb 15, 2008 at 9:59 PM
Edited Feb 15, 2008 at 10:00 PM
I had the same issue. I try what jbwood1 suggested and It's working fine.

The only problem now is that I get a Divide by zero error encountered when a user (not the original admin) try to view or add a new thread. When I add a new thread it get created.

So for testing I added the user to all the groups and still got the error. It seem only the original admin don't get that error. Any help would be appreciated.

Stack Trace:
SqlException (0x80131904): Divide by zero error encountered.
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.NextResult() +162
System.Data.ProviderBase.DataReaderContainer.NextResult() +16
System.Data.Common.DataAdapter.FillNextResult(DataReaderContainer dataReader) +104
System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +238
System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +170
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +175
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.Repeater.GetData() +50
System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232
System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53
System.Web.UI.WebControls.Repeater.DataBind() +72
System.Web.UI.WebControls.Repeater.EnsureDataBound() +55
System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12
System.Web.UI.Control.PreRenderRecursiveInternal() +86
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041