|
First, I think you meant s2 is the boss of s1.
As already mentioned, recursion (which is what you want) is supported in SQL Server 2005, and also in other products (Oracle, and DB2).
After much head-scratching, I finally just buried the Chain-Of-Command within a delimited text field for each employee (actually I was doing it for Bill-Of-Materials, but same principle); I called it the lineage.
CREATE TABLE employees(
ID INTEGER
, name VARCHAR(50)
, lineage VARCHAR(60)
);
When adding someone to the table, I need to know who they work for, then do:
INSERT INTO employees(name) VALUES (@mployee_name) ; then
UPDATE employees
SET lineage = (SELECT lineage
FROM employees
WHERE name = @manager_name) || ',' || ID
WHERE lineage IS NULL;
To get the full employee tree, I just selected with a LIKE:
SELECT * FROM employees WHERE lineage LIKE
(SELECT ID FROM employees WHERE name=@manager_name) || ',%' ;
(or something like that)
I ultimately decided that with the primary key on lineage, this would be substantially faster than using recursion since recursion would require grabbing data from all over the table, while in this method, the data is actually stored (via clustered index) under the manager.
Just FWIW.
David
|
|
|
|
|
I build an application that relies on SQL Notification to update a member in clients memory. I used a second select statement to read the members table after the original one because the original was using a view and multiple functions.
Works great.
But I was wondering for performance issues, is there a way to register for notification without returning a result set?
Right now I have the original statement :
SELECT [ID],[TemporaryID]...,ISNULL([dbo].GetProvinceName([ProvinceID], [CountryISO3], @Language), [Province]) AS Province...,[dbo].GetUserFullName([LastUpdateUser]) AS LastUpdateUser...
FROM [dbo].[vw_MembersFinder]
WHERE [LastUpdateDateTime] > @SyncDateTime AND
[Status] = @Status
So I read right after using :
SELECT [ID],[FirstName],[LastName],[Address1],[Address2],[City],[ProvinceID],[PostalCode],[CountryISO3],[ManInvalidAddress]
FROM [dbo].[Members]
WHERE [Status] = 'A'
This way I register correctly for notifications but of course I have to wait for two results sets.
Any good ideas?
|
|
|
|
|
Is it possible to delete a set of records by using WHERE clauses that access tables in two separate databases? I've looked all over the net and I cannot find a straight answer.
Thanks in advance
Brett A. Whittington
Application Developer
|
|
|
|
|
Here's a quick sample:
DELETE Item
FROM table1 WHERE MyValue IN (
SELECT MyValue2 FROM Database2.dbo.table2 WHERE MyCondition = 'Hello'
) The trick is to prepend the name of the database to the schema (the Database2.dbo bit in this sample).
|
|
|
|
|
Thanks for the quick response. My problem was how I was trying to use the 2nd database in the where clause. I was not trying it in an IN statement as your example shows. Awesome! 
Brett A. Whittington
Application Developer
|
|
|
|
|
Same principal applies. Pretend it's exactly like a table in the same database but put a [Database].[Schema] in front of it like "database2.dbo.table"
|
|
|
|
|
Hello sir,
Please guide me on how to store data and access
that stored data in ASP.NET using SQLSERVER.
Please suggest me over this matter....its urgent
sandeephs
|
|
|
|
|
Sandep H S wrote: its urgent
Theres not much point in putting that. Most people here have plenty of other stuff to be getting on with thats more urgent than you and they'll get around to answering when they can. Quite often it'll even make people delay answering as it's considered rude.
Sandep H S wrote: Please guide me on how to store data and access
that stored data in ASP.NET using SQLSERVER.
Thats a very broad subject with lots of articles and resources available. Theres a whole section here on CP about it:
http://www.codeproject.com/cs/database/[^]
Have a go with that and if you have any more problems that are specific (i.e. whats the best practise for disposing SqlCommand objects?) rather than broad questions (i.e. how do i learn to program) then we'll be happy to help.
|
|
|
|
|
Instead of updating in usual way, i use a simple binding for my data entry. It works but my problem is the last record that i recorded did not update. Only the previous records are updated, but the last did not.
my code for 'update' button is:
Me.BindingContext(DataSet21).EndCurrentEdit()<br />
Me.OleDbDataAdapter2.Update(DataSet21)<br />
MsgBox("Grade recorded!")
|
|
|
|
|
Rharzkie wrote: DataSet21
Rharzkie wrote: OleDbDataAdapter2
Wow! Have you ever thought of actually giving your variables descriptive names? What does DataSet21 mean to anyone anyway?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
It's the result of dropping data access components onto the form designer, which I would go as far as to call worst practice.
|
|
|
|
|
You can still edit the properties and give it a decent name without ever having to go near the code. Whilst I agree that the drag n drop data components are evil, thier use is no excuse for bad naming 
|
|
|
|
|
Just answer my question ok..
To run your program properly and to write the correct codes are more important that giving a descriptive name. I can run my program properly even though i did not change the name of my variables...
|
|
|
|
|
Rharzkie wrote: To run your program properly and to write the correct codes are more important that giving a descriptive name
I beg to differ - If you wrote this mess in the company I work for no-one would be happy with you. If you wrote this, then disappeared off (or got yourself fired - which, if you are writing code in this way, would be quite likely) and someone else had to maintain it, they would definitely be very unhappy with you.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
But giving things descriptive names is part of good programming practice. It is never acceptable to leave something in place that cannot be easily identified at a later stage. The important thing to remember here is that it only takes you a couple of seconds to rename them before there is any code present, but as soon as you have code that refers to it, you have to take much more care and this takes time.
|
|
|
|
|
hello everyone,
I have a small doubt regarding SQLReporting services.I want to generate a reports using reportmanager and sqlreporting services.Can anybody suggest how to generate a report ..PLz tell fast it is very urgent for me.
ok byeee
Murali
Murali
|
|
|
|
|
SQL SERVICES[^]
ballameharmurali wrote: PLz tell fast it is very urgent for me.
This is not good practice.
We are all here to help depneds upon our free time.
Regards,
Satips. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus
|
|
|
|
|
If you have visual studio installed, then you can generate reports through there.
|
|
|
|
|
I can't update my a record because when i run my program, my program stops when i click the update button. A dialog box appear and it says:
An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll
Additional information: There is no row at position 10.
My codes for the update button is:
<font color=blue>DataSet21.studentsubjects.Rows(ctr).BeginEdit()</font><br />
dr("grade") = txtgrade.Text<br />
DataSet21.studentsubjects.Rows(ctr).EndEdit()<br />
OleDbDataAdapter2.Update(DataSet21, "studentsubjects")<br />
MsgBox("The grade is recorded")
Is there anything wrong with my codes? The error is maybe at first line? How can i correct this?
|
|
|
|
|
Check whether the
DataSet21.studentsubjects.Rows(ctr) is not null...
before you do the edit!!!!
I was born dumb!!
 Programming made me laugh !!!
--sid--
|
|
|
|
|
Ah ok., it means that all my records have already data and not null, thanks...
|
|
|
|
|
Simply, you dont have record at position 10
I Love SQL
|
|
|
|
|
ok, thanks. i already put a data in each record so i can already update it but the problem is, the data that i enter goes in other row instead of the correct row.
Example:
my table goes like this: i have 3 fields-
1. ID(primary key)
2. Studname
3. grade
i have 4 records:
The 4 studname are- 'rissa', 'kristine', 'cherry', 'glenda'
i have to enter a grade for each. The data written in their grade is "no yet grade" so that it can't be null.
i search ID '1' which is for 'rissa', then i enter a grade for 'rissa which is '95', but the grade goes in the record of 'kristine'
what is the problem in that? how can i correct?
|
|
|
|
|
1. Drag one label in your form and name it lblID
2.In your GridView1 selectedIndexChange event (I am giving this example for asp.net) set text of selectedrow and specify cell's text then set text to lblID.
(In this case select row which contain name Risa)
lblID.Text = GridView1.SelectedRow.Cells[1].Text;//Instead index 1 in Cells property can differ from my example
3. In Update button set update command :
" Update YourTableName set Grade='95' where [ID]='"+lblID.Text+"'";
4.Then bind GridView1.
GridView1.DataBind();
I Love SQL
|
|
|
|
|
I'm developing a record keeping s/w for retirement plans.There are two user catagory;Plan Sponsor & Participant;each having unique id Ps_id&Emp_id.Only if he has a Ps_id or a Emp_id he can register into th site.
When he enters his catagory id, Register_btn has to check for this id in Plan Sponsor details and Employee details.If any of the table contains the id,then only he can go to th registration page.
Hw can I perform th checking for th catagory id in two different tables;Ps_details & Emp_details?
Delin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
