Hi,
I'm busy developing a tagging module in DNN 4.5.1 Basically it would allow users to tag a page, view and maintain their tags, and see tag clouds containning their tags, or other users tags, or most popular tags etc. you know your average de.licio.us rip-off.
The problem I'm having is with my SQL script, sql and stored procedures is not my strong point. Basically I adapted the sql script used for the Survey module. When I remove all the refferences to {databaseOwner}{objectQualifier} from the script, and run it in SQL server management studio's query analyzer, all is fine, and I get no errors. but when I run the script in DNN I get the following message. -->
System.Data.SqlClient.SqlException: Invalid column name 'TagID'. Invalid column name 'UserID'. Invalid column name 'TagDate'. Invalid column name 'TagText'. Invalid column name 'TagUrl'. Invalid column name 'TagHistory'. Invalid column name 'TagQty'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) create procedure dbo.GetTag @TagID int, @UserID int as select TagID, UserID, TagDate, TagText, TagUrl, TagHistory, TagQty
Here is the script I'm using --->
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}GetTag') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}GetTag
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}AddTag') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}AddTag
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}DeleteTag') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}DeleteTag
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}JPTags') and
OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}JPTags (
[TagID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[TagDate] [datetime] NOT NULL,
[TagText] [nvarchar] (500) NULL ,
[TagUrl] [nvarchar] (500) NULL ,
[TagHistory] [int] NULL ,
[TagQty] [int] NULL ,
) ON [PRIMARY]
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'PK_{objectQualifier}JPTags') and OBJECTPROPERTY(id,
N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}JPTags ADD CONSTRAINT
PK_{objectQualifier}JPTags PRIMARY KEY CLUSTERED
(
TagID
) ON [PRIMARY]
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'FK_{objectQualifier}JPTags_{objectQualifier}Users') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}JPTags ADD CONSTRAINT
FK_{objectQualifier}JPTags_{objectQualifier}Users FOREIGN KEY
(
UserID
) REFERENCES {databaseOwner}{objectQualifier}Users
(
UserID
) ON DELETE CASCADE
NOT FOR REPLICATION
GO
create procedure {databaseOwner}{objectQualifier}GetTag
@TagID int,
@UserID int
as
select TagID,
UserID,
TagDate,
TagText,
TagUrl,
TagHistory,
TagQty
GO
create procedure {databaseOwner}{objectQualifier}AddTag
@UserID int,
@TagDate datetime,
@TagText nvarchar(500),
@TagUrl nvarchar(500),
@TagHistory int,
@TagQty int
as
insert into {objectQualifier}JPtags (
UserID,
TagDate,
TagText,
TagUrl,
TagHistory,
TagQty
)
values (
@UserID,
@TagDate,
@TagText,
@TagUrl,
@TagHistory,
@TagQty
)
select SCOPE_IDENTITY()
GO
create procedure {databaseOwner}{objectQualifier}DeleteTag
@TagID int
as
delete
from {objectQualifier}JPTags
where TagID = @TagID
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/
Please, any help would be welcome, Like I said, SQL and stored procedures is not one of my strengths, so maybe the error is really obvious.
What would happen If I created the table's manually in my db? (besides from the fact that I wont be able to package and distribute my module to the public)?
OR ALTERNATIVELY
Does anyone know about a tagging module out there? something that works simmilar to de.liscio.us?
Once again, any help would be appreciated.
Thanking you in advance
Julian
|