|
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[^]
|
|
|
|
|
Thank you
Yes te article describe the problem.
For now what I was doing is to clear the dataset and refill it
I will try to find if I can get the returned record from the adapter
I'm using MySql connector
|
|
|
|
|
Hi,
I have two table like this:
quote_tb:
ID item vendor quote
11 a v1 100
11 a v2 200
11 b v1 400
11 b v2 300
11 c v1 555
11 c v2 777
22 a v1 122
22 a v2 222
22 b v1 322
22 b v2 422
22 c v1 555
22 c v2 777
vendor_tb
name phone# fax# address#
v1 1111 2222 3, x street
v2 1212 2323 4, m street
I am trying to write an sql that given me the following result:
lowestQ_tb
ID item vendor quote phone# fax#
11 a v1 100 1111 2222
11 b v2 300 1212 2323
11 c v1 555 1111 2222
i.e for each of the items a,b,c having the id(11) select the vendors that have the lease quote and then get the phone and fax numbers of the vendors form the table vendor_tb.
To get the first part i.e. lowest quote for each item, this is the sql I use:
SELECT f.item, f.vendor, f.quote
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
which gives me the result:
ID item vendor quote
11 a v1 100
11 b v2 300
11 c v1 555
I don't know how to take it further form here to get lowestQ_tb. Please can you tell me how to get the above desired result i.e tabele lowestQ_tb. Is it possible to do such a thing?
Thanks,
Tara
|
|
|
|
|
How about:
select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
inner join vendor_tb AS V
on V.name = Q.vendor
order by Q.ID, Q.item Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:
...
from (
select Q.ID, Q.item, min(vendor) AS MinVendor
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb AS Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
on Q.ID = MinS.ID
and Q.item = MinS.item
and Q.vendor = MinS.MinVendor
...It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions.
Regards
Andy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
