tried to search - got critical error - Help Module [Lead: Phil Beadle]

Forum DotNetNuke® Pro... Help Module [Le... tried to search - got critical error

11/1/2006 8:16 AM

	Peter
43 posts

tried to search - got critical error

hi guys, i tested the search button in the category module and it gave me this error:

A critical error has occurred.
The stored procedure 'dbo.Help_SearchTutorials' doesn't exist.

can this be fixed?

Thanks

11/9/2006 8:08 PM

	Philip Beadle
1142 posts
www.philipbeadle.net

Re: tried to search - got critical error

Here it is. Don't know why it didnt work the irst time. Just paste this into the SQL window.

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Help_SearchTutorials]

@Search nvarchar(100),
@ModuleCategoryId int

SELECT DISTINCT
{databaseOwner}{objectQualifier}Help_Tutorials.TutorialGUID,
{databaseOwner}{objectQualifier}Help_Tutorials.TutorialID,
{databaseOwner}{objectQualifier}Help_Tutorials.Title,
{databaseOwner}{objectQualifier}Help_Tutorials.ProductImage,
{databaseOwner}{objectQualifier}Help_Tutorials.Featured,
{databaseOwner}{objectQualifier}Help_Tutorials.KeyWords,
{databaseOwner}{objectQualifier}Help_Tutorials.DownloadName,
{databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc,
{databaseOwner}{objectQualifier}Help_Tutorials.Archive,
Description ='',
0 AS ViewOrder
FROM      {databaseOwner}{objectQualifier}Help_Tutorials INNER JOIN
          {databaseOwner}{objectQualifier}Help_TutorialCategory ON {databaseOwner}{objectQualifier}Help_Tutorials.TutorialID = {databaseOwner}{objectQualifier}Help_TutorialCategory.TutorialID INNER JOIN
          {databaseOwner}{objectQualifier}Help_Category ON {databaseOwner}{objectQualifier}Help_TutorialCategory.CategoryID = {databaseOwner}{objectQualifier}Help_Category.CategoryID
WHERE     ({databaseOwner}{objectQualifier}Help_Category.ModuleID = @ModuleCategoryId AND {databaseOwner}{objectQualifier}Help_Category.Visible = 1)
AND
(
({databaseOwner}{objectQualifier}Help_Tutorials.Title LIKE N'%' + @Search + '%') OR
({databaseOwner}{objectQualifier}Help_Tutorials.KeyWords LIKE N'%' + @Search + '%') OR
({databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc LIKE N'%' + @Search + '%') OR
({databaseOwner}{objectQualifier}Help_Tutorials.Description LIKE N'%' + @Search + '%')
)

make sure you add 2 carriage returns at the end in the SQL window :)

Philip Beadle (DNN Core Team)
My Blog
My DNN Blog
TDD DNN Module Template

11/10/2006 12:58 AM

	Peter
43 posts

Re: tried to search - got critical error

hi philip, i pasted the code in the SQL window and clicked "execute" and i get "There is an error in your query!" so i checked the "Run as Script" box and it went ok, then i try searching from help and i get the following error:

A critical error has occurred.
Parameter count does not match Parameter Value count.

i am testing on godaddy's "medium trust" account.. does it make a difference? i am not that great at SQL stuff..

let me know if you can fix this

Thanks
Peter

11/17/2006 2:22 AM

	ErikVB
2710 posts
www.apollo-software.nl

Re: tried to search - got critical error

Philip,

the 02.00.04 SQLdataprovider file has this for the Help_SearchTutorials sproc:

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Help_SearchTutorials]

@Search nvarchar(100)

AS

SELECT
  {databaseOwner}{objectQualifier}Help_Tutorials.TutorialGUID,
  {databaseOwner}{objectQualifier}Help_Tutorials.TutorialID,
  {databaseOwner}{objectQualifier}Help_Tutorials.Title,
  {databaseOwner}{objectQualifier}Help_Tutorials.ProductImage,
  {databaseOwner}{objectQualifier}Help_Tutorials.Featured,
  {databaseOwner}{objectQualifier}Help_Tutorials.KeyWords,
  {databaseOwner}{objectQualifier}Help_Tutorials.DownloadName,
  {databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc,
  {databaseOwner}{objectQualifier}Help_Tutorials.Archive,
  {databaseOwner}{objectQualifier}Help_Tutorials.Description,
        0 AS ViewOrder
FROM {databaseOwner}{objectQualifier}Help_Tutorials
WHERE   ({databaseOwner}{objectQualifier}Help_Tutorials.Title LIKE N'%' + @Search + '%') OR
  ({databaseOwner}{objectQualifier}Help_Tutorials.KeyWords LIKE N'%' + @Search + '%') OR
        ({databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc LIKE N'%' + @Search + '%') OR
  ({databaseOwner}{objectQualifier}Help_Tutorials.Description LIKE N'%' + @Search + '%')

GO

This sproc seems to work fine with the 02.00.04 version of the module. Could it be that you displayed a sproc from a newer version of the module here?

The reason why this didnt make it in the install is that the reference to the 02.00.04.sqldataprovider is missing in the help.dnn file

For those of you how did add the sproc Philip mentioned to their portal, please change the sproc like this:

ALTER PROCEDURE {databaseOwner}[{objectQualifier}Help_SearchTutorials]

@Search nvarchar(100)

AS

SELECT
  {databaseOwner}{objectQualifier}Help_Tutorials.TutorialGUID,
  {databaseOwner}{objectQualifier}Help_Tutorials.TutorialID,
  {databaseOwner}{objectQualifier}Help_Tutorials.Title,
  {databaseOwner}{objectQualifier}Help_Tutorials.ProductImage,
  {databaseOwner}{objectQualifier}Help_Tutorials.Featured,
  {databaseOwner}{objectQualifier}Help_Tutorials.KeyWords,
  {databaseOwner}{objectQualifier}Help_Tutorials.DownloadName,
  {databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc,
  {databaseOwner}{objectQualifier}Help_Tutorials.Archive,
  {databaseOwner}{objectQualifier}Help_Tutorials.Description,
        0 AS ViewOrder
FROM {databaseOwner}{objectQualifier}Help_Tutorials
WHERE   ({databaseOwner}{objectQualifier}Help_Tutorials.Title LIKE N'%' + @Search + '%') OR
  ({databaseOwner}{objectQualifier}Help_Tutorials.KeyWords LIKE N'%' + @Search + '%') OR
        ({databaseOwner}{objectQualifier}Help_Tutorials.ShortDesc LIKE N'%' + @Search + '%') OR
  ({databaseOwner}{objectQualifier}Help_Tutorials.Description LIKE N'%' + @Search + '%')

GO

and run it from Host > SQL

For those of you that read this prior to installing the module, just add this to the .dnn file:

        <file>
          <name>02.00.03.SqlDataProvider</name>
        </file>

(in the already existing <files> section of the file)

Erik van Ballegoij, Apollo Software
If what i am saying is wrong: blame Google
MLSkinHelper: the easy way to put editable text in your skin!

11/20/2006 2:48 AM

	Jan Olsmar
348 posts
www.olsmar.com/

Re: tried to search - got critical error

I did the changes below (abow) but is still not working. I have help dll 204.25045 and 101.25045

I get this

Message: DotNetNuke.Services.Exceptions.PageLoadException: Parameter count does not match Parameter Value count. ---> System.ArgumentException: Parameter count does not match Parameter Value count. at Microsoft.ApplicationBlocks.Data.SqlHelper.AssignParameterValues(SqlParameter[] commandParameters, Object[] parameterValues) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Modules.Help.SqlDataProvider.SearchTutorials(String searchText) at DotNetNuke.Modules.Help.HelpController.SearchTutorials(String searchText) at DotNetNuke.Modules.Help.Help.BindList(String searchText, Int32 categoryID) at DotNetNuke.Modules.Help.Help.OnModuleCommunication(Object s, ModuleCommunicationEventArgs e) at DotNetNuke.Modules.Help.NavPane.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint

Page 1 of 3

Forum

DotNetNuke® Pro...

Help Module [Le...

tried to search - got critical error