Database Design Of Categories
Nov 24, 2007I am creating a website that has categories such as:
Sports -> Soccer -> Soccer Shoes
I can not think of an effectively way to implement this into a database. Please help.
I am creating a website that has categories such as:
Sports -> Soccer -> Soccer Shoes
I can not think of an effectively way to implement this into a database. Please help.
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.
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 RelatedI 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?
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
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedHello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies View RelatedI have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View RelatedHi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies View RelatedHi,
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.
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
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
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
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
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
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
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....
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
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
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)
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
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
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 (??)
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 (??)
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
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.
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
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
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 RelatedI 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