CategoryMenu error

Jan 22, 2009 at 9:41 PM
Hi David,

I've tried installing NB_Store twice and sadly, I've failed twice.  The failure occurs after dropping CategoryMenu on a page, I get the following module load exception:

<><><><><><><><><><><><><><><><><><><><><><><><><>
DotNetNuke.Services.Exceptions.ModuleLoadException: Syntax error converting the nvarchar value 'False' to a column of data type bit. ---> System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'False' to a column of data type bit. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at DotNetNuke.Common.Utilities.CBO.FillCollection(IDataReader dr, Type objType) at NEvoWeb.Modules.NB_Store.CategoryController.GetCategories(Int32 PortalID, String Lang, Int32 ParentID, String Archived) at NEvoWeb.Modules.NB_Store.CategoryMenu.populateList(Int32 CatID) at NEvoWeb.Modules.NB_Store.CategoryMenu.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---
<><><><><><><><><><><><><><><><><><><><><><><><><>

I have set up one category, no products yet

I'm running DNN 4.9.0 using MSSQL 2000, IIS6 on W2003

Best wishes

Barry
Coordinator
Jan 23, 2009 at 7:14 AM
Hi Barry,

I've had some reports with version 01.00.08 and 01.00.09Beta, that the scripts had problems running on SQL2000.  But this should have been fixed in version 01.00.09.  Could you confirm what version your are running, and I'll look into a solution for you.

If you are running version 01.00.09 was it a clean installation or did you load the 01.00.08 or the Beta first?

Regards,
Dave.
Jan 23, 2009 at 8:11 AM
Hi David,

I'm running 1.00.09.

I did have 1.00.08, but before installing .09 I uninstalled each individual module with files, and then checked that the database had no NB_Store tables in it.  So as best I could manage, a clean installation.

As an aside, this is a general module question, is it possible when uninstalling a module, to have the uninstallation routine uninstall each individual part of that module (on selection of a checkbox), or is this not possible within dnn, ie can the manifest file be used to undo everything when only one part of the module is targetted for removal?

Many thanks

Barry
Coordinator
Jan 23, 2009 at 9:01 AM
Hi Barry,

Yes, what you did cleaned the database and modules from DNN, so it's not a legacy issue.

I've had a quick look and can see a possible area where a problem could appear on SQL2000.  I've not got any installations of SQL2000 to test with but if you try running the following script from HOST>SQL (make sure you tick the "run as script" option)

ALTER     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Categories_GetList]
@PortalID int,
@Lang nchar(5),
@ParentCategoryID int,
@Archived nvarchar(5)
AS
begin

if @ParentCategoryID = -1
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from {databaseOwner}[{objectQualifier}NB_Store_Categories] as C
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join {databaseOwner}[{objectQualifier}NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by PC.ListOrder, PCL.CategoryName, C.ListOrder, CL.CategoryName

end
else
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from {databaseOwner}[{objectQualifier}NB_Store_Categories] as C
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join {databaseOwner}[{objectQualifier}NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and C.ParentCategoryID = @ParentCategoryID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by C.ListOrder, CL.CategoryName

end

 
end



The thing to note is: C.Archived = convert(bit,@Archived)
The SQL2005 version was: C.Archived = @Archived)

The SQL2005 applies the conversion without being told (something I've just learnt, I would have expected an error in 2005 as well, that's why I missed it).

As I said, I can't test this so please get back to me and let me know if this solves it, so if anyonelse has a problem they know the answer.


In answer to your other question, NO.  If I did do somthing like that I couldn't be sure it would work in future releases of DNN.  However when you want to uninstall NB_Store you only need uninstall the NB_Store_ManagerMenu  this will uninstall everything except the other modules database placement entries, if you then re-install a new version of NB_Store, it should be fine. (Although for peace of mind I would recommend uninstalling all modules if you can!)

Regards,
Dave.





Jan 23, 2009 at 9:39 AM
Hi Dave,

I'm afraid that this hasn't solved my particular problem, here's my newly updated sproc:

<><><><><><><><><><><><><><><><><><><><>
CREATE     PROCEDURE dbo.[NEvoweb_NB_Store_Categories_GetList]
@PortalID int,
@Lang nchar(5),
@ParentCategoryID int,
@Archived nvarchar(5)
AS
begin

if @ParentCategoryID = -1
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from dbo.NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from dbo.[NB_Store_Categories] as C
left outer join dbo.NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join dbo.[NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join dbo.NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by PC.ListOrder, PCL.CategoryName, C.ListOrder, CL.CategoryName

end
else
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from dbo.NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from dbo.[NB_Store_Categories] as C
left outer join dbo.NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join dbo.[NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join dbo.NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and C.ParentCategoryID = @ParentCategoryID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by C.ListOrder, CL.CategoryName

end

 
end

GO

<><><><><><><><><><><><><><><><><><><><>

I restarted the application, just to be sure, and the error message that I get is :

<><><><><><><><><><><><><><><><><><><><>
Error: NB_Store_CategoryMenu is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Syntax error converting the nvarchar value 'False' to a column of data type bit. ---> System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'False' to a column of data type bit. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at DotNetNuke.Common.Utilities.CBO.FillCollection(IDataReader dr, Type objType) at NEvoWeb.Modules.NB_Store.CategoryController.GetCategories(Int32 PortalID, String Lang, Int32 ParentID, String Archived) at NEvoWeb.Modules.NB_Store.CategoryMenu.populateList(Int32 CatID) at NEvoWeb.Modules.NB_Store.CategoryMenu.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---
<><><><><><><><><><><><><><><><><><><><>

Hope this helps

Barry


<><><><><><><><><><><><><><><><><><><><>

<><><><><><><><><><><><><><><><><><><><>
Coordinator
Jan 23, 2009 at 10:11 AM
Dam!!!... I thought that would work!

I have another thought, that perhaps it's the fact SQL2000 doesn't recognise 'False' as a bit. (long time since I've use SQL2000 ;-) )

Could you try this script please...
>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ALTER     PROCEDURE {databaseOwner}[{objectQualifier}NEvoweb_NB_Store_Categories_GetList]
@PortalID int,
@Lang nchar(5),
@ParentCategoryID int,
@Archived nvarchar(5)
AS
begin

if not @Archived=''
begin
    if @Archived='False'
    begin
        set @Archived='0'
    end
    else
    begin
        set @Archived='1'
    end
end

if @ParentCategoryID = -1
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from {databaseOwner}[{objectQualifier}NB_Store_Categories] as C
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join {databaseOwner}[{objectQualifier}NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by PC.ListOrder, PCL.CategoryName, C.ListOrder, CL.CategoryName

end
else
begin

    select
        C.CategoryID,
        CL.Lang,
        CL.CategoryName,
        CL.CategoryDesc,
        CL.[Message],
        C.PortalID,
        C.Archived,
        C.CreatedByUser,
        C.CreatedDate,
        C.ParentCategoryID,
        PCL.CategoryName as ParentName,
        C.ListOrder,
        (select count(ProductID) from {databaseOwner}{objectQualifier}NB_Store_Products as P where P.categoryid = C.CategoryID) as ProductCount
from {databaseOwner}[{objectQualifier}NB_Store_Categories] as C
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS CL on CL.CategoryID = C.CategoryID and CL.Lang = @Lang
left outer join {databaseOwner}[{objectQualifier}NB_Store_Categories] as PC on C.ParentCategoryID = PC.CategoryID
left outer join {databaseOwner}{objectQualifier}NB_Store_CategoryLang AS PCL on PCL.CategoryID = PC.CategoryID and PCL.Lang = @Lang
where C.PortalID = @PortalID
and C.ParentCategoryID = @ParentCategoryID
and (C.Archived = convert(bit,@Archived) or @Archived ='')
order by C.ListOrder, CL.CategoryName

end

 
end

>>>>>>>>>>>>>>>>>>>>>>>>>

If this doesn't work I think I'll have to find a SQL2000 installation out of the archive!

Regards,
Dave



Coordinator
Jan 23, 2009 at 10:26 AM
Hi Barry,

I've just ran my local SQL2005 in compatibility mode for SQL2000 and this SPROC works fine?

Maybe I'm jumping down the wrong tunnel with this fix?

Anyway try the SPROC and see, in the meantime I'll tray and do a clean instalation with compatibility set to SQL2000 and see if I can recreate the problem.

Regards,
Dave.
Coordinator
Jan 23, 2009 at 11:02 AM
Hi Barry,

I've just done a fresh installation of the store with compatibility mode set to SQL2000 and everything seems OK (It could be compatibility mode doesn't pick this up).

Have you put any settings on the modules?

Regards,
Dave.
Jan 24, 2009 at 10:28 AM
Hi Dave,

apologies for my late reply.  I have not changed any settings on the modules, it happens when the category menu is dropped on the page.

*Update*
Aha!  You'll be pleased to know that running the second sproc update has cleared the problem.  However, to try to find out if it's peculiar to  my set up, I'll create a fresh dnn installation and put NB Store on there before I do anything else.

Thanks very much for your help

Barry