How To Create Categories Tree With Subcategories ?

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


ADVERTISEMENT

How To Create Categories Tree With Subcategories ?

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

How Do I Do Categories And Subcategories And More Subcategories?

May 1, 2006

Hi, I have a database that will have various categories:

take this:

Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas

(it's shoes)

I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories.... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.

View 7 Replies View Related

Ascending Categories And Ascended Subcategories

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

CREATE INDEX ..using B-tree Algorithm

Apr 30, 2007

Is it possible to specify an algorithm like B-TREE in create index syntax of MSSQL Server.

In MySQL i have the option to specify this in create table syntax .

Its very urgent.

View 1 Replies View Related

How To Get All Sub Categories For All Parent Categories

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

Categories And Sub Categories Best Practice

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

SQL 2012 :: Sort Tree Members In Right (tree) Structure?

Apr 6, 2015

I got assignment, how to make it appear in the right order .

/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/

[code]....

View 2 Replies View Related

Counting Items In A Hierarchical Category And Subcategories

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

Categories...

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

How To Add Sub-categories???

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

Categories And Sub Categories

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

SQL Agent Job Categories

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

Synchronize Categories

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

Search Categories

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

Multiple Categories In Same Row

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

Categories Table

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

How To Get The Top 10 Categories In Chart

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

Database Design Of Categories

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

Assigning To Multipule Categories

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

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 View Related

Design Issue (Sub Categories And Others)

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

Need To Select No Product Categories

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

Adding Record To Multiple Categories

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

Finding Missing Items That Are Not In Categories

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

Application / User Defined Categories?

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

Replication Question About Job Names And Categories

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

Column 'coloumname' Does Not Belong To Table Categories .

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

How To Retrieve Data Of A Product Which Falls Under Two Categories??

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

Join Category Table So That Categories Are In The Result On Only

Feb 22, 2007

SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name

FROM Image, JobTag, Tag, Job, Tagcat

WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID

ORDER BY Image.ImageID, Tag.SortOrder


I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?

View 1 Replies View Related

UPDATE A Variable To Different Categories By A Single Step?

Jul 23, 2005

Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end

View 6 Replies View Related

Brainstorming: Ideas For Categories Database Structure Please

Jul 20, 2005

HiThanks in advance for any ideas.What I'm trying to do, is have a category system like Yahoo or thesenewsgroups where you have a parent "COMP" and multiple children "LANG"or "DATABASES". However, these can also have children. So for"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etcMy question is, what is the best way to store these in a database andalso allow me to retrieve the dataI'm not sure how to procedeThanksSam

View 4 Replies View Related

Power Pivot :: Show All Categories Even If There Is No Data

Jul 22, 2015

I have a powerpivot table which groups customers into ranges of sales figures. When I use a slicer to slice by region, I lose some of the ranges because they contain no data. I need them to show up even if there is no data. I've checked all of the boxes to "Show items with no data on columns (and Rows). But this has no effect. I created a measure using the following formula which also has no effect.

NeverBlankAcct:=IF(ISBLANK(Count([CACCT])),0,Count([CACCT]))

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved