YAFLogo

herman_herman
15 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.
View All Posts by User
Sponsor
herman_herman
15 years ago
Some more information for you dear Programmers:cheesy:

It only happens when you set TinyMce as your editor.

Good luck

View All Posts by User
herman_herman
15 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

View All Posts by User
jshepler
15 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

View All Posts by User
herman_herman
15 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:

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

View All Posts by User
jshepler
15 years ago

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

View All Posts by User
herman_herman
15 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.

View All Posts by User
herman_herman
15 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:

View All Posts by User
jshepler
15 years ago

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

herman_herman wrote:

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

View All Posts by User
jshepler
15 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

View All Posts by User
Jaben
  • Jaben
  • 100% (Exalted)
  • YAF Developer
15 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.
View All Posts by User
jshepler
15 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.

Jaben wrote:

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

View All Posts by User
herman_herman
15 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:

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

And not:

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:

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:

View All Posts by User
Jaben
  • Jaben
  • 100% (Exalted)
  • YAF Developer
15 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. 🙂

View All Posts by User
jshepler
15 years ago

Am I right jshepler?

herman_herman wrote:

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

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)

herman_herman wrote:

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

View All Posts by User
Similar Topics
Users browsing this topic