YAFLogo

herman_herman
13 years ago
Installing as fresh copy or upgrading from previous version gives the follwoing error whenever you click topic or forum links.

Syntax error converting the nvarchar value 'True' to a column of data type bit.
Sponsor

herman_herman
13 years ago
Some more information for you dear Programmers:cheesy:
It only happens when you set TinyMce as your editor.

Good luck
herman_herman
13 years ago
Any idea?
I upgraded from 1.9.3 to 1.9.4 Beta just by copying files and not running Installation (SQL scripts).Everything was working fine but as soon as I ran the install wizard the above error appears whenever users try to access topics!!
Probably a True/False is replace by 1/0 in SQL! I am not sure but tracing SQL I get down to Table.sql file (somewhere in it:cheesy: )
Any comments?

Thanks
jshepler
13 years ago
Well, you didn't post the stack trace (or even the stored procedure being called) so nobody is going to know where to start looking other than "when clicking topic or forum links". Mentioning that it only happens when using TinyMCE is helpful, but nowhere near as useful as the stack trace.

Here's a shot in the dark...

The error tells us SQL server is receiving a string and not a bit (bool). Backtrack the stack trace until you find the code where the SqlCommand parameters are being set. Chances are the method parameters for that block of code are objects (instead of strongly-typed parameters) and the command parameter isn't setting the sql type.

I think ADO is smart enough to determine the actual type. You should run it through the debugger with a breakpoint there (where the SqlCommand parameter is being set) so you can see what the actual type being passed is. If it's not a bool, then you'll have to backtrack through the debugger's call stack to find where the value is being set to see why it's not a bool and fix it.


Or it could be something completely different and I'll shut up now...

not jsheLPer
herman_herman
13 years ago
One step closer: It does not matter which editor your choose.As soon as you change the editor error show up:

And all I have in my pocket:

Quote:

Server Error in '/YetAnotherForum.NET' Application.



Syntax error converting the nvarchar value 'True' to a column of data type bit.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'True' to a column of data type bit.

Source Error:


Line 433: else // no transaction
Line 434: {
Line 435: da.Fill( ds );
Line 436: }
Line 437:


Source File: D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs Line: 435

Stack Trace:


[SqlException (0x80131904): Syntax error converting the nvarchar value 'True' to a column of data type bit.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
YAF.Classes.Data.YafDBAccess.GetDatasetBasic(SqlCommand cmd, Boolean transaction) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:435
YAF.Classes.Data.YafDBAccess.GetData(SqlCommand cmd, Boolean transaction) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:461
YAF.Classes.Data.YafDBAccess.GetData(SqlCommand cmd) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:453
YAF.Classes.Data.DB.topic_list(Object forumID, Object userId, Object announcement, Object date, Object offset, Object count) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\DB.cs:3518
YAF.Pages.topics.BindData() in d:\Documents and Settings\Administrator\Desktop\yafsrc\YetAnotherForum.NET\pages\topics.ascx.cs:222
YAF.Pages.topics.Page_Load(Object sender, EventArgs e) in d:\Documents and Settings\Administrator\Desktop\yafsrc\YetAnotherForum.NET\pages\topics.ascx.cs:109
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627





Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053

jshepler
13 years ago
Quote:

Server Error in '/YetAnotherForum.NET' Application.



Syntax error converting the nvarchar value 'True' to a column of data type bit.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'True' to a column of data type bit.

Source Error:


Line 433: else // no transaction
Line 434: {
Line 435: da.Fill( ds );
Line 436: }
Line 437:


Source File: D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs Line: 435

Stack Trace:


[SqlException (0x80131904): Syntax error converting the nvarchar value 'True' to a column of data type bit.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
YAF.Classes.Data.YafDBAccess.GetDatasetBasic(SqlCommand cmd, Boolean transaction) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:435
YAF.Classes.Data.YafDBAccess.GetData(SqlCommand cmd, Boolean transaction) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:461
YAF.Classes.Data.YafDBAccess.GetData(SqlCommand cmd) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\YafDBAccess.cs:453
YAF.Classes.Data.DB.topic_list(Object forumID, Object userId, Object announcement, Object date, Object offset, Object count) in D:\Documents and Settings\Administrator\Desktop\yafsrc\YAF.Classes\YAF.Classes.Data\DB.cs:3518
YAF.Pages.topics.BindData() in d:\Documents and Settings\Administrator\Desktop\yafsrc\YetAnotherForum.NET\pages\topics.ascx.cs:222
YAF.Pages.topics.Page_Load(Object sender, EventArgs e) in d:\Documents and Settings\Administrator\Desktop\yafsrc\YetAnotherForum.NET\pages\topics.ascx.cs:109
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627





Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053



I highlighted the relevant line in the stack trace. The stored procedure is expecting one of the parameters being set in that block of code to be a bit (bool).

not jsheLPer
herman_herman
13 years ago
Getting closer.The problem is in "Procedure.sql" and has been introduced in revision 2611.I am checking to see what is wrong.
If you roll back to revision 2610 it works fine.
herman_herman
13 years ago
Another finding
Looks like a diary now:cheesy:
It is not the matter of editors.whatever you change in "Host Settings" causes this error (Display,Features, etc...)
Probably next time it will explode :lol:
jshepler
13 years ago
herman_herman wrote:

Getting closer.The problem is in "Procedure.sql"



How are you making this determination?

The .sql files are just text files (not .net code) that are used during yaf installation to install/upgrade the database. These files aren't touched when "clicking on forum or topic links" nor anywhere in the stack trace you posted.

not jsheLPer
jshepler
13 years ago
Figured it out. Looking at the latest SVN, the yaf_topic_list proc doesn't take a bit parameter so my guess was wrong. However, there is a line that's converting an nvarchar to a bit:

SELECT @ShowMoved = CAST(CAST(Value as NVarChar) AS BIT) FROM [dbo.yaf_Registry] WHERE Name='showmoved'

This doesn't work in SQL Server 2000 and lower - it is not able to convert the words "True" and "False" to bit values. The select will need to use a CASE block:

SELECT @ShowMoved = CASE WHEN CAST(Value as NVarChar) = 'True' THEN 1 ELSE 0 END FROM [dbo.yaf_Registry] WHERE Name='showmoved'

not jsheLPer
Jaben
  • Jaben
  • 100% (Exalted)
  • YAF Developer
13 years ago
Ugg. Frankly SQL 2K support is probably going out for v1.9.4 final. YAF needs to be cleaner and leaner if it's going to continue into the future. I really don't have the energy and time to support all types of legacy configurations. We are moving into a new decade now -- time to get a newer version of SQL Server.
jshepler
13 years ago
Jaben wrote:

Ugg. Frankly SQL 2K support is probably going out for v1.9.4 final. YAF needs to be cleaner and leaner if it's going to continue into the future. I really don't have the energy and time to support all types of legacy configurations. We are moving into a new decade now -- time to get a newer version of SQL Server.



I agree. Maybe it'd be a good idea to list the environment(s) yaf has been tested on (including SQL and IIS requirements)?

not jsheLPer
herman_herman
13 years ago
I just came here triumphantly to report that the problem is the one jshepler just found:roll:
I commented out that line and yaf start working again so i concluded that it is in that line.anyway..Thanks for fix but may i correct your syntax?

It should be:
Quote:

SELECT @ShowMoved = CASE WHEN CAST(Value as NVarChar) = 'True' THEN 1 ELSE 0 END FROM [dbo].[yaf_Registry] WHERE Name='showmoved'




And not:
Quote:

SELECT @ShowMoved = CASE WHEN CAST(Value as NVarChar) = 'True' THEN 1 ELSE 0 END FROM [dbo.yaf_Registry] WHERE Name='showmoved'



Am I right jshepler?

And as for Jaben:

Quote:

Ugg. Frankly SQL 2K support is probably going out for v1.9.4 final. YAF needs to be cleaner and leaner if it's going to continue into the future. I really don't have the energy and time to support all types of legacy configurations. We are moving into a new decade now -- time to get a newer version of SQL Server.



I personally do not agree.Although ,It is Jaben's work and he should decide which one suite him better but in my point of view as a simple and novice user I think that, if you switch to SQL2005 (Only) you will definitely loose lots of users as SQL2005 hosting is more expensive than SQL2000 (Almost double in in my country).
This was the matter of discussion Some time ago.I suggested that support for MySql should become part of yaf (officially) As MySQL is Free-OpenSource and hosting fee is much lower than MSSQL.(The same story is behind PHP)
From other side when you switch yaf to 2005 you are probably going to have lots of problems regarding users who want to upgrade from 2000 to 2005 just in case you do not want to drop support for upgrading.If you draw more user to Yaf and make yaf more popular then it is possible to make it more beneficial from commercial point of view,for example by selling additional modules, eye-candy themes,additional features,advertisements, professional support etc...
I think yaf project lacks professional and dedicated developers.Just a simple glance at SVN log shows that Jaben is carrying almost all the loads of this project alone and this is not acceptable for an enterprise level Bulletin Board software like Yaf.
Having said that, all these things are just my personal opinions.We already owe Yaf a lot.This amount of time,work,coding etc...all for free and even with the source, is nothing but just an amazing amount of benevolence and generosity and dedication to .net community.It is a real shame that Micro$oft has not given you at least a MVP award:evil:



Jaben
  • Jaben
  • 100% (Exalted)
  • YAF Developer
13 years ago
Thanks for your comments, Herman_Herman. Mek is on SQL 2k, too. I'm just at a loss sometimes. For instance, I "fixed" the major performance issues with YAF, but they can't work on SQL 2k I believe (someone might be able to prove me wrong). Makes the situation difficult going forward. Backwards compatibility is hard for even enterprise level commercial development.

Bbobb is supporting different DBs for YAF -- so MySQL is an option.

Of course, I would like more recognition. Frankly, I'd just like MS to see how valuable this project is to the ASP.NET community and put me on some kind of retainer. 🙂
jshepler
13 years ago
herman_herman wrote:


Am I right jshepler?


Probably. I don't use that syntax and fudged it. Sorry.

herman_herman wrote:


I personally do not agree.Although ,It is Jaben's work and he should decide which one suite him better but in my point of view as a simple and novice user I think that, if you switch to SQL2005 (Only) you will definitely loose lots of users as SQL2005 hosting is more expensive than SQL2000 (Almost double in in my country).
This was the matter of discussion Some time ago.I suggested that support for MySql should become part of yaf (officially) As MySQL is Free-OpenSource and hosting fee is much lower than MSSQL.(The same story is behind PHP)



While I can understand a case can be made to not drop SQL 2000, a line must be drawn somewhere and that line must move as progess is made. SQL 2000 is almost 10 years old, there's been 2 versions since then and a new one is around the corner (maybe). MS doesn't even support SQL 2000 anymore . Maintaining backwards compatibility is a LOT of work (probably the main reason Windows pataches take so long to test/release). I'm of the opinion that yaf has supported SQL 2000 far too long already. Maybe since Mek is still using it he'll keep yaf compatible, but I think it's unrealistic to expect yaf to remain compatible with obsolete systems.

I'm sorry your hosting provider charges more for 2005 than 2000. That doesn't make any sense to me and the couple of hosting providers I looked up didn't change more. I'm not saying I don't believe you, I'm suggesting you might want to look elsewhere for your hosting needs. Even if yaf continues supporting SQL 2000 now, it definately will stop eventually. Probably sooner rather than later.

not jsheLPer
YAF Logo Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved
About Us

The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

Powered by Resharper Donate with PayPal button