Counting Items In Categories
Oct 28, 2007
Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with
Like
Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)....
Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName
Which Produces
Real Estate12401 12
Extreme Sports3 4
I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports3 4
Cycling 5 0
View 2 Replies
ADVERTISEMENT
Aug 25, 2014
I am creating a report that will identify the website categories that our items are in. The purpose is to find items that are not in categories that they should be so they can be fixed. Attached is a csv of a subset of the data.
The data I attached has all of our Baskets by SKU (ItemNoSKU) and the associated web categories that those items are in. For example, we can see that ItemNoSKU AB107 is in web categories 4, 22, and 23.
What I have already done is get a Total Baskets vs Web Cat Baskets. I know the total baskets is 44:
Code:
SELECT MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY MerchCatDesc, MerchSubCatDesc
And I know the number of baskets in WebCat 23 is 43:
Code:
SELECT WebCatCd,MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY WebCatCd, MerchCatDesc, MerchSubCatDesc
So in this instance, I know there is 1 Basket ItemNoSKU that is not in WebCatCd 23. I need to list this "missing" ItemNoSKU along with the WebCatCd it is missing from. I am struggling on how to write the code to accomplish this for my full list of many categories and web categories.
View 11 Replies
View Related
Jun 22, 2004
Hi,
I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:
SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1
This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?
Cheers.
View 6 Replies
View Related
Nov 2, 2005
I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.
The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!
the table I have looks like this:
IDBadgeOrdNumberTimeDate
6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40
View 4 Replies
View Related
Jul 20, 2005
I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?
View 1 Replies
View Related
Mar 30, 2008
I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.
Categories========ID intParentID intCatName varchar(30)Items====IDCategoryIDItemID
There is a third table that links to items through ItemID, but this is not important for this problem!
What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.
An end result with two top-level categories and three subcategories in each might look like:
- Category 1 (20)---- Subcategory 1.1 (10)---- Subcategory 1.2 (5)---- Subcategory 1.3 (5)- Category 2 (14)---- Subcategory 2.1 (3)---- Subcategory 2.2 (4)---- Subcategory 2.3 (7)Hence the difficult bit is getting the total number in brackets for each category with subcategories.
What I have at the moment is:
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotalFROM Categories
What I would like is something along the lines of (pseudocode):
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotalFROM Categories
I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.
Thanks!
View 2 Replies
View Related
Jan 18, 2012
I have a table Category whose structure is as follows:
id
name
parentid
So i have number of nodes from the root of a specific category. How to get all the sub categories(ie nodes) for all parent categories?
View 14 Replies
View Related
Aug 9, 2007
I have an application which has a database table called Category. Its very simple, with fields id and categoryname.
My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.
Is this the best way? Or something else.
Thanks
View 2 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 Replies
View Related
Feb 17, 2007
Hi,
i'm devoloping a web application that contains categories for example
Category1
Category11
Category111
Category1111
...........
Category112
Category12
Category2
categories with categories with categories... someone can help me about designing an elegant table for this situation.
Thanks
Fdo.
View 2 Replies
View Related
Oct 27, 2004
Hi all,
Can any body tell me how to add sub-categories to a database (MS SQL Server 2000) please.
I have a db with the following tables set:
Products: ProductID, CategoryID, ProductName…
Categories: CategoryID, CategoryName …
If a sub-category needs to be added how would I add it and what fields would it contain and the relationship between the Products and Category Table be?
Also any useful tips on this would be appreciated.
TIA
View 3 Replies
View Related
Apr 23, 2008
Hello,
I am working on a web site that makes documents available to students.
I am using tags to classify each document content.
So I have the following tables:
Documents, Tags, DocumentsTags
I also need to categorize the documents:
Subject
|---- Level
|----- Year
For example:
Document_1 - Math > University Level > Second Year
How can I create a table to create such classification?
Thank You,
Miguel
View 2 Replies
View Related
May 6, 2005
Hi,
Is there a correct way to add to the list of Categories shown in the SQL Agent Job properties dialog? I have many jobs on my servers that are [Uncategorized(Local)] as there isnt a Category title that is applicable.
Do i just insert into sysCategories?
thanks
View 2 Replies
View Related
Sep 13, 2007
Hi,
I am trying to create a type of synchronization but I am having a lot of problems to make this work.
I have 3 tables:
CREATE TABLE Articles (ArticleId INT, ArticleText NVARCHAR(512))
CREATE TABLE Categories (CategoryId INT, CategoryText NVARCHAR(512))
CREATE TABLE CategoriesInCategories (ArticleId INT, CategoryId INT)
Consider the following tables content:
Articles Table
ArticleId ArticleText
1 Game ended 1-1
2 Book Review
Categories Table
CategoryId CategoryText
1 Sports
2 Entertainment
CategoriesInArticles Table
ArticleId CategoryId
1 1
2 2
I need to create the following procedures:
CreateArticle
Create Procedure [CreateArticle]
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End
This procedure should the the following:
1. Create the article in table Articles
2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.
For each category check if there is already one with the same CategoryText in table Categories.
If there is just get the CategoryId and add a new record into CategoriesInArticles with ArticleId and found CategoryId.
If there isn't any then create the new Category into table Categories and then add the new record to CategoriesInArticles.
DeleteArticle
Create Procedure [DeleteArticle]
@ArticleId INT
As
Begin
...
End
This procedure should the the following:
1. Delete the article in tables Articles and CategoriesInArticles given its ArticleId
2 .Delete categories from table Categories that are no longer associated with any other article (in table CategoriesInArticles)
UpdateArticle
Create Procedure [UpdateArticle]
@ArticleId INT,
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End
This procedure should the the following:
1. Update the values into Articles Table
2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.
I think the easiest way is to delete all the categories from ArticleId as it is done in DeleteArticle.
Then It creates the new categories as it is done in CreateArticle.
Consider I create a new article:
EXEC CreateArticle @ArticleText = "Book launched at sports event" @ArticleCategories = "Entertainment, Business"
The tables would become:
Articles Table
ArticleId ArticleText
1 Game ended 1-1
2 Book Review
3 Book launched at sports event
Categories Table
CategoryId CategoryText
1 Sports
2 Entertainment
3 Business
CategoriesInArticles Table
ArticleId CategoryId
1 1
2 2
3 2
3 3
NOTE: Because there is no category named Business in Categories this must be created.
Then I would updated it:
EXEC UpdateArticle @ArticleId = "3" @ArticleText = "Book launched at sports event" @ArticleCategories = "Sports, Tech"
The tables would become:
Articles Table
ArticleId ArticleText
1 Game ended 1-1
2 Book Review
3 Book launched at sports event
Categories Table
CategoryId CategoryText
1 Sports
2 Entertainment
4 Tech
CategoriesInArticles Table
ArticleId CategoryId
1 1
2 2
3 1
3 4
Note: Since Business category is no longer used by any other article then it is also deleted from Categories.
The new Tech category is then created and associated with the updated article as well as the existing category Sports.
Finally I would delete it:
EXEC DeleteArticle @ArticleId = "3"
The tables would become:
Articles Table
ArticleId ArticleText
1 Game ended 1-1
2 Book Review
Categories Table
CategoryId CategoryText
1 Sports
2 Entertainment
CategoriesInArticles Table
ArticleId CategoryId
1 1
2 2
Note: Since Tech category is no longer used by any other article then it is also deleted from Categories.
The Sports category is not deleted from Categories since it is used by Article with Id=1
Well, I hope I didn't miss anything and I explained it well.
I have been trying my code, either by creating a separate procedure named SynchronizeCategories or inside the Create, Delete and Update procedure.
Until now I was not able to make this work.
Here is the code I use in my SynchronizeCategories procedure:
INSERT INTO CategoriesInArticles(CategoryId, ArticleId)
SELECT c.CategoryId, @ArticleId
FROM Categories c INNER JOIN CSVTable(@ArticleCategories) ac
ON c.CategoryText = ac.String
LEFT JOIN CategoriesInArticles cia
ON c.CategoryId= cia.CategoryId
WHERE cia.CategoryId IS NULL
INSERT INTO Categories(CategoryId, CategoryText)
SELECT cia.CategoryId, NULL
FROM CategoriesInArticles cia
JOIN [Categories] c ON c.CategoryId = cia.CategoryId
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
WHERE c.CategoryId IS NULL
DELETE c
FROM Categories c
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
LEFT JOIN CategoriesInArticles cia ON c.CategoryId = cia.CategoryId
WHERE cia.CategoryId IS NULL
Could someone, please, help me out with this?
Thank You Very Much,
Miguel
View 4 Replies
View Related
Oct 24, 2007
I have two tables can I print the Description from Both of them as a single Column from a select statement
Something Like
Select Trade_Category.iCategory_Name as Names,
FreeCategory.sName as Names From Trade_Category,FreeCategory Where
FreeCategory.iParent = 0 OR Trade_Category.iParent_Category = 0
But instead of two columns
Names Names
A B
A B
A B
A
Just the One
Names
A
A
A
A
B
B
B
View 2 Replies
View Related
Apr 20, 2007
Hello folks,
I've been pounding on this for a while, after countless searching I have to break down and ask for help.
I have a table set up like so:
+-----+------------+---------------+------------+
| id | title | path | category |
+-----+------------+---------------+------------+
| 1 | Firemen | firemen.pdf | CGL |
| 2 | Garages | garages.pdf | E&O |
| 3 | Auto | autos.pdf | EPLI |
| 4 | Garages | garages.pdf | CGL |
| 5 | Houses | houses.pdf | E&O |
+-----+------------+---------------+------------+
If there's a title that has more than one category (Garages for example), I would like to be able to show both categories in the same row.
Like so:
Title: Firemen
Path: firemen.pdf
Cat: CGL
Title: Garages
Path: garages.pdf
Cat: E&O, CGL
Title: Auto
Path: autos.pdf
Cat: EPLI
I assume that it might be a better set up if the category was a separate table, but I was hoping there is a way around it. I've tried grouping and subqueries but can't seem to get anything to work.
If someone could steer me in the right direction, it would greatly be appreciated. Thanks much and happy Friday!
Erik
View 2 Replies
View Related
Jun 10, 2007
hi to all;
i want to make in my site the possibility for the user to make categories in the site
ex: he can add the category name"computer science" and then can add the category childs " data mining, software engineering,... "
so firstly i want to make a category table in my databasde to be able to save the category data
haw can i make this table? i mean what are the columns that should be in the table?what type of relationship should i make? and how to mahe it
sorry but i need any one to explain to me what should i do. and if anyone one know the solution please reply to me
thanks....
View 1 Replies
View Related
Sep 27, 2007
Hi,
I would like to know how to get the Top 10 Categories from Reporting Services chart without using the scripting in T-SQL to query from db.
Is there a function like Ranking and then we filter the Ranking to be less than or equal 10 ?
Please kindly direct me in step by step to the way on how to achieve this as I am quite new in Reporting Services
Thank you
best regards,
Tanipar
View 6 Replies
View Related
Nov 24, 2007
I am creating a website that has categories such as:Sports -> Soccer -> Soccer ShoesI can not think of an effectively way to implement this into a database. Please help.
View 4 Replies
View Related
Aug 18, 2004
Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it
When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.
If you need more clarification, post here and let me know.
Thanks in Advance,
Aaron Hawn (aaron@ionzion.net)
View 2 Replies
View Related
Jun 15, 2008
I want your expert opinion to find out the best design solution to this my current issue.
1 . I have a Member Table. These members are sub categorised and again and again. So I have three three category tables which are related and all three tables directly related to the Member table via Foreign keys.
IS THIS THE CORRECT DESIGN??
2. I Have a SKILLS table where Members can select their SKILLS from that. I record this in a separate table called "MEMBER SKILLS". There is a SKILL category called "OTHER", then the Member can write those skills which may be not listed in the SKILLS table. I have created a separate column in the "MEMBER SKILLS" table it self to record this free text.
IS THIS THE CORRECT DESIGN??
3. How do you resolve a design issue where there are many nested tables of categories and sub categories and the use can create these categories dynamically as well??
I hope all above make sense to you to give a logical answer. Thank you very much for you assistance.
View 2 Replies
View Related
Jan 19, 2014
I want to get a list of any Categories where ALL the products in that Category are not published (Published = 0). (I want to get the Categories where no products are listed for it). Here are the tables, not sure where to begin :
SELECT [Id], Published
FROM Product
WHERE Published = 0
SELECT [Id] ,[Name]
FROM Category
SELECT [Id] ,[ProductId] ,[CategoryId]
FROM Product_Category_Mapping
View 3 Replies
View Related
Dec 17, 2006
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
News
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)
View 1 Replies
View Related
Dec 17, 2006
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
News
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)
View 4 Replies
View Related
Jun 16, 2006
I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables:
FORMS and UNITS
I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units.
I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it.
any help???
below is the stored procedure I was using:
CREATE PROCEDURE dbo.USP_AddForm
@UNIT_IDint,
@F_TYPE varchar(20),
@Titlevarchar(100),
@Keywords ntext = NULL,
@Descriptionntext = NULL,
@FileNamevarchar(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @NewFOrmID INT
INSERT FORMS
(
UNIT_ID,
F_TYPE,
Title,
Keywords,
Description,
FileName,
RevDate
)
VALUES
(
@UNIT_ID,
@F_TYPE,
@Title,
@Keywords,
@Description,
@FileName, GetDate()
)
SELECT @newFormID = @@IDENTITY
SELECT newFormID = @newFormID
END
GO
View 1 Replies
View Related
Mar 9, 2004
I have about 10 required (never to change) categories in my tbAccountCategories table, but after that, the user can add as many
as he/she wishes. I use an identity field to identify each record. The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?
Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?
Mike B
View 4 Replies
View Related
Apr 18, 2014
I've been trying some examples on line and have not been able to get this to work.
Here is my table followed by what I am trying to output.
Id | ParentId | CategoryName
33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges
[Code] ....
I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories
48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges
[Code] ...
This is what I have currently, still a long way off :/
SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name
View 2 Replies
View Related
Jul 23, 2005
I have a TSQL script to add daily tables to replication and then runthe snapshot agent to distribute them to two subscribers. The scriptexecutes without errors, but when I check the running jobs for eachserver I see the following:JUST AN EXAMPLEJob1'Category' REPL-SnapshotJob2'Category' REPL-DistributionWhat is the difference between these two categories? Also 'Job1' worksproperly and receives the 3 new replicated tables, while 'Job2' seemsto be stuck on Step 2 and isn't receiving the 3 new replicated tables.Below is a copy of the stored procedure for reference.GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOCREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@@IOI_TABLEvarchar(64), @@TRADE_TABLE varchar(64), @@CHAT_TABLE varchar(64) ) ASDECLARE @SUBSCRIBER_NYPROD2 varchar(64)DECLARE @SUBSCRIBER_CTDEV2 varchar(64)DECLARE @SP_INSERT_PREFIX varchar(24)DECLARE @SP_DELETE_PREFIX varchar(24)DECLARE @SP_UPDATE_PREFIX varchar(24)DECLARE @INSERT_SP varchar(24)DECLARE @DELETE_SP varchar(24)DECLARE @UPDATE_SP varchar(24)SET @SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'SET @SUBSCRIBER_CTDEV2 = 'D02'SET @SP_INSERT_PREFIX = 'CALL sp_MSins_'SET @SP_DELETE_PREFIX = 'CALL sp_MSdel_'SET @SP_UPDATE_PREFIX = 'CALL sp_MSupd_'SET @INSERT_SP = @SP_INSERT_PREFIX + @@IOI_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@IOI_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@IOI_TABLEDECLARE @SCHEMA_OPTIONS intSET @SCHEMA_OPTIONS = 0x000000000000CEA3exec sp_addarticle @publication = N'Indii', @article = @@IOI_TABLE,@source_owner = N'dbo', @source_object = @@IOI_TABLE,@destination_table = @@IOI_TABLE, @type = N'logbased', @creation_script= null, @description = null, @pre_creation_cmd = N'drop',@schema_option = @SCHEMA_OPTIONS, @status = 16, @vertical_partition =N'false', @ins_cmd = @INSERT_SP, @del_cmd = @DELETE_SP, @upd_cmd =@UPDATE_SP, @filter = null, @sync_object = null, @auto_identity_range =N'false'exec sp_addsubscription @publication = N'Indii', @article =@@IOI_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@IOI_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'SET @INSERT_SP = @SP_INSERT_PREFIX + @@TRADE_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@TRADE_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@TRADE_TABLEexec sp_addarticle @publication = N'Indii', @article = @@TRADE_TABLE,@source_owner = N'dbo', @source_object = @@TRADE_TABLE,@destination_table = @@TRADE_TABLE, @type = N'logbased',@creation_script = null, @description = null, @pre_creation_cmd =N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,@auto_identity_range = N'false'exec sp_addsubscription @publication = N'Indii', @article =@@TRADE_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@TRADE_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'SET @INSERT_SP = @SP_INSERT_PREFIX + @@CHAT_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@CHAT_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@CHAT_TABLEexec sp_addarticle @publication = N'Indii', @article = @@CHAT_TABLE,@source_owner = N'dbo', @source_object = @@CHAT_TABLE,@destination_table = @@CHAT_TABLE, @type = N'logbased',@creation_script = null, @description = null, @pre_creation_cmd =N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,@auto_identity_range = N'false'exec sp_addsubscription @publication = N'Indii', @article =@@CHAT_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@CHAT_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'DECLARE @SNAPSHOT_JOB_NAME varchar(64)-- Run Snapshot for NY2 serverSET @SNAPSHOT_JOB_NAME = 'INNYWPP01PRODUCTION-Indii-Indii-1'EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME-- Run Snapshot for CT2 serveSET @SNAPSHOT_JOB_NAME = 'innywpp01production-Indii-Indii-D02-3'EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAMEPRINT 'added ' + @@IOI_TABLE + ' from replication'PRINT 'added ' + @@TRADE_TABLE + ' from replication'PRINT 'added ' + @@CHAT_TABLE + ' from replication'GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 1 Replies
View Related
Dec 22, 2007
I have a simple Stored Procedure with multiple select statements..doing
select linkid,linkdesc from links;select cateid,cate from categories... for example.
I created a dataset and then fill the tables see the code below ,CS DataSet ds = Obj.GetDataSet("query here); dlc.DataSource = ds.Tables[0]; DataTable llist = ds.Tables[1]; if (llist.Rows.Count > 7) { dr = ds.Tables[1].Rows[7]; dr["cateid"] = 0; dr["cate "] = "more..."; } else { System.Data.DataRow newDataRow = llist.NewRow(); newDataRow["cateid"] =0; newDataRow["cate "] = "more..."; llist.Rows.Add(newDataRow); } lls.DataSource = ds.Tables[1];DataBind(); .ASPX<asp:DataList ID="lls" RepeatColumns="4" runat="server" RepeatDirection="Horizontal" CellPadding="4" CellSpacing="4" BorderWidth="0" Width="100%"> <ItemTemplate> <asp:HyperLink Text='<%# Eval("cate") %>' CssClass='<%# "mcs" + Eval("cate").ToString()%>' NavigateUrl='<%# PickURL(Eval("cateid").ToString(),Eval("cate").ToString()) %>' runat="server" /> </ItemTemplate> <FooterStyle HorizontalAlign="right" /> </asp:DataList> but ITS WORKING FINE SOME TIMES ONLY , FEW TIMES ERROR OCCURRED THE ERROR IS Column 'cate' does not belong to table
Table1. I DON'T KNOW WHY THIS ERROR IS COME ANY BODY HAVING SOLUTION TO RESOLVE THIS PROBLEM DETAILED ERROR MESSAGE: Server Error in '/' Application.Column 'lname' does not belong to table
Table1.Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more information
about the error and where it originated in the code.Exception Details:
System.ArgumentException: Column 'lname' does not belong to table
Table1.Source Error:An unhandled exception was generated during
the execution of the current web request. Information regarding the origin and
location of the exception can be identified using the exception stack trace
below.Stack Trace:[ArgumentException: Column 'lname' does not
belong to table Table1.]System.Data.DataRow.GetDataColumn(String columnName)
+1887619System.Data.DataRow.set_Item(String columnName, Object value)
+14state_common_index.FillIndexPage()
+590state_common_index.Page_Load(Object sender, EventArgs e)
+1203System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o,
Object t, EventArgs e)
+15System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,
EventArgs e) +34System.Web.UI.Control.OnLoad(EventArgs e)
+99System.Web.UI.Control.LoadRecursive()
+47System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
View 1 Replies
View Related
May 12, 2006
I'm using MS SQL 2000. I developing a shopping cart where on the admin side when we are inserting or updating the products, there is a chance that a single product can fall into two categories therefore i'm giving the users a option of CheckBoxList control for the categories. The checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this??
Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear.
Thanks for your help and time in advance.
View 1 Replies
View Related
