search

DNNCommunity / DNN.Vendors

The Vendors module project allows admins to manage Vendor relationships and add Advertising banners to their site.
MIT License
7 stars 10 forks source link

Install doesn't create required DB objects #8

Closed roman-yagodin closed 6 years ago

roman-yagodin commented 7 years ago

Module installed on clean DNN 8.0.4 w/o any errors logged. But then I navigate to Admin / Vendors tab, the module throws critical error. Checking sys.tables shows that there is no Vendors table or anything like that. Executing 08.00.00.SqlDataProvider manually via Host / SQL - no errors, but still no luck as the script doesn't contain a single CREATE TABLE statement.

InnerMessage: Unknown object name "Vendors" InnerStackTrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at DotNetNuke.Data.PetaPoco.PetaPocoExt.ExecuteReader(Database database, String sql, Object[] args)
   at DotNetNuke.Data.SqlDataProvider.ExecuteReader(String procedureName, Object[] commandParameters)
   at Dnn.Modules.Vendors.Components.VendorsController.GetVendors(Int32 PortalId, Boolean UnAuthorized, Int32 PageIndex, Int32 PageSize, Int32& TotalRecords)
   at Dnn.Modules.Vendors.Vendors.SetDataSource()
   at Telerik.Web.UI.RadGrid.OnNeedDataSource(GridNeedDataSourceEventArgs e)
   at Telerik.Web.UI.RadGrid.ObtainDataSource(GridRebindReason rebindReason, Boolean IsBoundUsingDataSourceId)
   at Telerik.Web.UI.RadGrid.AutoDataBind(GridRebindReason rebindReason)
   at Telerik.Web.UI.RadGrid.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
viraya commented 7 years ago

This module came as default in the insallations of DNN v7. I installed a new DNN v7 website and copied the scripts of the tables it creates in order for the Vendors module to work.

Try the following queries on your database : 

/******* Vendors table creation ******/

USE [Database_Name]
GO

/****** Object:  Table [dbo].[Vendors]    Script Date: 9-1-2017 09:49:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Vendors](
    [VendorId] [int] IDENTITY(1,1) NOT NULL,
    [VendorName] [nvarchar](50) NOT NULL,
    [Street] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [Region] [nvarchar](50) NULL,
    [Country] [nvarchar](50) NULL,
    [PostalCode] [nvarchar](50) NULL,
    [Telephone] [nvarchar](50) NULL,
    [PortalId] [int] NULL,
    [Fax] [nvarchar](50) NULL,
    [Email] [nvarchar](50) NULL,
    [Website] [nvarchar](100) NULL,
    [ClickThroughs] [int] NOT NULL,
    [Views] [int] NOT NULL,
    [CreatedByUser] [nvarchar](100) NULL,
    [CreatedDate] [datetime] NULL,
    [LogoFile] [nvarchar](100) NULL,
    [KeyWords] [ntext] NULL,
    [Unit] [nvarchar](50) NULL,
    [Authorized] [bit] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Cell] [varchar](50) NULL,
 CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED 
(
    [VendorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_Vendors] UNIQUE NONCLUSTERED 
(
    [PortalId] ASC,
    [VendorName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Vendors] ADD  CONSTRAINT [DF_Vendors_ClickThroughs]  DEFAULT ((0)) FOR [ClickThroughs]
GO

ALTER TABLE [dbo].[Vendors] ADD  CONSTRAINT [DF_Vendors_Views]  DEFAULT ((0)) FOR [Views]
GO

ALTER TABLE [dbo].[Vendors] ADD  CONSTRAINT [DF_Vendors_Authorized]  DEFAULT ((1)) FOR [Authorized]
GO

ALTER TABLE [dbo].[Vendors]  WITH CHECK ADD  CONSTRAINT [FK_Vendor_Portals] FOREIGN KEY([PortalId])
REFERENCES [dbo].[Portals] ([PortalID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Vendors] CHECK CONSTRAINT [FK_Vendor_Portals]
GO

/******* Banners table creation ******/

USE [Database_Name]
GO

/****** Object:  Table [dbo].[Banners]    Script Date: 9-1-2017 09:47:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Banners](
    [BannerId] [int] IDENTITY(1,1) NOT NULL,
    [VendorId] [int] NOT NULL,
    [ImageFile] [nvarchar](100) NULL,
    [BannerName] [nvarchar](100) NOT NULL,
    [Impressions] [int] NOT NULL,
    [CPM] [float] NOT NULL,
    [Views] [int] NOT NULL,
    [ClickThroughs] [int] NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [CreatedByUser] [nvarchar](100) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [BannerTypeId] [int] NULL,
    [Description] [nvarchar](2000) NULL,
    [GroupName] [nvarchar](100) NULL,
    [Criteria] [bit] NOT NULL,
    [URL] [nvarchar](255) NULL,
    [Width] [int] NOT NULL,
    [Height] [int] NOT NULL,
 CONSTRAINT [PK_Banner] PRIMARY KEY CLUSTERED 
(
    [BannerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Banners] ADD  CONSTRAINT [DF_Banners_Views]  DEFAULT ((0)) FOR [Views]
GO

ALTER TABLE [dbo].[Banners] ADD  CONSTRAINT [DF_Banners_ClickThroughs]  DEFAULT ((0)) FOR [ClickThroughs]
GO

ALTER TABLE [dbo].[Banners] ADD  CONSTRAINT [DF_Banners_Criteria]  DEFAULT ((1)) FOR [Criteria]
GO

ALTER TABLE [dbo].[Banners] ADD  CONSTRAINT [DF_Banners_Width]  DEFAULT ((0)) FOR [Width]
GO

ALTER TABLE [dbo].[Banners] ADD  CONSTRAINT [DF_Banners_Height]  DEFAULT ((0)) FOR [Height]
GO

ALTER TABLE [dbo].[Banners]  WITH CHECK ADD  CONSTRAINT [FK_Banner_Vendor] FOREIGN KEY([VendorId])
REFERENCES [dbo].[Vendors] ([VendorId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Banners] CHECK CONSTRAINT [FK_Banner_Vendor]
GO

/******* Classification table creation ******/

USE [Database_Name]
GO

/****** Object:  Table [dbo].[Classification]    Script Date: 9-1-2017 09:47:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Classification](
    [ClassificationId] [int] IDENTITY(1,1) NOT NULL,
    [ClassificationName] [nvarchar](200) NOT NULL,
    [ParentId] [int] NULL,
 CONSTRAINT [PK_VendorCategory] PRIMARY KEY CLUSTERED 
(
    [ClassificationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Classification]  WITH CHECK ADD  CONSTRAINT [FK_Classification_Classification] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Classification] ([ClassificationId])
GO

ALTER TABLE [dbo].[Classification] CHECK CONSTRAINT [FK_Classification_Classification]
GO

/******* VendorClassification table creation ******/

USE [Database_Name]
GO

/****** Object:  Table [dbo].[VendorClassification]    Script Date: 9-1-2017 09:48:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VendorClassification](
    [VendorClassificationId] [int] IDENTITY(1,1) NOT NULL,
    [VendorId] [int] NOT NULL,
    [ClassificationId] [int] NOT NULL,
 CONSTRAINT [PK_VendorClassification] PRIMARY KEY CLUSTERED 
(
    [VendorClassificationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_VendorClassification] UNIQUE NONCLUSTERED 
(
    [VendorId] ASC,
    [ClassificationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[VendorClassification]  WITH CHECK ADD  CONSTRAINT [FK_VendorClassification_Classification] FOREIGN KEY([ClassificationId])
REFERENCES [dbo].[Classification] ([ClassificationId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[VendorClassification] CHECK CONSTRAINT [FK_VendorClassification_Classification]
GO

ALTER TABLE [dbo].[VendorClassification]  WITH CHECK ADD  CONSTRAINT [FK_VendorClassification_Vendors] FOREIGN KEY([VendorId])
REFERENCES [dbo].[Vendors] ([VendorId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[VendorClassification] CHECK CONSTRAINT [FK_VendorClassification_Vendors]
GO

The order is important since there are a couple of constraints and Foreign keys invovled.

roman-yagodin commented 7 years ago

Thanks, @viraya, I'll try this.

viraya commented 7 years ago

Great @roman-yagodin ! tell me if it worked

roman-yagodin commented 7 years ago

It works! :) Mostly OK, but still missing dbo.Affiliates.

This still need to be a part of SqlDataProvider script which included in the install package.

roman-yagodin commented 7 years ago

Resolved by 5fc7b7362303b0edfb93eb8df285a8785ce7c330, closing