Wrox Programmer Forums
|
BOOK: Professional DotNetNuke Module Programming ISBN: 978-0-470-17116-5
This is the forum to discuss the Wrox book Professional DotNetNuke Module Programming by Mitchel Sellers and Shaun Walker - Wrox DotNetNuke Series Editor; ISBN: 978-0-470-17116-5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional DotNetNuke Module Programming ISBN: 978-0-470-17116-5 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 20th, 2010, 09:58 PM
Registered User
 
Join Date: Jul 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Incorrect syntax near '{'.

Hello all..

I'm going through the book and I'm at the point where I setup the 01.00.00.SqlDataProvider and when I manually check the script for errors in SSMS by parsing it I get several errors refering to Incorrect syntax near '{'. Which almost completely revlove around {databaseOwner}.

I thought I may have missed something along the way so I downloaded the code from the Wrox site and even that script says the same error. I was wondering is this something that will through an error when I attempt to install the module?

The script details are below please let me know if I've missed something..

Code:
/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/** Create Table **/

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
		(
			[ModuleId] [int] NOT NULL,
			[EntryId] [int] NOT NULL IDENTITY(1, 1),
			[SubmitterName] nvarchar(255) NOT NULL,
			[SubmitterWebsite] nvarchar(255),
			[SubmitterComment] ntext,
			[SubmissionDate] DateTime NOT NULL,
			[IsApproved] BIT NOT NULL
		)

		ALTER TABLE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook] 
			ADD CONSTRAINT [PK_{objectQualifier}DNNModuleProgramming_CS_Guestbook] 
				PRIMARY KEY CLUSTERED  ([EntryId])
				
		CREATE NONCLUSTERED INDEX [IX_{objectQualifier}DNNModuleProgramming_CS_Guestbook] 
			ON {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook] 
				([ModuleID])

		ALTER TABLE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook] WITH NOCHECK 
			ADD CONSTRAINT [FK_{objectQualifier}DNNModuleProgramming_CS_Guestbook_{objectQualifier}Modules] 
				FOREIGN KEY ([ModuleID]) 
					REFERENCES {databaseOwner}[{objectQualifier}Modules]	
						([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
	END
GO


/** Drop Existing Stored Procedures **/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_InsertGuestbookEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_InsertGuestbookEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_ApproveGuestbookEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_ApproveGuestbookEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_DeleteGuestbookEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_DeleteGuestbookEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetApprovedEntries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetApprovedEntries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetAllEntries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetAllEntries]
GO


/** Create Stored Procedures **/
CREATE PROCEDURE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_InsertGuestbookEntry]
	@ModuleId INT,
	@SubmitterName NVARCHAR(255),
	@SubmitterWebsite NVARCHAR(255),
	@SubmitterComment NTEXT,
	@SubmissionDate DATETIME,
	@IsApproved BIT
AS
INSERT INTO {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
	(ModuleId, SubmitterName, SubmitterWebsite, SubmitterComment, SubmissionDate, IsApproved)
	VALUES
	(@ModuleId, @SubmitterName, @SubmitterWebsite, @SubmitterComment, @SubmissionDate, @IsApproved)
	
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_ApproveGuestbookEntry]
	@ModuleId INT,
	@EntryId INT
AS
UPDATE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
SET IsApproved = 1
WHERE ModuleId = @ModuleId
	AND EntryId = @EntryId
	
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_DeleteGuestbookEntry]
	@ModuleId INT,
	@EntryId INT
AS
DELETE FROM {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
WHERE ModuleId = @ModuleId
	AND EntryId = @EntryId
	
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetApprovedEntries]
	@ModuleId INT
AS
SELECT
	ModuleId,
	EntryId,
	SubmitterName,
	SubmitterWebsite,
	SubmitterComment,
	SubmissionDate,
	IsApproved
FROM {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
WHERE ModuleId = @ModuleId
	AND IsApproved = 1
ORDER BY SubmissionDate DESC

GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_GetAllEntries]
	@ModuleId INT
AS
SELECT
	ModuleId,
	EntryId,
	SubmitterName,
	SubmitterWebsite,
	SubmitterComment,
	SubmissionDate,
	IsApproved
FROM {databaseOwner}[{objectQualifier}DNNModuleProgramming_CS_Guestbook]
WHERE ModuleId = @ModuleId
ORDER BY SubmissionDate DESC

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/
Thanks in advance.
Bob
 
Old July 21st, 2010, 02:59 PM
Registered User
 
Join Date: Jul 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I got the answer to this when I posted the question in the DNN forums.

Bob - you can't test the script in SSMS without doing a substitution for {objectQualifier} and {databaseOwner}

What happens when this script is run, is that it is pre-processed by the DNN script engine, and a text find/replace is done on {objectQualifier} - from the value in your web.config (dnn_ by default) and {databaseOwner} - again from your web.config, and probably dbo. in most cases.

There's two options if you want to check syntax:
1. do a find/replace on those items in ssms and then check syntax
2. run it in your Host->sql script box, which uses the script engine to do the substitution for you.

It's a slightly tricky/messy part of DNN Module development, but it is very important as it gives your module flexibility to run on a variety of sql server setups. You should always prefix your objects with {databaseOwner}{objectQualifier} in the Sql, because all DNN installs tend to use a different combination depending on the adminsitrator, target environment and other factors.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 01:01 AM
Incorrect syntax near 'GO' vanik.cz Pro VB Databases 11 May 11th, 2009 04:59 PM
Incorrect syntax near '!' sinapra Classic ASP Databases 8 August 25th, 2004 02:15 AM
Incorrect syntax near '`' Hez Classic ASP Databases 3 August 5th, 2004 08:13 AM
Incorrect syntax...... Adam H-W Classic ASP Databases 8 June 21st, 2004 11:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.