DNN SQL Data Provider Syntax issue

Question

I'm new to DNN module development and web dev in general. I keep getting a syntax error near "ALTER TABLE" while installing the module. I've used the steps outlined in Chris Hammond's guide. DNN Guide

Here's my code

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}name_table]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}name_table](
    [file_name] [nchar](50) NOT NULL,
    [guid_key] [uniqueidentifier] NOT NULL,

ALTER TABLE {databaseOwner}[{objectQualifier}name_table] ADD CONSTRAINT [PK_{databaseOwner}{objectQualifier}name_table] PRIMARY KEY CLUSTERED ([guid_key])

END
GO

Take a closer look at the screenshot on the DNN Guide.. you'll see that the end of the create table doesn't end in a , it ends in a ). — xQbert

VDWWD VDWWD · Accepted Answer · 2017-09-21T16:12:50

I think the problem is that you have an Alter statement nested within the create table statement. It should be like this, with the constraint inside the create table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}name_table]') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}name_table](
    [file_name] [nchar](50) NOT NULL,
    [guid_key] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_{objectQualifier}name_table] PRIMARY KEY CLUSTERED ( [guid_key] )
)
END
GO

Or separate

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}name_table]') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}name_table](
    [file_name] [nchar](50) NOT NULL,
    [guid_key] [uniqueidentifier] NOT NULL
)
END
GO

ALTER TABLE {databaseOwner}[{objectQualifier}name_table] ADD CONSTRAINT [PK_{databaseOwner}{objectQualifier}name_table] PRIMARY KEY CLUSTERED ([guid_key])
GO

DNN SQL Data Provider Syntax issue

1 Answers