|
Hi,
I have a table called tblUserDetail which contains the following attributes:
- ID
- ManagerID (which will refer to ID)
- etc...
Each user can have only 1 manager, but each user can have 0 or more staff.
Now, I have the following structure
A G
---|--- ---|---
| | | | |
B C D H I
|
E
|
F
Therefore, if I choose person "A" (for example), it will allow me to add person "G" as person "A" staff.
If I choose person "F" it will also allow me to add person "G" as person "F" staff.
Now, I can handle 3 level of hierarchy, but I don't know how to do recursive in SQL statement.
Here is my SQL Statement:
eg.
thisUserID = [current selected user]
thisUserManagerID = [the selected manager id]
strSQL = "SELECT UserID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE " +
"UserID<>" + thisUserID.ToString() + " AND ManagerID<>" + thisUserID.ToString() + " AND " +
"UserID<>" + thisUserManagerID.ToString() + " AND ManagerID=-1 AND " +
"(NOT EXISTS(" +
"SELECT * FROM tblUserDetail u2 WHERE u2.UserID=" + thisUserManagerID.ToString() + " AND " +
"NOT EXISTS(" +
"SELECT * FROM tblUserDetail u3 WHERE u1.UserID<>u2.ManagerID" +
")" +
")" +
")";
Currently, if I choose person "F" it will allow me to add "A" and "G" as person "F" staff because it doesn't handle more than 3 level of hierarchy.
Now, this works perfectly for 3 level of hierarchy, but I need more than 3 level. Is there a way to do recursive statement within SQL statement?
Cheers 
|
|
|
|
|
SQL Server 2005 provides common table expressions (CTE) for recursion. You can view more here[^]
Regards,
Mehroz
|
|
|
|
|
Hi Syed,
I am having a trouble to bind between fields. Here is a simple example
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.UserID<>u1.ManagerID
)
SELECT * FROM NewUserDetail
And I got this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u1.ManagerID" could not be bound.
I need to bind value to recursively go through all the users so the user will not be able to select its manager or manager of its manager.
Any idea on how to tackle this problem?
Cheers
|
|
|
|
|
Seems you didnt get the cte syntax right. The second query inside the cte syntax generally joins with the cte itself, not the first query.
Lets have a cte that lists your user and all its subordinates
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
|
The problem resides in your recursive call. The line is "SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID"
Here you have to add a inner join to the NewUserDetail table.
I am also giving you an example. Hopefully it will solve your problem. The example is given below:
WITH TemplateTopic(parent_id, child_id, child_name, child_type, Level)AS
(
-- Anchor member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type, 0 AS Level
FROM tbltemplate_topics a Where Child_Id = 16
UNION ALL
---- Recursive member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type,
Level + 1 AS Level
FROM tbltemplate_topics a
INNER JOIN TemplateTopic AS d
ON a.parent_id = d.child_id
)
-- Statement that executes the CTE
SELECT * FROM TemplateTopic
(Make changes in your code accordingly)
Do good and have good.
|
|
|
|
|
Oh...thanks for pointing. I missed the inner join part, dont know why..
Here is the modified cte
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail Manager WHERE u2.ManagerID=Manager.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
|
Hi Syed,
I tried your example and it gave me this error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.
Any idea?
Thanks again
|
|
|
|
|
Hi Syed,
Nevermind, I figured out the syntax with the help of the example given by Silent Eagle (thanks Silent Eagle). It should read:
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT u2.UserID, u2.ManagerID, u2.FirstName + ' ' + u2.LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail AS Manager ON u2.UserID<>Manager.ManagerID
)
But it again gave me another error on execution.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
That is wierd because I did re-check and make sure there is no recursive employee-manager relation.
Any idea?
Thanks again
|
|
|
|
|
I see what is the problem.
I shouldn't put
u2.UserID<>Manager.ManagerID
instead I should put
u2.UserID=Manager.ManagerID
Thanks Syed and Silent Eagle for your help.
Cheers
|
|
|
|
|
IIf(IsEmpty(DLookUp("[Doctor Name (MD)]","tblQueryData","[PKey]='1'")),([tblMasterReferrals].[Doctor Name (MD)]) Like "*",DLookUp("[Doctor Name (MD)]","tblQueryData","[PKey]='1'")) Or Is Not Null
I have this strip of code in the Expression Builder for the criteria row of a column in an Access Database Query. My objective for this is to have this code filter the records by an entry in the first row of a table, in a column with a matching name. Also, if there is no entery for that column in the table then show all records, or no filter in that column. I have six six different columns in one query i would like to do this to. I have found that if i have a criteria entry in the the table for the query and i change that column's Expression Builder Code to say Or Is Null it works, it filters the query on that table entry. But if i take that entry out of the table, it will return nothing unless I change the Expression Builder Code to say Or Is Not Null, I want the query to return unfiltered records for that column if there is no entry in the table. Can anyone tell me how i can mess these two to make a fuctional query??
Lost!
|
|
|
|
|
hi,
i have created a database where information on the web page is pulled out from the database using asp.net and have an sql server database. however i need to add javascript functionality to it. the javascript functionality i want to add is a timed text one. does anyone have any suggestions as to how i can achieve this and what is the best way to do it, eg, use dataview, gridview and how i can link the javascript to it. if anyone thinks it would be easier for me to post up some of my code then i will upon request. i hope i have posted this is the right place, if not i will move it to the asp.net bit.
thanks for any help given, it is much needed
|
|
|
|
|
This has nothing to do with databases except for a brief mention of a database being involved. It seems that what you are asking is better suited to the ASP.NET forum.
|
|
|
|
|
Is there a database question in there? Like Colin said, you might be better off in the ASP.NET or Web Development forums.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
ok thanks, i wasnt sure where to post it, so iv posted on the asp.net bit now
|
|
|
|
|
Hello
After many tries it seems to me that there is NO safe way to programaticaly make change to a datagridview binded to a datatable in order to get those changes by the getchange method before updating database.
If data are entered manualy by the user : no problem
But if they are changed programaticaly, it seems that the changes are reflected at random :sometime yes, sometime no
The ONLY safe way I found is to use the DataBoundItem of the row in the DGV to acceed the row in the datatable and directly make changes in the datatable
Does anyone had such experience ?
|
|
|
|
|
hi all,
i developed an application in vb.net 2005 with Ms-sql 2000.
when i am working on my pc it's works OK and fine.
problem arrise when i install this application on a my client's pc. my client has installed a free version of ms- sql Express 2005.
followning is the Connection string i have used on my pc.
'
Conn = New SqlConnection("integrated security=SSPI;data source=.;persist security info=False;initial catalog=databasename")
it works ok on my pc.
the following connection i have used on my client's pc
Conn = New SqlConnection("data source=.;initial catalog=databasename ;user id =abc;password=abc")
whet i try to run this application on my client's pc it gives following error
System.NullReferenceException: Object reference not set to an instance of an object.
at FinAcct.FrmMainMdi.FrmMainMdi_Load(Object sender, EventArgs e)
rmshah
Developer
|
|
|
|
|
r_mohd wrote: System.NullReferenceException: Object reference not set to an instance of an object.
at FinAcct.FrmMainMdi.FrmMainMdi_Load(Object sender, EventArgs e)
This doesn't say anything about the connection object. What makes you think the conneciton object is the problem?
|
|
|
|
|
Hi
We are using SQL Server 2005. In a table we want to add a Unique constraint to ensure that only one record can be added for a student per day. The two columns for the constraint will be StudentIndex and IssueDate. We cannot use the whole IssueDate value because of the hour, min, seconds, milliseconds parts. Is it possible to use DateParts as part of a Unique constraint?
The following does not work:
CONSTRAINT UQ_Issues
UNIQUE ( DATEPART(dd, IssueDate), DATEPART(mm, IssueDate), DATEPART(yy, IssueDate), StudentIndex )
Any ideas will be appreciated.
Thanks
Kobus
|
|
|
|
|
Hi Kobus,
If you are not able to add a unique constraint to datepart, you can use a trigger to enforce this rule.
Regards,
Mehroz
|
|
|
|
|
Hi,
I'm using a custom membership provider and I am a bit confused.
I am currently work on the ValidateUser method which was overridden. The first stored procedure that I call is aspnet_Membership_GetPasswordWithFormat to get the password. The parameters that I need to supply is:
@ApplicationName
@UserName
@UpdateLastLoginActivityDate
@CurrentTimeUtc
I'm not sure when to make @UpdateLastLoginActivityDate true or false. Please can someone explain.
If the user tries to login, and the he/she supplied the incorrect password, then I call the aspnet_Membership_UpdateUserInfo to update the information, and again there are a couple of parameters that I do not understand what to set them to, namely:
@UpdateLastLoginActivityDate
@CurrentTimeUtc
@LastLoginDate
@LastActivityDate
I hope to hear from someone soon.
Thanks
Brendan
|
|
|
|
|
|
How do i run SQL server stored procedure in visual basic 6 to INSERT records to a table from the form (frmsuppliers)?
the properties are bundled into an array for insertion in the stored procedure as: -
Public Sub SaveSupplier(ByVal vntSupplierInfo As Variant)
'Declarations
Dim strSQL As String
Dim command As New ADODB.command
If vntSupplierInfo(0, 0) = 1 Then '
Dim a(0 To 7) As Variant
a(1) = vntSupplierInfo(2, 0)
a(2) = vntSupplierInfo(3, 0)
a(3) = vntSupplierInfo(4, 0)
a(4) = vntSupplierInfo(5, 0)
a(5) = vntSupplierInfo(6, 0)
a(6) = vntSupplierInfo(7, 0)
a(7) = vntSupplierInfo(0, 0)
'Create a connection in the database
command.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Asset;Data Source=AMKILA"
command.CommandText = "sp_SaveSupplier" 'find the insert stored procedure for that language
command.Execute , a() 'Execute the the array of a's into the stored procedure and run the SP
The error i get is:
Run-time error "-2147217904(80040e10)';
Procedure 'sp_savesupplier' expects parameter '@supplierID, which was not supplied.
Here is the procedure which i want to run in order to INSERT RECORDS in table called suppliers in the database called Asset.
CREATE PROCEDURE SP_SaveSupplier
@SupplierID smallint,
@SupplierCode varchar(10),
@CompanyName Varchar(100),
@Address Varchar(50),
@Telephone1 Varchar(20),
@Faxnumber Varchar(20),
@ContactPerson Varchar(30),
@Email Varchar(50)
AS
BEGIN
INSERT INTO Suppliers(SupplierID, Supplier_Code, Company_Name, Address, Telephone1, Faxnumber, Contact_Person, Email_Address)values(@supplierID, @SupplierCode, @CompanyName, @Address, @Telephone1, @Faxnumber, @ContactPerson, @Email )
END
GO
Kindly assist as soon as possible
Amkila 
|
|
|
|
|
I haven't used VB6 since I started working with C# 6 years ago, so I don't have it installed and can't give the exact syntax. But you need to define the command parameters and pass the values of your array to the appropriate paramters. Something like this:
'define paramsters
command.Parameters.Add("@SupplierId", ....... )
command.Parameters.Add("@SupplierCode", ....... )
command.Parameters.Add("@CompanyName", ....... )
command.Parameters.Add("@Address", ....... )
' ... add other parameters ...
'set parameter values
command.Parameters("@SupplierId").Value = a(1)
command.Parameters("@SupplierCode").Value = a(2)
command.Parameters("@CompanyName").Value = a(3)
command.Parameters("@Address").Value = a(4)
' ... set other parameters ...
command.Execute()
|
|
|
|
|
Hello : I've seen defferent post about tthis issue but no real fix
The problem:
If you add a new row with an autoincrement key in the database, using sqlcommand builder, it seems that the newly created key is not updated in the DS
So if just after that add you try to delete the row you get the message :
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."
One yay I've found to fix that is
1- Tho the update
1- clear the dataset ds.clear()
2- refill the ds using adapter adapter.fill(ds)
But it sounds a little bit odd
Any other Idea ?
|
|
|
|
|
I've never used the command builder because I'd rather control the SQL than have it written for me. But I assume the command builder isn't including the correct statement to retrieve the autoincrement value. Here's an msdn article on how to update the autoincrement value when you call DataAdapter.Update.
http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
