Forums (Obsolete)

Member

yko - 10/29/2008 9:06:59 AM

'Must declare the scalar variable "@ID"' error

Hi,

I installed Kentico on local machine and created site.
The I decided to move it to dev server.
I did the following:
1. Backed up Kentico database and restored it on dev server.
2. Copied the web project folder to dev server.
3. Updated the CMSConnectionString value in the web.config file.

And now I'm getting the following error in the CMS Site manager.
When I'm trying to edit a stylesheet:

[DataConnection.ExecuteQuery]: Query: SELECT * FROM CMS_CssStyleSheet WHERE [StylesheetID] = @ID: caused exception: Must declare the scalar variable "@ID".
On the Settings -> Web site:
[DataConnection.ExecuteQuery]: Query: SELECT * FROM CMS_SettingsCategory WHERE [CategoryID] = @ID: caused exception: Must declare the scalar variable "@ID".
etc

Some pages are ok, for example, Site properties page.

Please help me to overcome this problem.
Thanks

Kentico Developer

kentico_zbysekn - 10/30/2008 4:25:06 AM

RE:'Must declare the scalar variable "@ID"' error

Hi,
Could you please check what database collation you use? You can check this if you connect to the SQL Server using the SQL Server Management Studio, righ-click the server and open the Properties dialog. The collation is listed on the General tab -> Server Collation. Please note that Kentico CMS requires that you use case-insensitive (CI) collation for example SQL_Latin1_General_CP1_CI_AS.

You can change database collation using this query:

ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AS

Could you please also check the regional settings of your computer - are you using Turkish settings? I'm afraid this may be caused by the fact that the letter i/I is case sensitive in Turkish, even if you use case-insensitive collations on SQL Server. The only workaround for this would be installing Kentico CMS on a machine with English regional settings.

Best Regards,
Zbysek Nemec

Member

yko - 10/30/2008 7:12:26 AM

RE:'Must declare the scalar variable "@ID"' error

Hi,

thanks for your reply.
Checked collation: SQL_Latin1_General_CP1_CI_AS. It's correct.
Language: English (United States)

The same database works on local machine but not on dev server.
There is a slash character in the connection string for KenticoCMS (CMSConnectionString) on dev server: parameter 'Server'.
Could this be a reason?

Thanks


Kentico Support	kentico_jurajo - 10/30/2008 8:55:27 AM RE:'Must declare the scalar variable "@ID"' error Hi, This is very strange. If it is OK for you, could you please send us the DB and project backups so we can inspect the issue? Please send it to support@kentico.com (or send us the URL where we can download it). Thank you in advance. Best Regards, Juraj Ondrus

Member

yko - 10/30/2008 9:48:02 AM

RE:'Must declare the scalar variable "@ID"' error

Hi,

also dev server has SQL Server 2000 and SQL Server 2005.
SQL Server 2000 is on default port and SQL Server 2005 on custom port.
My KenticoCMS database - on SQL Server 2005.
So I have tried this in CMSConnectionString (to avoid using slash character in server name):
"Server=127.0.0.1,port#;..."

Could this be a reason?

Thanks

Kentico Support

kentico_jurajo - 10/31/2008 10:24:43 AM

RE:'Must declare the scalar variable "@ID"' error

Hi,

The port number and slash in the server name have no effect on this. As I mentioned before, could you please send us the backups of the DB and project to support@kentico.com (or the download links) so we can inspect this issue?
Please refer to this forum thread.

I am sorry for bothering you, but there seems to be something strange.
Thank you.

Best Regards,
Juraj Ondrus

Certified Developer v7

moprsal-seznam - 5/26/2010 4:27:17 AM

RE:'Must declare the scalar variable "@ID"' error

Hi,
i have similliar problem.

I'm getting following error:
When i acces to Shopping Card:

Query: SELECT * FROM COM_ShoppingCart WHERE [ShoppingCartID] = @ID: caused exception: Must declare the scalar variable "@ID".

When i try to view product detail:

Query: SELECT * FROM [Forums_Forum] WHERE [ForumName] = @ForumName AND ForumGroupId IN (SELECT GroupId FROM [Forums_ForumGroup] WHERE GroupSiteId = @siteId): caused exception: Must declare the scalar variable "@ForumName".

Other page are good. I use windows server 2008 R2, MS SQL 2008.
I have same web site on win XP with MS SQL 2008 and there it is ok.

Please help me, thanks.

Certified Developer v7

moprsal-seznam - 5/26/2010 6:22:20 AM

RE:'Must declare the scalar variable "@ID"' error

And if i want install new site, i get this error:

ERROR: An error occurred: [DataConnection.ExecuteQuery]: Query: -- ============================================= -- Author: Name -- Create date: 25.7.2007 -- Description: Stores log records to DB -- ============================================= CREATE PROCEDURE [Proc_Analytics_ProcessLog] @SiteID int, @Codename nvarchar(50), @Culture nvarchar(10), @ObjectName nvarchar(450), @ObjectID int, @Hits int, @HourStart datetime, @HourEnd datetime, @DayStart datetime, @DayEnd datetime, @WeekStart datetime, @WeekEnd datetime, @MonthStart datetime, @MonthEnd datetime, @YearStart datetime, @YearEnd datetime AS BEGIN /* Declare the @statisticsID variable */ DECLARE @statisticsID int; SET @statisticsID = 0; SELECT @StatisticsID = StatisticsID FROM Analytics_Statistics WHERE (StatisticsSiteID = @SiteID) AND (StatisticsCode = @Codename) AND ((StatisticsObjectName = @ObjectName) OR (StatisticsObjectName IS NULL AND @ObjectName IS NULL)) AND ((StatisticsObjectID = @ObjectID) OR (StatisticsObjectID IS NULL OR @ObjectID IS NULL)) AND ((StatisticsObjectCulture = @Culture) OR (StatisticsObjectCulture IS NULL AND @Culture IS NULL)); /* If @statisticsID is 0 insert new record */ IF @statisticsID = 0 BEGIN INSERT INTO Analytics_Statistics (StatisticsSiteID, StatisticsCode, StatisticsObjectName, StatisticsObjectID, StatisticsObjectCulture) VALUES (@SiteID, @Codename, @ObjectName, @ObjectID, @Culture); /* Get StatisticsID */ SELECT @StatisticsID = StatisticsID FROM Analytics_Statistics WHERE (StatisticsSiteID = @SiteID) AND (StatisticsCode = @Codename) AND ((StatisticsObjectName = @ObjectName) OR (StatisticsObjectName IS NULL AND @ObjectName IS NULL)) AND ((StatisticsObjectID = @ObjectID) OR (StatisticsObjectID IS NULL OR @ObjectID IS NULL)) AND ((StatisticsObjectCulture = @Culture) OR (StatisticsObjectCulture IS NULL AND @Culture IS NULL)); END /* Declare @hitsID and @hitsCount variables */ DECLARE @hitsID int, @hitsCount int; SET @hitsCount = 0; SET @hitsID = 0; /* HOURS */ SELECT @hitsID = HitsID, @hitsCount = HitsCount FROM [Analytics_HourHits] WHERE HitsStatisticsID=@statisticsID AND HitsStartTime=@HourStart AND HitsEndTime=@HourEnd; IF @hitsID > 0 UPDATE [Analytics_HourHits] SET HitsCount=(@hitsCount+@Hits) WHERE HitsID=@hitsID; ELSE INSERT INTO [Analytics_HourHits] ([HitsStatisticsID],[HitsStartTime],[HitsEndTime],[HitsCount]) VALUES (@statisticsID,@HourStart,@HourEnd,@Hits); /* DAYS */ SET @hitsID = 0; SELECT @hitsID = HitsID, @hitsCount = HitsCount FROM [Analytics_DayHits] WHERE HitsStatisticsID=@statisticsID AND HitsStartTime=@DayStart AND HitsEndTime=@DayEnd; IF @hitsID > 0 UPDATE [Analytics_DayHits] SET HitsCount=(@hitsCount+@Hits) WHERE HitsID=@hitsID; ELSE INSERT INTO [Analytics_DayHits] ([HitsStatisticsID],[HitsStartTime],[HitsEndTime],[HitsCount]) VALUES (@statisticsID,@DayStart,@DayEnd,@Hits); /* WEEKS */ SET @hitsID = 0; SELECT @hitsID = HitsID, @hitsCount = HitsCount FROM [Analytics_WeekHits] WHERE HitsStatisticsID=@statisticsID AND HitsStartTime=@WeekStart AND HitsEndTime=@WeekEnd; IF @hitsID > 0 UPDATE [Analytics_WeekHits] SET HitsCount=(@hitsCount+@Hits) WHERE HitsID=@hitsID; ELSE INSERT INTO [Analytics_WeekHits] ([HitsStatisticsID],[HitsStartTime],[HitsEndTime],[HitsCount]) VALUES (@statisticsID,@WeekStart,@WeekEnd,@Hits); /* MONTHS */ SET @hitsID = 0; SELECT @hitsID = HitsID, @hitsCount = HitsCount FROM [Analytics_MonthHits] WHERE HitsStatisticsID=@statisticsID AND HitsStartTime=@MonthStart AND HitsEndTime=@MonthEnd; IF @hitsID > 0 UPDATE [Analytics_MonthHits] SET HitsCount=(@hitsCount+@Hits) WHERE HitsID=@hitsID; ELSE INSERT INTO [Analytics_MonthHits] ([HitsStatisticsID],[HitsStartTime],[HitsEndTime],[HitsCount]) VALUES (@statisticsID,@MonthStart,@MonthEnd,@Hits); /* YEARS */ SET @hitsID = 0; SELECT @hitsID = HitsID, @hitsCount = HitsCount FROM [Analytics_YearHits] WHERE HitsStatisticsID=@statisticsID AND HitsStartTime=@YearStart AND HitsEndTime=@YearEnd; IF @hitsID > 0 UPDATE [Analytics_YearHits] SET HitsCount=(@hitsCount+@Hits) WHERE HitsID=@hitsID; ELSE INSERT INTO [Analytics_YearHits] ([HitsStatisticsID],[HitsStartTime],[HitsEndTime],[HitsCount]) VALUES (@statisticsID,@YearStart,@YearEnd,@Hits); END : caused exception: Must declare the scalar variable "@StatisticsID". Must declare the scalar variable "@StatisticsID".


Kentico Support	kentico_zdenekc - 5/31/2010 10:32:47 AM RE:'Must declare the scalar variable "@ID"' error Hello, It seems there is either some inconsistency in the DB, or if the instance has been upgraded, the upgrade procedure was not successfully finished or at least some definitions were not correctly updated. Could you confirm there were no errors during upgrade? Regards, Zdenek


Certified Developer v7	moprsal-seznam - 6/17/2010 3:22:46 AM RE:'Must declare the scalar variable "@ID"' error Yes, its inconsistency in the DB, so i delete all polls in DB and now its work. Thank you